MySQL Column Types in Drizzle ORM

MySQL Type Mapping in Drizzle

Drizzle's drizzle-orm/mysql-core module provides column builders tailored to MySQL's type system. MySQL has some unique types and conventions that differ from PostgreSQL and SQLite.

Common Type Mappings

MySQL Type	Drizzle Builder	Example
INT AUTO_INCREMENT	serial()	serial("id")
INT	integer()	integer("count")
TINYINT	tinyint()	tinyint("rank")
TINYINT(1)	boolean()	boolean("active")
SMALLINT	smallint()	smallint("age")
MEDIUMINT	mediumint()	mediumint("value")
BIGINT	bigint()	bigint("total", { mode: "number" })
VARCHAR(n)	varchar()	varchar("name", { length: 255 })
TEXT	text()	text("body")
BOOLEAN	boolean()	boolean("flag")
DATETIME	datetime()	datetime("created_at")
TIMESTAMP	timestamp()	timestamp("updated_at")
DATE	date()	date("birth_date")
JSON	json()	json("data")
DECIMAL(p,s)	decimal()	decimal("price", { precision: 10, scale: 2 })
FLOAT	float()	float("score")
DOUBLE	double()	double("lat")

MySQL-Specific Patterns

AUTO_INCREMENT: MySQL uses serial() which is a shorthand for BIGINT UNSIGNED AUTO_INCREMENT. For regular auto-incrementing integers, the converter maps INT AUTO_INCREMENT to serial().

TINYINT(1) as Boolean: MySQL historically uses TINYINT(1) to represent booleans. The converter recognizes this pattern and maps it to boolean().

DATETIME vs TIMESTAMP: MySQL distinguishes between DATETIME (no timezone conversion) and TIMESTAMP (stored as UTC). The converter maps each to its corresponding Drizzle builder.

Example Conversion

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(200) NOT NULL,
  total DECIMAL(10, 2) NOT NULL,
  is_paid TINYINT(1) DEFAULT 0,
  notes JSON,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Produces:

import { boolean, datetime, decimal, json, mysqlTable, serial, varchar } from "drizzle-orm/mysql-core";
import { sql } from "drizzle-orm";

export const orders = mysqlTable("orders", {
  id: serial("id").primaryKey(),
  customerName: varchar("customer_name", { length: 200 }).notNull(),
  total: decimal("total", { precision: 10, scale: 2 }).notNull(),
  isPaid: boolean("is_paid").default(false),
  notes: json("notes"),
  createdAt: datetime("created_at").default(sql`CURRENT_TIMESTAMP`),
});