⚡
NpgsqlRestAutomatic PostgreSQL Web Server
Create professional, high-performance HTTP REST APIs for PostgreSQL databases in minutes and generate type-safe client code automatically.
Skip to content
Example
Simple function with RESTful path parameters, expose HTTP endpoint that only admin role can call:
sql
create function get_product(product_id int)
returns table (
id int,
name text,
price numeric
)
language sql as
$$
select id, name, price from products where id = product_id
$$;
comment on function get_product(int) is '
HTTP GET /products/{product_id}
authorize admin
';Automatically generated TypeScript module in development environment:
typescript
// autogenerated at 2025-12-21T11:09:22.4550472+00:00
const baseUrl = "http://localhost:8080";
interface IPublicGetProductRequest {
productId: number;
}
interface IPublicGetProductResponse {
id: number | null;
name: string | null;
price: number | null;
}
/**
* function public.get_product(
* product_id int
* )
* returns table(
* id int,
* name text,
* price numeric
* )
*
* @remarks
* comment on function public.get_product is 'HTTP GET /products/{product_id}
* authorize admin';
*
* @param request
* @returns {status: number, response: IPublicGetProductResponse[], error: {status: number; title: string; detail?: string | null} | undefined}
*
* @see FUNCTION public.get_product
*/
export async function publicGetProduct(
request: IPublicGetProductRequest
) : Promise<{status: number, response: IPublicGetProductResponse[], error: {status: number; title: string; detail?: string | null} | undefined}> {
const response = await fetch(`${baseUrl}/products/${request.productId}`, {
method: "GET",
headers: {
"Content-Type": "application/json"
},
});
return {
status: response.status,
response: response.status == 200 ? await response.json() as IPublicGetProductResponse[] : [],
error: response.status == 200 ? undefined : await response.json()
};
}Database-First Approach
Static Type Checking End-To-End
Declarative API Design
AI Estimate
55-85%
reduction in API development time across backend and frontend, plus ongoing maintenance savings
Real World Project
~7,300 LOC
lines of code saved with ~57% reduction in codebase size
Real-time chat with Server-Sent Events using PostgreSQL RAISE statements:
sql
create function send_message(_channel_id int, _message text)
returns void
language plpgsql as
$$
begin
insert into messages (channel_id, content) values (_channel_id, _message);
raise notice '%', json_build_object('channel', _channel_id, 'message', _message)
using hint = 'authorize channel_' || _channel_id::text;
end;
$$;
comment on function send_message(int, text) is '
HTTP POST /channels/{_channel_id}/messages
sse /channels/events
';Call external APIs directly from PostgreSQL using HTTP Custom Types:
sql
create type weather_api as (
body text,
status_code int,
success boolean
);
comment on type weather_api is '
GET https://api.weather.com/v1/current?city={_city}
Authorization: Bearer ${WEATHER_API_KEY}
';
create function get_weather(_city text, _response weather_api)
returns json
language sql as
$$
select case when (_response).success
then (_response).body::json
else json_build_object('error', 'API call failed')
end
$$;
comment on function get_weather(text, weather_api) is 'HTTP GET /weather/{_city}';Type-safe TypeScript clients are automatically generated for all examples above.
...and much more!
