MySQL

How Reylo types queries on MySQL

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 MySQL: column types, null values, row counts, parameters and drivers.

MySQL versions8.0.22 or later; tested on 8.0, 8.4, 9.7 and 26.7
ConnectionA mysql:// URL in DATABASE_URL or direct_url
Driversmysql2, mariadb, PlanetScale and Bun.sql, and Drizzle over mysql2 or PlanetScale (Drivers)
Type checkingReylo's engine connects to MySQL itself, so your project needs no driver for it
View the schema behind the examples
create table users (
  id int auto_increment primary key,
  name varchar(100) not null,
  email varchar(255) unique,
  role enum('admin', 'member') not null default 'member',
  active boolean not null default true,
  created_at datetime(3) not null default (utc_timestamp(3))
);
create table orders (
  id bigint auto_increment primary key,
  user_id int not null,
  total decimal(12, 2) not null,
  placed_at datetime(6) not null default (utc_timestamp(6)),
  ships_on date,
  receipt blob,
  foreign key (user_id) references users (id)
);
create table events (
  id int auto_increment primary key,
  kind varchar(10) not null,
  payload json not null check (json_schema_valid('{
    "type": "object",
    "properties": {
      "x": { "type": "integer" },
      "code": { "type": "string" }
    },
    "required": ["x"]
  }', payload))
);

Data types

Each MySQL type maps to one TypeScript type, and .run() returns the same value for it on every driver, apart from a FLOAT's last digits (Decimals) and what Bun.sql loses (Drivers):

SQL typeTypeScriptThe value .run() returns
TINYINT, SMALLINT, MEDIUMINT, INT, YEARnumber2026
BIGINT, BIGINT UNSIGNED, COUNT(*)bigint9007199254740993n
BOOLEAN (a TINYINT(1)), BIT(1)booleantrue
A comparison, IS NULL, IN, BETWEEN, LIKE, EXISTS, NOT, AND, ORbooleanfalse
DECIMAL, SUM(…), AVG(…), /string"12.50"
FLOAT, DOUBLEnumber1.1
CHAR, VARCHAR, TEXTstring"Ada"
ENUMstring, not a union of its labels"member"
SETstring"a,c"
DATEstring"2026-10-20"
DATETIME, TIMESTAMPstring"2026-10-20T10:00:00.123"
TIMEstring"10:00:00"
JSONJson{ a: 1 }
JSON a JSON Schema CHECK holdswhat the schema admits{ x: 10 }
json_object(…), json_arrayagg(…)the object or array it builds{ total: 12.5 }
BINARY, VARBINARY, BLOB, BIT wider than 1Uint8ArrayUint8Array.of(1, 2, 255)
GEOMETRYunknownits bytes, a Uint8Array
VECTORnumber[][1, 2.5, -3]

Each example below pairs a SQL value with the value .run() returns for it: select <SQL> as v returns the row { v: <JavaScript> }.

Integers

A BIGINT, signed or unsigned, comes back as a JavaScript bigint. So do COUNT(*) and most integer expressions, such as id + 1 and length(name), since MySQL computes them as BIGINT. To get a number, cast to double:

import  from "./my_count_orders.sql";

const counts = await .();
const counts: {
    total: bigint;
    total_number: number;
}
select count(*) as total, cast(count(*) as double) as total_number
from orders

When an expression's type depends on the value you pass, it's typed as every type it could be. For example, :n + 1 is bigint | number | string, since MySQL may compute it as a BIGINT, a DOUBLE or a DECIMAL.

SQL
CAST(1 AS SIGNED) + 1
JavaScript
2n
SQL
CAST(1.5 AS DOUBLE) + 1
JavaScript
2.5
SQL
CAST(1.5 AS DECIMAL(2,1)) + 1
JavaScript
"2.5"

Decimals

A DECIMAL comes back as its exact text, keeping the column's scale. So do SUM(…), AVG(…) and division, since MySQL computes them as DECIMAL: sum(total) returns "19.75", and id / 2 returns "0.5000".

A FLOAT is a 32-bit float. On mysql2 it comes back as its exact value; mariadb, PlanetScale, and Bun.sql in a query without parameters, round it to its shorter text, 1.1. A DOUBLE is the same on every driver.

SQL
CAST(1.1 AS FLOAT)
JavaScript
1.100000023841858
SQL
CAST(1.1 AS DOUBLE)
JavaScript
1.1

Dates and times

No value comes back as a Date. Each is the text MySQL writes, with a T between the date and the time, and as many fraction digits as the column keeps:

import  from "./my_order_dates.sql";

const order = await .(, { : 1 });
const order: {
    placed_at: string;
    ships_on: string | null;
} | null
select placed_at, ships_on from orders where id = :id

If your runtime has Temporal, read the value with Temporal.PlainDateTime.from().

Fractions of a second

A value has as many fraction digits as its type: a DATETIME(3) always has three, even when they're zero, and a DATETIME has none.

SQL
CAST('2026-10-20 10:00:00.123456' AS DATETIME(6))
JavaScript
"2026-10-20T10:00:00.123456"
SQL
CAST('2026-10-20 10:00:00' AS DATETIME(3))
JavaScript
"2026-10-20T10:00:00.000"
SQL
CAST('2026-10-20 10:00:00' AS DATETIME)
JavaScript
"2026-10-20T10:00:00"

A TIMESTAMP's zone

MySQL returns a TIMESTAMP in the session's time_zone, which Reylo can't know, so the text has no zone. To read UTC, run set time_zone = '+00:00' on the connection, or store UTC in a DATETIME with utc_timestamp(), as the schema above does.

Truth values

MySQL has no boolean type: a BOOLEAN column is a TINYINT(1), and a comparison returns 1 or 0. Reylo types TINYINT(1) and BIT(1) columns as boolean, and every comparison your query makes:

import  from "./my_user_flags.sql";

const rows = await .();
const rows: {
    id: number;
    active: boolean;
    is_admin: boolean;
}[]
select id, active, role = 'admin' as is_admin from users

What counts

These are typed boolean: comparisons, IS, IN, BETWEEN, LIKE, REGEXP, EXISTS, NOT, AND, OR, XOR, TRUE and FALSE, and a CASE, IF, COALESCE, IFNULL, NULLIF or subquery whose every value is one of them.

SQL
'Ada' LIKE 'B%'
JavaScript
false
SQL
IF(1 = 1, TRUE, FALSE)
JavaScript
true

Where MySQL hides the column

A boolean column stays boolean through a subquery in from or a with query. It becomes a number where MySQL hides the column: through a view, in max(active) or active + 0, and in a comparison made inside a subquery.

SQL
TRUE + 0
JavaScript
1n
SQL
max(1 = 1)
JavaScript
1n
SQL
(SELECT b FROM (SELECT 1 = 1 AS b) d)
JavaScript
1n

Values other than 0 and 1

A TINYINT(1) can hold any number from −128 to 127. Reylo reads 0 and 1 as false and true, and throws an error naming the column for any other value. Store only 0 or 1, or read the number with active + 0.

JSON

A JSON column is typed Json, any JSON value, unless a JSON_SCHEMA_VALID CHECK constrains it and your query selects it straight from its table, or your query builds the JSON. Reylo exports the type:

type Json = string | number | boolean | null | { [key: string]: Json | undefined } | Json[];

Here payload is typed by its schema:

import  from "./my_events.sql";

const rows = await .();
const rows: {
    kind: string;
    payload: {
        x: number;
        code?: string;
        [key: string]: Json | undefined;
    };
}[]
select kind, payload from events

JSON your query builds with json_object and json_arrayagg is typed from what you put in it:

import  from "./my_order_summaries.sql";

const rows = await .();
const rows: {
    name: string;
    summary: {
        total: number;
        placed_at: string;
    };
}[]
select u.name,
  json_object('total', o.total, 'placed_at', o.placed_at) as summary
from orders o
join users u on u.id = o.user_id

A schema's type carries through json_arrayagg:

import  from "./my_events_by_kind.sql";

const groups = await .();
const groups: {
    kind: string;
    payloads: ({
        x: number;
        code?: string;
        [key: string]: Json | undefined;
    })[];
}[]
select kind, json_arrayagg(payload) as payloads
from events
group by kind

Values inside a document

Inside JSON, a value can differ from the same value in a column. Numbers past 2^53 lose precision in JSON.parse, a DATETIME is MySQL's text, and a boolean may be true or 1, depending on how MySQL writes it.

SQL
CAST('{"n": 9007199254740993}' AS JSON)
JavaScript
{ n: 9007199254740992 }
SQL
json_object('at', CAST('2026-10-20 10:00:00' AS DATETIME))
JavaScript
{ at: "2026-10-20 10:00:00.000000" }
SQL
json_object('b', 1 = 1)
JavaScript
{ b: true }
SQL
json_object('b', max(1 = 1))
JavaScript
{ b: 1 }

Where a schema's type applies

A schema's type reaches the column when you select it straight from its table, and inside json_object or json_arrayagg. Through a subquery or a with query, under a union, in an expression, and as a parameter written to the column, it's Json.

Query
select json_object('p', payload) as wrapped,
  json_extract(payload, '$.x') as x
from events
Params
void
Returns
{
  wrapped: {
    p: {
      x: number;
      code?: string;
      [key: string]: Json | undefined;
    };
  };
  x: Json;
}[]

An empty array as a default

Over no rows, json_arrayagg returns NULL. A default of an empty array keeps the array's type:

Query
select coalesce(json_arrayagg(id), json_array()) as ids
from orders
where user_id = :user_id
Params
{ user_id: number }
Returns
{ ids: number[] }

MySQL validates every schema as draft 4, whatever its $schema says, so const, if, prefixItems and contains don't narrow the type.

Vectors

A VECTOR, new in MySQL 9, comes back as a number[] of 32-bit floats. A vector parameter takes a number[], which works anywhere MySQL accepts a VECTOR; NaN and infinities are rejected before the query runs.

SQL
STRING_TO_VECTOR('[1, 2.5, 0.1]')
JavaScript
[1, 2.5, 0.10000000149011612]

Nullability

Every column is typed | null unless Reylo can tell it never is null. On MySQL, it starts from what the server reports for each column, then adds what the query's where and group by rule out:

NOT NULL columns

A table's column is non-null if it's NOT NULL.

Query
select name, email
from users
Params
void
Returns
{ name: string; email: string | null }[]

Outer joins

An outer join makes the joined table's columns nullable.

Query
select u.name, o.total
from users u
left join orders o on o.user_id = u.id
Params
void
Returns
{ name: string; total: string | null }[]

Tests that rule out NULL

A test that fails on NULL, in the where or a later inner join's on, makes its column non-null, and on a joined table shows the row exists. It doesn't count under an or that tests another column, or through coalesce, ifnull and <=>.

Query
select u.name, o.total, o.ships_on
from users u
left join orders o on o.user_id = u.id
where o.ships_on is not null
Params
void
Returns
{ name: string; total: string; ships_on: string }[]

Expressions

An expression is non-null when the server says so: coalesce and ifnull with a non-null argument, count, length, comparisons and integer arithmetic. The server is cautious with string and date functions, most casts and division, which stay nullable even over a NOT NULL column.

Query
select length(name) as n,
  upper(name) as shout,
  coalesce(upper(name), '') as shout_or_empty,
  id / 2 as half
from users
Params
void
Returns
{
  n: bigint;
  shout: string | null;
  shout_or_empty: string;
  half: string | null;
}[]

Aggregates

Without a group by, an aggregate other than count, bit_and, bit_or and bit_xor returns NULL over no rows. With one, every group has rows: json_arrayagg and json_objectagg are non-null, and sum, avg, min and max are non-null when their argument is.

Query
select user_id,
  json_arrayagg(id) as order_ids,
  max(ships_on) as last_ships_on,
  sum(total) as spent
from orders
group by user_id
Params
void
Returns
{
  user_id: number;
  order_ids: number[];
  last_ships_on: string | null;
  spent: string;
}[]

Subqueries

A scalar subquery that aggregates without a group by returns one row, so its count is non-null, whether it reads a table, a subquery in from or a with query. A column read from a with query or a subquery in from is as nullable as it is in that query.

Query
select d.id, d.orders
from (
  select u.id, (select count(*) from orders o where o.user_id = u.id) as orders
  from users u
) d
Params
void
Returns
{ id: number; orders: bigint }[]

Window functions

Over a window, sum, avg, min and max follow their argument, and json_arrayagg and json_objectagg are never null. A frame that can leave out the current row, such as rows between 1 following and 2 following, or a range frame with an offset keeps them nullable.

Query
select id,
  sum(total) over (order by placed_at) as running_total,
  max(ships_on) over (partition by user_id) as last_ships_on
from orders
Params
void
Returns
{
  id: bigint;
  running_total: string;
  last_ships_on: string | null;
}[]

WITH ROLLUP

A WITH ROLLUP adds summary rows whose grouped columns are NULL, and the types include null.

Query
select role, count(*) as n
from users
group by role with rollup
Params
void
Returns
{ role: string | null; n: bigint }[]

To tell Reylo a value is never null, give it a default with coalesce or ifnull, or filter out the nulls with where x is not null.

Cardinality

Reylo infers how many rows a query returns, and types .run()'s result to match:

.run() returnscardinalityWhen the query
Rowoneis an aggregate without a group by, or values row(…) of one row
Row | nullmaybeOnematches every column of a primary key or unique key with =, against a value of the key's own type, through joins on the joined table's numeric key, and through a derived table, a with query or a view that keeps the key; is a distinct or group by over values the where pins; or has limit 1
voidnoneis an insert, update, delete or replace, on duplicate key update included
Row[]manyis anything else

MySQL has no returning, so a write returns nothing. To read a new row's id, select last_insert_id() on the same connection:

import  from "mysql2/promise";
import  from "./my_last_insert_id.sql";

const  = .(..);
const  = await .();
// … run an insert on conn, then:
const { id } = await .();
const id: bigint
.();
select last_insert_id() as id

These don't count as a key match, so the query returns Row[]:

CaseExample
A join that matches one table's key while the other table matches many rowsfrom posts p join users u on u.id = p.user_id where u.id = :id
A join on a string key, compared under a collation MySQL picksjoin users u on u.email = p.email
An integer or decimal key compared with a float or double column, or with a parameter cast to one, which MySQL compares as doubles, so two keys past 2^53 match one valueon u.id = s.score, where id = cast(:x as double)
A distinct or group by not over the keyselect distinct total …
A string key compared with a number, which MySQL compares as doubleswhere email = 5
A TIMESTAMP key, since two instants can show as one time when clocks go backwhere created = :at
A WITH ROLLUP, which adds rowsgroup by id with rollup
A derived table, 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 tableselect * from (select id + 0 as id from users) u where u.id = :id
A union of columns that are not all integers or decimals, which MySQL compares under a type it picksselect email from users union select name from users

A multi-table delete or update returns nothing, and so does select … into @variable.

Parameters

Placeholders

Write parameters as :name, and pass .run() an object with those names. You can use MySQL's ? instead, but not both in one query; 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:

SQL typeA parameter takes
TINYINT, SMALLINT, MEDIUMINT, INT, YEARnumber
A BIGINT columnnumber, bigint or a string of digits
A TINYINT(1) or BIT(1) columnboolean or number
DECIMALstring
FLOAT, DOUBLEnumber
CHAR, VARCHAR, TEXT, ENUM, SETstring
DATE, DATETIME, TIMESTAMP, TIMEstring
JSONany JSON value
BINARY, VARBINARY, BLOBUint8Array
VECTORnumber[]

Integers

MySQL reports every integer parameter as a BIGINT, so Reylo types it from the column it's compared with or written to. Elsewhere it uses MySQL's type, as for limit:

Query
select name
from users
where id > :after
limit :n
Params
{ after: number; n: number }
Returns
{ name: string }[]

Dates and times

A date or time parameter takes the text, never a Date, since drivers convert a Date in different time zones:

import  from "./my_orders_since.sql";

await .(, { : "2026-10-12 19:06:48" });
select id from orders where placed_at > :since

A BINARY, VARBINARY or BLOB parameter takes only a Uint8Array, and rejects base64 or hex text before the query runs, since MySQL would store the text's own bytes. Decode the text into a Uint8Array first.

A JSON parameter is sent as JSON, so a string is stored as a JSON string: '{"a":1}' becomes "{\"a\":1}".

Where the query takes no null for a JSON parameter, null is JSON's null, which a NOT NULL JSON column can hold. 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 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.

Query
select id, name
from users
where role = coalesce(:role::nullable(text), role)
Params
{ role?: string | null }
Returns
{ id: number; name: string }[]

Written to a nullable column

Written directly into a column that accepts NULL, with no cast, in an insert, update, replace or on duplicate key update. A column accepts it unless it's NOT NULL or a check (… is not null) covers it.

Query
insert into orders (user_id, total, ships_on)
values (:user_id, :total, :ships_on)
Params
{ user_id: number; total: string; ships_on?: string | null }
Returns
void

Any other cast sets the parameter's whole type, so :email::text never accepts null. Neither coalesce nor a test for null (:p is null, :p <=> col) makes a parameter nullable, and one cast to nullable(…) but written to a column that refuses null is an error.

Casts

MySQL has no :: casts, so Reylo turns :p::type into MySQL's CAST. It also checks your value against the type before the query runs, since MySQL's CAST silently turns a bad value into 0 or NULL. MySQL's own cast(:p as …) works too.

You writeMySQL runs
::int, ::bigint, ::smallintCAST(? AS SIGNED)
::int unsignedCAST(? AS UNSIGNED)
::numeric(10,2), ::decimal(10,2)CAST(? AS DECIMAL(10,2))
::real, ::double precisionCAST(? AS FLOAT), CAST(? AS DOUBLE)
::text, ::varchar(20), ::uuid?, typed as text, since it then takes the collation of the column it meets
::date, ::time, ::datetimeCAST(? AS DATE), CAST(? AS TIME), CAST(? AS DATETIME(6))
::jsonCAST(? AS JSON)
::bytea, ::blobCAST(? AS BINARY)
::int in limit or offset?, since MySQL takes only a bare value there
::timestamptz, ::booleanAn error: MySQL has neither type

The check rejects an integer out of range, a decimal with more digits than its scale, and a date that doesn't exist ("2026-02-30").

A json_table column of a text type takes your database's collation, where its character set is utf8mb4, as JSON's is. MySQL would give it the connection's, which mysql2 sets to utf8mb4_unicode_ci while MySQL 8 makes a database utf8mb4_0900_ai_ci, and comparing it with a column would fail with "Illegal mix of collations". A type that names its own character set or collate is left as you wrote it, so to compare with a column of another collation, name that collation in the json_table column. Your own cast(… as char) takes the connection's collation, so write ::text instead, which takes the column's.

Database errors

With .try(), an error MySQL raised comes back as { error }, with the same fields on every driver:

FieldWhat it holds
codeThe error's name, as "ER_DUP_ENTRY", from the 1,777 that MySQL 8.4 sends a client, or undefined for a number it doesn't define, such as one a signal sets
errnoThe error's number, as 1062
sqlStateThe SQLSTATE, as "23000"
messageThe server's message
keyOn a duplicate entry, the key as the message names it, as users.email
constraintOn a foreign key or a check the row fails, the constraint
tableOn a foreign key the row fails, the table it belongs to
columnOn a null, a missing value, or a value too long or out of range, the column

Reylo names the error from MySQL's own list whichever driver ran it, since each driver keeps an older list, or MariaDB's. MySQL names a key, a constraint or a column only in its message, which Reylo reads in English, so on a server whose lc_messages is another language those fields are undefined.

When the query fires no trigger and calls no stored function, directly or through a view, its error type is narrowed: a duplicate entry names only the unique keys of the tables it writes, and a foreign key, check, null or value error only their constraints and columns.

Drivers

Reylo decodes its query results itself, so options you set on your client don't affect Reylo's queries, and Reylo's don't affect yours. Type checking reads your schema over the engine's own connection, whatever driver your app uses, PlanetScale and Bun.sql included.

Each example below runs this query:

select id, name, email from users where id = :id

mysql2

Pass a pool or a connection, from the promise API or the callback API, or a connection taken from a pool, which can run a transaction:

import  from "mysql2/promise";
import  from "mysql2";
import  from "./my_get_user.sql";

const  = .(..);
const  = await .(, { : 1 });

const  = await .();
await .();
await .(, { : 1 });
await .();
.();

const  = .(..);
await .(, { : 1 });

mariadb

The mariadb driver works with a MySQL server, as the others do: the type check refuses a MariaDB server. It takes only a mariadb:// URL, which Reylo reads as MySQL. Pass a pool or a connection, from the promise API or the callback API, or a connection taken from a pool, which can run a transaction:

import  from "mariadb";
import  from "mariadb/callback";
import  from "./my_get_user.sql";

const  = .(..);
const  = await .(, { : 1 });

const  = await .();
await .();
await .(, { : 1 });
await .();
await .();

const  = .(..);
await .(, { : 1 });

PlanetScale

Pass a connection, or the transaction it hands its callback:

// @planetscale/database 1.x: a 2.0 client is a type error
import {  } from "@planetscale/database";
import  from "./my_get_user.sql";

const  = ({ : .. });
const  = await .(, { : 1 });

await .(() => .(, { : 1 }));

Version 2.0 isn't supported, since it can't bind parameters.

Bun.sql

Pass the client, or a transaction, savepoint or reserved connection taken from it:

import {  } from "bun";
import  from "./my_get_user.sql";

const  = new (..);
const  = await .(, { : 1 });

await .(async () => {
  await .(, { : 1 });
  await .(() => .(, { : 1 }));
});

const  = await .();
await .(, { : 1 });
.();

Bun loses the microseconds of a DATETIME, a fraction that is all zeros, and a TIME's fraction in a query with parameters, before Reylo sees them. An expression that could be a BIGINT or a DECIMAL (round(:x)) comes back as a bigint when it has no decimal point, since Bun returns both as text. Bun 1.4 can't read a VECTOR at all (ERR_MYSQL_UNSUPPORTED_COLUMN_TYPE), so select vector_to_string(v) instead.

Drizzle

Pass a Drizzle database over mysql2 or PlanetScale, or a transaction or savepoint taken from it:

import {  } from "drizzle-orm/mysql2";
import  from "./my_get_user.sql";

const  = (..);
const  = await .(, { : 1 });

await .(async () => {
  await .(, { : 1 });
  await .(() => .(, { : 1 }));
});

Limits

What Reylo can't type on MySQL, and what to do instead:

LimitInstead
An ENUM's labels: an ENUM is typed stringCheck the value in TypeScript where you need the union
Where in a query an error is, which MySQL doesn't sayNone: the type check marks the whole query
A query Reylo's parser can't readNone: it keeps MySQL's types and nullability, returns Row[], and its JSON is Json
A sql_mode that changes how MySQL reads a query (NO_BACKSLASH_ESCAPES, ANSI_QUOTES, PIPES_AS_CONCAT)None: Reylo keeps MySQL's types, with no row counts or JSON shapes
A /*! … */ comment, which MySQL runs as SQL, so it's an errorWrite it as plain SQL
with … insert, which MySQL 8.4 rejectsPut the with after insert into … (…), as in Bulk inserts
A query past 500 operators or 200 levels of nestingNone: Reylo falls back to MySQL's own types and any number of rows
A statement other than a query, such as create index or show tablesNone: it returns nothing, or rows whose columns are all nullable, as MySQL reports
explain and call, whose rows MySQL describes only once they runNone: each is an error
A file of several statements, such as a migrationPut each statement in a file of its own
Released under the MIT License. Copyright © 2026 Colin McDonnell.

On this page

GitHub