NpgsqlRest

Appearance

Comments

Overview ​

NpgsqlRest is a production-ready, standalone web server that automatically transforms your PostgreSQL database into a REST API. It provides:

  • Automatic HTTP REST endpoints from SQL files, functions, and procedures
  • SQL files as endpoints — write plain .sql files containing PostgreSQL commands and get REST endpoints automatically
  • Code generation for JavaScript/TypeScript client libraries — including TanStack Query (React Query) hooks — and Dart client libraries for Flutter
  • Code generation for HTTP files for a simple way to quickly invoke and test your API
  • AI-agent interfaces — MCP tools, OpenAI/Anthropic function-calling schemas, and llms.txt, all from the same SQL comments
  • Declarative configuration using SQL comments and annotations

To get started, you need:

  • A PostgreSQL database for metadata and endpoint specifications
  • Configuration via JSON files, environment variables, or command line arguments

Declarative Approach ​

NpgsqlRest uses SQL comment annotations to configure API endpoints declaratively. This approach keeps your API configuration close to your SQL logic.

NpgsqlRest creates REST endpoints from two types of sources:

Plain SQL Files Flagship ​

The primary way to create endpoints. Place .sql files containing PostgreSQL commands in a directory, and NpgsqlRest creates REST endpoints automatically. Parameter types and return columns are inferred via PostgreSQL's wire protocol — no functions, no procedures, no boilerplate:

sql
sql
-- sql/get_users.sql
-- HTTP GET
-- @authorize admin
-- @cached department_id
select id, name, email from users where department_id = :department_id;

This creates a GET /api/get-users?departmentId=1 endpoint with authorization and caching.

Multi-command SQL files execute multiple statements in a single database round-trip:

sql
sql
-- sql/process_order.sql
-- HTTP POST
-- @result validate
select count(*) 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;

See the SQL File Source configuration for details and the complete SQL File Source tutorial for a hands-on guide.

PostgreSQL Routines (Functions and Procedures) ​

NpgsqlRest also generates endpoints from PostgreSQL functions and procedures using the built-in COMMENT system:

sql
sql
create function get_user_data(id int)
returns table (name text, email text)
language sql
begin atomic;
select name, email from users where users.id = get_user_data.id;
end;

comment on function get_user_data(id int) is '
HTTP GET /admin/get-user-data
@authorize admin
Cache-Control: public, max-age=31536000';

This creates a GET endpoint at /admin/get-user-data that requires admin authorization and sets cache control headers.

All endpoint sources generate HTTP test files for testing your API and typed client libraries — TypeScript/JavaScript (optionally with TanStack Query hooks) and Dart for Flutter — with type definitions ready for your frontend.

Technology & Distribution ​

NpgsqlRest is built on the latest .NET with the Kestrel web server, compiled using AOT (Ahead-of-Time) compilation for:

  • Zero dependencies - single executable file
  • Fast startup - native performance
  • Cross-platform - runs on Windows, macOS, and Linux

Built on .NET/Kestrel, NpgsqlRest includes all modern web server capabilities out of the box, ensuring enterprise-grade performance and reliability.

NpgsqlRest is free and open-source, allowing you to:

  • Customize builds for specific platforms
  • Modify functionality to meet your needs
  • Contribute to the project's development

Comments