# PostgreSQL to REST API and MCP Server

Source: https://wiki.faucetdb.ai/tutorial-postgres

**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](https://wiki.faucetdb.ai/admin-ui#connect-a-database) 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](https://wiki.faucetdb.ai/tutorial-postgres#give-ai-agents-access-mcp).

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](https://wiki.faucetdb.ai/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)

1. Open `http://localhost:8080` and sign in.
2. Go to **Databases** and click **Add database**.
3. 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
4. Check the **API name**. It is filled in from the database name and becomes the URL prefix (`/api/v1/<name>`).
5. Click **Test connection**. Faucet connects and reports how many tables it found, or explains what went wrong.
6. Click **Add database**. The REST API and MCP tools are live right away.

See [Admin UI](https://wiki.faucetdb.ai/admin-ui#connect-a-database) 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](https://wiki.faucetdb.ai/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](https://wiki.faucetdb.ai/rbac) 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](https://wiki.faucetdb.ai/mcp-server#connect-claude-code). 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](https://wiki.faucetdb.ai/filter-syntax) -- query filtering operators
- [RBAC](https://wiki.faucetdb.ai/rbac) -- restrict access per role
- [API Reference](https://wiki.faucetdb.ai/api-reference) -- full REST API documentation
- [Database Connectors](https://wiki.faucetdb.ai/database-connectors) -- all connector DSN formats