Aster Web Data / Connect your data

DuckDB

How Aster Web Data uses a DuckDB file, and how DuckDB's types, isolation and results shape what clients receive.

DuckDB suits a service that publishes an analytical file, such as a reporting extract your team already queries with DuckDB. The service opens the file inside its own process, so there is no database server to run, no network connection and no TLS. Filters, sorts, pages, counts and grouping run in DuckDB. Decimal division and 128-bit arithmetic run in the service, so the digits of those results do not depend on DuckDB. Where DuckDB's answer differs from OData's, DuckDB's stands; Departures from OData lists those places.

Two things decide whether DuckDB fits. A file can be open in one service instance at a time. And DuckDB has one isolation level, repeatable read, so an atomic group that checks a rule, which needs serializable isolation, is refused.

Connecting

Name the file from an environment variable or a file that holds its path:

databases:
  main:
    kind: duckdb
    url_file: database.path     # a file holding the path, or ':memory:'; url_env also works

:memory: gives a database held in memory, and each :memory: entry is a separate database. The parts form is refused, because DuckDB takes a path. The configuration reference lists every key with its default. These behave in a way particular to DuckDB:

• schema is main when absent, and every name is qualified.
• tls, ca_file, tls_strict and options are refused. A file has no transport.
• setup: recreate creates a missing schema, recreates each data source's table, and loads the seed data in one transaction.
• collation may only be binary.
• read_only: true also opens the file with access_mode = READ_ONLY. Read-only databases and privileges gives the rest.
• pool is accepted and not used. The service holds one DuckDB instance, and each request gets its own connection, and so its own snapshot, from it. A request never waits for a connection.

A second open of a file that another service instance or process holds is refused. The service then treats the file as unavailable and retries the open; A database goes down describes what clients see meanwhile.

DuckDB 1.5.6 is bundled with the service, with its JSON extension. Extension downloads and autoloading are disabled, and DuckDB runs with two threads.

Start-up check

DuckDB is the database whose column types are checked most strictly. When the file opens, the check refuses a column that does not store the field exactly:

• A date over a column that is not DATE, a time over one that is not TIME, and a timestamp over one that is not TIMESTAMP WITH TIME ZONE. TIMESTAMP, TIMESTAMP_NS, TIMESTAMP_MS and TIMESTAMP_S columns are refused.
• A decimal over a column that is not a DECIMAL with the field's precision and scale, so no write rescales a value silently.
• A text field over a column whose table definition names a collation. The database default must be binary, or the file does not open.

Other fields are accepted over any column. The configuration is checked before the file opens: a decimal needs a precision from 1 to 38 and a scale that does not exceed it, a temporal precision above six is refused, and a link set that needs ON DELETE CASCADE is refused, because DuckDB does not support that constraint. The check compares each column's facets and default with the configuration and says what to declare.

A failed check makes only that file's sets unavailable. To run the check on its own, use the check command.

Types

$metadata publishes what the configuration declares: a decimal's precision and scale, times and timestamps at their declared precision (6 by default), and a collation only where one is declared.

Collation and case

DuckDB exposes no collations to the service, and the service maps only binary. Text comparison, sorting and like are in code point order and case-sensitive, and every text column publishes AsterData.Collation as binary. tolower and toupper are DuckDB's lower and upper, which map Unicode letters.

Where each operation runs

DuckDB uses the same SQL forms as PostgreSQL, adjusted where DuckDB differs, and each operator that runs there is DuckDB's own. This request reads a customer with their two largest invoices and their invoice count:

/* aster read customers */ SELECT c."id", c."name", c."email", c."region" FROM "main"."customers" AS c WHERE c."id" = ?
-- binds: 3

/* aster read invoices */ SELECT q."id", q."customerId", q."issued", q."amount", q."paid", q.c6 FROM (SELECT i."id", i."customerId", i."issued", i."amount", i."paid", COUNT(*) OVER (PARTITION BY i."customerId") AS c6, i."customerId" AS "w", i."amount" AS c8, i."id" AS c9 FROM "main"."invoices" AS i WHERE i."customerId" IN (?)) AS q QUALIFY ROW_NUMBER() OVER (PARTITION BY q."w" ORDER BY q.c8 DESC, q.c9) <= ? ORDER BY q."amount" DESC, q."id"
-- binds: 3, 2

An $expand is read one level per statement, by the parents' keys. The per-parent page uses ROW_NUMBER() and the count a window, and the key lists are padded to a power of two by repeating a key, so the statement shapes stay few.

These run in DuckDB:

• Comparisons and ordering on booleans, integers, doubles, decimals, text, dates, times, timestamps, durations, GUIDs and enumerations, and equality on binary values. An integer constant beside a decimal is bound as a decimal, so DuckDB compares in the column's type. ne against a value on a nullable column is x <> ? OR x IS NULL, and two nullable columns compare with IS [NOT] DISTINCT FROM, so the answers are OData's two-valued ones.
• in lists, like, case, exists and sub-queries, including correlated ones.
• contains, startswith, endswith, length and concat on tables without a collection column, tolower and toupper, and year, month and day of a date.
• 64-bit integer arithmetic (div as //), decimals (+, -, *, %), doubles (+, -, *, /, %), divby, round, ceiling and floor.
• Sorting, offset and keyset paging, exact counts, grouping, distinct, aggregates, ordered concat, windows, unions, and the five join kinds. sum of integers, decimals and doubles, and avg of doubles.
• Inserts, updates, deletes and upserts, writes selected by criteria, RETURNING, preconditions on expected values, and atomic increments. An upsert is refused on a table with a 128-bit column, and an update or delete on one whose primary key includes a 128-bit column.

These run in the service:

• Decimal div, because DuckDB's / of two decimals is a double, and avg of integers or decimals, which DuckDB also returns as a double.
• 128-bit integer comparison and arithmetic.
• substring, trim, indexof and regular expressions.
• Date to text casts, other casts, and isof.
• Traverse, and nested inserts, which run member by member.

A filter on a decimal division or a 128-bit field reads the table's rows into the service, and those rows count against limits.residual_rows, so on a large table the request can be refused. Add a bounded condition, such as a key or a paged sort, as Limits describes.

Errors

Nulls and ordering

Each sort names its null placement, and the service writes NULLS FIRST or NULLS LAST as the sort asks: OData puts nulls first in ascending order and last in descending order. Text is ordered by code point. NaN equals itself and sorts after every other number.

A next page is a keyset query with the same null placement and directions as the sort. $count=true is exact and agrees with the page, as A page with its total shows.

Departures from OData

Where OData 4.01 defines a result and DuckDB gives another, DuckDB's result stands. These are the places:

• NaN equals itself and sorts greatest. OData compares NaN as IEEE does, unequal to everything.
• Double overflow: a double result past the finite range is INF or -INF, where OData raises an error.
• A zero divisor in an integer div or mod gives null, where OData raises an error. A double div by zero is INF, -INF or NaN, as OData's divby is.
• A decimal and an integer compare exactly, in a common DECIMAL of at most 38 digits. A pair whose common type would be wider fails instead of being compared.
• sum of a decimal is a DECIMAL(38, s). A result with more digits than a 28-digit decimal holds is refused when read.

round takes ties away from zero, as OData's does.

Writes and transactions

• Isolation. DuckDB has one isolation level, repeatable read, which reads a snapshot. A write runs in one transaction. An atomic group that checks a rule asks for serializable isolation, which DuckDB cannot give, so it is refused with 501 rather than run at a weaker level.
• Conflicts. Conflicting writes fail with 409 Conflict, and the service does not retry them. DuckDB takes no row locks, so SELECT ... FOR UPDATE is not used.
• ETags. DuckDB has no row version and no triggers, so etag: native is refused and a version column is advanced by your application or by Aster with versionField: version, which sets it to 1 on create and adds 1 on every update. Of two writers holding one tag, one succeeds and the other fails with 409 Conflict rather than 412. ETags and concurrent edits explains the tokens.
• Timeouts. A watchdog interrupts each statement at limits.statement_timeout_ms, and is cancelled when the statement ends, so it never interrupts a later one.

Indexes and sort order

The service creates no indexes on a DuckDB file. Each $expand level is read by the parents' keys in an IN (...) list, and every sort ends with the entity key so pages never overlap. What runs on your database shows the SQL for common requests.

Scaffold

The scaffold command reads DuckDB's catalog for the tables, columns and keys, and writes a starting data-source configuration for the types the start-up check accepts, leaving other columns as comments with the reason. Run it with the file's path: scaffold --kind duckdb --url-file PATH, or --url-env VAR. It opens the file as the server does, so another process that holds the file stops it, and it needs no privilege beyond opening the file. Connect your own database walks through it.