SQLite
How Reylo types queries on SQLite
Reylo lets you import .sql files into TypeScript, with every query typed from your live database. This page covers how it types and runs queries on SQLite: column types, null values, row counts, parameters and drivers.
| SQLite versions | Reylo checks each query with its own SQLite 3.53, and again with the SQLite of each driver your package.json lists (Functions) |
| Connection | A file: or sqlite: URL in DATABASE_URL or direct_url; a relative path starts at your package.json |
| Drivers | better-sqlite3, node:sqlite, bun:sqlite, libSQL, Turso, Cloudflare D1 and Durable Objects, sqlite3, sqlite and Bun.sql, and Drizzle over better-sqlite3, bun:sqlite, libSQL, D1 or Durable Objects (Drivers) |
| Type checking | Reylo's engine reads the database file itself, so your project needs no driver for it |
View the schema behind the examples
-- 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 doesn't enforce column types: a column's declared type only sets its affinity, except in a STRICT table (Declared types and STRICT tables). Reylo types each column by its declared type, and .run() returns the same value for it on every driver, or throws where a driver would round it (Drivers):
| Declared type, or expression | TypeScript | The value .run() returns |
|---|---|---|
INTEGER, INT, and any other type with INT in its name | number | 42 |
BIGINT, INT8, UNSIGNED BIG INT | bigint | 9007199254740993n |
REAL, FLOAT, DOUBLE | number | 1.5 |
TEXT, VARCHAR, and any type with CHAR, CLOB or TEXT | string | "Ada" |
BLOB | Uint8Array | Uint8Array.of(1, 2, 255) |
BOOLEAN, BOOL | boolean | true |
A comparison, IS NULL, IN, BETWEEN, LIKE, GLOB, EXISTS, NOT, AND, OR | boolean | false |
NUMERIC, DECIMAL, DATE, DATETIME, and any other name | number | string | 1.5, "2026-10-20" |
No type, or ANY in a STRICT table | unknown | whatever was stored |
count(*), length(…) | number | 2 |
json_object(…), json_group_array(…) | the object or array it builds | { total: 1250 } |
json_extract(…), ->, ->> | unknown | 1250 |
Each example below pairs a SQL value with the value .run() returns for it: select <SQL> as v returns the row { v: <JavaScript> }.
Declared types and STRICT tables
Reylo types a column by its declared type, following SQLite's affinity rules, as in the table above. Outside a STRICT table, any column can hold any kind of value, and .run() throws an error naming the column when it reads one its type doesn't allow:
import from "./lite_loose.sql";
const rows = await .();-- users is not strict: name and nick may hold a BLOB
select name, nick from usersSTRICT tables
A STRICT table enforces its column types, so they're exact there. It can't declare BOOLEAN, BIGINT, DATE or NUMERIC columns, so a flag in one is an INTEGER, typed number.
select id, total_cents, receipt
from ordersvoid{
id: number;
total_cents: number;
receipt: Uint8Array | null;
}[]Values of another kind
Outside a STRICT table, a value of another kind, such as the text 'abc' in an INTEGER column, makes .run() throw a TypeError that names the column. To read whatever the column holds, cast it:
select cast(nick as text) as nick
from usersvoid{ nick: string | null }[]CHECK constraints
A CHECK constraint doesn't narrow a type, since pragma ignore_check_constraints lets any connection skip it.
create table members (
id integer primary key,
role text not null check (role in ('admin', 'member'))
) strict;select role
from membersvoid{ role: string }[]Integers
A column declared BIGINT, INT8 or UNSIGNED BIG INT comes back as a JavaScript bigint, exact to 64 bits. Every other integer is a number, including INTEGER and INT columns, the rowid and count(*):
import from "./lite_user_orders.sql";
const rows = await .();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.idWhat stays a bigint
A BIGINT column stays a bigint through a subquery, a view, min(), max(), coalesce() and CASE. Here users.github_id holds 9007199254740993:
max(github_id)9007199254740993ncoalesce(github_id, 0)9007199254740993n(select github_id from users)9007199254740993nWhat SQLite computes from it
A value computed from a BIGINT, such as github_id + 1 or sum(github_id), is a plain integer, typed number. Here users.github_id holds 41:
github_id + 142sum(github_id)41Past what a number holds
An integer too large for a number, past ±(2^53 − 1), throws an error naming its column rather than come back rounded. Declare the column BIGINT, or cast the value to text: cast(max(id) as text). Some drivers round even a BIGINT (Drivers).
9007199254740993throws TypeErrorcast(9007199254740993 as text)"9007199254740993"Decimals
SQLite has no decimal type: a NUMERIC or DECIMAL column holds whatever was stored, so it's typed number | string. Store exact amounts as integers of the smallest unit, as orders.total_cents does; here it holds 1250 and 1251:
sum(total_cents)2501avg(total_cents)1250.5Dates and times
SQLite has no date or time type: a date is whatever you store, typed by its column. A date in a TEXT column is a string:
import from "./lite_order_dates.sql";
const order = await .(, { : 1 });select placed_at, ships_on from orders where id = :idInstants as ISO 8601 text
Store an instant as ISO 8601 text in UTC with a Z, as strftime('%Y-%m-%dT%H:%M:%fZ', 'now') writes it, which Temporal.Instant.from() and new Date() can read. SQLite's current_timestamp and datetime() write UTC without a zone, which looks like local time.
strftime('%Y-%m-%dT%H:%M:%fZ', '2026-10-20 10:00:00')"2026-10-20T10:00:00.000Z"datetime('2026-10-20T10:00:00.000Z')"2026-10-20 10:00:00"Columns declared DATE
A column declared DATE or DATETIME has NUMERIC affinity, so it can hold a number or text, typed number | string. Declare it TEXT instead, or INTEGER for Unix time.
Truth values
SQLite has no boolean type, and a comparison returns 1 or 0. Reylo types every comparison your query makes as boolean, and every column declared BOOLEAN or BOOL:
import from "./lite_user_flags.sql";
const rows = await .();select id, active, name like 'A%' as starts_with_a from usersWhat counts
These are typed boolean: comparisons, IN, BETWEEN, EXISTS, IS, NOT, AND, OR, like, glob, regexp and match, and a CASE, coalesce, ifnull, iif or subquery whose every value is one of them. A BOOLEAN column stays boolean through a subquery or a view.
iif(1 = 1, 0 = 1, 1 = 1)false(select active from users)trueValues other than 0 and 1
SQLite doesn't enforce a BOOLEAN column, so it can hold 2, 'yes' or a BLOB. Reylo reads 0 and 1 as false and true, and throws an error naming the column for any other value. Here users.active holds 2:
activethrows TypeErroractive + 02JSON
SQLite has no JSON type, so a stored document is TEXT, typed string. JSON your query builds with json_object, json_array, json_group_array or json_group_object is typed from what you put in it, and comes back parsed:
import from "./lite_order_summaries.sql";
const rows = await .();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_idValues inside a document
Inside JSON, a value can differ from the same value in a column: a boolean is 1 or 0, and integers past 2^53 lose precision in JSON.parse.
json_object('a', 1 = 1){ a: 1 }json_object('n', 9007199254740993){ n: 9007199254740992 }Built JSON inside built JSON
JSON you build can nest inside other JSON you build, directly or through coalesce and case. To nest a scalar subquery's JSON, wrap the subquery in json(…): without it, newer SQLite versions, such as 3.54.1 in Node 26.11's node:sqlite, nest it as text, so Reylo types it Json, any JSON value.
json_object('n', case when 1 then json_object('a', 1) end){ n: { a: 1 } }json_object('n', json((select json_object('a', 1)))){ n: { a: 1 } }Where it stops being JSON
Read through a subquery's column, a view or lag, built JSON loses SQLite's JSON marker, so Reylo types it unknown (a cast keeps the marker on SQLite 3.53, but not on Turso). Build the JSON in the query that returns it.
json_object('n', (select j from (select json_object('a', 1) as j))){ n: "{\"a\":1}" }Over no rows
Over no rows, json_group_array returns [], never null.
(select json_group_array(id) from users where 0)[]Reading a document
Values read with json_extract, -> and ->> are typed unknown, since they depend on the document.
json_extract('{"a":1}', '$.a')1'{"a":"x"}' ->> '$.a'"x"Functions
SQLite doesn't report what a function returns, so Reylo has its own table of SQLite's built-in functions: count returns a non-null integer, coalesce the type of its arguments, and upper text that is NULL only when its argument is.
Functions that answer NULL
A function that can return NULL for non-null arguments is typed | null. That includes the date functions, given text they can't read, and math functions such as sqrt and ln outside their domain. So date(placed_at) is string | null, while date('now') is a string.
date('not a date')nulldate('2026-10-20', '+1 fortnight')nullsqrt(-1)nullValues of several kinds
A value that is one of several, from coalesce, ifnull, case, iif, a min or max of several arguments, a lag or lead with a default, or a union's column, is typed by the values it can be. It's unknown where one of them may be another kind, such as json_extract(…).
select coalesce(ships_on, placed_at) as day,
coalesce(ships_on, json_extract('{"a":1}', '$.a')) as mixed
from ordersvoid{ day: string; mixed: unknown | null }[]Functions missing from the table
A built-in function missing from Reylo's table, such as unhex, returns unknown. Cast its result to type it:
select unhex('ff') as raw,
cast(unhex('ff') as blob) as bytesvoid{ raw: unknown | null; bytes: Uint8Array | null }Your driver's SQLite may be older than Reylo's: libSQL ships SQLite 3.45.1, which has no json_pretty. So the type check also runs each query on every one of better-sqlite3, @libsql/client, sqlite3 and @tursodatabase/database in your package.json, and reports any error with that driver's message. With wrangler in your package.json and a D1 database in its config, it also runs each query on Cloudflare D1 as wrangler runs it locally, since D1 blocks some of SQLite's functions, such as json_pretty and power. Durable Objects block the same ones. It can't check bun:sqlite.
Nullability
Every column is typed | null unless Reylo can tell it never is null. SQLite itself reports nothing useful here, so Reylo reads your schema and the query:
NOT NULL columns
A table's column is non-null if it's declared NOT NULL.
select name, nick
from usersvoid{ name: string; nick: string | null }[]Outer joins
An outer join makes the joined table's columns nullable.
select u.name, o.total_cents
from users u
left join orders o on o.user_id = u.idvoid{ name: string; total_cents: number | null }[]Tests that rule out NULL
A test that fails on NULL in the where makes its column non-null, and on a joined table shows the row exists.
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 nullvoid{ name: string; total_cents: number; ships_on: string }[]Expressions
Reylo's table of SQLite's functions says which are non-null over non-null inputs: length, coalesce, || and arithmetic are; division by a column is not, since dividing by zero returns NULL. Arithmetic on a REAL with no answer is the one exception (Limits).
select upper(nick) as shout,
coalesce(nick, name) as label,
length(name) as n,
100 / id as per_id
from usersvoid{
shout: string | null;
label: string;
n: number;
per_id: number | null;
}[]Aggregates
An aggregate other than count returns NULL over no rows.
select count(*) as n, max(total_cents) as top
from ordersvoid{ n: number; top: number | null }Window functions
A window function is typed like any other function: lag is nullable, since the first row has no previous row. Over a window, min and max follow their argument, unless a filter or a frame that can leave out the current row keeps them nullable. A sum or avg is nullable, since SQLite returns NaN as NULL, and so is group_concat, which libSQL and Turso return as NULL over a frame of empty strings.
select id,
lag(total_cents) over (order by id) as previous_total,
row_number() over (order by id) as n,
max(total_cents) over (partition by user_id) as largest_total
from ordersvoid{
id: number;
previous_total: number | null;
n: number;
largest_total: number;
}[]To tell Reylo a value is never null, say so in the query: coalesce(x, …) gives a default, and where x is not null filters out the nulls.
Cardinality
Reylo infers how many rows a query returns, and types .run()'s result to match:
.run() returns | cardinality | When the query |
|---|---|---|
Row | one | is an aggregate without a group by, values of one row, or inserts one row and has returning, an on conflict do update included |
Row | null | maybeOne | matches every column of a primary key, an integer primary key or a unique index with =, through joins on the joined table's key, and through a subquery, a with query or a view that keeps the key; has limit 1; is a distinct or group by over values the where pins; or is an update or delete by key with returning |
void | none | is an insert, update, delete or replace without returning |
Row[] | many | is anything else |
These don't count as a key match, so the query returns Row[]:
| Case | Example |
|---|---|
| A unique index on an expression, or a partial one | where lower(email) = :email, even with a unique index on lower(email) |
A TEXT or untyped key compared with a number, which SQLite converts, so '1' and '01' both match | where code = cast(:code as integer) |
A key compared under another collation: SQLite uses the left side's, so 'x' and 'X' both match | r.k = c.code, with a NOCASE column on the left |
A column you declared named rowid, which isn't the real rowid | where rowid = :id |
A subquery, a with query or a view that passes the key through an expression, repeats rows with union all, or joins several rows per row of the keyed table | select * from (select id + 0 as id from users) u where u.id = :id |
Parameters
Placeholders
Write parameters as :name, @name or $name, one style per query, and pass .run() an object with those names, without the prefix. You can use SQLite's ? or ?1 instead, but not beside names; then .run() takes an object keyed by number, { 1: 42 }.
What a parameter takes
A parameter accepts the same values .run() returns for its type, so you can pass a row straight into the next query:
| Affinity | A parameter takes |
|---|---|
INTEGER | number |
A BIGINT, INT8 or UNSIGNED BIG INT column | number or bigint |
REAL | number |
TEXT | string |
BLOB | Uint8Array |
NUMERIC, a BOOLEAN or DATE column's included | number, string or boolean |
| A typed JSON parameter | what its schema admits |
A BLOB parameter takes only a Uint8Array, and rejects base64 or hex text before the query runs, since SQLite would store it as TEXT. Decode the text into a Uint8Array first.
Where the query takes no null for a JSON parameter, null is JSON's null, the text null. A coalesce or a null test doesn't make a parameter take null, so write coalesce(:doc::nullable(json), doc) to keep doc for null.
Parameters without a type
SQLite can type a parameter only in these places:
- compared with a typed value, including inside a row comparison (
(created_at, id) < (:at, :id)) - written to a column by an
insertorupdate - in a
cast, arithmetic,limitandoffset,in,between, or acasebranch - as an argument of a built-in function in Reylo's table (
lower(:s)is text,abs(:n)a number)
Anywhere else, such as a bare select :x or :x is null, the parameter is unknown until you cast it:
import , { type Params } from "./lite_echo.sql";select :value as value, :count::int as countBy default, such a parameter is a type-check error asking for a cast. With strict_params: false in .reylo.yaml, it's typed unknown instead, as above.
Parameters that may be null
A parameter accepts null in two places. Such a parameter is optional in .run(), and leaving it out passes null.
Cast to nullable(…)
A cast to nullable(type) makes the parameter that type or null.
select id, name
from users
where name = coalesce(:name::nullable(text), name){ name?: string | null }{ id: number; name: string }[]Written to a nullable column
Written directly into a column that accepts NULL, with no cast. A column accepts it unless it's NOT NULL or a check (… is not null) covers it.
insert into orders (user_id, total_cents, ships_on)
values (:user_id, :total_cents, :ships_on){
user_id: number;
total_cents: number;
ships_on?: string | null;
}voidAny other cast sets the parameter's whole type, so :email::text never accepts null. Neither coalesce nor a test for null (:p is null) makes a parameter nullable, and one cast to nullable(…) but written to a column that refuses null is an error.
Casts
SQLite has no :: casts, so Reylo turns :p::type into SQLite's CAST. It also checks your value against the type before the query runs, since SQLite's CAST never fails: CAST('abc' AS INTEGER) is 0. SQLite's own cast(:p as …) works too.
| You write | SQLite runs |
|---|---|
::int, ::integer, ::bigint | CAST(? AS INTEGER) |
::real, ::float, ::double precision | CAST(? AS REAL) |
::text, ::varchar(20) | CAST(? AS TEXT) |
::numeric, ::decimal | CAST(? AS NUMERIC) |
::date, ::time, ::timestamp, ::uuid | CAST(? AS TEXT), since SQLite's CAST to a date reads '2026-10-12' as 2026 |
::blob | CAST(? AS BLOB) |
::json, ::jsonb | json(?), jsonb(?) |
::boolean | An error, since SQLite has no boolean: pass 1 or 0 and write ::integer |
An array, such as ::text[] | An error, since SQLite has no arrays |
The check rejects a non-integer for ::int, and any value outside SQLite's 64-bit range.
Database errors
With .try(), an error SQLite raised comes back as { error }, with the same fields on every driver:
| Field | What it holds |
|---|---|
code | The result code's name, as "SQLITE_CONSTRAINT_UNIQUE": the most specific one your driver gives |
errcode | Its number, as 2067 |
message | SQLite's message |
table, columns | On a unique, not-null or STRICT type violation, the table and its columns |
constraint | On a check the row fails, the check's name, or its expression when it has none; on a unique index of an expression, the index |
Most drivers give the extended code, such as SQLITE_CONSTRAINT_UNIQUE. The sqlite3 driver and the sqlite package give only the primary one, SQLITE_CONSTRAINT, and Turso's engine gives none from 0.8 on, so its code is undefined and only message names the failure. SQLite doesn't name the foreign key a row fails.
When the query fires no trigger, its error type is narrowed: a unique, not-null or STRICT type violation names only the tables and columns it writes, and the indexes of their expressions. A CHECK isn't narrowed, since SQLite's catalog doesn't name it, so the index names apply to the extended code alone: under the primary SQLITE_CONSTRAINT, the constraint may be a CHECK's.
Drivers
Reylo decodes its query results itself, so settings on your database connection don't affect Reylo's queries, and Reylo's don't affect yours. Type checking needs no driver: the engine reads your schema from the database file itself.
Each example below runs this query:
select id, name, email from users where id = :idbetter-sqlite3
Pass the database. It's synchronous, so .runSync() works too, inside its db.transaction(fn) as well:
import from "better-sqlite3";
import from "./lite_get_user.sql";
const = new ("app.db");
const = await .(, { : 1 });
const = .((: number) =>
.(, { }),
);
const = (1);An integer past 2^53 comes back exact.
node:sqlite
Pass a DatabaseSync. It's synchronous, so .runSync() works too, between a begin and a commit:
import { } from "node:sqlite";
import from "./lite_get_user.sql";
const = new ("app.db");
const = await .(, { : 1 });
.("begin");
const = .(, { : 1 });
.("commit");An integer past 2^53 comes back exact.
bun:sqlite
Pass the database. It's synchronous, so .runSync() works too, inside its db.transaction(fn) as well:
import { } from "bun:sqlite";
import from "./lite_get_user.sql";
const = new ("app.db");
const = await .(, { : 1 });
const = .((: number) =>
.(, { }),
);
const = (1);An integer past 2^53 comes back exact.
libSQL
Pass the client, or a transaction taken from it:
import { } from "@libsql/client";
import from "./lite_get_user.sql";
const = ({ : "file:app.db", : "bigint" });
const = await .(, { : 1 });
const = await .("write");
await .(, { : 1 });
await .();An integer past 2^53 comes back exact with intMode: "bigint" or "string"; with the default intMode, libSQL itself throws a RangeError. With intMode: "string", an integer in a column typed unknown comes back as a string of digits, since libSQL can't tell it from text.
Turso
Pass the database, or the transaction .transactionAsync() hands its callback. The Database of @tursodatabase/database/compat is synchronous, so .runSync() works on it, inside its db.transaction(fn) as well:
import { } from "@tursodatabase/database";
import { } from "@tursodatabase/database/compat";
import from "./lite_get_user.sql";
const = await ("app.db");
const = await .(, { : 1 });
await .(() => .(, { : 1 }))();
const = new ("app.db");
const = .((: number) =>
.(, { }),
);
const = (1);An integer past 2^53 comes back exact.
Cloudflare D1
Pass the binding, or a session from .withSession():
import type { } from "@cloudflare/workers-types";
import from "./lite_get_user.sql";
export default {
async (: Request, : { : }) {
const = await .(., { : 1 });
const = await .(..(), { : 1 });
return .();
},
};D1 rounds an integer past 2^53, so Reylo throws an error rather than return it, even for a BIGINT column.
Durable Objects
Pass the object's ctx.storage.sql. It's synchronous, so .runSync() works on it, inside ctx.storage.transactionSync(fn) as well:
import { } from "cloudflare:workers";
import from "./lite_get_user.sql";
export class extends {
(: number) {
return .(this..., { });
}
(: number) {
const { } = this.;
return .(() =>
.(., { }),
);
}
}The storage rounds an integer past 2^53, so Reylo throws an error rather than return it, even for a BIGINT column. It can't bind a bigint parameter past 2^53 either, so Reylo rejects one unless it's written to a BIGINT column.
sqlite3
Pass the database:
import from "sqlite3";
import from "./lite_get_user.sql";
const = new .("app.db");
const = await .(, { : 1 });The driver rounds an integer past 2^53, so Reylo throws an error rather than return it, even for a BIGINT column. It can't bind a bigint parameter past 2^53 either, so Reylo rejects one unless it's written to a BIGINT column.
sqlite
Pass the database open() returns, which can run a transaction:
import { } from "sqlite";
import from "sqlite3";
import from "./lite_get_user.sql";
const = await ({ : "app.db", : . });
const = await .(, { : 1 });
await .("begin");
await .(, { : 1 });
await .("commit");The driver rounds an integer past 2^53, so Reylo throws an error rather than return it, even for a BIGINT column. It can't bind a bigint parameter past 2^53 either, so Reylo rejects one unless it's written to a BIGINT column.
Bun.sql
Pass the client, or a transaction or savepoint taken from it:
import { } from "bun";
import from "./lite_get_user.sql";
const = new ({
: "sqlite",
: "app.db",
: true,
});
const = await .(, { : 1 });
await .(async () => {
await .(, { : 1 });
await .(() => .(, { : 1 }));
});Without safeIntegers, Bun rounds an integer past 2^53, so Reylo throws an error rather than return it, even for a BIGINT column. On Bun 1.4.3, Bun.sql returns no rows for a query containing a " inside a single-quoted string, such as json_extract('{"a":1}', '$.a'): pass the document as a parameter instead, json_extract(:doc::text, '$.a').
Drizzle
Pass a Drizzle database over better-sqlite3, bun:sqlite, libSQL, D1 or Durable Objects, or a transaction taken from it. Over D1, Drizzle has no transactions. Over better-sqlite3, bun:sqlite and Durable Objects, .runSync() works inside its transactions:
import { } from "drizzle-orm/better-sqlite3";
import from "./lite_get_user.sql";
const = ("app.db");
const = await .(, { : 1 });
.(() => .(, { : 1 }));Limits
What Reylo can't type on SQLite, and what to do instead:
| Limit | Instead |
|---|---|
Declared types outside a STRICT table, which SQLite doesn't enforce, so a value of another kind makes .run() throw | Make tables strict where you can |
| Functions your app registers, or an extension's, which Reylo's SQLite doesn't have: the type check fails with "no such function" | Call the function in TypeScript on what .run() returns |
Attached databases and TEMP tables, since the type check reads one database file | None: a query on one is an error in the type check |
Virtual table modules on a driver the type check doesn't run the query on, such as bun:sqlite or a Durable Object: Reylo's SQLite has FTS3, FTS4, FTS5, R-tree, Geopoly and dbstat | Check that driver's SQLite yourself |
REAL arithmetic with no answer, such as 1e999 * 0, which SQLite returns as NULL where Reylo types a number | Write coalesce(price * qty, 0) where a REAL may hold an infinity |
| A query past 350 nested expressions, which SQLite rejects with "Expression tree is too large" | Split the query up |
A statement other than a query, such as create index or pragma table_info(users) | None: it returns nothing, or rows whose columns are all unknown and nullable |
| A file of several statements, such as a migration | Put each statement in a file of its own |