Zero to CRUD API: Auto-Generate REST Endpoints from PostgreSQL Tables

CRUD · No SQL Required · TypeScript · Rapid Development · January 2026

What if you could build a complete REST API with Create, Read, Update, and Delete operations without writing a single line of backend code? With NpgsqlRest's CRUD auto-generation, you define a table, add a comment, and you're done. This post walks through building a fully functional Contacts Manager application in under 50 lines of SQL.

The Traditional Way vs. The NpgsqlRest Way

Building a typical CRUD API requires:

Traditional Stack	Lines of Code
Database schema	~20
ORM models	~50
Repository layer	~100
Service layer	~150
Controller/routes	~200
Validation logic	~100
TypeScript types	~50
API documentation	~100
Total	~770 lines

With NpgsqlRest:

NpgsqlRest	Lines of Code
Database schema + comment	~15
Config JSON	~20
TypeScript types	Auto-generated
API documentation	Auto-generated
Total	~35 lines

That's a 95% reduction in code. And the code you don't write has no bugs.

How It Works

NpgsqlRest reads your PostgreSQL table structure and automatically generates REST endpoints based on:

Table columns become request/response fields
Primary keys enable update and delete operations
Column types become TypeScript types
Table comments control which operations are exposed

The magic is in one SQL comment:

sql

comment on table contacts is 'HTTP';

This single line creates:

Method	Endpoint	Operation
GET	`/api/contacts/`	Select all rows
GET	`/api/contacts/?id=1`	Filter by any column
PUT	`/api/contacts/`	Insert new row
PUT	`/api/contacts/returning/`	Insert and return data
POST	`/api/contacts/`	Update existing row
DELETE	`/api/contacts/?id=1`	Delete row

Plus additional endpoints for conflict handling, returning variants, and more.

Project Structure

Source Code: The complete working example is available at github.com/NpgsqlRest/npgsqlrest-docs/examples/11_crud_auto_api

11_crud_auto_api/
├── sql_11/
│   ├── V1__example_11_schema.sql         # Tables and sample data
│   ├── R__example_11_contacts_api.sql    # API annotations (repeatable)
│   ├── R__example_11_login.sql           # Authentication function
│   └── R__example_11_logout.sql          # Logout function
├── src/
│   ├── example11Api.ts                   # Auto-generated by NpgsqlRest
│   └── app.ts                            # Frontend application
├── public/
│   └── index.html                        # HTML entry point
└── config.json                           # NpgsqlRest configuration

Step 1: Define Your Table (15 Lines)

The schema is simple - just define your table structure:

sql

-- V1__example_11_schema.sql

drop schema if exists example_11 cascade;
create schema example_11;

-- A simple contacts table
create table example_11.contacts (
    id serial primary key,
    name text not null,
    email text,
    phone text,
    created_at timestamp default now()
);

-- Sample data
insert into example_11.contacts (name, email, phone) values
    ('Alice Johnson', 'alice@example.com', '555-0101'),
    ('Bob Smith', 'bob@example.com', '555-0102'),
    ('Carol White', 'carol@example.com', '555-0103');

That's it. No ORM mappings. No repository interfaces. No service classes. Just a table.

Step 2: Enable the API (9 Lines)

In a separate repeatable migration, add the API annotations:

sql

-- R__example_11_contacts_api.sql

-- Enable only the CRUD endpoints we need
-- disabled = turn off all endpoints by default
-- enabled = turn on only what we use
comment on table example_11.contacts is '
HTTP
@disabled
@enabled select, insert_returning, update, delete
@authorize
';

Let's break down each line:

Annotation	Effect
`HTTP`	Enable CRUD endpoint generation for this table
`@disabled`	Start with all operations disabled
`@enabled select`	Enable GET (list/filter contacts)
`@enabled insert_returning`	Enable PUT with response (create contact, return created data)
`@enabled update`	Enable POST (update contact)
`@enabled delete`	Enable DELETE (remove contact)
`@authorize`	Require authentication for all operations

This fine-grained control means you only expose what you need. No accidental data leaks.

Step 3: Configure NpgsqlRest (20 Lines)

json

{
  "ApplicationName": "11_crud_auto_api",

  "StaticFiles": {
    "RootPath": "./11_crud_auto_api/public"
  },

  "Auth": {
    "CookieAuth": true,
    "CookieAuthScheme": "cookies",
    "CookieValidDays": 1,
    "CookieName": "example_11_auth"
  },

  "NpgsqlRest": {
    "IncludeSchemas": [ "example_11" ],
    "RequiresAuthorization": true,

    "CrudSource": {
      "Enabled": true,
      "CommentsMode": "OnlyWithHttpTag",
      "CrudTypes": ["All"]
    },

    "ClientCodeGen": {
      "FilePath": "./11_crud_auto_api/src/{0}Api.ts"
    }
  }
}

Key settings:

CrudSource.Enabled: true - Turn on automatic CRUD generation
CommentsMode: "OnlyWithHttpTag" - Only create endpoints for tables with HTTP comment
ClientCodeGen.FilePath - Auto-generate TypeScript client

The Generated API

When NpgsqlRest starts, it reads the table structure and generates these endpoints:

GET - List and Filter

bash

# Get all contacts
curl http://localhost:8080/api/example-11/contacts/

# Filter by any column
curl http://localhost:8080/api/example-11/contacts/?name=Alice%20Johnson
curl http://localhost:8080/api/example-11/contacts/?email=bob@example.com
curl http://localhost:8080/api/example-11/contacts/?id=1

Response:

json

[
  {
    "id": 1,
    "name": "Alice Johnson",
    "email": "alice@example.com",
    "phone": "555-0101",
    "createdAt": "2026-01-07T12:00:00"
  }
]

PUT - Create

bash

curl -X PUT http://localhost:8080/api/example-11/contacts/returning/ \
  -H "Content-Type: application/json" \
  -d '{"name": "David Brown", "email": "david@example.com", "phone": "555-0104"}'

Response (the created contact with auto-generated ID):

json

[
  {
    "id": 4,
    "name": "David Brown",
    "email": "david@example.com",
    "phone": "555-0104",
    "createdAt": "2026-01-07T12:30:00"
  }
]

POST - Update

bash

curl -X POST http://localhost:8080/api/example-11/contacts/ \
  -H "Content-Type: application/json" \
  -d '{"id": 1, "name": "Alice Smith", "phone": "555-9999"}'

The primary key (id) identifies which row to update. Only provided fields are modified.

DELETE - Remove

bash

curl -X DELETE "http://localhost:8080/api/example-11/contacts/?id=1"

The Generated TypeScript Client

NpgsqlRest automatically generates a fully-typed TypeScript client:

typescript

// example11Api.ts (auto-generated)

interface IContactsGetRequest {
    id?: number | null;
    name?: string | null;
    email?: string | null;
    phone?: string | null;
    createdAt?: string | null;
}

interface IContactsGetResponse {
    id: number | null;
    name: string | null;
    email: string | null;
    phone: string | null;
    createdAt: string | null;
}

export async function contactsGet(
    request: IContactsGetRequest
) : Promise<{
    status: number,
    response: IContactsGetResponse[],
    error: {status: number; title: string; detail?: string | null} | undefined
}> {
    const response = await fetch(baseUrl + "/api/example-11/contacts" + parseQuery(request), {
        method: "GET",
        headers: { "Content-Type": "application/json" },
    });
    return {
        status: response.status,
        response: response.ok ? await response.json() : undefined!,
        error: !response.ok ? await response.json() : undefined
    };
}

export async function contactsPut(request: IContactsPutRequest) { /* ... */ }
export async function contactsPost(request: IContactsPostRequest) { /* ... */ }
export async function contactsDelete(request: IContactsDeleteRequest) { /* ... */ }

Every column becomes a typed property. Every endpoint becomes a typed function. If you rename a column in PostgreSQL, the TypeScript build fails - catching errors before production.

The Frontend Application

Using the generated client, the entire frontend is straightforward:

typescript

// app.ts
import {
    login,
    logout,
    contactsGet,
    contactsPut,      // INSERT with returning
    contactsPost,     // UPDATE
    contactsDelete    // DELETE
} from "./example11Api.ts";

// GET all contacts
async function loadContacts() {
    const result = await contactsGet({});
    if (result.status === 200) {
        renderContacts(result.response);
    }
}

// PUT - Create new contact
async function createContact(name: string, email: string, phone: string) {
    const result = await contactsPut({
        name,
        email: email || null,
        phone: phone || null
    });

    if (result.status === 200 && result.response.length > 0) {
        showMessage(`Contact "${result.response[0].name}" created!`);
        loadContacts();
    }
}

// POST - Update contact
async function updateContact(id: number, name: string, email: string, phone: string) {
    const result = await contactsPost({
        id,
        name,
        email: email || null,
        phone: phone || null
    });

    if (result.status === 204) {
        showMessage("Contact updated!");
        loadContacts();
    }
}

// DELETE - Delete contact
async function deleteContact(id: number) {
    const result = await contactsDelete({ id });

    if (result.status === 204) {
        showMessage("Contact deleted!");
        loadContacts();
    }
}

No manual fetch calls with string URLs. No manual JSON parsing. No manual type definitions. The generated client handles everything.

Fine-Grained Access Control

The annotation system gives you precise control over what's exposed:

Enable Specific Operations

sql

-- Read-only table
comment on table audit_log is '
HTTP
@disabled
@enabled select
';

-- Insert-only table (no updates or deletes)
comment on table events is '
HTTP
@disabled
@enabled insert_returning
';

-- Full CRUD except delete
comment on table products is '
HTTP
@disabled
@enabled select, insert_returning, update
';

Require Authentication

sql

-- All operations require login
comment on table contacts is '
HTTP
@authorize
';

-- Only updates require authorization
comment on table public_data is '
HTTP
for select
@anonymous
for update, delete
@authorize
';

Custom Endpoints

sql

-- Custom URL path
comment on table users is '
HTTP GET /v1/users
HTTP POST /v1/users
';

Understanding CRUD Tags: The `for` Keyword

When NpgsqlRest generates CRUD endpoints from a table, each operation receives tags that identify what type of operation it is. The for keyword creates a scope where subsequent annotations apply only to endpoints matching those tags.

Available CRUD Tags

Operation	Tags
Select	`select`, `read`, `get`
Insert	`insert`, `put`, `create`
Insert Returning	`insert_returning`, `returning`
Update	`update`, `post`
Update Returning	`update_returning`, `returning`
Delete	`delete`
Delete Returning	`delete_returning`, `returning`
On Conflict Do Nothing	`on_conflict_do_nothing`, `on_conflict`
On Conflict Do Update	`on_conflict_do_update`, `on_conflict`

How `for` Creates Scopes

The for keyword starts a scope - all annotations that follow apply only to operations matching those tags, until the next for or end of comment:

sql

comment on table orders is '
HTTP

for select                    -- Start scope for SELECT endpoints
@allow_anonymous              -- SELECT is public
@cached                       -- SELECT results are cached

for insert update             -- Start scope for INSERT and UPDATE
@authorize                    -- Require authentication

for delete                    -- Start scope for DELETE only
@authorize admin              -- Require admin role
';

This creates different configurations for different operations from a single table comment.

Real-World Tag Patterns

Pattern 1: Read-Only API

Expose data for querying but prevent any modifications:

sql

comment on table reference_data is '
HTTP
@disabled insert, update, delete
';

Or using the whitelist approach:

sql

comment on table reference_data is '
HTTP
@disabled
@enabled select
';

Pattern 2: Append-Only Audit Log

Allow inserting records but prevent updates and deletes:

sql

comment on table audit_log is '
HTTP
@disabled update, delete
';

Pattern 3: Simplified API (No RETURNING Variants)

Keep the API surface minimal by removing /returning/ endpoints:

sql

comment on table orders is '
HTTP
@disabled returning
';

The returning tag matches all RETURNING variants: insert_returning, update_returning, delete_returning, and their on_conflict counterparts.

Pattern 4: Upsert-Only (Idempotent API)

For sync endpoints where clients should only use upsert operations:

sql

comment on table sync_state is '
HTTP
@disabled
for on_conflict
@enabled
';

Only /on-conflict-do-update/ and /on-conflict-do-nothing/ endpoints are exposed.

Pattern 5: Public Read, Authenticated Write

sql

comment on table products is '
HTTP
for select
@allow_anonymous

for insert update delete
@authorize
';

Anyone can browse products, but only authenticated users can modify them.

Pattern 6: Different Roles for Different Operations

sql

comment on table sensitive_data is '
HTTP
for select
@authorize viewer

for update
@authorize editor

for delete
@authorize admin
';

Viewers can read, editors can modify, only admins can delete.

The Whitelist Pattern (Recommended)

For production APIs, start with everything disabled and explicitly enable what you need:

sql

comment on table contacts is '
HTTP
@disabled                                    -- 1. Disable all endpoints
@enabled select, insert_returning, update, delete  -- 2. Enable only these
@authorize                                   -- 3. All require auth
';

Why use whitelist?

Explicit is better than implicit - You know exactly what's exposed
Safer defaults - New CRUD types in future versions won't be auto-enabled
Cleaner API surface - Clients see only the endpoints they need
Easier security auditing - Clear documentation of what's public

For full documentation on the tag system, see Tags annotation, Disabled annotation, and Enabled annotation.

Running the Example

bash

cd examples/11_crud_auto_api

# Create database tables
bun run db:up

# Build frontend
bun run build

# Start server
bun run dev

Open http://127.0.0.1:8080 and login with bob / bob123.

What You Get Without Writing

Let's tally what NpgsqlRest generated automatically:

Component	Traditional	NpgsqlRest
REST endpoints (4 operations)	Hand-coded routes	Auto-generated
Request validation	Manual checks	Column constraints
Response serialization	JSON library config	Built-in
TypeScript interfaces	Hand-maintained	Auto-generated
API client functions	Hand-written fetch	Auto-generated
Query parameter filtering	Custom parsing	Built-in
Primary key handling	ORM configuration	Automatic
Authentication hooks	Middleware setup	Annotations
Error responses	Custom error handling	RFC 7807 compliant

Beyond Basic CRUD

The CRUD auto-generation is just the starting point. You can combine it with:

Computed Columns

sql

create table orders (
    id serial primary key,
    items jsonb,
    subtotal numeric generated always as (
        (select sum((item->>'price')::numeric * (item->>'quantity')::int)
         from jsonb_array_elements(items) as item)
    ) stored
);

The generated API includes subtotal as a read-only field.

Row-Level Security

sql

alter table contacts enable row level security;

create policy contacts_user_policy on contacts
    using (user_id = current_setting('app.user_id')::int);

Combined with NpgsqlRest's user context injection, each user only sees their own data.

Custom Functions for Complex Operations

When CRUD isn't enough, add functions for complex logic:

sql

create function example_11.merge_contacts(
    _primary_id int,
    _secondary_id int
) returns example_11.contacts
language plpgsql
as $$
declare
    _result example_11.contacts;
begin
    -- Merge logic here
    return _result;
end;
$$;

comment on function example_11.merge_contacts is 'HTTP POST';

The function appears alongside CRUD endpoints in the generated client.

Performance

CRUD auto-generation has zero runtime overhead compared to hand-written endpoints:

Direct SQL execution - No ORM translation layer
Prepared statements - Queries are parameterized and cached
Streaming responses - Large result sets don't consume memory
Connection pooling - Efficient database connection reuse

In benchmarks, NpgsqlRest consistently outperforms traditional frameworks.

When to Use CRUD Auto-Generation

Ideal for:

Admin panels and dashboards
Internal tools
MVPs and prototypes
Data management interfaces
Any table-centric UI

Consider custom functions when:

Complex business logic spans multiple tables
You need computed fields not in the table
Operations require transactions with specific isolation
You want to hide the underlying schema

The beauty is you can mix both approaches. Use CRUD for simple tables, custom functions for complex operations.

Conclusion

Building a REST API shouldn't require hundreds of lines of boilerplate. With NpgsqlRest's CRUD auto-generation:

Define your table - The schema is your API contract
Add one comment - HTTP enables everything
Fine-tune with annotations - Enable only what you need
Get a typed client - TypeScript interfaces generated automatically

The Contacts Manager example demonstrates a complete, production-ready CRUD application in:

15 lines of SQL for the schema
9 lines of SQL for API annotations
20 lines of JSON configuration
~44 lines total vs ~770 lines in a traditional stack

That's a 95% reduction in code - and the code you don't write has no bugs. The types you don't maintain never drift. The endpoints you don't document are never outdated.

That's the power of making PostgreSQL the single source of truth.

Source Code: View the complete example on GitHub

More Blog Posts:
Custom Types & Multiset · Performance & High Availability · Benchmark 2025 · End-to-End Type Checking · Database-Level Security · Multiple Auth Schemes & RBAC · PostgreSQL BI Server · Secure Image Uploads · CSV & Excel Ingestion · Real-Time Chat with SSE · External API Calls · Reverse Proxy & AI Service · NpgsqlRest vs PostgREST vs Supabase

Get Started:
Quick Start Guide · Installation · CRUD Configuration

Zero to CRUD API: Auto-Generate REST Endpoints from PostgreSQL Tables ​

The Traditional Way vs. The NpgsqlRest Way ​

How It Works ​

Project Structure ​

Step 1: Define Your Table (15 Lines) ​

Step 2: Enable the API (9 Lines) ​

Step 3: Configure NpgsqlRest (20 Lines) ​

The Generated API ​

GET - List and Filter ​

PUT - Create ​

POST - Update ​

DELETE - Remove ​

The Generated TypeScript Client ​

The Frontend Application ​

Fine-Grained Access Control ​

Enable Specific Operations ​

Require Authentication ​

Custom Endpoints ​

Understanding CRUD Tags: The for Keyword ​

Available CRUD Tags ​

How for Creates Scopes ​

Real-World Tag Patterns ​

Pattern 1: Read-Only API ​

Pattern 2: Append-Only Audit Log ​

Pattern 3: Simplified API (No RETURNING Variants) ​

Pattern 4: Upsert-Only (Idempotent API) ​

Pattern 5: Public Read, Authenticated Write ​

Pattern 6: Different Roles for Different Operations ​

The Whitelist Pattern (Recommended) ​

Running the Example ​

What You Get Without Writing ​

Beyond Basic CRUD ​

Computed Columns ​

Row-Level Security ​

Custom Functions for Complex Operations ​

Performance ​

When to Use CRUD Auto-Generation ​

Conclusion ​

Comments

Zero to CRUD API: Auto-Generate REST Endpoints from PostgreSQL Tables

The Traditional Way vs. The NpgsqlRest Way

How It Works

Project Structure

Step 1: Define Your Table (15 Lines)

Step 2: Enable the API (9 Lines)

Step 3: Configure NpgsqlRest (20 Lines)

The Generated API

GET - List and Filter

PUT - Create

POST - Update

DELETE - Remove

The Generated TypeScript Client

The Frontend Application

Fine-Grained Access Control

Enable Specific Operations

Require Authentication

Custom Endpoints

Understanding CRUD Tags: The `for` Keyword

Available CRUD Tags

How `for` Creates Scopes

Real-World Tag Patterns

Pattern 1: Read-Only API

Pattern 2: Append-Only Audit Log

Pattern 3: Simplified API (No RETURNING Variants)

Pattern 4: Upsert-Only (Idempotent API)

Pattern 5: Public Read, Authenticated Write

Pattern 6: Different Roles for Different Operations

The Whitelist Pattern (Recommended)

Running the Example

What You Get Without Writing

Beyond Basic CRUD

Computed Columns

Row-Level Security

Custom Functions for Complex Operations

Performance

When to Use CRUD Auto-Generation

Conclusion