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 versions | 8.0.22 or later; tested on 8.0, 8.4, 9.7 and 26.7 |
| Connection | A mysql:// URL in DATABASE_URL or direct_url |
| Drivers | mysql2, mariadb, PlanetScale and Bun.sql, and Drizzle over mysql2 or PlanetScale (Drivers) |
| Type checking | Reylo'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 type | TypeScript | The value .run() returns |
|---|---|---|
TINYINT, SMALLINT, MEDIUMINT, INT, YEAR | number | 2026 |
BIGINT, BIGINT UNSIGNED, COUNT(*) | bigint | 9007199254740993n |
BOOLEAN (a TINYINT(1)), BIT(1) | boolean | true |
A comparison, IS NULL, IN, BETWEEN, LIKE, EXISTS, NOT, AND, OR | boolean | false |
DECIMAL, SUM(…), AVG(…), / | string | "12.50" |
FLOAT, DOUBLE | number | 1.1 |
CHAR, VARCHAR, TEXT | string | "Ada" |
ENUM | string, not a union of its labels | "member" |
SET | string | "a,c" |
DATE | string | "2026-10-20" |
DATETIME, TIMESTAMP | string | "2026-10-20T10:00:00.123" |
TIME | string | "10:00:00" |
JSON | Json | { a: 1 } |
JSON a JSON Schema CHECK holds | what the schema admits | { x: 10 } |
json_object(…), json_arrayagg(…) | the object or array it builds | { total: 12.5 } |
BINARY, VARBINARY, BLOB, BIT wider than 1 | Uint8Array | Uint8Array.of(1, 2, 255) |
GEOMETRY | unknown | its bytes, a Uint8Array |
VECTOR | number[] | [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 .();select count(*) as total, cast(count(*) as double) as total_number
from ordersWhen 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.
CAST(1 AS SIGNED) + 12nCAST(1.5 AS DOUBLE) + 12.5CAST(1.5 AS DECIMAL(2,1)) + 1"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.
CAST(1.1 AS FLOAT)1.100000023841858CAST(1.1 AS DOUBLE)1.1Dates 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 });select placed_at, ships_on from orders where id = :idIf 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.
CAST('2026-10-20 10:00:00.123456' AS DATETIME(6))"2026-10-20T10:00:00.123456"CAST('2026-10-20 10:00:00' AS DATETIME(3))"2026-10-20T10:00:00.000"CAST('2026-10-20 10:00:00' AS DATETIME)"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 .();select id, active, role = 'admin' as is_admin from usersWhat 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.
'Ada' LIKE 'B%'falseIF(1 = 1, TRUE, FALSE)trueWhere 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.
TRUE + 01nmax(1 = 1)1n(SELECT b FROM (SELECT 1 = 1 AS b) d)1nValues 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 .();select kind, payload from eventsJSON 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 .();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_idA schema's type carries through json_arrayagg:
import from "./my_events_by_kind.sql";
const groups = await .();select kind, json_arrayagg(payload) as payloads
from events
group by kindValues 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.
CAST('{"n": 9007199254740993}' AS JSON){ n: 9007199254740992 }json_object('at', CAST('2026-10-20 10:00:00' AS DATETIME)){ at: "2026-10-20 10:00:00.000000" }json_object('b', 1 = 1){ b: true }json_object('b', max(1 = 1)){ 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.
select json_object('p', payload) as wrapped,
json_extract(payload, '$.x') as x
from eventsvoid{
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:
select coalesce(json_arrayagg(id), json_array()) as ids
from orders
where user_id = :user_id{ user_id: number }{ 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.
STRING_TO_VECTOR('[1, 2.5, 0.1]')[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.
select name, email
from usersvoid{ name: string; email: string | null }[]Outer joins
An outer join makes the joined table's columns nullable.
select u.name, o.total
from users u
left join orders o on o.user_id = u.idvoid{ 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 <=>.
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 nullvoid{ 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.
select length(name) as n,
upper(name) as shout,
coalesce(upper(name), '') as shout_or_empty,
id / 2 as half
from usersvoid{
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.
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_idvoid{
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.
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
) dvoid{ 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.
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 ordersvoid{
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.
select role, count(*) as n
from users
group by role with rollupvoid{ 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() returns | cardinality | When the query |
|---|---|---|
Row | one | is an aggregate without a group by, or values row(…) of one row |
Row | null | maybeOne | matches 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 |
void | none | is an insert, update, delete or replace, on duplicate key update included |
Row[] | many | is 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 .();.();select last_insert_id() as idThese don't count as a key match, so the query returns Row[]:
| Case | Example |
|---|---|
| A join that matches one table's key while the other table matches many rows | from 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 picks | join 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 value | on u.id = s.score, where id = cast(:x as double) |
A distinct or group by not over the key | select distinct total … |
| A string key compared with a number, which MySQL compares as doubles | where email = 5 |
A TIMESTAMP key, since two instants can show as one time when clocks go back | where created = :at |
A WITH ROLLUP, which adds rows | group 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 table | select * 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 picks | select 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 type | A parameter takes |
|---|---|
TINYINT, SMALLINT, MEDIUMINT, INT, YEAR | number |
A BIGINT column | number, bigint or a string of digits |
A TINYINT(1) or BIT(1) column | boolean or number |
DECIMAL | string |
FLOAT, DOUBLE | number |
CHAR, VARCHAR, TEXT, ENUM, SET | string |
DATE, DATETIME, TIMESTAMP, TIME | string |
JSON | any JSON value |
BINARY, VARBINARY, BLOB | Uint8Array |
VECTOR | number[] |
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:
select name
from users
where id > :after
limit :n{ after: number; n: number }{ 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 > :sinceA 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.
select id, name
from users
where role = coalesce(:role::nullable(text), role){ role?: string | null }{ 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.
insert into orders (user_id, total, ships_on)
values (:user_id, :total, :ships_on){ user_id: number; total: string; 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, :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 write | MySQL runs |
|---|---|
::int, ::bigint, ::smallint | CAST(? AS SIGNED) |
::int unsigned | CAST(? AS UNSIGNED) |
::numeric(10,2), ::decimal(10,2) | CAST(? AS DECIMAL(10,2)) |
::real, ::double precision | CAST(? 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, ::datetime | CAST(? AS DATE), CAST(? AS TIME), CAST(? AS DATETIME(6)) |
::json | CAST(? AS JSON) |
::bytea, ::blob | CAST(? AS BINARY) |
::int in limit or offset | ?, since MySQL takes only a bare value there |
::timestamptz, ::boolean | An 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:
| Field | What it holds |
|---|---|
code | The 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 |
errno | The error's number, as 1062 |
sqlState | The SQLSTATE, as "23000" |
message | The server's message |
key | On a duplicate entry, the key as the message names it, as users.email |
constraint | On a foreign key or a check the row fails, the constraint |
table | On a foreign key the row fails, the table it belongs to |
column | On 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 = :idmysql2
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:
| Limit | Instead |
|---|---|
An ENUM's labels: an ENUM is typed string | Check the value in TypeScript where you need the union |
| Where in a query an error is, which MySQL doesn't say | None: the type check marks the whole query |
| A query Reylo's parser can't read | None: 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 error | Write it as plain SQL |
with … insert, which MySQL 8.4 rejects | Put the with after insert into … (…), as in Bulk inserts |
| A query past 500 operators or 200 levels of nesting | None: 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 tables | None: it returns nothing, or rows whose columns are all nullable, as MySQL reports |
explain and call, whose rows MySQL describes only once they run | None: each is an error |
| A file of several statements, such as a migration | Put each statement in a file of its own |