pREST in 10 Minutes: Docker Compose to REST API to MCP Endpoint

You can go from an empty folder to a REST API over PostgreSQL in about 10 minutes. The same server also exposes a read-only MCP endpoint at /_mcp.

All it takes is a two-service Docker Compose file: Postgres plus the prest/prest:v2.4.2 image. Five PREST_PG_* environment variables point pREST at the database. Every command below comes 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 end up with

By the end, one prestd container serves all of this over your Postgres table:

  • Filtered, paged and sorted reads.
  • Inserts, updates and deletes.
  • JWT auth and a table permission.
  • A custom SQL route with a bound parameter.
  • A read-only MCP endpoint that answers tools/list.

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: write 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, as the docs tell you to do.
  • Auth starts off, so the first requests are simpler.
  • 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. It 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. That 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: create a table and make 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. To check the table structure without opening psql, GET /show/prest/public/todos lists the columns.

Step 3: filter, page and sort

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 and 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.

Nothing in pREST stops DELETE /prest/public/todos with no query string from emptying the table. That’s one more reason not to leave writes open to anonymous clients, which 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.
  • 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. That’s 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. It 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. Use auth.type = "basic" if you would rather POST /auth --user name:pass. It also covers asymmetric algorithms in jwt.algo, plus JWKS and OpenID well-known URLs for tokens from an identity provider.

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.
  • 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 give one identity narrower rules than the table default. They match on the JWT sub claim or username. ignore_table exempts a table from restrict mode.

One thing I would not copy from this tutorial into production is 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: add a custom query with a bound parameter

Anything that does not fit /{database}/{schema}/{table} goes in a SQL file under queries.location. The route is /_QUERIES/{folder}/{script}. 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.

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. Since v2.4.2, a value that trips the screen fails the request with a 400. That covers quotes, --, ::, or a SQL keyword inside a multi-word value. For anything a user types, bind it. sqlList does the same for repeated parameters, and ident handles 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 .

To keep scripts in the database instead of on disk, use queries.storage = "database" and the /_QUERIES/registry API from v2.2.0. The custom queries page has the full [queries] block.

Step 8: call 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. 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. Tools 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 and prest.select_table. You also get 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, and there are no insert, update or delete tools. /_QUERIES scripts are not exposed through MCP. To hide the catalog listings from agents, use the [expose] settings on the configuration page. Since v2.4.1 they apply to MCP as well as REST.

To actually use it from an editor, the MCP server tutorial continues from here. It has tools/call examples and the Cursor and Claude Desktop configuration.

What to do next

Open http://localhost:3000/_studio/. Studio is embedded in the binary since v2.2.0. It’s a read-only, same-origin client of the REST and MCP APIs you just used, so it accepts the same JWT. It’s the quickest way to see what your instance exposes.

Point it at a real database. Nothing above depended on pREST creating the table. Set PREST_PG_URL or the PREST_PG_* variables at an existing Postgres, and the tables are there. For more than one database, 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. The v2 guide has the version table and the advisory list. It shows 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. .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. Give it 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. The existing tables are exposed immediately. Postgres-compatible engines such as CockroachDB, YugabyteDB and TimescaleDB are listed on the databases page. MySQL 8 works since v2.5 (MySQL walkthrough). SQLite doesn’t.

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. With auth enabled it needs the same credentials as protected REST routes. It only lists tables your permissions allow reading.

Sources

Docs: docs.prestd.com. Compose file: docker-compose-prod.yml.