NpgsqlRestAutomatic PostgreSQL Web Server

Example ​

Let's create a simple function, add comment declration to expose HTTP endpoint that only admin role can call:

create function my_todo(_user text)
returns table (
  what text, 
  who text
)
language sql as $$
select 'Hello World', _user
$$;

comment on function my_todo(text) is '
HTTP GET /hello
authorize admin
';

Automatically generated HTTP file for testing and development:

@host=http://localhost:8080

// function public.my_todo(
//     _user text
// )
// returns table(
//     what text,
//     who text
// )
//
// comment on function public.my_todo is 'HTTP GET /hello
// authorize admin';
GET {{host}}/hello?user=ABC

###

Automatically generated TypeScript fetch module with interface declaration that you can import and use in your Frontend project immediately:

// autogenerated at 2025-08-23T11:09:22.4550472+00:00
import parseQuery from "query";
const baseUrl = "http://localhost:8080";

interface IPublicMyTodoRequest {
    user: string | null;
}

interface IPublicMyTodoResponse {
    what: string | null;
    who: string | null;
}

export const publicMyTodoUrl = (request: IPublicMyTodoRequest) => baseUrl + "/hello" + parseQuery(request);

/**
 * function public.my_todo(
 *     _user text
 * )
 * returns table(
 *     what text,
 *     who text
 * )
 *
 * @remarks
 * comment on function public.my_todo is 'HTTP GET /hello
 * authorize admin';
 *
 * @param {IPublicMyTodoRequest} request
 * @returns {{status: number, response: IPublicMyTodoResponse[] | string}}
 *
 * @see FUNCTION public.my_todo
 */
export async function publicMyTodo(
    request: IPublicMyTodoRequest
) : Promise<{status: number, response: IPublicMyTodoResponse[] | string}> {
    const response = await fetch(publicMyTodoUrl(request), {
        method: "GET",
        headers: {
            "Content-Type": "application/json"
        },
    });
    return {
        status: response.status,
        response: response.status == 200 ? await response.json() as IPublicMyTodoResponse[] : await response.text()
    };
}

Feature List ​

• Instant API Generation. Automatically creates REST endpoints from PostgreSQL functions, procedures, tables, and views.
• Minimal Configuration. Works out-of-the-box with any PostgreSQL database with minimal configuration file. You only need connection info to get started.
• Comment Annotations. Control and configure endpoint behavior from your database using declarative comment annotations system.
• Declarative Configuration. Declare to database how your endpoint should behave. Focus on end-results of your system, not on how it will be implemented.
• HTTP Customization. Set methods, paths, content types, and response headers directly in your database declarations.
• Authentication Control. Configure authorization, roles, and security per endpoint in your database declarations.
• Real-Time Streaming. Enable server-sent events and fine control event scoping (user, roles, etc.) directly in your database declarations.
• Response Formatting. Control output formats, caching, timeouts, and raw responses in your database declarations, and more.
• JavaScript. Generate automatically fetch modules for all endpoints in development mode. Slash development time dramatically and reduce bugs.
• TypeScript. Generate type-safe interfaces and types for generated fetch modules. Bring static type checking for your PostgreSQL database.
• HTTP Files. Auto-generated REST client files, for all generated endpoints, for testing, development and auto-discovery.
• Multiple Auth Methods. Cookie authentication, Bearer tokens authentication, Basic authentication and external OAuth providers.
• Encrypted Tokens. Encrypted security tokens with advanced encryption key management and storage options (file, database, etc.).
• CORS Support. Cross-origin resource sharing configuration for Bearer token access.
• Built-in Password Validation. Built-in extendable and secure password hashing and validation. PBKDF2-SHA256 with 600,000 iterations aligned with OWASP's 2023+ recommendations.
• OAuth Integration. Google, LinkedIn, GitHub, Microsoft and Facebook support built-in.
• Claims-based security. User assertions cached in encrypted security token.
• Role-Based Authorization. Fine-grained access control with PostgreSQL role integration.
• Claim or Role Parameter Mapping. Automatically map user claims or roles to parameters.
• Claim or Role Context Mapping. Automatically map user claims or roles to PostgreSQL connection context.
• CSRF Protection. Antiforgery token support for secure uploads and form submissions.
• SSL/TLS. Full HTTPS support with certificate management.
• PostgreSQL Security and Encryption. Database connection security features courtesy of Npgsql. Includes SSL, Certificates, Kerberos and more.
• High Performance. Blazing fast native executable. See Performance Benchmarks.
• Connection Pooling. Built-in connection pooler, courtesy of Npgsql.
• KeepAlive, Auto-prepare, Buffer Size. Other performance tweaks and settings courtesy of Npgsql.
• Failover, Load Balancing. Set multiple hosts in connection string for failover and balancing.
• Multiple Connections. Define multiple connections and set specific connections (read-only, write-only) per endpoint in your database declarations.
• Connection Retry. Robust and configurable built-in connection retry mechanism.
• Thread Pool Optimization. Configurable thread pool settings for maximum throughput.
• Request Optimization. Kestrel server tuning with configurable limits.
• Response Compression. Brotli and Gzip compression with configurable levels.
• HTTP Caching. Define endpoint caching per endpoint in your database declarations.
• Server Caching. Define endpoint in-memory server caching per endpoint in your database declarations.
• Server-Sent Events. Innovative real-time streaming with PostgreSQL RAISE INFO statements. No database locking.
• Live Notifications. Push updates to clients in real-time.
• Event Sources. Auto-generated client code for streaming connections.
• Custom Scopes. Define Server-Sent Event Scope (specific user, groups of users or roles, etc.) per endpoint or per event in your database declarations.
• Native Executables. Native executable builds, including ARM versions, have zero dependencies and extremely fast startup times.
• Containerization. Docker-ready hub images.
• NPM Package. Additional distribution channel as NPM package.
• Environment Configuration. Flexible environment variable and configuration management.
• Data Protection. Advanced encryption and data protections with rotating encrypted keys and multiple key storage options.
• Structured Logging. Industry standard Serilog logger for Console, rolling file or PostgreSQL database logging.
• Excel Processing. Upload handler for Excel files that supports Excel content processing.
• Free And Open Source. Fully open-source under the MIT license.
• Upload Handlers. Multiple upload handlers implemented: File System, Large Objects, CSV/Excel, etc., with code generation. Make complex upload and processing pipelines in minutes.
• Static Files. Built-in serving of static content with high speed template parser for user claims and authorization features.
• Request Tracking. Detailed request analytics and connection monitoring.
• Performance Metrics. Built-in performance monitoring and diagnostics.
• Error Handling. Advanced PostgreSQL error code mapping to HTTP status codes.
• Custom Headers. Configurable request/response header management in your database declarations.
• IP Tracking. Client IP address parameter or PostgreSQL connection context for tracking.
• CRUD Support for Tables and View. Generate REST Endpoints for tables and views, to insert, update, select with conflict resolutions (on conflict do update or do nothing).
• .NET Library Integration. Version with core features implemented as .NET Nuget library for .NET project integration.