# SQLite

> How Reylo types and runs SQL on SQLite. Every type and the value it returns, nulls, row counts, parameters, drivers and limits.

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

This page is the reference behind [the main docs](https://reylo.dev/) for SQLite: the place to look when a type, a `| null`, a row count or a parameter surprises you. [Postgres](https://reylo.dev/postgres) and [MySQL](https://reylo.dev/mysql) have the same page, with every answer in the same place.

| | |
| --- | --- |
| Versions | The type check prepares each statement in Reylo's own SQLite 3.53, against the schema of your database file; your app runs on its driver's SQLite ([Functions](https://reylo.dev/sqlite#functions)) |
| URL | A `file:` or `sqlite:` URL and a path, which starts at the directory of `package.json`, in `DATABASE_URL` or [`direct_url`](https://reylo.dev/#direct_url) |
| Type check | Reads the schema through Node's built-in `node:sqlite`, so it needs no driver, from your database file or [your schema files](https://reylo.dev/#schema-files) |
| Drivers | `better-sqlite3`, `node:sqlite`, `bun:sqlite`, libSQL, Turso's engine, Cloudflare D1, `sqlite3`, `sqlite` and `Bun.sql` ([Drivers](https://reylo.dev/sqlite#drivers)) |

**View the schema behind the examples**

```sql
-- 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'))
);
```

```sql
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;
```

```sql
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](https://reylo.dev/sqlite#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:

| 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` |

### 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:

`main.ts`:

```ts
import userOrders from "./lite_user_orders.sql";

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

`lite_user_orders.sql`:

```sql
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
```

**What stays a `bigint`**

A `BIGINT` column stays a `bigint` through a subquery, a view, `min()`, `max()`, `coalesce()` and `CASE`. These pairs read `users.github_id`, which holds 9007199254740993.

- `github_id` → `9007199254740993n`
- `max(github_id)` → `9007199254740993n`
- `coalesce(github_id, 0)` → `9007199254740993n`
- `(select github_id from users)` → `9007199254740993n`

**What SQLite computes from it**

What SQLite computes from a `BIGINT`, such as `github_id + 1`, `abs(github_id)` or `sum(github_id)`, is an integer like `count(*)`, a `number`. Here `users.github_id` holds 41.

- `github_id + 1` → `42`
- `abs(github_id)` → `41`
- `sum(github_id)` → `41`

**Past what a `number` holds**

A `number` past ±(2^53 − 1), which it cannot hold, is an error that names its column, never a rounded number. Declare the column `BIGINT`, or cast the value to text in the statement: `cast(max(id) as text)`.

- `9007199254740993` → `throws DecodeError`
- `cast(9007199254740993 as text)` → `"9007199254740993"`

**Drivers that read it exactly**

Past 2^53, what a `BIGINT` comes back as depends on the driver. The drivers `better-sqlite3`, `node:sqlite`, `bun:sqlite` and Turso's engine read the exact integer, so it comes back exact on them. The pair reads `users.github_id`, which holds 9007199254740993.

- `github_id` → `9007199254740993n`

**Drivers that round it**

The drivers `sqlite3` and `sqlite`, Cloudflare D1, and `Bun.sql` without `safeIntegers` round an integer past 2^53 before Reylo sees it, and Reylo refuses the rounded number, in a `BIGINT` column too. The pair reads `users.github_id`, which holds 9007199254740993.

- `github_id` → `throws DecodeError`

**On libSQL**

At its default `intMode`, libSQL refuses an integer past 2^53 itself, with a `RangeError` that names no column, as in the first pair. With `intMode: "bigint"` or `"string"` a `BIGINT` is exact, as in the second. Both read `users.github_id`, which holds 9007199254740993.

- `github_id` → `throws RangeError`
- `github_id` → `9007199254740993n`

**No `BIGINT` in a `STRICT` table**

A `STRICT` table takes no `BIGINT` column, so SQLite does not hold one to integers ([Declared types and STRICT tables](https://reylo.dev/sqlite#declared-types-and-strict-tables)).

**Integer parameters**

An `INTEGER` parameter takes a `number`.

### 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.

**Exact amounts**

Keep an exact amount as an integer of its smallest unit, as `orders.total_cents` does, or as text.

- `1250` → `1250`
- `'12.50'` → `"12.50"`

**Sums and averages**

The `sum()` of integers is an integer, and `avg()` and `total()` are reals. Here `orders.total_cents` holds 1250 and 1251.

- `sum(total_cents)` → `2501`
- `avg(total_cents)` → `1250.5`
- `total(total_cents)` → `2501`

### 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:

`main.ts`:

```ts
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
```

`lite_order_dates.sql`:

```sql
select placed_at, ships_on from orders where id = :id
```

**Instants as ISO 8601 text**

Store an instant as ISO 8601 text in UTC with `Z`, as `strftime('%Y-%m-%dT%H:%M:%fZ', 'now')` writes it, which `Temporal.Instant.from()` and `new Date()` read. SQLite's date functions read it too, and since every value has the same width, text order is time order.

- `strftime('%Y-%m-%dT%H:%M:%fZ', '2026-10-20 10:00:00')` → `"2026-10-20T10:00:00.000Z"`

**UTC with no zone**

SQLite's `current_timestamp` and `datetime()` write UTC with no zone, which a reader can't tell from local time.

- `datetime('2026-10-20T10:00:00.000Z')` → `"2026-10-20 10:00:00"`
- `datetime(1792490400, 'unixepoch')` → `"2026-10-20 10:00:00"`

**Columns declared `DATE`**

A column declared `DATE` or `DATETIME` has `NUMERIC` affinity, so it holds a number or a text, `number | string`. Declare it `TEXT`, or `INTEGER` for Unix time.

**Text that is no date**

SQLite's date functions, `date()`, `datetime()`, `unixepoch()` and the rest, answer `NULL` for text they cannot read as a date, so keep every date in one form they read: ISO 8601 text, or Unix time in an `INTEGER`.

- `date('20/10/2026')` → `null`
- `date('2026-10-20T10:00:00.000Z')` → `"2026-10-20"`

**Casting a parameter to a date**

A parameter's `::date` or `::timestamp` cast is SQLite's `CAST(… AS TEXT)` ([Casts](https://reylo.dev/sqlite#casts)), since SQLite's `CAST` to a date reads `'2026-10-12'` as 2026.

- `cast('2026-10-12' as date)` → `2026`

### 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`:

`main.ts`:

```ts
import userFlags from "./lite_user_flags.sql";

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

`lite_user_flags.sql`:

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

**What the statement computes**

A truth value the statement computes is a comparison, `IN`, `BETWEEN`, `EXISTS`, `IS`, `NOT`, `AND`, `OR`, `TRUE`, `FALSE`, `like`, `glob`, `regexp` and `match`, and a `CASE`, `coalesce`, `ifnull`, `iif` or subquery whose every value is one.

- `1 = 1` → `true`
- `'Ada' like 'B%'` → `false`
- `iif(1 = 1, 0 = 1, 1 = 1)` → `false`

**Through a subquery or a view**

A column declared `BOOLEAN` reads as a boolean through any subquery or view too. The pair reads `users.active`, which holds 1.

- `(select active from users)` → `true`

**Flags in a `STRICT` table**

A `STRICT` table takes no `BOOLEAN` column, so a flag there is an `INTEGER`, a `number`.

**Values other than 0 and 1**

SQLite holds a column declared `BOOLEAN` to nothing, so it may hold 2, `'yes'` or a `BLOB`. Reylo reads 0 and 1 as `false` and `true`, and refuses any other value, naming its column. Read the stored number with `active + 0`. Here `users.active` holds 2.

- `active` → `throws DecodeError`
- `active + 0` → `2`

**Truth value parameters**

A truth value parameter is bound as 1 or 0, since `better-sqlite3` refuses a boolean and libSQL would bind `true` as the real 1.0.

**Inside JSON the statement builds**

A truth value is the number SQLite writes, `1` or `0`.

- `json_object('a', 1 = 1)` → `{ a: 1 }`

### 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`:

`main.ts`:

```ts
import orderSummaries from "./lite_order_summaries.sql";

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

`lite_order_summaries.sql`:

```sql
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
```

**Built JSON inside built JSON**

Built JSON nests inside other built JSON through `coalesce`, `case` and a scalar subquery.

- `json_object('n', (select json_object('a', 1)))` → `{ n: { a: 1 } }`
- `json_object('n', case when 1 then json_object('a', 1) end)` → `{ n: { a: 1 } }`

**Where it stops being JSON**

Through a subquery's or a view's column, a cast or `lag`, SQLite may no longer know it is JSON, and Reylo types it `unknown`. A column or `lag` loses it; a cast keeps it on SQLite 3.53 and loses it on Turso's engine.

- `json_object('n', (select j from (select json_object('a', 1) as j)))` → `{ n: "{\"a\":1}" }`
- `json_object('n', lag(json_object('a', 1), 0) over ())` → `{ n: "{\"a\":1}" }`

**Over no rows**

A `json_group_array` over no rows is `[]`, never `null`.

- `(select json_group_array(id) from users where 0)` → `[]`

**Reading a document**

What `json_extract`, `->` and `->>` return is `unknown`, since it depends on the document.

- `json_extract('{"a":1}', '$.a')` → `1`
- `'{"a":[1,2]}' -> '$.a'` → `"[1,2]"`
- `'{"a":"x"}' ->> '$.a'` → `"x"`

**JSON Schema checks**

A `CHECK` that holds a column to a JSON Schema (sqlite-jsonschema's `jsonschema_matches`) types nothing, since `pragma ignore_check_constraints` lets any connection write past it.

**Integers past 2^53**

JavaScript's `JSON.parse` rounds an integer past 2^53 inside a document.

- `json_object('n', 9007199254740993)` → `{ n: 9007199254740992 }`

**Typed JSON parameters**

A typed JSON parameter (`:events::json('…')`) is bound as its JSON text, which `json_each` reads, as in the main docs' [JSON parameters](https://reylo.dev/#typed-json).

### 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.

**Binary parameters**

A `BLOB` parameter takes a `Uint8Array`, a `Buffer` or not, on every driver.

### Declared types and STRICT tables

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

**Names with `INT`**

A type with `INT` in its name is `INTEGER`, whatever else the name holds.

**Names with `CHAR`, `CLOB` or `TEXT`**

Failing that, a type with `CHAR`, `CLOB` or `TEXT` in its name is `TEXT`.

**Names with `BLOB`**

Failing those, a type with `BLOB` in its name is `BLOB`.

**Names with `REAL`, `FLOA` or `DOUB`**

Failing those, a type with `REAL`, `FLOA` or `DOUB` in its name is `REAL`.

**Any other name**

Any other type is `NUMERIC`.

A column declared `BIGINT`, `INT8` or `UNSIGNED BIG INT` is `INTEGER` too, and a `bigint` by its width ([Integers](https://reylo.dev/sqlite#integers)). Outside a `STRICT` table, SQLite stores a value of any class in any column:

`main.ts`:

```ts
import loose from "./lite_loose.sql";

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

`lite_loose.sql`:

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

**A `STRICT` table's promise**

In a `STRICT` table the declared type is a promise: SQLite refuses a value of another class, and the types above are exact. Declare tables `strict` wherever you can.

**What a `STRICT` table takes**

A `STRICT` table takes only `INT`, `INTEGER`, `REAL`, `TEXT`, `BLOB` and `ANY`, so no `BOOLEAN`, `BIGINT`, `DATE` or `NUMERIC` column.

**Columns of any class**

A column declared with no type, or `ANY` in a `STRICT` table, holds any class, so it is `unknown`.

**Columns declared `BOOLEAN`**

A `BOOLEAN` column, which has `NUMERIC` affinity, is a `boolean` by its declared name ([Truth values](https://reylo.dev/sqlite#truth-values)).

**Check constraints**

A `CHECK` narrows no type, since `pragma ignore_check_constraints` lets a connection write past it: `check (role in ('admin', 'member'))` leaves `role` a `string`.

### 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.

**Dates that fail**

A function that SQLite answers with `NULL` for some non-null arguments is `| null` whatever its arguments. The date and time functions return `NULL` for text they cannot read as a date and for a failing modifier, so `date(placed_at)` is `string | null`, while `date('now')` and `datetime('now', '+7 days')` are `string`.

- `date('not a date')` → `null`
- `date('2026-10-20', '+1 fortnight')` → `null`
- `date('2026-10-20', '+7 days')` → `"2026-10-27"`

**Math outside its domain**

The math functions with a domain (`sqrt`, `ln`, `log`, `acos`, `pow`, …) return `NULL` outside it, so they are `| null` too.

- `sqrt(-1)` → `null`
- `sqrt(4)` → `2`

**Formats that write nothing**

The `printf` function returns `NULL` when its format writes nothing, so it is `| null` too.

- `printf('')` → `null`

**Functions the table lacks**

A built-in function the table lacks, such as `unhex`, is `unknown`. Cast its result to type it: `cast(unhex(hash) as blob)`.

**Functions your app registers**

Reylo prepares the statement in its own SQLite, which has none of the functions your application registers (`better-sqlite3`'s `db.function()`, `node:sqlite`'s `db.function()`) or a loadable extension adds. A call to one fails the type check, cast or not: "no such function: slugify". Keep the call out of the `.sql` file and apply it in TypeScript.

**Which functions exist**

Which functions exist is the answer of the SQLite in the Node that runs the type check, from `pragma_function_list`: a call to one it lacks is an error at the call, "no such function: soundex on this database's SQLite". Builds differ in the math functions, `soundex` and each release's additions.

**Your app's SQLite**

Your app runs on its driver's SQLite, which may be older: `better-sqlite3` 12.11 carries 3.53.2, and libSQL, Cloudflare D1 and `bun:sqlite` carry their own. Turso's engine is a rewrite of SQLite, not a build of it, with functions of its own.

**Argument counts**

The type check knows a function by its name alone, so an argument count only a newer SQLite takes (`iif` with two arguments, before 3.48) is not caught.

**Functions in the schema**

A table whose `CHECK`, index or generated column calls a function the type check's SQLite lacks is read with that function stubbed, so it types as any other.

### Values Reylo refuses

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

**An integer past ±(2^53 − 1)**

Reylo refuses one in a column typed `number`, which cannot hold it, and one the driver already rounded, in a `BIGINT` column too. Declare the column `BIGINT`, or read it with `cast(n as text)`.

- `9007199254740993` → `throws DecodeError`

**A truth value other than 0 or 1**

Reylo refuses one in a column declared `BOOLEAN` or `BOOL`, a `BLOB` included. Store 0 or 1 in it, or read the stored number with `active + 0`. Here `users.active` holds the `BLOB` `x'01'`.

- `active` → `throws DecodeError`

## 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](https://reylo.dev/sqlite#limits)).
- **An aggregate** other than `count` is `NULL` over no rows.

`main.ts`:

```ts
import nicknames from "./lite_nicknames.sql";

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

`lite_nicknames.sql`:

```sql
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:

`main.ts`:

```ts
import shipped from "./lite_shipped.sql";

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

`lite_shipped.sql`:

```sql
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
```

`main.ts`:

```ts
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;
//       }[]
```

`lite_order_gaps.sql`:

```sql
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:

| Resolves to | `cardinality` | On SQLite, when the statement |
| --- | --- | --- |
| `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; 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 |

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](https://reylo.dev/sqlite#parameters-that-may-be-null)):

| 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 `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:

`main.ts`:

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

`lite_echo.sql`:

```sql
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`](https://reylo.dev/#strict_params) 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](https://reylo.dev/sqlite#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:

`main.ts`:

```ts
import usersByNick from "./lite_users_by_nick.sql";

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

`lite_users_by_nick.sql`:

```sql
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.

| 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)` |
| `::blob` | `CAST(? AS BLOB)` |
| `::json`, `::jsonb` | `json(?)`, `jsonb(?)` |

- 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.

| Driver | How Reylo reads its values | Lost before Reylo sees it |
| --- | --- | --- |
| `better-sqlite3`, `node:sqlite`, `bun:sqlite` | Reylo's own statement, prepared once per database, with its own integer setting, whatever the database's | |
| libSQL | The driver's rows, each value given its type | At its default `intMode`, an integer past 2^53, which libSQL refuses; with `intMode: "string"`, a value Reylo types `unknown` that SQLite stored as an integer comes back as its digits, since libSQL gives nothing to tell it from text |
| Turso's engine, `@tursodatabase/database` | Reylo's own statement, prepared per call, with its own integer setting, whatever the database's | |
| `sqlite3`, `sqlite` | The driver's rows; the statement is not kept, since a statement left open makes the app's `close()` fail | An integer past 2^53, rounded |
| Cloudflare D1 | The driver's rows; a `BLOB`'s array of numbers made a `Uint8Array` | An integer past 2^53, rounded |
| `Bun.sql` | The driver's rows | An integer past 2^53, rounded, without `safeIntegers`; no rows, with no error, for a statement with a `"` inside a single-quoted string |

- 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](https://reylo.dev/#typed-json), 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](https://reylo.dev/sqlite#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.