Aster Web Data / Connect your data

SQLite

How Aster Web Data uses a SQLite file, and how SQLite's storage, single writer and results shape what clients receive.

SQLite suits a service whose data lives in one file on the same machine: a small deployment, a test suite, or a first try of Aster Web Data, which is why Get started uses it. 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 $expand run in SQLite, in tables the service creates as STRICT, so SQLite itself refuses a value of the wrong type. Where SQLite's answer differs from OData's, SQLite's stands; Departures from OData lists those places.

Two things decide whether SQLite fits. It allows one writer at a time, so the service serialises every write to a file. And decimals are stored as scaled 64-bit integers, so their precision is at most 18 digits.

Connecting

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

databases:
  local:
    kind: sqlite
    url_file: db.path      # a file holding the path, or ':memory:'; url_env also works

The path is relative to the working directory, and :memory: gives a database held in memory for as long as the service runs. A file: URI is refused. The configuration reference lists every key with its default. These behave in a way particular to SQLite:

• schema, tls, ca_file, tls_strict and options are refused. A file has one schema, main, and no transport.
• setup: existing uses the file as it is, and the database is unavailable if the file is missing. setup: recreate creates the file, recreates each data source's table as a STRICT table, and loads the seed data in one transaction.
• collation may be BINARY or NOCASE, on the database or on a field. Any other collation is refused at start-up, naming the field.
• read_only: true opens the file read only. A :memory: database cannot be read only, and two entries that name the same file must agree on the setting. Read-only databases and privileges gives the rest.
• pool.checkout_timeout is also SQLite's busy timeout, the longest a writer waits. At least one connection is kept open.

Two entries that name the same file, after the path is resolved, share one open database, one pool and one writer. Each :memory: entry is a separate database.

SQLite 3.53.2 is bundled with the service; no system library is used. When it opens a file, the service checks that the engine allows at least 30,000 bound parameters.

Start-up check

The configuration is checked before the file opens: decimals need a declared precision of at most 18 and a declared scale, and times and timestamps a precision of at most six fractional digits.

When an existing file opens, each field's column must have the affinity of the field's type (INTEGER, REAL, TEXT or BLOB), and temporal columns must use BINARY collation. Every value of a decimal column must be an integer, and the whole column is read to check it. The service also decodes 128 rows of every other column, so a malformed date, or a fractional value in an integer field, is refused with the column named. This matters for a file that another program wrote without STRICT tables, where a column keeps whatever a writer stored under its affinity.

A file that is missing or fails the check makes only its own sets unavailable; A database goes down describes what clients see. To run the check on its own, use the check command.

Types

Each field is stored in the form below. Tables the service creates are STRICT.

Dates, times and timestamps sort correctly as text because every value has the same width, which is why the start-up check samples those columns and requires BINARY collation on them.

A decimal is an integer with the scale fixed by the column definition, so 18 digits is the most that fits. A comparison with a constant is exact at every scale: the constant is rounded in the direction that keeps the comparison's result, so an index on the column can serve it.

Collation and case

• BINARY is the default: code point order on UTF-8, with no padding.
• NOCASE folds ASCII letters only, for comparison, ordering and the text functions.
• A comparison uses the column's own collation.
• Case-insensitive search. AsterData.icontains, istartswith, iendswith and ilike run as LIKE ... ESCAPE '\', with the needle's \, % and _ escaped. SQLite's LIKE folds ASCII letters only, whatever the column's collation, so 'DRILL' matches drill and 'É' does not match é.
• Case mapping. tolower and toupper fold ASCII letters only.

Where each operation runs

Each OData operator becomes the SQLite operator with the same meaning, so SQLite's comparison, arithmetic and errors give the answer. 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", (SELECT json_group_array(json(c1)) FROM (SELECT json_object('cells', JSON_ARRAY(CAST(i."issued" AS TEXT), CAST(i."amount" AS TEXT), CAST(i."id" AS TEXT))) AS c1 FROM "main"."invoices" AS i WHERE i."customerId" = c."id" ORDER BY i."amount" DESC, i."id" LIMIT ?1)) AS "invoices", (SELECT COUNT(*) FROM "main"."invoices" AS i2 WHERE i2."customerId" = c."id") AS c6 FROM "main"."customers" AS c WHERE c."id" = ?2
-- binds: 2, 3

The invoices come from a sub-query per customer that builds them with json_group_array, with the nested sort, page and count in the same statement. A table with a double column is read by key batches instead, because the bundled JSON writes some doubles in text that is not their shortest round-trip form.

These run in SQLite:

• Comparisons, in lists, case, exists and sub-queries, joins, unions, windows, sorting, offset and keyset paging, grouping, count, min and max. ne against a value on a nullable column is x <> ? OR x IS NULL, and two nullable columns compare with IS and IS NOT, so the answers are OData's two-valued ones.
• contains, startswith and endswith. On a BINARY column they compare bytes, so embedded NULs and non-ASCII text give the right answer. On a NOCASE column they use LIKE with the needle escaped, which folds ASCII letters as the column does.
• like under NOCASE, where a trailing lone backslash in a pattern matches itself, and the case-insensitive functions above.
• concat, tolower and toupper, and casts of integers and dates to text.
• 64-bit integer arithmetic (/ truncates toward zero), doubles (+, -, *, /), and decimals (comparison, add, sub, neg, mul and div). Decimal mul is exact. Decimal div truncates toward zero at the larger of the two scales, so 1.5 div 2 is 0.7 and 10.00 div 3 is 3.33.
• sum of 64-bit integers, which raises an error past 64 bits, sum of decimals, and sum and avg of doubles.

These run in the service, within the limits on rows it may hold: like under BINARY (SQLite's LIKE would fold ASCII letters), length, substring, trim, indexof, regular expressions, divby, mod of decimals and doubles, avg of integers and decimals, round, floor and ceiling, date parts, 128-bit comparisons and arithmetic, other casts to text, and Traverse.

Errors

Nulls and ordering

Each sort key names its null placement, and the service writes NULLS FIRST or NULLS LAST as the sort asks. The placement is left out for a column the table declares not null, so an index on it can serve the sort. NaN is never stored, and -0.0 equals 0.0.

$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 SQLite gives another, SQLite's result stands. These are the places:

• Division by zero gives null for every numeric type. OData makes an integer div or mod by zero an error, and gives INF, -INF or NaN for div on doubles and for divby, which runs in the service here.
• Integer and decimal overflow in a filter compares a real-number approximation instead of failing. In a returned value it is refused.
• Decimal div truncates at the larger operand scale. OData's decimal quotient is not truncated.
• tolower and toupper fold ASCII letters only.
• NOCASE comparison folds ASCII case only.
• The case-insensitive functions (icontains, istartswith, iendswith, ilike) fold ASCII case only.

Writes and transactions

• One writer. Writable files use WAL mode. A write transaction takes the database's writer gate, then starts with BEGIN IMMEDIATE, so writes from one service run one at a time and readers keep the snapshot they started with. A writer waits up to checkout_timeout, which also covers writes from another process, and then fails with 503 PoolExhausted.

• Isolation. Serializable is the default, and it is what writes get. Repeatable read is accepted, as a WAL snapshot. Other levels are refused.

• Atomic groups. A $batch atomicity group is one transaction. Because writes are serialised, a group waits for the writer gate instead of retrying on contention.

• Nested inserts run as separate inserts inside one transaction, one at a time when a statement would exceed the bound-parameter budget.

• ETags. SQLite has no row version, so etag: native is refused and a set has no ETag until it declares a token. The usual token is a version column a trigger advances, named in etag: {columns: [version]}, or versionField: version to have Aster advance it:

  ALTER TABLE orders ADD COLUMN version INTEGER NOT NULL DEFAULT 1;
  CREATE TRIGGER orders_version AFTER UPDATE ON orders FOR EACH ROW WHEN NEW.version = OLD.version
  BEGIN UPDATE orders SET version = OLD.version + 1 WHERE id = NEW.id; END;
  

  ETags and concurrent edits explains the tokens.

• Bound parameters. A statement binds at most 30,000 values. Key batches are sized to stay within that: 8,192 keys for one key column. A statement over the budget is refused, never truncated.

• Statement timeout. A progress handler checks limits.statement_timeout_ms every 1,000 virtual-machine steps and interrupts the statement once it has passed.

• :memory: databases use a named shared cache, which cannot use WAL.

Across SQLite and PostgreSQL, in either direction, an $expand or a navigation takes two statements, and a per-parent page with a count takes three; Reads across databases explains how they are combined.

Indexes and sort order

Index every column you filter or sort on, and every navigation's foreign key, as Indexes describes. SQLite's EXPLAIN QUERY PLAN shows whether a filter uses its index, including a temporal column under BINARY and a decimal compared with a constant. A comparison between two columns, or one over an arithmetic result, scans the table.

Scaffold

The scaffold command reads pragma_table_xinfo, pragma_foreign_key_list and each text column's collation, and names each declared type by SQLite's affinity rules. INT types become long, REAL-like types float, text types text and BLOB binary, with foreign keys as navigations. Decimals, dates and booleans are left as comments with the reason, because their storage form is a choice you make. A scaffolded file passes check unchanged. Connect your own database walks through it.