NpgsqlRest 3.21.0

NpgsqlRest

Your SQL is the API

Automatic REST API for PostgreSQL | 6.1x faster than PostgREST

SQL files and PostgreSQL objects become REST endpoints. TypeScript clients are generated automatically. Tests are SQL files too.

4,500+ req/s on a single host · 2,484 integration tests · 12K LOC SQL in production · MIT licensed

"Simplicity is the ultimate sophistication." — Leonardo da Vinci

SQL is declarative — your API should be too. With NpgsqlRest, you write SQL and annotate it with comments to declare what you want from your endpoint: caching, timeouts, retries, authorization, rate limiting, and everything in between. No controllers, no models, no mapping layers. Backend features become simple declarations on your SQL objects, putting PostgreSQL at the dead center of your architecture — the opposite of Clean Architecture, which treats the database as a detail. Here, PostgreSQL is the architecture.

Install

Requires PostgreSQL >= 13. Native executables have zero runtime dependencies. See the Installation Guide for all options and details.

From SQL to REST API

Write a SQL file:

-- sql/process_order.sql
-- HTTP POST
-- @authorize admin
-- @result validate
-- @single
select count(*) as found from orders where id = :order_id;
update orders set status = 'processing' where id = :order_id;
-- @result confirm
select id, status from orders where id = :order_id;

That gives you POST /api/process-order:

{"validate": 1, "result2": 1, "confirm": [{"id": 42, "status": "processing"}]}

And a generated TypeScript client with full type safety:

interface IProcessOrderResponse {
    validate: number;
    result2: number;
    confirm: { id: number, status: string }[];
}

export async function processOrder(
    request: IProcessOrderRequest
) : Promise<ApiResult<IProcessOrderResponse>> {
    const response = await fetch(baseUrl + "/api/process-order", {
        method: "POST",
        body: JSON.stringify(request)
    });
    return {
        status: response.status,
        response: response.ok ? await response.json() as IProcessOrderResponse : undefined!,
        error: !response.ok && response.headers.get("content-length") !== "0" ? await response.json() as ApiError : undefined
    };
}

No framework, no ORM, no boilerplate. The named placeholder :order_id — used three times, bound once — is the API parameter, and every result key is typed, down to the @single scalar and the column shapes. ApiResult<T> carries the status and a typed error, so failures are type-safe too.

Endpoint Sources

SQL files are the easiest way to get started — drop a .sql file in a folder and you have an endpoint. Functions give you the full power of PL/pgSQL with true end-to-end type checking. Use both together, or whichever fits.

All sources share the same annotation system: @authorize, @param, @returns, @void, @single, @cached, @path, and 50+ others.

Declarative Annotations

Declare what you want from your endpoint — caching, authorization, timeouts, retries, rate limiting, output format — right where the SQL lives:

/*
HTTP GET /users/
@authorize admin, user
@cached
@cache_expires_in 30sec
@timeout 5min
@table_format = excel
@excel_file_name = users.xlsx
*/
select id, name, email, role
from users
where $1 is null or department_id = $1;

Same pattern for PostgreSQL functions via comment on function ... is '...'. No middleware to register, no decorators, no controllers — the SQL is the source of truth. See all annotations.

Tests Are SQL Files Too

No test framework, no running server, no mocks. npgsqlrest --test invokes the real endpoint pipeline in-process, on the test's own transaction — insert fixtures, call the endpoint (it sees your uncommitted rows), assert with SQL, roll back:

-- tests/get_users.test.sql
begin;

insert into users (email) values ('fixture@example.com');

/*
GET /api/get-users
# @claim user_id=1
*/
select status = 200, 'authenticated caller gets 200' from _response;
select body::jsonb @> '[{"email": "fixture@example.com"}]', 'fixture is listed' from _response;

rollback;

npgsqlrest ./config.json --test

PASS  tests/get_users.test.sql  (2 assertions, 52ms)
19 passed, 0 failed, 0 error(s)  —  19 assertions in 9 files
endpoint coverage: 2/2 (100%)

Parallel isolated connections, throwaway test databases (create database app_test_{rnd5} as a plain setup step), per-test database clones, tags, JUnit XML, and endpoint coverage with a CI threshold gate — Testing Guide.

Watch Mode

npgsqlrest ./config.json --watch

Save a .sql file and the running API restarts with the change (~1s). create or replace a function in psql and the endpoint is live seconds later — the database itself is watched, annotations included. The TypeScript client regenerates every cycle, so your frontend types follow your SQL as you type. Add --test and it becomes a millisecond test loop instead — a changed test re-runs alone; a changed endpoint rebuilds in-process. Watch Mode.

Features

• Multi-command SQL scripts — multiple statements in one file execute as a batch, returning named result sets
• Named parameters in SQL files — where email = :email; the placeholder is the API parameter, repeated names bind once
• SQL test runner (--test) — tests as plain SQL files, in-process endpoint invocation inside the test's transaction, throwaway test databases, JUnit XML, endpoint-coverage gating
• Watch mode (--watch) — restart on SQL file, configuration, and database routine changes; test watch re-runs in milliseconds
• MCP server — @mcp exposes any endpoint as a Model Context Protocol tool for AI agents, same auth and rate limits
• AI function-calling schemas & llms.txt — project the MCP tool set into OpenAI and Anthropic tools array documents plus an llms.txt capability document, generated and served even without the /mcp endpoint
• TypeScript/JS code generation and .http files — types flow from PostgreSQL to your frontend, with optional TanStack Query (React Query) v5 hooks (useQuery/useMutation + query-key factories)
• Dart client generation — typed Flutter clients on package:http: request/response models with fromJson/toJson, multipart uploads with progress, SSE subscriptions, MockClient testability
• AOT-compiled native binaries — zero dependencies, instant startup
• 6.1x faster than PostgREST at 100 concurrent users
• 50+ comment annotations — @authorize, @param, @returns, @void, @single, @result, @skip, @cached, @proxy, and more
• Auth — cookie auth, Basic auth, JWT claims, role-based access, @authorize, @allow_anonymous
• Column-level encryption, security-sensitive endpoints, IP address binding
• Response caching with per-endpoint expiration control
• Rate limiting per endpoint
• SSE streaming via RAISE INFO/NOTICE with graceful shutdown
• HTTP QUERY method — the standardized safe method with a request body; parameters bind from the JSON body, clients treat it as a cacheable read
• File uploads — large objects, file system, MIME filtering
• Reverse proxy — forward to upstream services, transform proxy responses
• HTTP custom types — PostgreSQL composite types that call external APIs and return structured responses
• Composite type support — nested JSON, arrays of composites, @returns to skip Describe
• OpenAPI 3.0 / 3.1 spec generation (configurable SpecVersion)
• CSV/Excel/HTML table format response handlers

How does it compare?

NpgsqlRest vs PostgREST vs Supabase

From the Blog

• Case Study: 74 Endpoints, Zero Backend Code — A production app built entirely on NpgsqlRest: ~74 endpoints, 12K LOC of SQL, zero C# or Python.
• PostgreSQL REST API Benchmark 2026 — 14 frameworks, identical PostgreSQL functions: NpgsqlRest vs PostgREST, Django, FastAPI, Spring Boot, Go, Rust, and more.
• From SQL to Type-Safe TypeScript — End-to-end type safety: typed fetch modules generated directly from PostgreSQL functions and SQL files.

Documentation

npgsqlrest.github.io — getting started, configuration, annotations, examples.

Claude Code skill

A Claude Code skill that teaches the agent how to build with NpgsqlRest — both endpoint sources (database functions/procedures/tables/views and .sql files), the full annotation set, configuration, HTTP custom types, proxy, caching, auth, SSE, MCP, client code generation (TypeScript/React Query hooks, Dart), the SQL test runner (--test), and watch mode (--watch). It lives in .claude/skills/npgsqlrest/ and bundles a complete annotation reference and a full annotated appsettings.json.

Install it for your own NpgsqlRest projects — per-user (available everywhere):

mkdir -p "$HOME/.claude/skills/npgsqlrest"
for f in SKILL.md annotations-reference.md configuration-reference.jsonc; do
  curl -fsSL "https://raw.githubusercontent.com/NpgsqlRest/NpgsqlRest/master/.claude/skills/npgsqlrest/$f" \
    -o "$HOME/.claude/skills/npgsqlrest/$f"
done

…or scope it to a single project by copying those three files into <your-project>/.claude/skills/npgsqlrest/. The agent then loads it automatically when you work on NpgsqlRest SQL, annotations, or config. The two reference files are generated from npgsqlrest --annotations and npgsqlrest --config; regenerate them with those commands if your installed version differs.

About

NpgsqlRest is built and maintained by Vedran Bilopavlović. The C# library, parser, codegen, and runtime are hand-written, covered by 2,484 integration tests, and battle-tested in production.

Support This Project ❤️

NpgsqlRest is free, MIT-licensed, and built by one person out of passion. If it saves you time, GitHub Sponsors is the primary way to support it — Patreon and Buy Me a Coffee work too. Read more on the support page.

Contributing

Contributions are welcome — see CONTRIBUTING.md for how to build, run the tests, and what a good PR looks like. Small, well-scoped issues are labeled good-first-issue.

Security

Please report vulnerabilities privately via GitHub Private Vulnerability Reporting — see SECURITY.md. Do not open public issues for security problems.

License

MIT