PostgreSQL

PostgreSQL

API Dock can serve PostgreSQL tables alongside file tables (Parquet, CSV, ... read by DuckDB). A PostgreSQL table is defined like any other table: in a version config's tables, in a shared schema, or as a shared global table (see Shared Database Config). It points at a named connection. Route SQL and [[table]] references work as described in SQL Database Support.

pip install 'api-dock[postgres]'     # psycopg (with its own libpq) and psycopg_pool

Connections and tables

Connections live in the shared api_dock_config/databases/config.yaml:

database:
  connections:
    core:
      host: db.example.com
      dbname: soundhub
      user: api_dock_readonly
      password: env:SOUNDHUB_DB_PASSWORD     # read from the environment at startup
      sslmode: verify-full
      sslrootcert: /etc/ssl/certs/provider-ca.pem
      pool:                                  # optional (defaults shown)
        min_size: 1
        max_size: 4
        timeout: 5                           # seconds a request waits for a connection
        max_waiting: 16
        startup_timeout: 3                   # seconds the startup probe waits
      statement_timeout_ms: 10000            # optional (default 10 s)

  schema:
    core_v1:
      recordings: {connection: core, table: public.recordings}   # PostgreSQL table
      projects:   {connection: core, table: public.projects}
    birdnet_2p4:
      detections: {uri: s3://bucket/birdnet/2.4/detections.parquet}   # file table

• A table with table: (its PostgreSQL name, schema.table or table) is a PostgreSQL table; one with uri: (or a plain string) is a file table.
• connection can come from meta, like region/public: meta: {connection: core} then recordings: {table: public.recordings}. File tables ignore a connection in meta.
• A connection's keys are libpq connection fields plus pool and statement_timeout_ms. options, default_transaction_read_only and statement_timeout can't be set (API Dock sets them). connect_timeout defaults to 5 seconds.
• Any value can be written env:NAME to read it from the environment variable NAME.
• Connection names and PostgreSQL table names must be lower case (letters, digits and underscores); each part of a table name is at most 63 bytes.
• Use sslmode: verify-full with your provider's CA bundle for databases reached over a network.
• Connect with a login that can only SELECT from the tables it serves.

Which engine runs a route

API Dock decides per route, from the tables the route can reference (every selector branch and query param, including union members):

The route's tables	Engine
All on one PostgreSQL connection (unions included)	PostgreSQL, natively, through the connection's pool
Files only, or no tables	DuckDB (as before)
A mix of files and PostgreSQL, or PostgreSQL tables on more than one connection	DuckDB, which attaches the PostgreSQL connections read-only

The engine decides the SQL dialect: native routes are PostgreSQL SQL, DuckDB routes are DuckDB SQL. A shared route that runs on different engines for different versions (e.g. a Parquet version and a PostgreSQL version) needs SQL valid in both; plain SELECT/WHERE/GROUP BY/COUNT/UPPER/||/ILIKE are.

An optional engine: on a route checks or forces the choice:

- route: recordings/{{id}}
  engine: postgres     # startup fails if this route can't run natively on one connection
  sql: SELECT * FROM [[recordings]] WHERE id = {{id}}

- route: everything
  engine: duckdb       # run on DuckDB even though it could run natively
  sql: SELECT * FROM [[*.recordings]] r

Choosing the engine is a config lookup; it needs no database round trip.

Unions and mixing sources

[[*.table]], [[*!.table]] and schema-group unions work with PostgreSQL tables, and can mix PostgreSQL and file tables. source_columns and {{self.*}} work as usual (see Cross-Schema Queries).

• Same connection: the union runs natively. PostgreSQL has no UNION ALL BY NAME, so API Dock lines columns up by name itself, using typed NULLs for columns a table lacks. It reads each table's columns once and keeps them until restart (a changed table definition needs a restart).
• Mixed or several connections: the union runs on DuckDB with the PostgreSQL connections attached read-only; the union, joins and aggregations run in DuckDB.

SQL details (native routes)

• [[table]] is written as quoted names: "public"."recordings" AS "recordings" after FROM/JOIN, "recordings" elsewhere. [[schema.table]] gets no alias, so FROM [[core_v1.recordings]] r works.
• Write % as usual: API Dock doubles it for psycopg (LIKE '%' || {{q}} || '%').
• Values are bound parameters, sent as text and converted by PostgreSQL to the column's type. A value that can't be converted returns 500 Database query error.

Safety

Every connection (native or attached) uses read-only transactions, the statement timeout and UTC. Each native query runs in its own transaction, rolled back after the rows are read, and is sent as a prepared statement, so multi-statement SQL fails. These guard against config mistakes; they don't replace a SELECT-only login.

Responses and errors

• PostgreSQL types become JSON: dates/times as ISO strings (times with a zone in UTC), numerics as numbers (floats, which may lose precision), UUIDs and network addresses as strings, intervals as seconds, bytes as base64 text, arrays as lists, JSON/JSONB as JSON.
• 503 Database unavailable: the database can't be reached, no connection is free within pool.timeout, pool.max_waiting requests are already waiting, or (on DuckDB routes) a connection can't be attached. Queries are never retried.
• 500 Database query error: invalid SQL, a value of the wrong type, a permission error, or a query over the statement timeout.

Startup and lifecycle

• At startup API Dock checks every connection entry, every PostgreSQL table (known connection, valid name) and every route's engine (engine: postgres that can't be honoured stops startup).
• The FastAPI server (api-dock start) opens one pool per connection when it starts and closes them when it stops. env: values are read then; an unset variable, or a field the installed libpq doesn't know, stops startup. A database that can't be reached is logged as a warning; its routes return 503 until it's reachable.
• Connections are fixed at startup (changing a host or rotating a password needs a restart); database configs and routes are still read on each request.
• Pools are shared by every database/version using a connection. To keep a heavy route from slowing others, give it its own connection name (same database, separate pool).
• Flask is not supported with PostgreSQL connections (--backbone flask refuses the config).

Using RouteMapper directly (see Python API and Deployment):

from api_dock.route_mapper import RouteMapper

mapper = RouteMapper("api_dock_config/config.yaml")
await mapper.start()          # opens the pools (on this event loop)
try:
    response = await mapper.map_database_route("core", "1.0/recordings/7")
    print(response.status_code, response.content)   # a ProxyResponse
finally:
    await mapper.aclose()

Without start(), a route that runs natively on PostgreSQL returns a 500 error saying the mapper wasn't started. Use the mapper on the event loop that started it; aclose() is safe to call more than once.