The fastest way to get a REST API over a PostgreSQL database is a two-service Docker Compose file: Postgres plus the prest/prest:v2.4.2 image, pointed at it with five PREST_PG_* environment variables. From there every table is a URL, and the same server exposes a read-only MCP endpoint at /_mcp. This tutorial goes from an empty folder to filtered reads, writes, JWT auth, a table permission, a custom SQL route with a bound parameter and an MCP tools/list call, with every command taken from the pREST docs.
If you want the background first, the pREST v2 guide lists every release and which one to run. The short version is v2.4.2, for the reasons in the v2.4.1 and v2.4.2 post.
Table of Contents
- What you need
- Step 1: the compose file
- Step 2: a table and the first requests
- Step 3: filters, pagination and ordering
- Step 4: insert, update, delete
- Step 5: turn on JWT auth
- Step 6: restrict a table
- Step 7: a custom query with a bound parameter
- Step 8: the MCP endpoint
- Next steps
- FAQ
What you need
Docker with Compose, curl, and htpasswd from apache2-utils for the auth step (the docs use it to bcrypt a password). jq is optional but makes the JSON readable. Nothing else gets installed on your machine; prestd runs inside the container.
Step 1: the compose file
The docs’ Start with Docker page has you download the repo’s docker-compose-prod.yml. The file below is that stack with three changes: the image is pinned to v2.4.2 instead of latest (the docs tell you to pin it), auth starts off so the first requests are simpler, and two files are mounted that later steps use. Save it as docker-compose.yml in an empty folder.
services:
postgres:
image: postgres:18
environment:
- POSTGRES_USER=prest
- POSTGRES_DB=prest
- POSTGRES_PASSWORD=prest
ports:
- "5432:5432"
healthcheck:
test: ["CMD-SHELL", "pg_isready -d $${POSTGRES_DB} -U $${POSTGRES_USER}"]
interval: 5s
retries: 10
prest:
image: prest/prest:v2.4.2
environment:
- PREST_DEBUG=false
- PREST_PG_HOST=postgres
- PREST_PG_PORT=5432
- PREST_PG_USER=prest
- PREST_PG_PASS=prest
- PREST_PG_DATABASE=prest
- PREST_CONF=/prest.toml
- PREST_QUERIES_LOCATION=/queries
volumes:
- ./prest.toml:/prest.toml:ro
- ./queries:/queries:ro
depends_on:
postgres:
condition: service_healthy
ports:
- "3000:3000"
The PREST_PG_* variables and their defaults are on the configuration page, which also documents PREST_CONF (the path to the TOML file, default ./prest.conf) and PREST_QUERIES_LOCATION (default ./queries). Keep PREST_DEBUG=false: the same page says debug mode disables the JWT middleware at runtime, which would make step 5 look like it does nothing.
Create the two mounted paths before starting, or Docker will create prest.toml as a directory:
mkdir queries
printf '[http]\nport = 3000\n' > prest.toml
docker compose up -d
curl -s http://localhost:3000/_health
/_health is the liveness probe; it pings the default database. /_ready does the same for every registered alias if you later add more databases.
Step 2: a table and the first requests
pREST needs something to expose, so create a table through the Postgres container:
docker compose exec postgres psql -U prest -d prest -c "
CREATE TABLE public.todos (
id serial PRIMARY KEY,
title text NOT NULL,
done boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO public.todos (title, done) VALUES
('buy milk', false),
('write the pREST tutorial', true),
('fix the garage door', false);"
The URL shape is /{database}/{schema}/{table}, and GET maps to SELECT. The database here is prest, because that is POSTGRES_DB above:
curl -s http://localhost:3000/databases | jq .
curl -s http://localhost:3000/schemas | jq .
curl -s http://localhost:3000/tables | jq .
curl -s http://localhost:3000/prest/public/todos | jq .
The first three are catalog routes from the API reference. The last one returns the three rows as a JSON array. GET /show/prest/public/todos lists the columns if you want to check the table structure without opening psql.
Step 3: filters, pagination and ordering
Everything below is a query-string parameter from the parameters page. A plain field=value is an equality filter; operators are written as field=$op.value.
# equality and boolean operators
curl -s 'http://localhost:3000/prest/public/todos?done=$false' | jq .
# case-insensitive pattern match (the % wildcards need URL encoding, so let curl do it)
curl -sG http://localhost:3000/prest/public/todos --data-urlencode 'title=$ilike.%milk%' | jq .
# set membership
curl -s 'http://localhost:3000/prest/public/todos?id=$in.1,3' | jq .
# OR between two conditions
curl -sG http://localhost:3000/prest/public/todos --data-urlencode '_or=id=$eq.1||id=$eq.2' | jq .
Pagination is _page and _page_size, with a default page size of 10. Ordering is _order, with a leading - for descending. _select limits the returned columns and _count counts instead of returning rows:
curl -s 'http://localhost:3000/prest/public/todos?_page=1&_page_size=2&_order=-created_at' | jq .
curl -s 'http://localhost:3000/prest/public/todos?_select=id,title&_order=title' | jq .
curl -s 'http://localhost:3000/prest/public/todos?_count=*' | jq .
The single quotes matter in a shell. $false, $ilike and $in all start with a dollar sign and bash will expand them to nothing inside double quotes.
The parameters page has more than I use here: $gt, $gte, $lt, $lte, $ne, $nin, $null, $notnull, $like, $nlike, _groupby with sum:, avg: and friends, _distinct, ltree operators, and the pgvector _korder and :vecdist forms that arrived in v2.4.0.
Step 4: insert, update, delete
POST inserts a JSON body, PATCH or PUT updates the rows matched by the query string, and DELETE deletes them:
curl -s -X POST http://localhost:3000/prest/public/todos \
-H 'Content-Type: application/json' \
-d '{"title": "call the plumber"}' | jq .
curl -s -X PATCH 'http://localhost:3000/prest/public/todos?id=1' \
-H 'Content-Type: application/json' \
-d '{"done": true}' | jq .
curl -s -X DELETE 'http://localhost:3000/prest/public/todos?id=3' | jq .
The filter on PATCH and DELETE is the WHERE clause. The API reference warns that an unconditional update hits every row, and the same is true of delete. There is nothing in pREST that stops DELETE /prest/public/todos with no query string from emptying the table, so this is one more reason not to leave writes open to anonymous clients, which is what the next two steps fix.
Step 5: turn on JWT auth
Auth in pREST is a users table plus a POST /auth route that returns a signed JWT. Three settings are involved, all on the configuration page: PREST_AUTH_ENABLED turns on the token endpoint, PREST_JWT_KEY is the HMAC secret, and PREST_JWT_DEFAULT makes every route except the whitelist (^\/auth$ by default) require a Bearer token. Since v2.4.2 the key must be at least 32 bytes for the default HS256; a shorter key is treated as unusable at startup and auth stays off with an error in the logs, which is the failure mode you least want to discover late.
First create the users table. The Start with Docker page uses the built-in migration for this, then inserts a user with a bcrypt hash made by htpasswd (bcrypt is the v2 default for auth.encrypt):
docker compose exec prest prestd migrate up auth
HASH=$(htpasswd -nbBC 10 x prest | cut -d: -f2)
docker compose exec postgres psql -U prest -d prest -c \
"INSERT INTO prest_users (name, username, password) VALUES ('pREST Full Name', 'prest', '$HASH')"
The x in the htpasswd call is a throwaway username; cut keeps only the hash. Now add the three variables to the prest service in docker-compose.yml and recreate it:
- PREST_AUTH_ENABLED=true
- PREST_JWT_DEFAULT=true
- PREST_JWT_KEY=change-me-to-a-random-32-byte-key!!
docker compose up -d
curl -s http://localhost:3000/prest/public/todos
That last request should now be rejected with a 401. Get a token and retry with it:
curl -s -X POST http://localhost:3000/auth \
-H 'Content-Type: application/json' \
-d '{"username": "prest", "password": "prest"}'
Copy the JWT from the response into a variable and send it as a Bearer token:
TOKEN='paste-the-token-here'
curl -s http://localhost:3000/prest/public/todos \
-H "Authorization: Bearer $TOKEN" | jq .
The auth reference covers the rest: auth.type = "basic" if you would rather POST /auth --user name:pass, asymmetric algorithms in jwt.algo, JWKS and OpenID well-known URLs if the tokens come from an identity provider instead of pREST.
Step 6: restrict a table
Auth says who the caller is. Permissions say what they can touch, and they live in the TOML file. From the permissions page: restrict = true makes every unlisted table inaccessible, each [[access.tables]] entry grants some of read, write and delete, and fields limits the visible columns. Replace prest.toml with this and restart the service:
[http]
port = 3000
[access]
restrict = true
[[access.tables]]
name = "todos"
schema = "public"
permissions = ["read"]
fields = ["id", "title", "done"]
docker compose restart prest
curl -s http://localhost:3000/prest/public/todos -H "Authorization: Bearer $TOKEN" | jq .
curl -s -X POST http://localhost:3000/prest/public/todos \
-H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \
-d '{"title": "should fail"}'
The read now returns rows without created_at, and the insert is refused because write is not in the list. [[access.users]] blocks on the same page let you give one identity (matched on the JWT sub claim or username) narrower rules than the table default, and ignore_table exempts a table from restrict mode.
Which brings up the one thing I would not copy from this tutorial into production: the Postgres role. The prest user above owns the database, and the v2.3.0 advisory specifically calls out running pREST as a superuser. Create a role with only the grants your tables need and put that in PREST_PG_USER.
Step 7: a custom query with a bound parameter
Anything that does not fit /{database}/{schema}/{table} goes in a SQL file under queries.location. The custom queries page maps the file suffix to the verb: .read.sql is GET, .write.sql is POST, .update.sql is PUT/PATCH, .delete.sql is DELETE. The route is /_QUERIES/{folder}/{script}.
Create queries/todos/search.read.sql:
SELECT id, title, done, created_at
FROM public.todos
WHERE title ILIKE {{sqlVal "q"}}
ORDER BY created_at DESC
{{sqlVal "q"}} is the part to pay attention to. It renders as $1 and appends the caller’s value to the query arguments, so the value travels to Postgres as a parameter and is never part of the SQL text. The older form '{{.q}}' interpolates the value into the statement, and since v2.4.2 a value that trips the screen (quotes, --, ::, or a SQL keyword inside a multi-word value) fails the request with a 400. For anything a user types, bind it. sqlList does the same for repeated parameters and ident for table or column names, which cannot be bound.
The folder is mounted into the container. Call the route; if it reports the script as not found, restart the prest service so it sees the new file:
curl -sG http://localhost:3000/_QUERIES/todos/search \
-H "Authorization: Bearer $TOKEN" \
--data-urlencode 'q=%milk%' | jq .
If you would rather keep scripts in the database than on disk, queries.storage = "database" and the /_QUERIES/registry API from v2.2.0 do that; the custom queries page has the full [queries] block.
Step 8: the MCP endpoint
There is nothing to turn on. Since v2.1.0 the same prestd process serves a read-only MCP endpoint at /_mcp, and the MCP over HTTP page documents no config key for it. It uses the auth and permissions you just configured: with PREST_JWT_DEFAULT=true both the GET discovery payload and the POST JSON-RPC calls need the Bearer token, and the tools you see are generated only for the tables your rules allow reading.
# discovery payload: server metadata and the tool catalog
curl -s http://localhost:3000/_mcp -H "Authorization: Bearer $TOKEN" | jq .
# JSON-RPC tools/list
curl -s -X POST http://localhost:3000/_mcp \
-H "Authorization: Bearer $TOKEN" \
-H 'Content-Type: application/json' \
-d '{"jsonrpc":"2.0","id":1,"method":"tools/list"}' | jq '.result.tools[].name'
You should see the generic tools (prest.list_databases, prest.list_schemas, prest.list_tables, prest.describe_table, prest.select_table) plus one schema-aware tool per readable table, here prest.select.prest.public.todos. The supported methods are initialize, tools/list and tools/call; selects are capped at 100 rows; there are no insert, update or delete tools; and /_QUERIES scripts are not exposed through MCP. If you want to hide the catalog listings from agents, the [expose] settings on the configuration page apply to MCP as well as REST since v2.4.1.
To actually use it from an editor, the MCP server tutorial continues from here with tools/call examples and the Cursor and Claude Desktop configuration.
Next steps
Open http://localhost:3000/_studio/. Studio is embedded in the binary since v2.2.0 and is a read-only, same-origin client of the REST and MCP APIs you just used, so it accepts the same JWT. It is the quickest way to see what your instance exposes.
Point it at a real database. Nothing above depended on the table being created by pREST; set PREST_PG_URL or the PREST_PG_* variables at an existing Postgres and the tables are there. If you need more than one, PREST_PG_SINGLE=false with DATABASE_ALIAS_N / DATABASE_URL_N pairs routes by the first path segment (multi-database).
Compare before you commit. pREST vs PostgREST covers the trade-offs against the other common way to put REST in front of Postgres, and the v2 guide has the version table and the advisory list so you know why the image tag above says v2.4.2 and not something earlier.
FAQ
Do I need to write any code to use pREST?
No. The REST routes come from the database catalog, auth and permissions are environment variables and a TOML file, and custom routes are SQL files. The only code in this tutorial is one CREATE TABLE and one SELECT with a {{sqlVal "q"}} placeholder.
Can pREST write to the database?
Yes, over REST. POST, PATCH/PUT and DELETE on /{database}/{schema}/{table} map to INSERT, UPDATE and DELETE, and .write.sql, .update.sql and .delete.sql scripts do the same for custom queries. The MCP endpoint is read-only: it has no write tools and returns an error for unsupported tool names.
How do I restrict a table in pREST?
Set restrict = true under [access] in prest.toml, then add one [[access.tables]] entry per table you want reachable with the permissions it should allow (read, write, delete) and optionally a fields list. Unlisted tables become inaccessible. Per-user overrides go in [[access.users]], matched on the JWT sub claim or username.
Does pREST work with an existing database?
Yes, that is the main use. Point PREST_PG_URL (or DATABASE_URL, or the individual PREST_PG_* variables) at any PostgreSQL 9.5 or newer and the existing tables are exposed immediately. Postgres-compatible engines such as CockroachDB, YugabyteDB and TimescaleDB are listed on the databases page; MySQL and SQLite are not supported in current releases.
Where is the MCP endpoint in pREST?
At /_mcp on the same host and port as the REST API, so http://localhost:3000/_mcp in this tutorial. GET returns the discovery payload and POST accepts JSON-RPC initialize, tools/list and tools/call. It is on by default from v2.1.0, needs the same credentials as protected REST routes when auth is enabled, and only lists tables your permissions allow reading.