How do teams build an API directly from PostgreSQL?

What are the ways to build an API on PostgreSQL?

There are three: write the API yourself, generate it from the schema, or adopt a platform that generates it and adds services around it. They differ in what you write and in where the rule “who may see this row” lives.

A hand-written server gives you full control and costs a route, a query and a test for every operation. A generated server reads the catalog — tables, columns, keys, foreign keys, functions — and serves an API for all of it, so the API follows the schema rather than a list of routes. A platform does the same for its API layer and also runs auth, storage and a dashboard for you; Supabase’s REST API is PostgREST.

The generated tools also differ from each other. PostgREST serves REST only. PostGraphile serves GraphQL and is built to have its schema reshaped through plugins. Hasura serves GraphQL with permissions kept as metadata, plus RESTified endpoints. Postrust serves REST in PostgREST’s dialect and GraphQL in Hasura’s from one process. The trade-offs are laid out tool by tool in PostgreSQL GraphQL tools compared and on the comparison pages.

Which open-source projects serve REST and GraphQL from one database?

Postrust serves both from one process. The other routes pair two components or put one dialect on top of the other.

• Postrust (MIT): REST in PostgREST’s dialect and GraphQL in Hasura’s, generated from the same schema cache, with the same JWT claims reaching the same RLS policies on both.
• Hasura: GraphQL first, with RESTified endpoints defined over GraphQL queries.
• Supabase: PostgREST for REST and a PostgreSQL extension for GraphQL, as part of the platform.
• PostgREST and PostGraphile side by side: two deployments over one database, each with its own configuration and dialect.

What does a schema-generated API give you?

Create, read, update and delete on every exposed table and view, filtering, ordering, pagination, related rows through foreign keys, and SQL functions as endpoints. In Postrust the REST surface covers:

• CRUD: GET, POST, PATCH, PUT (upsert) and DELETE on /{table}.
• Filtering with operators such as eq, gt, ilike, in and is, and full-text search with fts.
• Embedding: select=*,customer(name) returns related rows nested in the response, following foreign keys.
• Pagination by limit and offset, or a Range header, with Prefer: count=exact for a total.
• RPC: a function is called at /rpc/{function}; a stable one also answers GET.

The GraphQL surface at /v1/graphql generates the matching fields: a root field per table with where, order_by, limit and offset, _by_pk and _aggregate fields, relationships, and insert, update and delete mutations. A mutation naming several root fields runs them in one transaction. How the GraphQL schema is derived from tables and keys is covered in GraphQL from a PostgreSQL schema; every operator is in the API reference and the GraphQL docs.

Where do authentication and permissions live?

In PostgreSQL. The API server verifies a JWT, runs the request as the database role the token names, and exposes its claims to SQL; grants and row-level security (RLS) policies then decide what that request can do.

In Postrust, PGRST_JWT_SECRET is the key tokens are verified with and PGRST_DB_ANON_ROLE is the role for requests without one. A policy reads the caller from current_setting('request.jwt.claims'), which is set in every REST and every GraphQL transaction, so one policy covers both surfaces. The GraphQL side also accepts Hasura’s contract: a role from x-hasura-default-role, chosen with X-Hasura-Role from the token’s x-hasura-allowed-roles. Policies are worked through in row-level security for a PostgreSQL API and in Authentication.

The server should connect as a least-privilege authenticator role, never as a superuser: a superuser bypasses row-level security.

How do clients get realtime updates?

Through GraphQL subscriptions. In Postrust each subscription field is a live query: the answer now, and the answer again whenever it changes, over a WebSocket at /v1/graphql/ws using the graphql-transport-ws protocol.

The subscription root mirrors the query root, so the same filters apply, and a subscription runs under the same identity and policies as a query. Hasura’s cursor-based _stream subscriptions are not implemented. See Realtime.

How do you serve a PostgreSQL schema as REST and GraphQL with Postrust?

Create the schema, start Postrust pointed at the database, and query it. The example below uses two related tables and one function; nothing is configured per table.

1. Create the schema and an anonymous role

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    year INT NOT NULL,
    author_id INT NOT NULL REFERENCES authors (id)
);

INSERT INTO authors (name) VALUES
    ('Ursula K. Le Guin'),
    ('Octavia E. Butler');

INSERT INTO books (title, year, author_id) VALUES
    ('A Wizard of Earthsea', 1968, 1),
    ('The Dispossessed', 1974, 1),
    ('Kindred', 1979, 2),
    ('Parable of the Sower', 1993, 2);

-- A read-only function, served as RPC
CREATE FUNCTION books_since(since INT) RETURNS SETOF books
    LANGUAGE sql STABLE
    AS $$ SELECT * FROM books WHERE year >= since ORDER BY year $$;

-- The role anonymous requests run as
CREATE ROLE web_anon NOLOGIN;
GRANT USAGE ON SCHEMA public TO web_anon;
GRANT SELECT ON public.authors, public.books TO web_anon;

Postrust uses PostgreSQL roles for access control. Requests without a token run as web_anon, which may only read these two tables.

2. Start Postrust

From source, with a Rust toolchain and PostgreSQL 12 or later, as in Getting Started:

# Clone the repository
git clone https://github.com/postrust/postrust.git
cd postrust

# Build in release mode
cargo build --release

# Set required environment variables
export DATABASE_URL="postgres://user:password@localhost:5432/mydb"
export PGRST_DB_ANON_ROLE="web_anon"

# Start the server
./target/release/postrust

Or with Docker. The image is postrust/postrust; point DATABASE_URL at your database:

docker run -d \
  --name postrust \
  -p 3000:3000 \
  -e DATABASE_URL="postgres://user:pass@host:5432/db" \
  -e PGRST_DB_ANON_ROLE="web_anon" \
  -e PGRST_JWT_SECRET="your-secret-key" \
  postrust/postrust:latest

Pre-built binaries are on the releases page. The server listens on port 3000. By default the REST API is mounted under /api; with PGRST_COMPAT_MODE=true it is also served at the root, as PostgREST serves it.

3. Query it over REST

# Every book, newest first
curl "http://localhost:3000/api/books?order=year.desc"

# Filter and pick columns
curl "http://localhost:3000/api/books?year=lt.1975&select=title,year"

# Embed the related author through the foreign key
curl "http://localhost:3000/api/books?select=title,authors(name)"

# And the other direction: each author with their books
curl "http://localhost:3000/api/authors?select=name,books(title,year)"

# Paginate
curl "http://localhost:3000/api/books?limit=2&offset=2"

# Call the function
curl -X POST http://localhost:3000/api/rpc/books_since \
  -H "Content-Type: application/json" \
  -d '{"since": 1975}'

The query grammar is PostgREST’s. One difference by default: an RPC result comes back wrapped and keyed by the function name, as [{"books_since": ...}]; compatibility mode returns it bare, as PostgREST does.

4. Query the same data over GraphQL

# The same books, each with its author
curl -X POST http://localhost:3000/v1/graphql \
  -H "Content-Type: application/json" \
  -d '{"query": "{ books(order_by: [{year: desc}]) { title year author { name } } }"}'

# Each author with a filtered list of books, and a count
curl -X POST http://localhost:3000/v1/graphql \
  -H "Content-Type: application/json" \
  -d '{"query": "{ authors { name books(where: {year: {_lt: 1975}}) { title } books_aggregate { aggregate { count } } } }"}'

The foreign key from books to authors becomes author on a book and books on an author. Both requests run as web_anon, under the same grants as the REST calls. Opening http://localhost:3000/v1/graphql in a browser serves GraphQL Playground.

Logic that does not belong in SQL, such as a webhook handler, can be added as an Axum route in the same binary; see Custom Routes.

Where can you deploy a generated PostgreSQL API?

Postrust is not tied to one platform: the same API runs as a binary, in a container, on Kubernetes, on a platform such as Fly.io or Railway, on AWS Lambda, or on Cloudflare Workers. Pick the one your team already operates.

• Binary: build with cargo build --release -p postrust-server and run ./target/release/postrust, configured by environment variables.
• Docker: the postrust/postrust image, alone or in Docker Compose next to PostgreSQL.
• Kubernetes: there is a Helm chart.
• Fly.io and Railway: deploy with each CLI and set DATABASE_URL as a secret or variable.
• AWS Lambda: a native adapter, postrust-lambda, serves the same router as the server, so REST, GraphQL, JWT authentication and row-level security behave identically. Build with cargo lambda build --release -p postrust-lambda and deploy with AWS SAM or the Serverless Framework.
• Cloudflare Workers: the postrust-worker adapter runs the server’s own REST request pipeline in a Worker, reaching PostgreSQL through Hyperdrive, with the schema cache in Workers KV and every request taking on the token’s role and claims. What it serves, host by host, is in the Cloudflare Workers docs.

Commands, templates and a production checklist are in Deployment; every environment variable is in Configuration.

When should you write the API by hand instead?

When most endpoints are logic rather than data access, or when the public API must not mirror the tables. A generated API exposes what the schema and grants say; it does not orchestrate calls to other services.

Many teams use both: a generated API for data access, and a small hand-written service or custom route for the rest. Views and functions in a dedicated schema, exposed with PGRST_DB_SCHEMAS, keep the public shape separate from the tables underneath. If you want auth, storage and a dashboard run for you, a platform is the shorter path; if you already have a PostgreSQL database and want an API in front of it rather than a platform around it, a generated server is the smaller step.