Cross Schema Queries

Cross-Schema Queries

Union references read the same table from several shared schemas in one query, for example every model's detections table. This page covers the union syntax, the source_columns route option, the {{self.*}} placeholders, and a worked "overlaps" route. Schemas and schema groups are defined in the Shared Database Config.

- route: detections/all
  source_columns: [schema]
  sql: SELECT detections.* FROM [[*.detections]] detections

This returns the rows of every shared schema's detections table, each with a schema_name column naming the schema it came from.

Union references

Reference	Reads
[[*.detections]]	every shared schema that has a detections table, including the current one
[[*!.detections]]	the same, minus the current database/version's schema:
[[group1.detections]]	the schemas listed in schema_groups.group1
[[group1!.detections]]	that group, minus the current schema

# api_dock_config/databases/config.yaml
schema_groups:
  bullfrog_models:
    - birdnet_bullfrog_2p4v0p5
    - perch_bullfrog_8p0v0p5

"The current schema" is the schema: of the database/version handling the request (see Shared Database Config).

Rules

• FROM/JOIN only, alias required. A union expands to a parenthesized subquery, so it may only appear directly after FROM or JOIN, and you must give it an alias: FROM [[*.detections]] detections. Use the alias everywhere else (detections.id); a union reference outside FROM/JOIN is an error.
• Columns are matched by name. Members are combined with DuckDB's UNION ALL BY NAME, so tables don't need the same column order. A column missing from some members comes back as NULL for their rows.
• Members are read in the order the schemas appear in the config (for *) or in the group.
• * skips schemas that don't have the table. It is an error if no schema has it.
• For a group, every member must have the table; a member without it is an error.
• ! applies only to * and groups ([[birdnet_2p4!.detections]] is an error). If the current database/version has no schema:, ! removes nothing.
• If ! removes every member, the query still runs and returns no rows, with the excluded table's columns.
• Each member is a schema view, so it gets its own storage credentials: one union can mix S3 regions and public/private buckets (see storage credentials).
• Every union reference is resolved at startup; an unknown group or table stops startup.

For example, with schemas a, b and c that all have detections, and the current schema a:

-- FROM [[*!.detections]] d
FROM ((SELECT * FROM b.detections) UNION ALL BY NAME (SELECT * FROM c.detections)) d

source_columns

Union rows carry only the tables' real columns. To know where each row came from, add source_columns to the route. Each member then adds literal columns with its source facts:

Fact	Default column	Value
schema	schema_name	the member's schema
name	name	the database whose schema: is that schema
version	version	that database's version

source_columns: [schema, name, version]          # list: default column names
source_columns: {schema: _schema, name: model}   # mapping: pick facts and rename them

• name and version come from the databases listed in the main config: API Dock finds the database/version whose schema: is the member schema. They are NULL when no database/version uses that schema, when several do, or (for version) when the database is unversioned. They are only looked up for routes that set source_columns.
• Unknown facts and column names that aren't plain identifiers stop startup.
• A source column with the same name as a real column of the table makes the request fail with a 500 error. This isn't caught at startup. Rename the source column with the mapping form.
• To filter on a source column without returning it, use DuckDB's EXCLUDE: SELECT detections.* EXCLUDE (schema_name) FROM [[*.detections]] detections WHERE ....
• source_columns only affects union references.

{{self.*}} placeholders

Placeholder	Value
{{self.schema}}	the current database/version's schema:
{{self.name}}	the current database name (the slug in the URL)
{{self.version}}	the current version (latest already resolved)

They are bound values like any other {{variable}}, so write them without quotes: detections.schema_name = {{self.schema}}. A value that isn't known (no schema:, or an unversioned database) is bound as NULL. They can be used in route SQL and query param SQL, in any route, not only unions.

Example: the overlaps route

A shared route every database/version can serve: all detections, from any model, that overlap the given detection in time on the same recording.

# api_dock_config/databases/config.yaml
routes:
  - 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}})

Step by step, for GET /owl/5.0/detections/42/overlaps where owl/5.0 has schema: owl_5p0:

1. The source row. [[detections]] is unqualified, so it resolves to the current version's table, owl_5p0.detections. The src CTE holds the recording and time window of detection 42.
2. All candidates. [[*.detections]] detections reads every schema's detections table, aliased detections. source_columns adds schema_name, name and version to each row.
3. Overlap. The join keeps rows on the same recording whose [start_time, end_time) overlaps the source window. Rows that only touch at an endpoint don't overlap.
4. Exclude only the source row. {{self.schema}} is owl_5p0, so the WHERE removes the row whose schema is owl_5p0 and whose id is 42. Matching on schema and id matters when ids repeat across models: excluding on id alone would also drop another model's detection that happens to have id 42. Other overlapping detections from owl_5p0 itself are kept.
5. Where each row came from. Each result row has schema_name, name and version, for example owl_5p0, owl, 5.0.

To limit the candidates to a group, use FROM [[bullfrog_models.detections]] detections; to leave out the current model entirely, use [[*!.detections]].

The route assumes every database/version it is added to has a schema: with a detections table: the CTE's [[detections]] must resolve at startup. Use include/exclude (see Shared Database Config) to keep it away from others.

Shared filters on a union

Query params written against the table name, such as

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

render [[detections]] as detections outside FROM/JOIN. Because the union is aliased detections, ?confidence=0.5 filters the overlapping rows too. Alias the union with the table name whenever you want shared filters to apply to it.

Query param conditions are added at the end of the SQL, joined with AND when the SQL already contains WHERE (anywhere, including inside a CTE), and with WHERE otherwise. Keep the union's own WHERE as the last clause of the outer query, as in the example, so the conditions land in it. See Query Parameters.

Unions with PostgreSQL

Union members can be PostgreSQL tables, file tables, or a mix. source_columns, {{self.*}} and ! work the same way.

Members	Runs on	How
All PostgreSQL, on one connection (and the rest of the route on that connection too)	PostgreSQL, natively	PostgreSQL has no UNION ALL BY NAME, so API Dock lists every column in each member, in order of first appearance, with a typed NULL for columns a table lacks, and joins them with UNION ALL. If ! removes every member, the first member is read with WHERE false.
PostgreSQL and files, or several connections	DuckDB	DuckDB attaches the PostgreSQL connections read-only and runs the union as above.

For a native union, API Dock reads each table's columns from PostgreSQL the first time it is needed and keeps them until restart, so a changed table definition needs a restart. A shared route that runs natively for some versions and on DuckDB for others needs SQL valid in both dialects. See PostgreSQL for how the engine is chosen.

See also

• Shared Database Config: schemas, schema_groups, shared routes
• SQL Database Support: [[table]] references and route SQL
• Query Parameters: filters, sorting, pagination
• PostgreSQL: PostgreSQL tables and engines