MySQL

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

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

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

Reylo decodes every value of its own queries itself, on every driver, so each SQL type has one TypeScript type and one form at run time. 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> }.

Integers

A BIGINT, signed or unsigned, is a JavaScript bigint, and so is COUNT(*). MySQL computes most integer expressions as a BIGINT too, so id + 1, length(name), coalesce(n, 0) and lag(n) over (…) are bigint. MySQL's CAST has no 32-bit integer, so cast to double where you want a number:

import countOrders from "./my_count_orders.sql";

const counts = await countOrders.run(db);
       
const counts: {
    total: bigint;
    total_number: number;
}

select count(*) as total, cast(count(*) as double) as total_number
from orders

Decimals

A DECIMAL is its exact text, "12.50", with the column's scale. So are SUM(…) of any column, AVG(…) and a division, which MySQL computes as DECIMAL: sum(total) is "19.75", and id / 2 is "0.5000".

Dates and times

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

import orderDates from "./my_order_dates.sql";

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

select placed_at, ships_on from orders where id = :id

Where your runtime has Temporal, Temporal.PlainDateTime.from() reads the time.

Truth values

MySQL has no boolean type: BOOLEAN is a TINYINT(1), and a comparison is the integer 1 or 0. Reylo makes a table's TINYINT(1) and BIT(1) columns booleans, and every truth value the statement computes:

import userFlags from "./my_user_flags.sql";

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

select id, active, role = 'admin' as is_admin from users

JSON

A JSON value is parsed, and typed unknown, unless a JSON_SCHEMA_VALID CHECK holds the column and the statement reads it straight from its table, or the statement builds the JSON:

import events from "./my_events.sql";

const rows = await events.run(db);
      
const rows: {
    kind: string;
    payload: {
        x: number;
        code?: string;
        [key: string]: unknown;
    };
}[]

select kind, payload from events

JSON the statement builds with json_object and json_arrayagg is typed from their arguments, each value as MySQL writes it in JSON:

import orderSummaries from "./my_order_summaries.sql";

const rows = await orderSummaries.run(db);
      
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

Through json_arrayagg the schema's type holds:

import eventsByKind from "./my_events_by_kind.sql";

const groups = await eventsByKind.run(db);
       
const groups: {
    kind: string;
    payloads: {
        x: number;
        code?: string;
        [key: string]: unknown;
    }[];
}[]

select kind, json_arrayagg(payload) as payloads
from events
group by kind

Binary

A BINARY, a VARBINARY, every BLOB and a BIT wider than 1 are a Uint8Array. The mysql2 driver gives Reylo a Buffer, which is one, so .run() returns it as it is.

ENUM and SET

An ENUM is a string, its label, and a SET is a string of its members joined by commas, "a,c", on every driver.

Values Reylo refuses

Each driver hands Reylo exact integers and dates as text, so one value is refused, with an error that names its column:

Nullability

A column is | null unless Reylo infers it never is. On MySQL that starts from the server: it marks each result column NOT NULL or not, and Reylo reads that mark and adds what the statement's where and group by rule out.

• A table's column is non-null where the table says NOT NULL, exactly.
• An outer join makes the joined table's columns nullable, unless a test in the where that fails on NULL shows the joined row exists, as where p.published_at is not null or where p.id > 0 does, or a later inner join's on does.
• An expression is non-null where the server says so: coalesce and ifnull with a non-null argument, count, length, arithmetic over non-null integers, comparisons.
  • The server is cautious with string and date functions, most casts and division: upper(name), concat(name, '!'), date(created_at), cast(id as char) and id / 2 are | null even when their input is NOT NULL.
• A test in the where that fails on NULL makes the column it tests non-null: where email is not null, where email like '%@%', where lower(email) = :email. A test under an or whose other arm tests another column counts for nothing, nor do coalesce, ifnull and <=>.
• The json_arrayagg aggregate is NULL over no rows, and never under a group by, where every group has a row.
• A WITH ROLLUP adds a row per level whose grouped columns are NULL, and the type says so.
• A correlated subquery in the select list makes the server clear NOT NULL on the outer table's columns. Reylo asks again without the subquery and restores it.

A test in the where shows the joined row exists, so its columns are non-null:

import shipped from "./my_shipped.sql";

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

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

Where the server cannot see what you know, coalesce and ifnull give a default, and the column is non-null:

import shouts from "./my_shouts.sql";

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

select upper(name) as shout, coalesce(upper(name), '') as shout_or_empty
from users

Row counts

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

MySQL has no returning, so a write resolves to nothing. Read a new id with last_insert_id(), on the same connection, since it is that connection's:

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

const db = mysql.createPool(process.env.DATABASE_URL);
const conn = await db.getConnection();
// … run an insert on conn, then:
const { id } = await lastInsertId.run(conn);
       
const id: bigint
conn.release();

select last_insert_id() as id

• A joined table's key counts for nothing: posts join users on users.id = posts.user_id where posts.id = :id is Row[].
• A distinct or group by over values the where pins counts for nothing unless they are the key.
• A string key compared with a number counts for nothing, since MySQL compares the two as doubles: where email = 5 is Row[].
• A TIMESTAMP key counts for nothing: where the clocks go back an hour, two instants show as one time.
• A WITH ROLLUP adds rows, and a with query named like a table is not the table, so neither has a key.
• A statement the server reads otherwise than its text, under the session's sql_mode, counts for nothing (How the text is read).
• A multi-table delete or update resolves to nothing, and so does a select … from … into @variable.
• 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. Reylo sends ? and .run() takes an object keyed by name. A statement may use MySQL's own ? instead, never beside :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.
• An @name is a MySQL user variable, never a parameter.

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

• MySQL describes every integer parameter as a BIGINT, and every BIT one alike. So where the statement names the column, Reylo types a parameter by the column it is compared with or written to:
  • in a comparison such as col = :p, an in or a between;
  • in an insert's values and an update's set.
• Elsewhere Reylo takes what MySQL says: where id = :id on an INT column, and limit :n, take a number.
• Reylo's MySQL drivers bind a true as 1.
• A DATE, DATETIME or TIMESTAMP parameter takes the wall-clock text, "2026-10-12 19:06:48", and refuses a Date: drivers write a Date's time in different zones, the app's, the server's or UTC.
• A JSON parameter reaches the database as its JSON text on every driver, so a string is a JSON string: '{"a":1}' is stored as the string "{\"a\":1}".
• A typed JSON parameter, :events::json('…'), reaches MySQL as cast(? as json) holding its JSON text, after .run() has filled in its schema's defaults and checked the value against the schema.

Parameters without a type

MySQL types every parameter from where the statement uses it, since 8.0.22. One that nothing else types, such as a bare select :x, is a string.

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 (values, insert … set, update … set, on duplicate key update, replace), unless it also fills a NOT NULL column.

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

import usersByRole from "./my_users_by_role.sql";

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

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

Casts

MySQL has no ::, so Reylo writes :p::type as MySQL's CAST, and checks the value against the type before the statement runs, since MySQL's CAST turns a value it cannot read into 0 or NULL instead of failing. MySQL's own cast(:p as …) works too.

• The check refuses an integer out of the type's range, a decimal with more digits than its scale keeps, and a date that does not exist ("2026-02-30"), before the statement runs.
• In limit and offset, which take only a bare value on MySQL, :n::int is written as the bare ?, and a cast to another type is an error.
• A cast to ::timestamptz or ::boolean is an error: MySQL has no type for an instant with its zone, and no boolean to cast to.

How the text is read

MySQL reads its text its own way, and Reylo reads it the same way:

• A backslash in a string escapes the character after it, in single and double quotes: 'it\'s :not' holds no placeholder.
• A # starts a comment to the end of the line, and so does -- with a space after it: :a --:b is :a - (-:b). A block comment ends at its first */.
• Where NO_BACKSLASH_ESCAPES or ANSI_QUOTES would read a statement otherwise, Reylo asks for the session's sql_mode and reads the statement by it.
• A || is OR unless the session sets PIPES_AS_CONCAT: name || 'x' is a number. Write concat(name, 'x').
• A built-in's name with a space before its parenthesis, count (x), is a stored function's unless the session sets IGNORE_SPACE, as mysql2 does.
• Where the session reads the statement otherwise than its text, Reylo keeps the server's types and infers no cardinality and no JSON shape.
• A /*! … */ comment is SQL MySQL runs, so a statement with one is an error: write it as plain SQL.

Drivers

Each driver reads MySQL's protocol as it always does, and Reylo makes only the choice per type that follows, for its own query, on the client you pass. A parser or option you set for your own queries does not reach Reylo's, and none of Reylo's reaches yours.

• The mysql2 driver runs on .execute(), the binary protocol, and its callback API through .promise(), both on the app's own pool or connection.
• On Bun.sql, an expression that may be a BIGINT or a DECIMAL by what is bound (round(:x)) reads digits without a point as a BIGINT, since Bun gives both as text.
• Each driver takes its transactions and connections too: mysql2's promise or callback API, PlanetScale's .transaction(), and Bun.sql's .begin(), .savepoint() and .reserve().

The type check reads the schema through mysql2, so an app on PlanetScale or Bun.sql adds mysql2 as a dev dependency.

Limits

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

• What a write returns. MySQL has no returning, so a write resolves to nothing: read a new id with select last_insert_id() on the same connection, and a changed row with a select after it.
• An ENUM's labels. An ENUM is a string (ENUM and SET).
• Where an error is. A MySQL prepare error has no position, so the type check's error covers the whole statement.
• A statement Reylo's parser does not read. Reylo reads the statement itself for row counts and JSON shapes. One it cannot read keeps the server's types and nullability, resolves to Row[], and its JSON is unknown.
• A with before an insert. MySQL 8.4 refuses with … insert, so a with follows insert into … (…), as in the main docs' JSON parameters.
• A parameter in place of a name. A parameter is a value: select * from :table is MySQL's syntax error, and order by :column sorts by a constant. Choose a column with case :sort when 'name' then name end.
• A statement with more than 500 operators (a + b + …, x = 1 or x = 2 or …), or nested past 200 levels (196 nested function calls, 195 nested cases or 97 nested subqueries), which Reylo types from the server's description alone: any number of rows.
• One statement per file. A file holds one select, insert, update, delete, replace, values or table. A call is an error in the type check, and so is a file of several statements or of DDL, such as a migration.