SQLite

Every type, null, row count and parameter rule on SQLite

This page is the reference behind the main docs for SQLite: the place to look when a type, a | null, a row count or a parameter surprises you. Postgres and MySQL have the same page, with every answer in the same place.

-- not strict, since a STRICT table takes no BOOLEAN or BIGINT column
create table users (
  id integer primary key,
  name text not null,
  email text unique,
  nick text,
  github_id bigint,
  active boolean not null default 1,
  created_at text not null
    default (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

create table orders (
  id integer primary key,
  user_id integer not null references users (id),
  total_cents integer not null,
  placed_at text not null
    default (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  ships_on text,
  receipt blob
) strict;

create table events (
  id integer primary key,
  kind text not null,
  payload text not null
) strict;

Data types

SQLite types values, not columns: a declared type gives a column an affinity, which Reylo types it by, and only a STRICT table holds a column to it (Declared types and STRICT tables).

In each pair below, the SQL is a value and the JavaScript is what Reylo returns for it: select <SQL> as v gives the row { v: <JavaScript> }. Reylo decodes every value of its own queries itself, on every driver:

Integers

The declared width decides, as on Postgres and MySQL: a column declared BIGINT, INT8 or UNSIGNED BIG INT is a JavaScript bigint, exact to SQLite's 64 bits, and every other integer is a number, INTEGER, INT, the rowid and count(*) included:

import userOrders from "./lite_user_orders.sql";

const rows = await userOrders.run(db);
      
const rows: {
    id: number;
    github_id: bigint | null;
    orders: number;
}[]

select u.id, u.github_id, count(o.id) as orders
from users u
left join orders o on o.user_id = u.id
group by u.id

Decimals

SQLite has no decimal type: a NUMERIC or DECIMAL column holds an integer, a real or text, whichever the row stored, so it is number | string. A REAL is a double, the same on every driver.

Dates and times

SQLite has no date or time type. A date is the text, the number or the real you store, and its type is its column's: a TEXT column's date is a string, as SQLite's own date functions write it:

import orderDates from "./lite_order_dates.sql";

const order = await orderDates.run(db, { id: 1 });
      
const order: {
    placed_at: string;
    ships_on: string | null;
} | null

select placed_at, ships_on from orders where id = :id

Truth values

SQLite has no boolean type, and computes a truth value as 1 or 0. Reylo makes every truth value the statement computes a boolean, and a column declared BOOLEAN or BOOL:

import userFlags from "./lite_user_flags.sql";

const rows = await userFlags.run(db);
      
const rows: {
    id: number;
    active: boolean;
    starts_with_a: boolean;
}[]

select id, active, name like 'A%' as starts_with_a from users

JSON

SQLite has no JSON type: a stored document is TEXT, a string. JSON the statement builds with json_object, json_array, json_group_array or json_group_object is typed from its arguments and comes back parsed, each value as SQLite writes it in JSON, a truth value as 1 or 0:

import orderSummaries from "./lite_order_summaries.sql";

const rows = await orderSummaries.run(db);
      
const rows: {
    name: string;
    summary: {
        total_cents: number;
        active: number | string;
    };
}[]

select u.name,
  json_object('total_cents', o.total_cents, 'active', u.active) as summary
from orders o
join users u on u.id = o.user_id

Binary

A BLOB is a Uint8Array on every driver: better-sqlite3, Turso's engine, sqlite3 and sqlite give a Buffer, which is one, libSQL an ArrayBuffer and Cloudflare D1 an array of numbers, which Reylo turns into one.

Declared types and STRICT tables

Reylo types a column by its declared type's affinity, by the rule SQLite applies, in this order:

A column declared BIGINT, INT8 or UNSIGNED BIG INT is INTEGER too, and a bigint by its width (Integers). Outside a STRICT table, SQLite stores a value of any class in any column:

import loose from "./lite_loose.sql";

const rows = await loose.run(db);
      
const rows: {
    name: string;
    nick: string | null;
}[]

-- users is not strict: name and nick may hold a BLOB
select name, nick from users

Functions

SQLite says nothing of a function's result, so Reylo keeps a table of SQLite's built-in functions: count is a non-null integer, coalesce takes its arguments' type, upper is text that is NULL only where its argument is.

Values Reylo refuses

Every SQLite driver hands Reylo the stored value, so two values are refused, each with an error that names its column:

Nullability

A column is | null unless Reylo infers it never is. SQLite's own check says "may be null" of everything but a literal or a column, so Reylo reads the schema and the statement:

• A table's column is non-null where it is declared NOT NULL.
• An outer join makes the joined table's columns nullable, by SQLite's own mark on the join.
• A test in the where that fails on NULL makes the column non-null: where nick is not null, where lower(nick) = :nick, and a left join's column tested in the where.
• An expression is non-null by Reylo's table of SQLite's functions: length(name), coalesce(nick, name), name || '!' and id + 1 over non-null inputs, count. A division is | null, since SQLite answers NULL for a division by zero. Arithmetic on a REAL with no answer is the one exception (Limits).
• An aggregate other than count is NULL over no rows.

import nicknames from "./lite_nicknames.sql";

const rows = await nicknames.run(db);
      
const rows: {
    shout: string | null;
    label: string;
}[]

select upper(nick) as shout, coalesce(nick, name) as label
from users

A test in the where shows the joined row exists, so its columns are non-null; a window function is typed like any other, lag nullable on the first row:

import shipped from "./lite_shipped.sql";

const rows = await shipped.run(db);
      
const rows: {
    name: string;
    total_cents: number;
    ships_on: string;
}[]

import orderGaps from "./lite_order_gaps.sql";

const rows = await orderGaps.run(db);
      
const rows: {
    id: number;
    total_cents: number;
    previous_total: number | null;
    n: number;
}[]

select u.name, o.total_cents, o.ships_on
from users u
left join orders o on o.user_id = u.id
where o.ships_on is not null

select id, total_cents,
  lag(total_cents) over (order by id) as previous_total,
  row_number() over (order by id) as n
from orders

Where you know more than Reylo can infer, say it in the statement: coalesce(x, …) gives a default, and a where x is not null test both filters and makes the column non-null.

Row counts

How many rows .run() resolves to comes from what Reylo infers the statement can return:

What counts for nothing on SQLite:

• A unique index on an expression, or with a where. A statement with where lower(email) = :email is Row[], whatever index lower(email) has.
• A key that SQLite converts. SQLite gives a numeric side's affinity to a TEXT or untyped key, so where code = cast(:code as integer) matches '1' and '01'.
• A key compared under another collation. SQLite compares under the left side's collation, so a NOCASE column on the left of a plain key, r.k = c.code, meets 'x' and 'X'.
• A declared column named rowid, which is no rowid.

At run time a Row statement throws unless it gets exactly one row, and a Row | null statement throws when it gets more than one.

Parameters

Placeholders

Write each parameter as :name, or as SQLite's own @name or $name, one prefix per statement. Reylo sends ?, and .run() takes an object keyed by name, without the prefix.

• A statement may use SQLite's positional ? or ?1 instead, never beside a name, and the object's keys are then the numbers, { 1: 42 }.
• Each parameter appears once in a statement, so that one place types it. To use a value twice, select it once in a with and read it from there.
• SQLite reads $1 as a parameter named $1, so Reylo refuses it and says to write ?1.

What a parameter takes

Every value .run() returns goes back in as a parameter of its type, on every driver, so a row can be passed straight to the next statement; a null goes back in where the parameter takes one (Parameters that may be null):

• A boolean is bound as 1 or 0 on every driver.
• The sqlite3 and sqlite drivers bind a bigint as NULL, so Reylo passes them one within 2^53 as a number and one past it as its digits to a BIGINT column's parameter, and refuses one past it anywhere else.

Parameters without a type

SQLite types a parameter only where its own reading of the statement places one:

• compared with a typed expression, or as an element of a row value compared with a typed one ((created_at, id) < (:at, :id))
• an insert's or update's column
• a cast, arithmetic, limit and offset, in, between, and a case arm
• an argument of a built-in function from Reylo's table (lower(:s) is text, abs(:n) a number, coalesce(:a, 0) an integer)

Anywhere else, such as a bare select :x or :x is null, the parameter is unknown until a cast types it:

import echo, { type Params } from "./lite_echo.sql";
                     
type Params = {
    value: unknown;
    count: number;
}

select :value as value, :count::int as count

By default such a parameter is an error in the type check, which asks for a cast; strict_params: false in .reylo.yaml leaves it unknown, as above. An argument of a function your application registers never gets that far: the call fails the type check (Limits).

Parameters that may be null

A parameter takes null in two places:

• as the first argument of coalesce or ifnull, with a cast or without;
• written whole, with no cast, to a column that may hold NULL.

A cast is the parameter's whole type, so :email::text never takes null, and a test for null (:p is null, :p is col) makes nothing nullable. A column holds NULL unless it is NOT NULL or a check (… is not null) holds it:

import usersByNick from "./lite_users_by_nick.sql";

const users = await usersByNick.run(db, { nick: null });
                                          
nick: string | null

select id, name from users
where nick = coalesce(:nick::text, nick)

Casts

SQLite has no ::, so Reylo writes :p::type as SQLite's CAST, by the type's affinity, and checks the value against the type before the statement runs, since SQLite's CAST never fails: CAST('abc' AS INTEGER) is 0, and CAST('1.9' AS INTEGER) is 1. SQLite's own cast(:p as …) works too.

• The check refuses a number that is no integer for ::int, and a value out of SQLite's 64-bit range, before the statement runs.
• A ::boolean cast is an error: SQLite's CAST to a boolean reads a value as the number it starts with. Pass 1 or 0 and write ::integer.
• SQLite has no arrays, so a ::text[] cast is an error.

How the text is read

Reylo reads the statement as SQLite does: a :name, @name or $name is a placeholder only outside single-quoted strings (with ''), double-quoted names and comments (-- and /* … */).

Drivers

Every SQLite driver turns each stored value into a JavaScript value in native code, and the drivers differ only on an integer and a BLOB.

Reylo runs its own query on the database you pass and gives each value its type after the driver, so a setting of yours does not reach Reylo's query, and none of Reylo's reaches yours.

• The drivers better-sqlite3, node:sqlite, bun:sqlite and the Database of @tursodatabase/database/compat take .runSync() too, which returns the result itself, so it works inside their db.transaction(fn).
• The drivers sqlite3 and sqlite bind a bigint as NULL, so Reylo passes them one that a number holds as that number, and refuses a larger one before the statement runs. Every other SQLite driver binds a bigint exactly.
• Each driver takes its transactions too: libSQL's .transaction(), the handle Turso's .transactionAsync() passes its function, Bun.sql's .begin() and .savepoint(), and D1's .withSession() sessions.
• On Bun 1.4.3, Bun.sql returns no rows for a statement whose text holds a " inside a single-quoted string, such as json_extract('{"a":1}', '$.a'), so .run() throws "Expected exactly one row, got 0" where one row is expected. Pass the document as a parameter: json_extract(:doc::text, '$.a').
• Turso's engine 0.8 stops the process on a recursive WITH whose anchor and recursive member both read a CTE over json_each, such as one over a JSON parameter, with no Reylo involved. Write that CTE as materialized.

The type check needs no driver: it reads the schema through node:sqlite, which Node has built in since 22.5.

Limits

The edges of what Reylo types on SQLite, each with what to write instead:

• A declared type outside a STRICT table, which SQLite does not hold a value to. Declare tables strict.
• Built JSON read through a subquery's or a view's column, a cast or lag, which SQLite may no longer mark as JSON, so it is unknown. Build it in the query that returns it (JSON).
• A function your application registers, or an extension's. Reylo prepares the statement in its own SQLite, which lacks the function, so the type check fails with "no such function: slugify", cast or not. Keep the call out of the .sql file and apply it in TypeScript to what .run() returns.
• A built-in function Reylo's table lacks, such as unhex. Its result is unknown: cast it in the statement, cast(unhex(hash) as blob).
• A table in an attached database. The type check reads one database file, and an ATTACH happens on your app's connection at run time, so select … from other.users is an error: the database has no schema other. A TEMP table is the same.
• A virtual table's module. Reylo's SQLite has FTS3, FTS4, FTS5, R-tree, Geopoly and dbstat, so a schema that uses one is read, and the type check does not ask whether your driver's SQLite has the module.
• Arithmetic on a REAL that has no answer. SQLite answers NULL where a real's arithmetic is not a number, and Reylo types it non-null: +, - and * over non-null values, and / and % by a literal other than 0.
  • Such a value is null where the type says number: 1e999 * 0, price - price where price holds 1e999, and 5 % 0.5, whose divisor % reads as the integer 0.
  • Write coalesce(price * qty, 0) where a REAL may hold an infinity. An overflow is no such case: 9e307 * 9e307 is Infinity.
• A parameter in place of a name. A parameter is a value: select * from :table is SQLite's syntax error, and order by :column sorts by a constant. Choose a column with case :sort when 'name' then name end.
• A statement nested deeper than 350 expressions, which is SQLite's error, "Expression tree is too large".
• One statement per file. A file holds one select, insert, update, delete, replace or values. A file of several statements or of DDL, such as a migration, is an error in the type check.