SQL Database Support

SQL Database Support

A database config maps URL routes to SQL queries. API Dock runs each query with DuckDB over files (local disk, S3, GCS, Azure, HTTP(S)) or on PostgreSQL, and returns the rows as JSON.

# api_dock_config/config.yaml
databases:
  - users_db

# api_dock_config/databases/users_db.yaml
name: users_db
description: User records

tables:
  users: s3://my-bucket/users.parquet
  permissions: s3://my-bucket/permissions.parquet

routes:
  - route: users
    sql: SELECT [[users]].* FROM [[users]]

  - route: users/{{user_id}}
    sql: SELECT [[users]].* FROM [[users]] WHERE [[users]].user_id = {{user_id}}

GET /users_db/users/42 runs

SELECT users.* FROM 's3://my-bucket/users.parquet' AS users WHERE users.user_id = ?   -- ? = '42'

and returns [{"user_id": 42, "name": "...", ...}].

Config files

List each database under databases: in the main config. API Dock reads its config from the databases/ folder next to the main config file:

Layout	URL
databases/<name>.yaml	/<name>/<route>
databases/<name>/<version>.yaml (one file per version)	/<name>/<version>/<route>, and /<name>/latest/<route>

See Versioning for how versions and latest work. A database/version can also be defined inline in the shared databases/config.yaml (slugs); see Shared Database Config.

Keys

Key	Required	Description
name	no	The database's name, for people reading the config. The URL uses the name listed in the main config.
description, authors	no	Descriptive metadata.
schema	no	A shared schema (from databases/config.yaml) that unqualified [[table]] references fall back to. See Shared Database Config.
tables	no*	Table definitions: name: <definition>.
queries	no	Named SQL queries, referenced as [[query_name]].
routes	yes	List of routes, each with route and sql (and optional query_params).
query_params	no	Query params applied to every route in this file. See Query Parameters.

* Tables can also come from the shared config (global tables and schemas), so a version file may have no tables of its own.

Database configs also take the cookies and authentication settings described in Authentication and Cookies.

Tables

A table definition is a URI string, or a mapping with uri (or path) plus storage settings:

tables:
  users: s3://my-bucket/users.parquet                   # S3
  events: s3://my-bucket/events/**/*.parquet            # glob over many files
  permissions: gs://my-bucket/permissions.parquet       # Google Cloud Storage
  posts: az://my-container/posts.parquet                # Azure
  regions: https://example.org/data/regions.parquet     # HTTP(S)
  local_data: data/local_data.parquet                   # local file

  audit_log:
    uri: s3://my-private-bucket/audit.parquet
    region: us-east-1
    public: false

The storage backend comes from the URI prefix: s3:// (or s3a://), gs://, az:// (or azure://), http:///https://, and anything else is a local path (resolved by DuckDB, relative to the directory API Dock runs in).

File formats. Parquet is the tested format. The URI is handed to DuckDB as '<uri>', so DuckDB picks the reader from the file extension: .csv and .json files also work.

Storage settings

Key	Backend	Meaning
region	S3	AWS region. Defaults to AWS_DEFAULT_REGION or AWS_REGION; without one, DuckDB detects it (which can cause redirects).
public	S3	true: try anonymous access first, then fall back to the AWS credential chain.
public	GCS	true: read without credentials.
service_account	GCS	Path to a service account JSON file. DuckDB reads it from the process-wide GOOGLE_APPLICATION_CREDENTIALS, so it's set only if that variable is unset, and one server uses one service account (a different one on another table is ignored with a warning).
key_id, secret	GCS	HMAC key pair. Without them, the GCS credential chain is used.
endpoint	GCS	Custom endpoint, used with key_id/secret.
bearer_token	HTTP(S)	Sent as Authorization: Bearer <token>.
auth_headers	HTTP(S)	Mapping of extra request headers.
cookies	HTTP(S)	Mapping sent as a Cookie header.

Without settings, S3 uses the AWS credential chain (environment variables, ~/.aws files, IAM roles, SSO) and Azure uses the Azure credential chain (for example AZURE_STORAGE_CONNECTION_STRING).

Each request sets up storage access for the version's tables plus any shared tables the query references. S3 region and public can differ from table to table (each such table gets its own path-scoped secret). GCS and HTTP settings are merged into one set per backend, so tables on the same backend should use the same credentials.

Default settings for every table (for example one region for all) go in the shared config's meta; see Shared Database Config.

PostgreSQL tables

A mapping with connection and table (instead of uri) is a PostgreSQL table:

tables:
  recordings: {connection: core, table: public.recordings}

Connections are defined in the shared databases/config.yaml. See PostgreSQL for connections, which engine runs a route, and SQL differences.

Table references: [[table]]

Write [[name]] to refer to a table. How it expands depends on where it is:

Reference	After FROM / JOIN	Anywhere else
[[users]]	's3://my-bucket/users.parquet' AS users	users
[[schema.table]] (shared schema)	schema.table	table
[[*.table]], [[group.table]] (union)	a UNION ALL BY NAME subquery	not allowed

Rules:

• Don't add an alias after an unqualified reference. FROM [[users]] already ends in AS users, so FROM [[users]] u is invalid SQL. Refer to the table as users (or [[users]]).
• A qualified [[schema.table]] has no alias, so you may add one: FROM [[birdnet_2p4.detections]] d.
• A union reference must be given an alias: FROM [[*.detections]] detections. See Cross-Schema Queries.
• A reference is a table source when it directly follows FROM or JOIN (any whitespace, or an opening parenthesis, in between), or follows a comma in a FROM list. Everywhere else it becomes the table name, so FROM [[a]] JOIN [[b]] ON [[a]].id = [[b]].id works. (Before 0.10.0 a reference counted as a table source whenever FROM or JOIN appeared in the 20 characters before it, which broke short clauses like that one.)

An unqualified name is looked up first in the version's tables, then in its schema, then in the shared global tables. See Shared Database Config.

Named queries: [[query]]

Put long SQL in queries and use [[query_name]] as a route's whole sql:

queries:
  user_permissions: |
    SELECT users.user_id, users.name, permissions.permission_name
    FROM [[users]]
    JOIN [[permissions]] ON users.user_id = permissions.user_id
    WHERE users.user_id = {{user_id}}

routes:
  - route: users/{{user_id}}/permissions
    sql: "[[user_permissions]]"

Quote the value in YAML ("[[...]]"), since a bare [[ starts a YAML list. A named query is only recognized when it is the entire sql (or the entire SQL of a Conditional SQL branch); inside longer SQL, [[name]] is read as a table. Named queries can use [[table]] references and {{variables}}.

Routes and path variables

Each route has a route pattern and sql. {{name}} in the pattern matches one path segment and makes its value available as {{name}} in the SQL:

routes:
  - route: groups/{{group_id}}/items
    sql: SELECT [[items]].* FROM [[items]] WHERE [[items]].group_id = {{group_id}}

• Leading and trailing slashes in the pattern are ignored.
• A request matches a route when it has the same number of segments and every fixed segment is equal. The first matching route in the list wins (a version's own routes come before shared ones).
• sql can also be a rule list that picks a different query per request; see Conditional SQL.
• query_params add filters, sorting, paging and fixed responses driven by the query string; see Query Parameters.

{{variables}} in SQL can come from path segments, query string values, query param defaults, and cookies ({{cookies.<name>}}, for cookies allowed by the cookies setting). {{self.schema}}, {{self.name}} and {{self.version}} give the database/version being queried (see Cross-Schema Queries). A variable with no value gives a 500 SQL query error.

Route listing

Request	Response
GET /<db>/<version>/ (or /<db>/ for an unversioned database)	{"routes": ["users", "users/{{user_id}}", ...]}
GET /<db>/ for a versioned database	{"versions": ["0.1", "0.2"]}

The route list includes routes added from the shared config.

How values reach the database

API Dock never pastes request values into SQL. Each {{variable}} in sql, queries, and query param sql, multivalue_sql and conditional sql becomes a placeholder, and its value is sent to the database separately (a bound parameter). The database converts the value to the column's type, so number, date and boolean filters work as written. A value that isn't valid for its column, such as ?age=25 OR true against an integer column, is rejected with an error instead of being run as SQL.

Because values are sent separately, write variables without quotes:

Write	Not
department = {{department}}	department = '{{department}}'
UPPER(name) = UPPER({{name}})	UPPER(name) = UPPER('{{name}}')
name ILIKE '%' || {{name}} || '%'	name ILIKE '%{{name}}%'

• For compatibility with configs written for 0.8.x and earlier, a string that is exactly one variable ('{{department}}') is read as {{department}}.
• Any other quoted variable, like '%{{name}}%', would be literal text, so API Dock refuses to start and prints the fixed form. Build patterns with || as in the last row.
• A variable can't be a column name ("{{column}}"), and can't be inside a SQL comment.
• A template can't end inside a -- comment, because API Dock appends WHERE conditions and sql_append clauses on the same line. Put the comment on its own line or use /* */.

sql_append is the exception. Its values are column names, ASC/DESC or numbers, which can't be sent as bound values, so they are written into the SQL text. A value must be a non-negative integer or a comma-separated list of column names (optionally qualified, each optionally followed by ASC/DESC and NULLS FIRST/LAST); see Query Parameters. Any other value gives a 500 SQL query error. The same check applies to the sql_append of a Conditional SQL branch.

Startup checks

When API Dock starts, it checks every database and version listed in the main config, as requests will see them: merged with the main config and with the shared routes and query params that apply to that version. If anything is wrong it refuses to start and names the database, version, route and template:

ValueError: Database 'owl' version '4.0', route '/detections/{{id}}/overlaps': sql: Table 'all_modelz.detections' not found in database configuration

It checks that:

• the shared databases/config.yaml is valid (slugs, schema_groups, and the types of its sections)
• every route and query param has a valid shape (for example, sql is a string or a rule list, and each query param has at least one known key)
• no {{variable}} is inside a quoted string (other than a string that is exactly one variable), a quoted identifier or a comment, and no template ends inside a comment
• every [[table]], [[schema.table]], [[*.table]] and [[group.table]] reference resolves, unions are used only after FROM/JOIN, and source_columns is valid
• PostgreSQL tables name a known connection, and a route's engine (if set) is possible

It does not run the SQL, so SQL syntax errors and missing columns show up on the first request.

Responses

A successful query returns 200 with a JSON array, one object per row, keyed by column name ([] when no rows match). Values are converted to JSON:

Database type	JSON
date, time, timestamp	ISO 8601 string
decimal	number (float; may lose precision)
bytes / blob	base64 string
UUID, IP address	string
interval	number of seconds
list, struct / map	array, object (converted item by item)

Errors

Errors are JSON, usually {"error": "<message>"}.

Status	When
400	A Conditional SQL rule list matched nothing ("No matching query configuration for the given parameters"), or a required query param is missing. Both can be customized.
404	Unknown database, version, or route ("Route 'x' not found in database 'db'").
500 SQL query error	The SQL couldn't be built: unknown table or named query, a variable with no value, or an sql_append value that fails the character check.
400 Invalid value for a query parameter	A request value can't be converted to the type it's compared with, e.g. ?age=abc for an integer column or a timestamp for a number column. detail has the database's message (value and type, no SQL). Since 0.11.0; before, this was a 500.
500 Database query error	The database rejected the query (bad SQL, unknown column, bad data in a table, a permission error).
503 Database unavailable	A PostgreSQL connection can't be reached. See PostgreSQL.

Authentication failures return the status and body set in the database's authentication config; see Authentication and Cookies.

Performance tips

• Prefer COUNT(*) over COUNT(DISTINCT ...) when rows are already unique. If a join would duplicate rows, aggregate the joined table first, then join:

  SELECT detections.common_name, COUNT(*) AS count,
         COALESCE(SUM(revisions.revcount), 0) AS revcount
  FROM [[detections]]
  LEFT JOIN (
    SELECT observation_id, COUNT(*) AS revcount FROM [[revisions]] GROUP BY observation_id
  ) revisions ON revisions.observation_id = detections.id

  In a real deployment this cut a histogram query's memory use about 10x compared with COUNT(DISTINCT detections.id) over the joined rows.

• Prefer sql_append for ORDER BY, GROUP BY and LIMIT that query params control. Query param filters join the base query's top-level WHERE and go before a trailing GROUP BY / ORDER BY / LIMIT in the base SQL (since 0.10.0; earlier versions appended them to the end, which broke such queries). sql_append clauses are added at the end. See Query Parameters.

• Queries run in worker threads, each on its own in-memory DuckDB connection. On small servers, set DuckDB's memory_limit and threads and cap concurrent queries with max_concurrent_queries under settings.duckdb; see Configuration.

• Use Parquet, and globs (**/*.parquet) for partitioned data.