Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

MySQL Driver

MySQL migration support with inline ENUM columns, btree/fulltext/spatial indexes, and InnoDB-aware type handling

Capabilities

Feature Supported
Native enums Yes (inline ENUM column type)
Native arrays No (stored as JSON)
Index types btree, fulltext, spatial
Transactions Limited — DDL statements implicitly commit

Type Mappings

VibORM Scalar MySQL Type
string() TEXT
int() INT
bigint() BIGINT
number() DOUBLE
decimal({ precision, scale }) DECIMAL(precision,scale)
boolean() TINYINT(1)
s.dateTime() DATETIME(3)
date() DATE
time() TIME(3)
json() JSON
blob() BLOB
s.enum() Inline ENUM('a', 'b', ...)
Array scalars JSON

DATETIME(3) and TIME(3) preserve JavaScript Date millisecond precision. Decimal-list JSON contains canonical coefficient strings, never JSON numbers, and carries one exact VibORM column-comment marker so introspection can recover the element precision and scale.

Exact decimal requirements

Every effectful MySQL migration command proves MySQL 8.0.16 or later and a strict SQL mode on its pinned session before its first effect. Earlier releases parse CHECK syntax but do not enforce the named constraints that prove a decimal conversion before MODIFY COLUMN. The accepted modes are STRICT_TRANS_TABLES and STRICT_ALL_TABLES.

The V1 provider domain is uniform for scalar and list fields: precision <= 65, scale <= 30, and precision + scale <= 65. A descriptor with precision 65 and scale 30 is therefore outside the admitted API even though MySQL can spell DECIMAL(65,30) in another context.

The proof is command-wide, not a decimal-statement classifier. Migration work can include provider-authored DDL and manual artifacts whose value effects cannot be classified safely before execution. One admission proof therefore protects the complete command without adding a per-statement mode query. Ordinary typed writes do not add a session-admission query. Arithmetic remains fail-loud on non-strict sessions because its UPDATE checks the exact intermediate and final domains before assignment. Configure strict mode for raw SQL and other out-of-band writes.

A scalar descriptor conversion uses a reversible three-statement proof bracket: add one reserved named CHECK, run MODIFY COLUMN, then drop the proof. Each completed DDL statement is an implicit commit boundary. An interruption can therefore leave at most one reserved proof behind.

The next locked migration command authenticates that proof’s reserved name, column, and exact predicate before its first effect. A foreign collision, malformed proof, or multiple reserved proofs is refused before any DROP. The authenticated cleanup is planned once per command, runs in the first sequential program, and is recorded through the same last-completed-statement boundary as the conversion statements. A later failure reports the last statement that completed, including the recovery DROP when it completed.

A decimal list can widen precision without rewriting its member strings when its scale does not change. The migration still adds a validating CHECK, runs MODIFY COLUMN to replace the descriptor marker, then drops the check. A list change that must rewrite JSON members is refused before effects: MySQL DDL implicitly commits, and a column CHECK cannot validate every member of a JSON array before that boundary.

PlanetScale has no admitted effectful migration route. Decimal-list marker create/introspect/alter survival is therefore proved on the MySQL dialect substrate; a hosted PlanetScale connection can only provide the available introspection leg.

Portable String Collation

VibORM’s MySQL adapter targets MySQL 8.0+. New tables use utf8mb4_0900_bin. Its binary, no-pad comparison keeps case, accents, and trailing spaces distinct for primary keys and unique constraints, matching PostgreSQL and SQLite-family behavior.

Existing MySQL tables are not converted automatically because VibORM’s schema snapshot does not track table collation. Apply an explicit migration for each existing table that participates in portable string comparisons:

ALTER TABLE `users`
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_bin;

This rebuilds character columns and their indexes and may lock or copy a large table. Review the database’s online-DDL plan and deploy the migration deliberately; a code upgrade alone does not change existing schema semantics.

Auto-Increment

AUTO_INCREMENT requires an integer column type (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT); the driver rejects it on anything else:

const user = s.model({
  id: s.int().id().increment(),
}).map("users");

Generates:

CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY
);

Enums

MySQL has no standalone enum type — the ENUM is part of the column definition:

const user = s.model({
  status: s.enum(["active", "inactive", "pending"]),
}).map("users");

Generates:

CREATE TABLE `users` (
  `status` ENUM('active', 'inactive', 'pending') NOT NULL
);

An explicit enum .name() affects PostgreSQL type identity only; MySQL always keeps the inline ENUM(...). Removing values requires explicit mappings, including nullable columns.

Changing an enum’s values therefore issues a MODIFY COLUMN for every column using it.

Index Types

The driver generates and introspects btree (default), FULLTEXT, and SPATIAL indexes. InnoDB does not support user-defined hash indexes. See Index Options for declaring indexes on models.

A prefix or functional index is declared with .rawIndex(). VibORM appends its marker as the index’s COMMENT (so the text cannot say COMMENT, ALGORITHM or LOCK) and recognizes the index by it. MySQL changes an index comment only by re-creating the index, so renaming a column a raw index names re-creates it.

Indexed TEXT Columns

InnoDB cannot directly index an unbounded TEXT column through this column-list DSL. A bare string() participating in a primary key, unique constraint, index, or foreign key maps to the conservative VARCHAR(191) default. This limits stored values to 191 characters; adding a key to existing TEXT can narrow the column and is labeled destructive. Choose an explicit native MYSQL.STRING.VARCHAR(length) for a different bound, subject to the provider’s index-byte limits. Strict mode and typed ValueTooLongError prevent silent truncation, but do not add upstream length validation.

Column Defaults

MySQL requires expression defaults for TEXT, BLOB and JSON. The driver retains represented literal defaults and wraps them in parentheses, including JSON objects and list containers. Declared bigint and Date/DateTime literals also become database defaults; DateTime uses the same naive UTC physical encoder as model writes. Function defaults and unsupported application generators remain application-only. Adding a required column to a populated table without a database default requires an explicit manual data migration; push refuses before effects instead of inventing values. Spatial defaults have no supported literal declaration.

Limitations

Limitation Behavior
DDL in transactions Statements implicitly commit — a failed migration can leave earlier statements applied
Native arrays Stored as JSON
Hash indexes Not supported by InnoDB

Next Steps

Was this page helpful?