db.opelly.me — Postgres over HTTPS

A gateway to the shared Postgres 17 cluster. Each API key is bound to one database and one access level (read or full), and Postgres itself enforces it. Use the key with the HTTPS JSON API from anywhere (serverless functions, scripts, agents), or as the password in a normal postgres:// URL so ORMs, drivers and migration tools work unchanged.

  • HTTPhttps://db.opelly.me/api/v1
  • Postgrespg.opelly.me:443
  • Version0.1.0

For agents: read https://db.opelly.me/llms.txt (the same content as Markdown). OpenAPI 3.1: /api/openapi.json.

Overview#

Two ways in, same key, same permissions:

UseWhen
postgres:// URL The app uses an ORM or migration tool (Prisma, Drizzle, sqlx, SQLAlchemy, Django…), needs transactions spanning several round-trips, LISTEN/NOTIFY, COPY, or a long-lived connection pool.
HTTP JSON API Serverless/edge functions, scripts, agents, cron jobs, anything that can't hold a TCP connection. Atomic multi-statement work goes through /api/v1/transaction.
MethodPathAuthPurpose
GET/api/v1/whoamikeyKey's database, access, limits, endpoints
GET/api/v1/schemakeyTables, columns, keys, indexes, enums
POST/api/v1/querykeyRun one SQL statement
POST/api/v1/transactionkeyRun up to 100 statements atomically
GET/llms.txt, /api/openapi.json, /healthz—Docs, spec, health

Keys & auth#

Keys look like opdb_<id>_<secret> — a 10-character id ([a-z0-9]) and a 32-character secret ([A-Za-z0-9]). Each key is bound to exactly one database and one access level:

  • read — SELECT only. Writes and DDL fail with sqlstate 25006 (read-only transaction) or 42501 (permission denied), returned as HTTP 403.
  • full — acts as the database's owner role: INSERT/UPDATE/DELETE, CREATE/ALTER/DROP, migrations.

Keys can also carry an expiry, an IP/CIDR allowlist, a custom row cap, a custom statement timeout, and a query-logging switch. Keys are created, listed and revoked in the admin UI (behind SSO); the full key is shown once.

Authorization: Bearer opdb_k7x2m9q4ab_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# or
X-Api-Key: opdb_k7x2m9q4ab_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Treat a key like a password: keep it in an environment variable (DB_API_KEY / DATABASE_URL), never in source code, logs, or browser bundles.

Quick start#

export DB_API_KEY=opdb_xxxxxxxxxx_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

# 1. What can this key do?
curl -s https://db.opelly.me/api/v1/whoami -H "Authorization: Bearer $DB_API_KEY"

# 2. What tables exist?
curl -s https://db.opelly.me/api/v1/schema -H "Authorization: Bearer $DB_API_KEY"

# 3. One statement, with parameters
curl -s https://db.opelly.me/api/v1/query -H "Authorization: Bearer $DB_API_KEY" \
  -H 'Content-Type: application/json' \
  -d '{"sql": "select id, name from users where id = $1", "params": [42]}'

# 4. Several statements, all or nothing
curl -s https://db.opelly.me/api/v1/transaction -H "Authorization: Bearer $DB_API_KEY" \
  -H 'Content-Type: application/json' \
  -d '{"statements": [
        {"sql": "insert into orders(user_id) values ($1) returning id", "params": [42]},
        {"sql": "update users set order_count = order_count + 1 where id = $1", "params": [42]}
      ]}'

POST /api/v1/query#

Runs exactly one SQL statement. Only sql is required.

Request

{
  "sql": "select id, name from users where id = $1",
  "params": [42],
  "row_mode": "object",
  "max_rows": 1000,
  "timeout_ms": 5000
}

Response 200

{
  "command": "SELECT",
  "row_count": 1,
  "columns": [{"name": "id", "type": "int4"},
              {"name": "name", "type": "text"}],
  "rows": [{"id": 42, "name": "Ada"}],
  "notices": [],
  "warnings": [],
  "duration_ms": 1.7
}
  • row_mode: "object" (default) or "array" — rows as [[42, "Ada"]], ordered like columns. Duplicate column names in object mode get _2, _3 suffixes and a warnings entry.
  • max_rows and timeout_ms can only lower the key's limits.
  • row_count is rows returned for SELECT / RETURNING, rows affected for INSERT/UPDATE/DELETE (from the command tag).
  • One statement per request: ;-separated scripts are rejected. Transaction control (BEGIN, COMMIT, ROLLBACK, …) is rejected — use /transaction.
  • Every statement runs in its own transaction. If the result would exceed max_rows or the 16 MiB response cap, the transaction is rolled back and you get 413 result_too_large, so a capped UPDATE … RETURNING never half-applies.
  • Statements that can't run inside a transaction (VACUUM, CREATE INDEX CONCURRENTLY, …) are retried outside one automatically.

POST /api/v1/transaction#

Runs up to 100 statements in a single transaction. Any failure rolls back everything, and the error carries statement_index (0-based).

Request

{
  "statements": [
    {"sql": "insert into t(a) values ($1) returning id",
     "params": [1]},
    {"sql": "update counters set n = n + 1"}
  ],
  "read_only": false,
  "row_mode": "object"
}

Response 200

{
  "results": [
    {"command": "INSERT", "row_count": 1,
     "columns": [{"name": "id", "type": "int8"}],
     "rows": [{"id": 7}], "notices": [], "warnings": []},
    {"command": "UPDATE", "row_count": 1,
     "columns": [], "rows": [], "notices": [], "warnings": []}
  ],
  "duration_ms": 3.1
}

Statements can't pass results to each other through params. When a later statement needs an earlier value, use a CTE in one statement (with x as (insert … returning id) insert … select id from x) or a postgres:// connection.

GET /api/v1/schema#

Describes every table and view the key can see. Optional ?schema=public filter. estimated_rows comes from planner statistics, so it's cheap but approximate.

{
  "database": "town",
  "schemas": ["public"],
  "tables": [{
    "schema": "public", "name": "users", "kind": "table", "comment": null, "estimated_rows": 120,
    "columns": [{"name": "id", "type": "bigint", "nullable": false,
                 "default": "nextval('users_id_seq'::regclass)", "comment": null}],
    "primary_key": ["id"],
    "foreign_keys": [{"name": "posts_user_id_fkey", "columns": ["user_id"],
                      "references": {"schema": "public", "table": "users", "columns": ["id"]}}],
    "indexes": [{"name": "users_pkey", "definition": "CREATE UNIQUE INDEX ...",
                 "unique": true, "primary": true}],
    "privileges": {"select": true, "insert": false, "update": false, "delete": false}
  }],
  "enums": [{"schema": "public", "name": "mood", "values": ["sad", "ok", "happy"]}]
}

kind is one of table, view, materialized_view, partitioned_table, foreign_table.

GET /api/v1/whoami#

{
  "key_id": "k7x2m9q4ab", "label": "blog prod", "database": "town", "access": "read",
  "expires_at": null,
  "limits": {"max_rows": 10000, "max_response_bytes": 16777216, "statement_timeout_ms": 30000,
             "max_concurrent_requests": 8, "max_postgres_connections": 10},
  "endpoints": {"query": "https://db.opelly.me/api/v1/query",
                "transaction": "https://db.opelly.me/api/v1/transaction",
                "schema": "https://db.opelly.me/api/v1/schema",
                "docs": "https://db.opelly.me/llms.txt"},
  "postgres_url": "postgresql://town:<key>@pg.opelly.me:443/town?sslmode=require"
}

Parameters#

Use $1, $2, … placeholders with values in params. Never build SQL by interpolating values into the string.

JSON paramSent to Postgres as
nullNULL
true / falseboolean
numbernumeric text (exact digits)
stringtext, converted by Postgres to the inferred type (uuid, timestamptz, date, enum, …)
objectJSON text (for json/jsonb parameters)
arrayPostgres array ({1,2,3}), or JSON text when the parameter's type is json/jsonb

Postgres infers each parameter's type from context; cast when it can't: select $1::int + 1, where id = any($1::bigint[]), values ($1::jsonb).

Type mapping#

PostgresJSON
NULLnull
booltrue / false
int2, int4, int8, oidnumber. int8 is exact in the JSON text, but JavaScript's JSON.parse loses precision above 253 — select col::text if that matters.
float4, float8, numericnumber (exact digits); NaN / Infinity → string
json, jsonbnested JSON
arraysJSON arrays, elements mapped recursively
timestamptz"2026-11-19T12:34:56.789+00:00" (session TimeZone is UTC)
timestamp / date / time"2026-11-19T12:34:56.789" / "2026-11-19" / "12:34:56"
bytea"\\x0a0b…" (Postgres hex text)
everything elsestring in Postgres text form (uuid, text, interval, inet, enums, pgvector, …)

Each entry in columns includes the Postgres type name (int4, text, timestamptz, _int4 for int4[], …).

Limits#

LimitDefault
Rows per statement result10,000 (per-key configurable up to 100,000)
Response size16 MiB
Statement timeout30 s (per-key configurable up to 300 s); the statement is cancelled in Postgres
Request body2 MiB
Concurrent HTTP requests per key8
Postgres (wire) connections per key10
Statements per transaction100

Paginate rather than pulling everything: order by id limit 500, then keyset where id > $1 order by id limit 500 with the last id seen. Avoid large OFFSETs, and aggregate in SQL instead of fetching rows to count them.

Errors#

{"error": {"code": "sql_error", "message": "relation \"userz\" does not exist", "sqlstate": "42P01",
           "detail": null, "hint": null, "position": 15, "statement_index": null,
           "docs": "https://db.opelly.me/#errors"}}

position is a 1-based character offset into sql; statement_index is set for /transaction.

HTTPcodeMeaning → what to do
400invalid_requestBad JSON, missing sql, multiple statements, or transaction control → fix the request; split scripts into /transaction statements.
400sql_errorPostgres rejected the statement → read message, hint, sqlstate; check /schema. Common: 23505 unique violation, 23503 foreign key, 42P01 unknown table, 42703 unknown column, 22P02 bad input syntax.
401unauthorizedMissing, unknown, revoked or expired key.
403forbiddenIP not in the allowlist, database disabled, permission denied (42501), or a write with a read key (25006). Retrying won't help; it needs a different key.
404not_foundUnknown route.
408timeoutThe statement exceeded its timeout and was cancelled in Postgres → add indexes/filters, paginate, or raise the key's timeout.
413result_too_largeOver max_rows or 16 MiB; nothing was applied → add LIMIT, paginate, select fewer columns.
429too_many_requestsPer-key concurrency limit or auth-failure rate limit → back off with jitter.
503upstream_unavailablePostgres unreachable or pool exhausted → retry with backoff.
500internalProxy bug → retry once; report if it persists.

Retry only 429 and 503 (and 408 if the statement is safe to repeat). Don't blindly retry non-idempotent writes after a timeout.

postgres:// URL#

postgresql://<database>:<key>@pg.opelly.me:443/<database>?sslmode=require
  • Port 443; TLS is mandatory. sslmode=require works with every driver; with libpq 16+ you can verify the certificate via sslmode=verify-full&sslrootcert=system (not for node-postgres and other non-libpq drivers, which treat it as a file path).
  • The password is the key. The database name must equal the key's database. The user name is ignored; by convention, use the database name.
  • Everything Postgres supports works (transactions, prepared statements, COPY, LISTEN/NOTIFY, migrations), within the key's privileges.
  • Both classic STARTTLS and sslnegotiation=direct work. Query cancel needs a client that sends cancel requests over TLS (libpq 17+, most native drivers); otherwise rely on statement_timeout.
  • Keep client pools small: at most 10 connections per key.
psql "postgresql://town:$DB_API_KEY@pg.opelly.me:443/town?sslmode=require"

Examples#

JavaScript — HTTP (fetch)

Works in Node 18+, Deno, Bun and Cloudflare Workers. Never ship a key to a browser.

async function sql(sql, params = []) {
  const res = await fetch("https://db.opelly.me/api/v1/query", {
    method: "POST",
    headers: { "Authorization": `Bearer ${process.env.DB_API_KEY}`, "Content-Type": "application/json" },
    body: JSON.stringify({ sql, params }),
  });
  const body = await res.json();
  if (!res.ok) throw new Error(`${body.error.code}: ${body.error.message}`);
  return body.rows;
}
const users = await sql("select id, name from users where created_at > $1 order by id limit 100", ["2026-01-01"]);

Python — HTTP (requests)

import os, requests
S = requests.Session()
S.headers["Authorization"] = f"Bearer {os.environ['DB_API_KEY']}"

def sql(sql, params=()):
    r = S.post("https://db.opelly.me/api/v1/query", json={"sql": sql, "params": list(params)}, timeout=60)
    body = r.json()
    if not r.ok:
        raise RuntimeError(f"{body['error']['code']}: {body['error']['message']}")
    return body["rows"]

rows = sql("select id, name from users where id = $1", [42])

Node — postgres:// (node-postgres)

import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, ssl: true, max: 5 });
const { rows } = await pool.query("select * from users where id = $1", [42]);

Python — postgres:// (psycopg 3)

import os, psycopg
with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    rows = conn.execute("select * from users where id = %s", (42,)).fetchall()

Go — postgres:// (pgx v5)

pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
var name string
err = pool.QueryRow(ctx, "select name from users where id = $1", 42).Scan(&name)

Rust — postgres:// (sqlx)

let pool = sqlx::postgres::PgPoolOptions::new().max_connections(5)
    .connect(&std::env::var("DATABASE_URL")?).await?;
let name: String = sqlx::query_scalar("select name from users where id = $1")
    .bind(42_i64).fetch_one(&pool).await?;

Prisma

// schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

# .env
DATABASE_URL="postgresql://town:<key>@pg.opelly.me:443/town?sslmode=require&connection_limit=5"

Migrations (prisma migrate deploy, sqlx migrate run, Alembic, …) need a full key.