NpgsqlRestYour SQL is the API

The Whole Idea, in 19 Slides ​

PostgreSQL in. REST API, typed TypeScript client, and AI-agent tools out — with real, reproducible numbers from a product in production. Use the arrows, thumbnails, or your keyboard (←/→, F for fullscreen, N for speaker notes).

The backend that writes itself · 20261 / 19

‹›

‹ PrevNext ›►

Notes⤢ Fullscreen

12345678910111213141516171819

A Fully Declarative Backend ​

SQL declares what data. Annotations declare what behavior. Configuration declares what infrastructure. Tests declare what correctness — also in SQL. There is no imperative glue anywhere — no controllers, no services, no mappers to keep in sync. And it is built on the declarative language that has been running the world's data for 50 years — the one every developer, and every LLM, already knows: SQL.

Declarative Annotations ​

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

SQL File

PostgreSQL Function

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, per-test clones, tags, JUnit XML, and endpoint coverage with a CI threshold gate. And with --watch, the running server restarts on SQL file, configuration, and database routine changes — create or replace a function in psql and the endpoint is live seconds later, TypeScript client regenerated.

From the Blog ​

Tests Are SQL Files TooThe story behind NpgsqlRest 3.19.0 — write endpoint tests as plain .sql files that invoke the real endpoint in-process, inside the test's own transaction; watch mode that turns the live database schema into your type checker; and why database testing was never actually impossible — you were just using the wrong database.

Case Study: 74 Endpoints, Zero Backend Code — A Production App Built Entirely on NpgsqlRestWhat it actually looks like to ship a production application without a controller layer. Real numbers from a finance/visualization app: ~74 HTTP endpoints, 12K LOC of SQL, zero lines of C# or Python, an estimated 3,500–7,300 LOC of host-language boilerplate eliminated, a 5× faster iteration loop on signature changes, and roughly 55–100 hours of typing avoided across the project's evolution.The Backend That Writes Itself — NpgsqlRest in 19 SlidesAn interactive slide deck with page controls, keyboard navigation, and speaker notes — the whole NpgsqlRest pitch with real, reproducible numbers from a product in production.Turn PostgreSQL into MCP Tools an AI Agent Can CallNpgsqlRest 3.17.0 adds Model Context Protocol support. Annotate a function or .sql file with @mcp and it becomes a tool an AI agent can discover and call — one source, two interfaces (REST + MCP), structured output, and per-tool authorization.From SQL to Type-Safe TypeScriptGenerate typed fetch modules and TypeScript interfaces directly from PostgreSQL functions and SQL files. End-to-end type safety with @tsclient annotations.NpgsqlRest 3.13.0: Cache Profiles, Auth Schemes, Per-User Rate LimitsConditional caching with When rules, short-lived sensitive sessions, per-user rate limiting, and multi-tenant search_path with pgBouncer.PostgreSQL REST API Benchmark 2026Performance comparison of NpgsqlRest vs PostgREST, Django, FastAPI, Spring Boot, Go, Rust, and more — 14 frameworks, identical PostgreSQL functions.SQL REST APIThe story behind NpgsqlRest 3.12.0 — SQL file endpoints, the philosophy of database-first development, and why I think Clean Architecture got it wrong.REST APIs from Plain SQL FilesBuild REST APIs directly from .sql files — no functions, no procedures, no boilerplate. Multi-command endpoints, automatic parameter inference, and TypeScript generation.NpgsqlRest vs PostgREST vs SupabaseSide-by-side comparison of three PostgreSQL REST API platforms: performance benchmarks, features, authentication, file handling, and self-hosting complexity.Performance, Scalability, and High AvailabilityProduction patterns built into the runtime: caching with profiles and When-rules, connection and command retries, partitioned rate limiting, thread-pool tuning, multi-host failover, and read-replica routing.