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.
Before running the examples, ensure you have:
- PostgreSQL running locally (port 5432)
- Bun runtime installed (bun.sh)
- A database named
example_dbwith default credentials (postgres/postgres)
- Clone the repository:
git clone https://github.com/NpgsqlRest/npgsqlrest-docs.git
cd npgsqlrest-docs/examples- Install dependencies (downloads the NpgsqlRest binary and sets up required tools):
bun install- Navigate to any example directory and run it:
cd 1_my_first_function
# Apply database migrations
bun run db:up
# Start the server (also rebuilds TypeScript and HTTP files)
bun run dev- Visit
http://127.0.0.1:8080to see the result.
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 |
| 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 |
| 16_scrap_demo | Web scraping in SQL: fetch a product listing with an HTTP Custom Type, parse the HTML with PostgreSQL XPath, and return the best-value laptop by a weighted score | Web Scraping with HTTP Types |
| 17_scrap_demo_2 | Web scraping in SQL: fetch a book catalog with an HTTP Custom Type, parse it with XML functions, and return the average book price on the page | Web Scraping with HTTP Types |
| 18_scrap_proxy_demo | Combine an HTTP Custom Type with a reverse proxy: fetch the page server-side, then @proxy the scraped HTML to an upstream service in the request body via @body_parameter_name. OmitAutomaticParameters keeps the generated client a clean no-argument call |
Web Scraping with HTTP Types |
These examples use the 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 |
Expose your .sql files as Model Context Protocol tools that an AI agent can discover and call — one source, two interfaces (REST + MCP).
| Example | Description | Related Blog Post |
|---|---|---|
| 15_mcp_server | An "Acme Store" MCP server: each .sql file is both a typed REST endpoint and an @mcp tool. Includes a dual-panel web page (REST storefront + live MCP browser), a real Claude agent driving the store, MCP-only tools, and per-tool authorization |
PostgreSQL as MCP Tools |
Test endpoints with plain .sql files using the built-in SQL test runner (npgsqlrest --test) — in-process endpoint invocation, transactional isolation, test databases, and endpoint coverage. Run with bun run test (or bun run test-watch) inside each example.
| Example | Description |
|---|---|
| 19_testing_basic | The basics: co-located layout (app.sql next to app.test.sql), boolean-SELECT and DO-block assertions, HTTP blocks with the _response table, multi-step scenario files |
| 20_testing_newdb | A fresh test database per run: named Setup/Teardown steps (create database on an admin connection + migrations), {rnd5} unique names, one test per file, authorization + user parameters, a tag taxonomy (smoke/auth/fixtures/login), deferrable-constraint fixtures — and the login.sql endpoint uses named parameters (:email, :password) |
| 21_testing_isolation | Perfect per-test isolation via a template database: migrations run once into a template, the shared run database and two per-test clones are created from it, deterministic sequence ids proven in parallel clones, a shared annotation profile attached with \ir carrying @setup/@teardown/@connection/@tag |
Typed clients beyond plain TypeScript fetch modules — a Dart client for Flutter and TanStack Query (React Query) hooks generated alongside the TypeScript client.
| Example | Description | Docs |
|---|---|---|
| 22_dart_client | Generate a typed Dart client for Flutter with the NpgsqlRest.DartClient plugin: request/response model classes with fromJson/toJson, ApiResult<T>/ApiError status wrappers, and plain package:http calls — three endpoint shapes (query-string GET with a database default, URL path parameter + @single, JSON-body POST) plus MockClient testability |
Dart Code Generation |
| 23_react_query_hooks | Generate TanStack Query v5 hooks alongside the TypeScript client (ClientCodeGen.ReactQuery): useQuery/useMutation hooks with exported query-key factories and QueryKeyPrefix, types derived from the client functions, a @tsclient_hooks = off opt-out, and explicit cache invalidation through the key factories in a consumer component |
React Query Hooks |
Each example provides these scripts:
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