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
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 v3.18.2	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

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

MCP Server (SqlFileSource) v3.17.0

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

SQL Test Runner v3.19.0

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 the new 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

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