Shared Database Config

Shared Database Config

The optional file api_dock_config/databases/config.yaml holds what several databases and versions have in common: shared tables and schemas, inline database/version definitions (slugs), named groups of schemas, and routes and query params added to every database/version. This page describes each section. For reading one table from many schemas at once, see Cross-Schema Queries.

# api_dock_config/databases/config.yaml
database:
  meta:                       # defaults for every table
    region: us-west-2
    public: true
  schema:
    birdnet_2p4:
      detections: s3://my-bucket/birdnet/2.4/detections.parquet
    owl_5p0:
      detections: s3://my-bucket/owl/5.0/detections.parquet

slugs:
  - name: birdnet
    version: "2.4"
    schema: birdnet_2p4
  - name: owl
    version: "5.0"
    schema: owl_5p0

routes:
  - route: detections/{{id}}
    sql: SELECT [[detections]].* FROM [[detections]] WHERE [[detections]].id = {{id}}

With birdnet and owl listed under databases: in the main config.yaml, this serves /birdnet/2.4/detections/{id} and /owl/5.0/detections/{id}, each reading its own schema's detections table.

File layout

Top-level key	Type	Purpose
database	mapping	Global tables, meta defaults, schema groups of tables, PostgreSQL connections
slugs	list	Database/version configs defined inline instead of as files
lookups	mapping	Queries whose rows generate slugs or fill values (see Lookups)
schema_groups	mapping	Named lists of schemas, for union queries
routes	list	Routes added to every database/version
query_params	list	Query params added to every database/version
route_inclusions, route_exclusions	list	Limit all shared routes to, or opt out of them for, some databases/versions
query_inclusions, query_exclusions	list	The same for shared query params

Every section is optional. A section with the wrong type (for example routes as a mapping) stops startup with an error naming the file.

The database mapping

Inside database, three keys are reserved:

Key	Meaning
meta	Default table metadata, applied to every table
schema	Named groups of tables
connections	Named PostgreSQL connections (see PostgreSQL)

Every other key is a global table, available as [[name]] in any database config.

database:
  sites: s3://my-bucket/shared/sites.parquet          # global table (string form)
  species:
    uri: s3://my-other-bucket/species.parquet         # global table (mapping form)
    region: us-west-1
    public: false

  meta:
    region: us-west-2
    public: true

  schema:
    birdnet_2p4:
      detections:
        uri: s3://my-bucket/birdnet/2.4/detections.parquet
      revisions:
        uri: s3://my-bucket/birdnet/2.4/revisions.parquet
    owl_5p0:
      detections:
        uri: s3://my-bucket/owl/5.0/detections.parquet
        public: false

A table is written the same way everywhere (global, in a schema, or in a version config's tables): a URI string, or a mapping with uri (or path) plus metadata such as region and public. A mapping with table: instead of uri: is a PostgreSQL table.

meta defaults

meta applies to every table: global tables, schema tables, and the tables in each version config's own tables. A table's own keys override meta. In the example above, species uses us-west-1 and public: false; every other table uses us-west-2 and public: true.

meta may also set connection, which PostgreSQL tables (table:) pick up; file tables ignore it.

Schemas

A schema is a named set of tables. Two ways to use one:

1. A version config names its schema with schema:, and its unqualified [[table]] references fall back to that schema.
2. Any route can address any schema directly as [[schema.table]].

connections

Named PostgreSQL connections. Tables point at one with connection: and table::

database:
  connections:
    core:
      host: db.example.com
      dbname: core
      user: api_dock_readonly
      password: env:CORE_DB_PASSWORD
  schema:
    core_v1:
      recordings: {connection: core, table: public.recordings}

See PostgreSQL for connection settings and how routes run on PostgreSQL.

A version config's schema: and [[table]] lookup

# api_dock_config/databases/birdnet/2.4.yaml
name: birdnet
schema: birdnet_2p4
tables:
  notes: s3://my-bucket/birdnet/2.4/notes.parquet     # only this version sees it

routes:
  - route: recordings/{{recording_id}}/detections
    sql: SELECT [[detections]].* FROM [[detections]] WHERE [[detections]].recording_id = {{recording_id}}

An unqualified [[name]] is looked up in this order, and the first match wins:

1. the version config's own tables
2. the shared schema named by its schema: key
3. the shared global tables

Here [[detections]] isn't in tables, so it comes from birdnet_2p4. To keep a table private to one database/version, define it in that version's tables. A schema: that names no shared schema stops startup with a message naming the database and version (since api_dock 0.10.0; before, it was silently skipped and lookup went on to the global tables).

How an unqualified [[table]] expands is covered in SQL Database Support: after FROM/JOIN it becomes '<uri>' AS table, so don't add your own alias after it.

[[schema.table]] from any route

Any route can read any schema's table, so owl/5.0 can query [[birdnet_2p4.detections]]. Qualified references are created as DuckDB views (CREATE SCHEMA birdnet_2p4 plus CREATE VIEW birdnet_2p4.detections), only for the tables a query actually references.

Where	[[birdnet_2p4.detections]] becomes
After FROM/JOIN	birdnet_2p4.detections (no alias)
Anywhere else	detections

Because no alias is added after FROM/JOIN, you can write your own: FROM [[birdnet_2p4.detections]] b. You can also use the full name in plain SQL, for example birdnet_2p4.detections.id.

- route: detections/
  sql: >
    SELECT detections.common_name, COUNT(notes.id) AS note_count
    FROM [[birdnet_2p4.detections]]
    LEFT JOIN [[notes]] ON notes.detection_id = birdnet_2p4.detections.id
    GROUP BY birdnet_2p4.detections.common_name

expands to

SELECT detections.common_name, COUNT(notes.id) AS note_count
FROM birdnet_2p4.detections
LEFT JOIN 's3://my-bucket/birdnet/2.4/notes.parquet' AS notes ON notes.detection_id = birdnet_2p4.detections.id
GROUP BY birdnet_2p4.detections.common_name

Rules:

• Outside FROM/JOIN, a qualified reference becomes the bare table name because DuckDB doesn't accept schema.table.*. So [[birdnet_2p4.detections]].* gives detections.*, which works when you haven't aliased the table. If you alias it (FROM [[birdnet_2p4.detections]] b), use the alias: b.*.
• Schema and table names used in [[schema.table]] must be plain identifiers (letters, digits, underscores, not starting with a digit).
• A [[schema.table]] that doesn't exist stops startup.

Storage credentials per table

Each table carries its own effective metadata (meta plus its own keys). For each query, API Dock sets up one default S3 secret, then adds a secret scoped to the table's path for every S3 table whose region or public differs from that default. DuckDB picks the secret with the longest matching scope, so one query can join tables in different regions, or public and private buckets. For a glob URI (s3://bucket/owl/**/*.parquet) the scope is the directory before the first glob character (s3://bucket/owl/).

Credentials are set up for every table in the version config's tables plus every shared table the query references.

slugs: inline database/version configs

Simple database/version configs, often just a description and a schema, can be written in the shared file instead of as files:

slugs:
  - name: birdnet-bullfrog          # the database slug in the URL
    version: "2.4v0.5"              # one version
    description: American Bullfrog classifier from BirdNET 2.4
    schema: birdnet_bullfrog_2p4v0p5

  - name: owl
    authors: [API Team]             # defaults for every version below
    versions:
      - version: "4.0"
        description: Owl model 4.0
        schema: owl_4p0
      - version: "5.0"
        description: Owl model 5.0
        schema: owl_5p0
        authors: [Owl Team]         # a version's own key overrides the default

  - name: notes                     # no version/versions: an unversioned database
    tables:
      notes: s3://my-bucket/notes.parquet

• Each entry needs a name, and either version, versions (a non-empty list of mappings, each with a version), or neither (unversioned).
• Every other key works exactly as in a database config file: description, authors, schema, tables, queries, routes, query_params, and so on. In a versions list, the entry's other keys are defaults that each version's own keys override.
• A slug is served only if its name is listed under databases: in the main config.yaml, like a file-based database.
• Files and slugs can be mixed, even for one database. A database's versions are the union of its version files (databases/<name>/<version>.yaml) and its slug versions, so latest, the versions listing at /<name> and the catalog endpoints see both. If a file and a slug define the same database/version, the file wins.
• The same name may appear in several entries (for example one entry per version).
• schema: can define the schema in place instead of naming one: schema: {name: owl_6p0, tables: {detections: s3://bucket/owl6.parquet}}. The schema is added to database.schema, so unions and schema_groups can use it. Defining an existing schema name with different tables is an error.
• An entry (or a versions entry) with from: <lookup> is a template: it generates one entry per row of a lookup, with {{row.<column>}} filled in. A static entry or version file wins over a generated one.
• Quote versions. YAML reads 2.10 as the number 2.1, so write version: "2.10".
• These are errors and stop startup: an entry without name, an entry with both version and versions, an empty versions list or an item without version, the same name/version defined twice, or a name with both versioned and unversioned entries.

See Versioning for how versions are resolved in URLs.

Shared routes and query_params

Top-level routes and query_params are added to every database/version (file or slug), which suits APIs where each model/version serves the same endpoints over its own schema.

routes:
  - route: recordings/{{recording_id}}/detections/
    sql: SELECT [[detections]].* FROM [[detections]] WHERE [[detections]].recording_id = {{recording_id}}
  - route: detections/{{id}}
    sql: SELECT [[detections]].* FROM [[detections]] WHERE [[detections]].id = {{id}}

query_params:
  - confidence:
      sql: "[[detections]].confidence >= {{confidence}}"
  - limit:
      sql_append: LIMIT {{limit}}

How they merge with a version config:

• The version config wins. Its own routes come first, so they are matched first, and a shared route with the same shape is dropped. Shape means the same path segments, ignoring {{param}} names and leading/trailing slashes: detections/{{id}} and /detections/{{detection_id}}/ are the same route. Shared routes with new shapes are added after the version's own routes.
• Shared query_params behave like the version config's top-level query_params: they apply to every route. A param with the same name in the version config's top-level query_params replaces the shared one, and a param on a route replaces both for that route.
• Each shared route still needs its [[table]] references to resolve for every database/version it is added to. If a version lacks a table, limit the route with include/exclude (below), or startup stops.

Query param options (sql, multivalue_sql, sql_append, default, required, ...) are described in Query Parameters.

Selecting databases/versions: include, exclude and the top-level lists

Shared items can be limited to, or kept away from, some databases/versions.

Key	Where	Effect
include	on a shared route or query param	add the item only to the listed databases/versions
exclude	on a shared route or query param	don't add the item to the listed databases/versions
route_inclusions	top level	add shared routes only to the listed databases/versions
route_exclusions	top level	add no shared routes to the listed databases/versions
query_inclusions	top level	add shared query params only to the listed databases/versions
query_exclusions	top level	add no shared query params to the listed databases/versions

routes:
  - route: not_for_everyone/{{id}}
    sql: SELECT [[other]].* FROM [[other]] WHERE [[other]].id = {{id}}
    exclude:
      - 'slug1/3.0'                 # string form
      - slug: slug2                 # mapping form
        version: "2.3"
      - slug: slug3
        version: '*'                # every version

query_params:
  - start_time:
      sql: "[[detections]].start_time >= {{start_time}}"
      include: ['owl']              # only owl, every version

route_inclusions: ['birdnet', 'owl/5.0']
route_exclusions: ['legacy_db']
query_exclusions:
  - slug: slug4
    version: "1.0"

Entry forms:

Entry	Matches
'<slug>/<version>'	that version
'<slug>' or '<slug>/*'	every version, and the unversioned database
{slug: <slug>, version: <version>}	that version
{slug: <slug>} or {slug: <slug>, version: '*'}	every version, and the unversioned database

Rules:

• An empty or missing list means no restriction.
• An item is added only if it passes the top-level lists and its own include/exclude. Inclusion lists are checked first; exclusion lists then remove matches.
• Versions compare numerically when both sides are numbers, so 4, 4.0 and "4.0" match. Otherwise they compare as text.
• latest in a URL is resolved to a real version before matching.
• An entry without a slug is an error.
• include and exclude are removed from an item before it is merged.

Same route, different versions

The same route (by shape) or query param (by name) may appear more than once in the shared file. Each database/version gets the first one whose lists select it. Complementary include/exclude lists therefore give one database a different version of an endpoint:

routes:
  - route: detections/
    exclude: ['birdnet/2.4']
    sql: SELECT [[detections]].* FROM [[detections]]
  - route: detections/
    include: ['birdnet/2.4']        # birdnet/2.4 also has a revisions table
    sql: >
      SELECT [[detections]].*, [[revisions]].id AS revision_id
      FROM [[detections]]
      LEFT JOIN [[revisions]] ON [[revisions]].observation_id = [[detections]].id

schema_groups

Named lists of schemas, used by union references such as [[group.table]] (see Cross-Schema Queries):

schema_groups:
  bullfrog_models:
    - birdnet_bullfrog_2p4v0p5
    - perch_bullfrog_8p0v0p5

Startup stops if a group name isn't a plain identifier, a group has the same name as a schema, a group isn't a non-empty list, or a group names a schema that doesn't exist under database.schema.

Startup checks

API Dock checks the shared file when it starts, and checks every database/version listed in the main config as requests will see it: merged with the main config and with the shared routes and query params selected for it. A problem stops startup with a message naming the database, version, route and template, for example:

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

See SQL Database Support for the full list of checks.

Complete example

A realistic shared file for an API serving several detection models, each model/version with its own schema. It is modelled on a real deployment.

# api_dock_config/databases/config.yaml
database:
  meta:
    region: us-west-2
    public: true
  schema:
    birdnet_2p4:                    # birdnet/2.4
      detections:
        uri: s3://my-bucket/birdnet/2.4/detections.parquet
      revisions:
        uri: s3://my-bucket/birdnet/2.4/revisions.parquet
    birdnet_bullfrog_2p4v0p5:       # birdnet-bullfrog/2.4v0.5
      detections:
        uri: s3://my-bucket/birdnet-bullfrog/2.4v0.5/detections.parquet
    owl_4p0:                        # owl/4.0
      detections:
        uri: s3://my-bucket/owl/4.0/detections/**/*.parquet
    owl_5p0:                        # owl/5.0
      detections:
        uri: s3://my-bucket/owl/5.0/detections.parquet
    perch_bullfrog_8p0v0p5:         # perch-bullfrog/8.0v0.5
      detections:
        uri: s3://my-bucket/perch-bullfrog/8.0v0.5/detections.parquet

schema_groups:
  bullfrog_models:
    - birdnet_bullfrog_2p4v0p5
    - perch_bullfrog_8p0v0p5

slugs:
  - name: owl
    versions:
      - version: "4.0"
        description: Owl model 4.0
        schema: owl_4p0
      - version: "5.0"
        description: Owl model 5.0
        schema: owl_5p0
  - name: birdnet
    version: "2.4"
    description: BirdNET 2.4 detections
    schema: birdnet_2p4
  - name: birdnet-bullfrog
    version: "2.4v0.5"
    description: American Bullfrog classifier from BirdNET 2.4
    schema: birdnet_bullfrog_2p4v0p5
  - name: perch-bullfrog
    version: "8.0v0.5"
    description: American Bullfrog classifier from Perch 8.0
    schema: perch_bullfrog_8p0v0p5

routes:
  - route: recordings/{{recording_id}}/detections/
    sql: SELECT [[detections]].* FROM [[detections]] WHERE [[detections]].recording_id = {{recording_id}}

  - route: detections/{{id}}
    sql: SELECT [[detections]].* FROM [[detections]] WHERE [[detections]].id = {{id}}

  # Every detection, from any model/version, overlapping this one in time on the
  # same recording, except this detection itself. See Cross-Schema-Queries.
  - route: detections/{{id}}/overlaps
    source_columns: [schema, name, version]
    sql: |
      WITH src AS (
        SELECT recording_id, start_time, end_time FROM [[detections]] WHERE id = {{id}}
      )
      SELECT detections.*
      FROM [[*.detections]] detections
      JOIN src ON detections.recording_id = src.recording_id
              AND detections.start_time < src.end_time
              AND detections.end_time   > src.start_time
      WHERE NOT (detections.schema_name = {{self.schema}} AND detections.id = {{id}})

  # Species counts with ?count, detection rows otherwise. birdnet/2.4 gets its
  # own version that also counts revisions.
  - route: detections/
    exclude: ['birdnet/2.4']
    sql:
      - when: count
        then:
          sql: >
            SELECT detections.common_name, detections.scientific_name, COUNT(*) AS count
            FROM [[detections]]
          sql_append: GROUP BY detections.common_name, detections.scientific_name ORDER BY count DESC
      - else: SELECT [[detections]].* FROM [[detections]]
  - route: detections/
    include: ['birdnet/2.4']
    sql:
      - when: count
        then:
          sql: >
            SELECT detections.common_name, detections.scientific_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
          sql_append: GROUP BY detections.common_name, detections.scientific_name ORDER BY count DESC
      - else: SELECT [[detections]].* FROM [[detections]]

query_params:
  - recording:
      sql: "[[detections]].recording_id = {{recording}}"
      multivalue_sql: "[[detections]].recording_id IN {{recording}}"
  - confidence:
      sql: "[[detections]].confidence >= {{confidence}}"
  - common_name:
      sql: "UPPER([[detections]].common_name) = UPPER({{common_name}})"
  - start_time:
      sql: "[[detections]].start_time >= {{start_time}}"
  - end_time:
      sql: "[[detections]].end_time <= {{end_time}}"
  - sort:
      sql_append: ORDER BY {{sort}} {{direction}}
  - direction:
      default: ASC
  - offset:
      sql_append: OFFSET {{offset}}
  - limit:
      sql_append: LIMIT {{limit}}

And the main config lists the slugs:

# api_dock_config/config.yaml
name: detections-api
databases:
  - owl
  - birdnet
  - birdnet-bullfrog
  - perch-bullfrog

Notes on the example:

• The when: count selector is described in Conditional SQL. Query-param WHERE conditions are added before a branch's sql_append, so filters also apply to the count queries.
• {{var}} values are bound parameters: write them without quotes (UPPER({{common_name}}), not UPPER('{{common_name}}')). See Query Parameters.
• owl_4p0 uses a glob URI; its scoped credentials (if it needed any) would cover s3://my-bucket/owl/4.0/detections/.