PostgreSQL to REST API and MCP Server

How do I turn a PostgreSQL database into a REST API? Add the database to Faucet, create an API key, and start the server. Every table is then available at /api/v1/mydb/_table/<table> with filters, sorting and pagination, described by an OpenAPI 3.1 spec at /openapi.json. AI agents can use the same database through the MCP endpoint at /mcp, limited by the same API key's role.

bash

faucet db add --name mydb --driver postgres \
  --host localhost --user myuser --password-prompt --database mydb
faucet role create --name reader --verbs GET
faucet key create --role reader          # prints the key once
faucet serve

curl "http://localhost:8080/api/v1/mydb/_table/customers?limit=10" \
  -H "X-API-Key: faucet_YOUR_KEY"

Prefer a browser? The admin UI does the same with a form and a Test connection button. To give Claude, Cursor or another AI agent access, see Give AI Agents Access.

This tutorial walks you through connecting a PostgreSQL database to Faucet and querying it via the REST API.

Prerequisites

Faucet installed and running (faucet serve) — see Getting Started
An admin account created (faucet admin create)
A PostgreSQL server with a database and at least one table
Supported versions: PostgreSQL 9.6 through 17, self-hosted or on Amazon RDS, Aurora, Supabase, Neon or Azure Database

Step 1: Connect PostgreSQL to Faucet

The DSN format for PostgreSQL:

postgres://username:password@host:port/database?sslmode=disable

Via Admin UI (easiest)

Open http://localhost:8080 and sign in.
Go to Databases and click Add database.
Pick PostgreSQL and fill in:
- Host and Port: for example localhost and 5432 (the default)
- Database: the database name, for example mydb
- Username and Password: special characters need no escaping
- SSL mode: leave Prefer for local databases; choose Require for hosted ones such as Amazon RDS, Supabase or Neon
Check the API name. It is filled in from the database name and becomes the URL prefix (/api/v1/<name>).
Click Test connection. Faucet connects and reports how many tables it found, or explains what went wrong.
Click Add database. The REST API and MCP tools are live right away.

See Admin UI for screenshots of this flow.

Via CLI

With separate connection flags (Faucet builds and escapes the connection string):

bash

faucet db add --name mydb --driver postgres \
  --host localhost --port 5432 --user myuser --password 's3cret@!' --database mydb

Or with a connection string:

bash

faucet db add \
  --name mydb \
  --driver postgres \
  --dsn "postgres://user:pass@localhost:5432/mydb?sslmode=disable"

TIP

A server that is already running loads databases added with faucet db add on its next start (faucet stop && faucet serve). Databases added in the admin UI or through the API are live immediately.

Via API

bash

TOKEN=$(curl -s http://localhost:8080/api/v1/system/admin/session \
  -H "Content-Type: application/json" \
  -d '{"email":"admin@example.com","password":"changeme123"}' \
  | jq -r '.session_token')

curl -X POST http://localhost:8080/api/v1/system/service \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "mydb",
    "driver": "postgres",
    "dsn": "postgres://user:pass@localhost:5432/mydb?sslmode=disable"
  }'

Step 2: Verify the Connection

bash

faucet db test mydb

Or list tables via the API:

bash

curl http://localhost:8080/api/v1/mydb/_table \
  -H "Authorization: Bearer $TOKEN"

Expected response:

json

{
  "resource": [
    {"name": "customers"},
    {"name": "orders"},
    {"name": "products"}
  ]
}

Step 3: Query Your Data

List all records

bash

curl "http://localhost:8080/api/v1/mydb/_table/customers?limit=10" \
  -H "Authorization: Bearer $TOKEN"

Filter records

bash

curl "http://localhost:8080/api/v1/mydb/_table/orders?filter=status%20%3D%20'shipped'" \
  -H "Authorization: Bearer $TOKEN"

Select specific fields

bash

curl "http://localhost:8080/api/v1/mydb/_table/customers?fields=name,email" \
  -H "Authorization: Bearer $TOKEN"

Paginate

bash

curl "http://localhost:8080/api/v1/mydb/_table/orders?limit=10&offset=0&order=created_at%20DESC" \
  -H "Authorization: Bearer $TOKEN"

Count records

bash

curl "http://localhost:8080/api/v1/mydb/_table/orders?include_count=true" \
  -H "Authorization: Bearer $TOKEN"

Step 4: Insert, Update, and Delete

Insert a record

bash

curl -X POST http://localhost:8080/api/v1/mydb/_table/customers \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"resource": [{"name": "Acme Corp", "email": "contact@acme.com"}]}'

PostgreSQL supports RETURNING, so the response includes server-generated values:

json

{
  "resource": [
    {"id": 42, "name": "Acme Corp", "email": "contact@acme.com", "created_at": "2024-01-15T10:30:00Z"}
  ]
}

Update a record

bash

curl -X PATCH "http://localhost:8080/api/v1/mydb/_table/customers?ids=42" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "Acme Corporation"}'

Delete a record

bash

curl -X DELETE "http://localhost:8080/api/v1/mydb/_table/customers?ids=42" \
  -H "Authorization: Bearer $TOKEN"

PostgreSQL-Specific Notes

RETURNING clause

PostgreSQL fully supports RETURNING. INSERT and UPDATE responses include the actual row data with server-generated values (serial IDs, defaults, triggers).

Schema selection

PostgreSQL organizes tables into schemas. The default is public. To use a different schema:

bash

faucet db add \
  --name mydb \
  --driver postgres \
  --dsn "postgres://user:pass@localhost/mydb?sslmode=disable&search_path=myschema" \
  --schema myschema

Hosted PostgreSQL (Amazon RDS, Aurora, Supabase, Neon)

Hosted PostgreSQL services require TLS. In the admin UI, set SSL mode to Require. On the command line, pass --ssl-mode require, or add sslmode=require to the connection string:

bash

faucet db add --name mydb --driver postgres \
  --host db.example.com --user api --password 's3cret@!' --database mydb --ssl-mode require

# or, with a connection string from your provider's dashboard
faucet db add --name mydb --driver postgres \
  --dsn "postgres://user:pass@db.example.com:5432/mydb?sslmode=require"

Use the host, port, database and user shown in your provider's connection settings. Make sure the database accepts connections from the machine running Faucet (security groups or IP allow-lists). See Database Connectors → PostgreSQL for every DSN parameter.

Stored procedures

Faucet can execute PostgreSQL stored procedures and functions:

bash

curl -X POST http://localhost:8080/api/v1/mydb/_proc/get_active_users \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"params": {"min_age": 18}}'

Supported features

Feature	Supported
Schema introspection	Yes
RETURNING clause	Yes
Upsert (ON CONFLICT)	Yes
Stored procedures	Yes
Views	Yes
Parameter style	`$1, $2, $3`
Default schema	`public`

Give AI Agents Access (MCP)

faucet serve also runs an MCP server at http://localhost:8080/mcp. Connect an agent with an API key, and the key's role decides which tables it can read or change. For Claude Code:

bash

claude mcp add --transport http faucet http://localhost:8080/mcp \
  --header 'X-API-Key: faucet_YOUR_KEY'

The agent can then call faucet_list_tables, faucet_describe_table and faucet_query on mydb. For Cursor, VS Code, Windsurf, Claude Desktop and ChatGPT, see MCP Server → Connect an AI agent. Use a read-only role for agents unless they need to write.

Troubleshooting

"password authentication failed"

Check your username and password. PostgreSQL uses pg_hba.conf to control authentication methods per host.

"no pg_hba.conf entry for host"

Your client IP is not allowed in pg_hba.conf. Add an entry for your IP or use sslmode=require.

"SSL is not enabled on the server"

Add ?sslmode=disable to the DSN for local development, or enable SSL on your PostgreSQL server for production.

"relation does not exist"

The table doesn't exist in the specified schema. Check your search_path or --schema setting, and verify the table exists:

sql

\dt myschema.*

What's Next

Filter Syntax -- query filtering operators
RBAC -- restrict access per role
API Reference -- full REST API documentation
Database Connectors -- all connector DSN formats

PostgreSQL to REST API and MCP Server ​

Prerequisites ​

Step 1: Connect PostgreSQL to Faucet ​

Via Admin UI (easiest) ​

Via CLI ​

Via API ​

Step 2: Verify the Connection ​

Step 3: Query Your Data ​

List all records ​

Filter records ​

Select specific fields ​

Paginate ​

Count records ​

Step 4: Insert, Update, and Delete ​

Insert a record ​

Update a record ​

Delete a record ​

PostgreSQL-Specific Notes ​

RETURNING clause ​

Schema selection ​

Hosted PostgreSQL (Amazon RDS, Aurora, Supabase, Neon) ​

Stored procedures ​

Supported features ​

Give AI Agents Access (MCP) ​

Troubleshooting ​

"password authentication failed" ​

"no pg_hba.conf entry for host" ​

"SSL is not enabled on the server" ​

"relation does not exist" ​

What's Next ​

PostgreSQL to REST API and MCP Server

Prerequisites

Step 1: Connect PostgreSQL to Faucet

Via Admin UI (easiest)

Via CLI

Via API

Step 2: Verify the Connection

Step 3: Query Your Data

List all records

Filter records

Select specific fields

Paginate

Count records

Step 4: Insert, Update, and Delete

Insert a record

Update a record

Delete a record

PostgreSQL-Specific Notes

RETURNING clause

Schema selection

Hosted PostgreSQL (Amazon RDS, Aurora, Supabase, Neon)

Stored procedures

Supported features

Give AI Agents Access (MCP)

Troubleshooting

"password authentication failed"

"no pg_hba.conf entry for host"

"SSL is not enabled on the server"

"relation does not exist"

What's Next