NpgsqlRest

Automatic REST API Server for PostgreSQL

Transform your PostgreSQL database into a production-ready REST API server with automatic TypeScript code generation and end-to-end type safety.

Documentation | Getting Started | Configuration | Annotations

Installation

npm install npgsqlrest

This downloads the appropriate native executable for your platform (Windows, macOS, or Linux).

Quick Start

1. Create a PostgreSQL function with annotation:

create function hello_world()
returns text
language sql
as $$
select 'Hello World'
$$;

comment on function hello_world() is 'HTTP GET /hello';

2. Create configuration file (appsettings.json):

{
  "ConnectionStrings": {
    "Default": "Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=postgres"
  }
}

3. Run the server:

npx npgsqlrest

Your API is now live at http://localhost:8080/hello

Usage

Run with npx

npx npgsqlrest

Run directly (after install)

./node_modules/.bin/npgsqlrest

With custom config

npx npgsqlrest myconfig.json

Override config via CLI

npx npgsqlrest --urls=http://localhost:3000
npx npgsqlrest --log:minimallevels:npgsqlrest=debug

Show help

npx npgsqlrest --help

Show version

npx npgsqlrest --version

TypeScript Code Generation

Enable automatic TypeScript client generation:

{
  "ConnectionStrings": {
    "Default": "Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=postgres"
  },
  "NpgsqlRest": {
    "ClientCodeGen": {
      "Enabled": true,
      "FilePath": "./src/api/{0}Api.ts"
    }
  }
}

On startup, NpgsqlRest generates type-safe fetch functions for all your endpoints:

// Auto-generated
export async function publicHelloWorld(): Promise<{status: number, response: string}> {
    const response = await fetch(baseUrl + "/hello", { method: "GET" });
    return { status: response.status, response: await response.text() };
}

Key Features

• Instant API Generation - REST endpoints from functions, procedures, tables, and views
• Declarative Configuration - Configure endpoints via SQL comment annotations
• TypeScript/JavaScript Generation - Auto-generate frontend code with full type safety
• High Performance - AOT-compiled native executable, 6x faster than PostgREST
• RESTful Path Parameters - Routes like /products/{id} with URL parameter extraction
• Authentication - Cookie auth, JWT, Bearer tokens, OAuth (Google, GitHub, etc.)
• Authorization - Role-based access control with PostgreSQL integration
• Caching - In-memory, Redis, or HybridCache with stampede protection
• Rate Limiting - Built-in rate limiter with multiple policies
• Server-Sent Events - Real-time streaming via PostgreSQL RAISE INFO
• Reverse Proxy - Forward requests to upstream services
• OpenAPI 3.0 - Auto-generated API documentation

Configuration Example

{
  "ConnectionStrings": {
    "Default": "Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=postgres"
  },
  "Urls": "http://localhost:8080",
  "Log": {
    "MinimalLevels": {
      "NpgsqlRest": "Debug"
    }
  },
  "Auth": {
    "CookieAuth": true
  },
  "NpgsqlRest": {
    "ClientCodeGen": {
      "Enabled": true,
      "FilePath": "./src/api/{0}Api.ts"
    },
    "HttpFileOptions": {
      "Enabled": true,
      "NamePattern": "./http/{0}_{1}.http"
    }
  }
}

For complete configuration options, see Configuration Reference.

Comment Annotations

Control endpoint behavior directly in SQL:

comment on function my_func() is '
HTTP GET /api/resource/{id}
@authorize admin, user
@cached
@timeout 30s
Content-Type: application/json
';

See Annotation Guide for all available annotations.

Supported Platforms

The npm package includes native executables for:

• Windows - x64
• macOS - x64, ARM64 (Apple Silicon)
• Linux - x64, ARM64

Alternative Installation Methods

| Method | Command | |--------|---------| | Docker | docker pull vbilopav/npgsqlrest:latest | | Direct Download | GitHub Releases | | .NET Library | dotnet add package NpgsqlRest |

Requirements

• PostgreSQL >= 13
• Node.js >= 14 (for npm/npx)

Links

• Documentation
• Getting Started Guide
• Configuration Reference
• Comment Annotations
• GitHub Repository
• Changelog
• Docker Hub

License

MIT License - see LICENSE