pRESTd

pREST (PostgreSQL REST), is a simple production-ready API, that delivers an instant, realtime, and high-performance application on top of your existing or new Postgres database.

PostgreSQL version 9.5 or higher

Contributor License Agreement -

Problems we solve

The pREST project is the API that addresses the need for fast and efficient solution in building RESTful APIs on PostgreSQL databases. It simplifies API development by offering:

1. A lightweight server with easy configuration;
2. Direct SQL queries with templating in customizable URLs;
3. Optimizations for high performance;
4. Enhanced developer productivity;
5. Authentication and authorization features;
6. Pluggable custom routes and middlewares.

Overall, pREST simplifies the process of creating secure and performant RESTful APIs on top of your new or old PostgreSQL database.

Read more.

Why we built pREST

When we built pREST, we originally intended to contribute and build with the PostgREST project, although it took a lot of work as the project is in Haskell. At the time, we did not have anything similar or intended to keep working with that tech stack. We've been building production-ready Go applications for a long time, so building a similar project with Golang as its core was natural.

Additionally, as Go has taken a huge role in many other vital projects such as Kubernetes and Docker, and we've been able to use the pREST project in many different companies with success over the years, it has shown to be an excellent decision.

1-Click Deploy

Heroku

Deploy to Heroku and instantly get a realtime RESTFul API backed by Heroku Postgres:

Documentation

Visit https://docs.prestd.com/

MCP over HTTP

pREST can expose a read-only MCP-style HTTP endpoint at /_mcp on the same server that already serves catalog, CRUD, and script routes.

This route is intended to reuse the existing pREST request pipeline rather than introduce a separate process or transport. That means the MCP surface inherits the same deployment model, auth, ACL, and database routing behavior already used by the rest of the API.

Endpoint shape

• GET /_mcp returns a discovery payload with server metadata and available tools.
• POST /_mcp accepts JSON-RPC style requests for MCP operations.

Currently supported methods:

• initialize
• tools/list
• tools/call

Read-only tools

The first implementation is intentionally read-only. It exposes generic discovery tools and schema-aware table tools:

• prest.list_databases
• prest.list_schemas
• prest.list_tables
• prest.describe_table
• prest.select_table
• prest.select.{database}.{schema}.{table}

The schema-aware prest.select.{database}.{schema}.{table} tools are generated from the catalog and give MCP clients a stable, explicit read path for known tables.

Generated table tools now include typed input schemas derived from table metadata. MCP clients can inspect these schemas to discover:

• allowed columns
• allowed order_by fields (field and -field for descending)
• column-aware filters
• limit and offset

In multi-database mode, tool generation expands across registered aliases.

Example initialize request:

POST /_mcp
Content-Type: application/json

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "initialize"
}

Example tool call:

POST /_mcp
Content-Type: application/json

{
  "jsonrpc": "2.0",
  "id": 2,
  "method": "tools/call",
  "params": {
    "name": "prest.describe_table",
    "arguments": {
      "database": "prest-test",
      "schema": "public",
      "table": "test"
    }
  }
}

Example schema-aware table tool call with projection, filter, and ordering:

POST /_mcp
Content-Type: application/json

{
  "jsonrpc": "2.0",
  "id": 3,
  "method": "tools/call",
  "params": {
    "name": "prest.select.prest-test.public.test",
    "arguments": {
      "columns": ["id", "name"],
      "filters": {
        "name": "prest tester"
      },
      "order_by": ["id"],
      "limit": 5,
      "offset": 0
    }
  }
}

Safety model

• The MCP endpoint is read-only by design in the current version.
• Unsupported tools return 400 Bad Request.
• Existing pREST database routing and identifier validation still apply.
• Auth and ACL stay in the HTTP stack instead of being reimplemented in the MCP layer.
• When auth is enabled, /_mcp requires authentication like other protected routes.

Tests

The MCP route is covered by unit and integration tests, including live HTTP checks under integration/controllers/mcp_test.go and route coverage in integration/router/routes_test.go.

Multi-database

pREST uses the first URL path segment as the database selector for CRUD, catalog, and optional script routes. Two modes are supported:

URL routing

All table operations use /{database}/{schema}/{table}:

GET /tenant-a/public/users
POST /tenant-a/public/orders
GET /tenant-a/public
GET /_QUERIES/tenant-a/myqueries/get_all

Script routes accept an optional database prefix (/_QUERIES/{database}/{queriesLocation}/{script}). When omitted, the default database (pg.database) is used.

Request flow: validate alias → set connection context → open or reuse pool for that alias → execute query.

Configuration

Registry sources are merged in priority order: indexed env pairs → TOML (env wins on conflict).

Environment variables (production / Kubernetes)

Register databases with contiguous 1-based index pairs:

DATABASE_ALIAS_1=tenant-a
DATABASE_URL_1=postgres://user:pass@cluster-a.example.com:5432/app_a?sslmode=require
DATABASE_ALIAS_2=tenant-b
DATABASE_URL_2=postgres://user:pass@cluster-b.example.com:5432/app_b?sslmode=require

PREST_DATABASE_ALIAS_N and PREST_DATABASE_URL_N are accepted as aliases of the above keys.

See install-manifests/kubernetes/deployment.yaml for a multi-secret example with liveness/readiness probes.

TOML (local development)

pg.* remains the default/fallback profile; registry entries override host, port, and credentials per alias:

[pg]
database = "prest-test"
single = false

[[databases]]
alias = "prest-test"
host = "postgres"
port = 5432
database = "prest-test"
user = "postgres"
pass = "postgres"
ssl.mode = "disable"

[[databases]]
alias = "secondary-db"
host = "postgres-b"
port = 5432
database = "secondary-cluster"
user = "postgres"
pass = "postgres"
ssl.mode = "disable"

When no registry is configured, legacy DATABASE_URL / pg.* behavior is unchanged.

Alias vs physical database name

• URLs and access rules use the alias (e.g. tenant-a).
• Connection pools use the profile's database, host, and credentials (e.g. app_a on cluster-a.example.com).
• When alias equals the physical database name (legacy mode), behavior matches pre-registry pREST.

pg.single

Set pg.single = false to allow routing to multiple databases or aliases. When true and a registry is active, only the default database alias is accepted.

Connection pooling

Pools are keyed by connection URI; aliases that share the same URI share a pool. Connections are opened lazily on first request per alias.

Connection budgeting: plan for replicas × aliases × pg.maxopenconn connections per cluster. Use PgBouncer or RDS Proxy when many aliases are registered.

Health checks

Access control

access.tables entries support an optional database field for per-alias permissions:

[[access.tables]]
database = "tenant-a"
schema = "public"
name = "users"
permissions = ["read"]

Local testing

Multi-cluster integration tests live in integration/controllers/multicluster_test.go. They require