Automatic REST API Server for PostgreSQL | 6.1x faster than PostgREST

Turn SQL files and PostgreSQL objects into a production-ready REST API server with automatic TypeScript code generation and end-to-end type safety.

Documentation | Getting Started | Configuration Reference | Annotation Guide

Write a SQL file:

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

Get a fully configured REST endpoint: POST /api/process-order

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

With auto-generated TypeScript client:

export async function processOrder(orderid: number) : Promise<{
    validate: { found: 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, parameter inference, and type safety — all from a SQL file.

NpgsqlRest generates REST endpoints from multiple sources. Use whichever fits your use case — or combine them:

SQL files and functions/procedures are the primary sources — each with distinct advantages. SQL files are easier to manage and version alongside your application code. Functions and procedures offer full PostgreSQL type checking and the power of PL/pgSQL. Use both together for the best of both worlds.

All sources share the same annotation system — @authorize, @param, @cached, @path, and 50+ other annotations work everywhere.

• SQL Files as Endpoints — Drop a .sql file in a folder and get a REST endpoint. Single or multi-command scripts, with automatic HTTP verb detection, parameter inference, and full annotation support
• Multi-Command Scripts — SQL files with multiple statements execute as a batch via NpgsqlBatch, returning a JSON object with named result sets. Void commands return rows-affected count
• Instant API from Database Objects — Automatically creates REST endpoints from PostgreSQL functions, procedures, tables, and views
• Declarative Configuration — Configure endpoints using SQL comment annotations — same syntax works in both SQL files and database object comments
• Code Generation — Auto-generate frontend TypeScript/JavaScript code and .http files for testing. End-to-end type safety from PostgreSQL to your frontend
• High Performance — 6.1x faster than PostgREST at 100 concurrent users with low latency and high throughput
• Native Executables — AOT-compiled binaries with zero dependencies and instant startup
• Enterprise Ready — Authentication, authorization, rate limiting, caching, SSE streaming, encryption, OpenAPI 3.0, and more

PostgreSQL is not just storage. It's a powerful computation engine and development platform with decades of optimization. NpgsqlRest puts PostgreSQL at the center and generates everything else:

• SQL files as endpoints — Write a .sql file, get a REST endpoint. No functions needed for simple queries
• Schema as contract — Your tables, views, and functions become REST endpoints. One source of truth, zero drift
• SQL comments as config — Routes, auth rules, caching — all declared where the logic lives
• Types flow outward — PostgreSQL types generate TypeScript clients automatically. No manual mappings
• No middle tier — No ORM impedance mismatch, no N+1 queries, no controller boilerplate

NpgsqlRest includes a comprehensive set of enterprise-grade features, all configurable through SQL comment annotations:

• Authentication & Authorization — Cookie auth, Basic auth, JWT-compatible claims, role-based access control, @authorize, @allow_anonymous
• Security — Column-level encryption/decryption, security-sensitive endpoints, IP address parameter binding
• Caching — Response caching with configurable expiration, per-endpoint cache control
• Rate Limiting — Configurable rate limiter policies per endpoint
• Server-Sent Events (SSE) — Real-time streaming via RAISE INFO/NOTICE, scoped to matching or authorized clients
• File Uploads — Large object and file system upload handlers with MIME type filtering
• Reverse Proxy — Forward requests to upstream services with passthrough or transform modes
• HTTP Custom Types — PostgreSQL functions can call external HTTP APIs via annotated composite types
• OpenAPI 3.0 — Auto-generated API documentation
• Table Format Handlers — Pluggable response renderers (Excel, CSV, custom formats)
• 50+ Comment Annotations — Declarative endpoint configuration covering every aspect of API behavior

For complete documentation, visit npgsqlrest.github.io

See the detailed comparison: NpgsqlRest vs PostgREST vs Supabase

• PostgreSQL >= 13
• No runtime dependencies for native executables

For complete documentation including configuration options, authentication setup, TypeScript generation, and more, visit npgsqlrest.github.io

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

MIT License