NpgsqlRest 3.12.0

NpgsqlRest

Automatic REST API for PostgreSQL | 6.1x faster than PostgREST

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

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

Most backend code shouldn't exist. If your API layer is just shuffling data between PostgreSQL and HTTP, why write it by hand? Write SQL, annotate it with a comment, and let the server figure out the rest. No controllers, no models, no mapping layers. The less code you maintain, the fewer bugs you ship.

<p align="center"> <img src="clean.png?v=2" alt="NpgsqlRest Architecture" width="340"> </p>

Install

Requires PostgreSQL >= 13. Native executables have zero runtime dependencies.

From SQL to REST API

Write a SQL file:

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

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:

export async function processOrder(orderid: number) : Promise<{
    validate: number[],
    result2: number,
    confirm: { id: number, status: string }[]
}> {
    const response = await fetch(baseUrl + "/api/process-order", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ orderid }),
    });
    return await response.json();
}

No framework, no ORM, no boilerplate. Authorization, parameters, type safety — from a SQL file.

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.

Features

• Multi-command SQL scripts — multiple statements in one file execute as a batch, returning named result sets
• TypeScript/JS code generation and .http files — types flow from PostgreSQL to your frontend
• 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
• 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 spec generation
• CSV/Excel/HTML table format response handlers

How does it compare?

NpgsqlRest vs PostgREST vs Supabase

Documentation

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

Contributing

Contributions are welcome. Open a pull request with a description of your changes.

License

MIT