Examples

This section provides hands-on examples demonstrating NpgsqlRest features. Each example builds on the previous one, progressively introducing more advanced concepts.

New to NpgsqlRest? Start with the SQL File examples — they're the recommended way to build endpoints and don't require any PostgreSQL function definitions.

All examples are available in the examples repository on GitHub.

Prerequisites

Before running the examples, ensure you have:

• PostgreSQL running locally (port 5432)
• Bun runtime installed (bun.sh)
• A database named example_db with default credentials (postgres/postgres)

Getting Started

1. Clone the repository:

bash

git clone https://github.com/NpgsqlRest/npgsqlrest-docs.git
cd npgsqlrest-docs/examples

2. Install dependencies (downloads the NpgsqlRest binary and sets up required tools):

bash

bun install

3. Navigate to any example directory and run it:

bash

cd 1_my_first_function

# Apply database migrations
bun run db:up

# Start the server (also rebuilds TypeScript and HTTP files)
bun run dev

4. Visit http://127.0.0.1:8080 to see the result.

Available Examples

Function-Based Examples (RoutineSource)

These examples use PostgreSQL functions and procedures as the endpoint source:

Example	Description	Related Blog Post
1_my_first_function	The basics: creating a PostgreSQL function and exposing it as an HTTP endpoint with automatic TypeScript client generation	End-to-End Type Checking
2_static_type_checking	How NpgsqlRest's autogenerated client code provides static type safety, catching breaking changes at build time	End-to-End Type Checking
3_security_and_auth	Database-level security with cookie-based authentication and the principle of least privilege	Database-Level Security
4_passwords_tokens_roles	Password verification, JWT/Bearer tokens, role-based access control (RBAC), and external OAuth providers	Multiple Auth Schemes & RBAC
5_csv_basic_auth	CSV exports with HTTP Basic Auth, Excel integration, and type composition for BI use cases	PostgreSQL BI Server
6_image_uploads	Secure image uploads with file system storage, PostgreSQL Large Objects, and progress tracking	Secure Image Uploads
7_csv_excel_uploads	CSV and Excel file ingestion with row-by-row processing and automatic TypeScript clients	CSV & Excel Ingestion
8_simple_chat_client	Real-time chat application using Server-Sent Events (SSE) and PostgreSQL RAISE statements	Real-Time Chat with SSE
9_http_calls	External API calls from PostgreSQL using HTTP custom types defined in type comments	External API Calls
10_proxy_ai_service	Reverse proxy with transform mode for caching AI responses and external service integration	Reverse Proxy & AI Service
11_crud_auto_api	Auto-generate REST CRUD endpoints from PostgreSQL tables with whitelist patterns	Zero to CRUD API
12_custom_types	Custom PostgreSQL composite types and multiset returns for complex nested JSON responses	Custom Types & Multiset
13_passkey	WebAuthn passkey authentication with pure SQL: passwordless login using device biometrics	Passkey SQL Auth
14_table_format	Excel export and stats endpoints with HTML table format output and cookie authentication	Excel Exports Done Right

SQL File Examples (SqlFileSource) v3.12.0

These examples use the new SQL File Source plugin — endpoints are generated directly from .sql files without needing PostgreSQL functions. Each is the SQL File equivalent of the function-based example above:

Example	Description	Function-Based Equivalent
1_my_first_function_sql_file	The basics: creating an endpoint from a .sql file with automatic TypeScript client generation	1_my_first_function
2_static_type_checking_sql_file	Static type safety with SQL File Source — catching breaking changes at build time	2_static_type_checking
3_security_and_auth_sql_file	Database-level security with cookie-based authentication using SQL files	3_security_and_auth
4_passwords_tokens_roles_sql_file	Password verification, JWT/Bearer tokens, and RBAC using SQL files	4_passwords_tokens_roles
5_csv_basic_auth_sql_file	CSV exports with HTTP Basic Auth using SQL files	5_csv_basic_auth
6_image_uploads_sql_file	Secure image uploads with file system storage and Large Objects using SQL files	6_image_uploads
7_csv_excel_uploads_sql_file	CSV and Excel file ingestion with row-by-row processing using SQL files	7_csv_excel_uploads
8_simple_chat_client_sql_file	Real-time chat application using SSE and SQL files	8_simple_chat_client
9_http_calls_sql_file	External API calls from PostgreSQL using SQL files	9_http_calls
10_proxy_ai_service_sql_file	Reverse proxy with AI response caching using SQL files	10_proxy_ai_service
12_custom_types_sql_file	Custom composite types and nested JSON responses using SQL files	12_custom_types
14_table_format_sql_file	Excel export and stats endpoints with HTML table format using SQL files	14_table_format

Available Commands

Each example provides these scripts:

Command	Description
bun run dev	Start NpgsqlRest server (rebuilds TypeScript and HTTP files)
bun run build	Compile TypeScript to JavaScript
bun run watch	Watch mode for TypeScript changes
bun run db:up	Apply database migrations
bun run db:list	List pending migrations

Next Steps

After completing these examples, explore:

• Configuration Reference - All available configuration options
• Annotations Reference - SQL comment annotations for endpoint customization
• Code Generation - Client code generation options