The Architecture: UI → Database

NpgsqlRest is a compiled AOT executable (no runtime to install) that reads SQL files from your repo and exposes them as REST endpoints. The comment annotations on the SQL control auth, caching, rate limiting, timeouts, and more. No controllers, no services, no repositories, no ORMs.

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

Run npgsqlrest pointed at PostgreSQL, and:

$ curl -s 'localhost:8080/users/?department_id=1' | jq
[
  { "id": 1, "name": "Alice", "email": "alice@acme.io", "role": "admin" },
  { "id": 4, "name": "Diana", "email": "diana@acme.io", "role": "admin" },
  { "id": 7, "name": "Eve",   "email": "eve@acme.io",   "role": "user"  }
]

The API surface is exactly the SQL you write — joins, CTEs, window functions — nothing else. You can audit your entire API with grep.

Types Flow from PostgreSQL

NpgsqlRest generates a typed TypeScript client (and Dart) from the SQL metadata:

interface IUsersRequest {
  department_id?: string | null;
}
interface IUsersResponse {
  id: number | null;
  name: string | null;
  email: string | null;
  role: string | null;
}
export async function users(
  request: IUsersRequest
) : Promise> {
  // fetch call, generated, boring, correct
}

Change the SQL, regenerate the client, tsc catches everything. No hand-written contract to drift.

Tests Are SQL Files Too

Run npgsqlrest --test to invoke endpoints in-process on their own transaction. Insert fixtures, call the endpoint, assert, rollback:

-- 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;

No mocks, no test framework, no running server. It even reports endpoint coverage.

Performance: #2 in Benchmark

In an independent benchmark of 20 services, NpgsqlRest placed #2 with 3,500+ req/s on 4 cores — ahead of Go and Rust frameworks in that round.

Built-in Enterprise Features

  • Auth: cookies, JWT, OAuth2 flows, per-user rate limits
  • Multi-host failover and read-replica routing
  • Load balancing, connection and command retries
  • Partitioned rate limiting, cache profiles with conditional rules
  • Thread-pool tuning
  • Excel and HTML response rendering
  • All configured in JSON, no custom middleware

AI-Ready: @mcp Annotation

The @mcp annotation exposes any endpoint as a tool for AI agents (OpenAI/Anthropic tool schemas, llms.txt). Same auth, same rate limits — no backdoor.

Real Production App

The author shipped a real app with ~74 endpoints, 12K lines of SQL, zero lines of C# or Python. The case study includes honest numbers and annoyances.

Who Is This For?

If you've ever looked at a controller that does nothing but forward a request and thought "I typed all of this for what?" — give it 15 minutes.

Docs: https://npgsqlrest.github.io/ Quick start: https://npgsqlrest.github.io/guide/ GitHub: https://github.com/NpgsqlRest/NpgsqlRest