MySQL column types
We have native support for all of them, yet if that's not enough for you - feel free to create custom types.
Integer
A signed integer, stored in 0
, 1
, 2
, 3
, 4
, 6
, or 8
bytes depending on the magnitude of the value
import { int, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
id: int('id')
});
CREATE TABLE `table` (
`int` int,
);
tinyint
import { tinyint, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
tinyint: tinyint('tinyint')
});
CREATE TABLE `table` (
`tinyint` tinyint,
);
smallint
import { smallint, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
smallint: smallint('smallint')
});
CREATE TABLE `table` (
`smallint` smallint,
);
mediumint
import { mediumint, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
mediumint: mediumint('mediumint')
});
CREATE TABLE `table` (
`mediumint` mediumint,
);
bigint
import { bigint, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
bigint: bigint('bigint')
});
bigint('...', { mode: 'number' | 'bigint' });
CREATE TABLE `table` (
`bigint` bigint,
);
real
import { real, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
real: real('real')
});
CREATE TABLE `table` (
`real` real,
);
import { real, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
realPrecision: real('real_precision', { precision: 1,}),
realPrecisionScale: real('real_precision_scale', { precision: 1, scale: 1,}),
});
CREATE TABLE `table` (
`real_precision` real(1),
`real_precision_scale` real(1, 1),
);
decimal
import { decimal, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
decimal: decimal('decimal')
});
CREATE TABLE `table` (
`decimal` decimal,
);
import { decimal, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
decimalPrecision: decimal('decimal_precision', { precision: 1,}),
decimalPrecisionScale: decimal('decimal_precision_scale', { precision: 1, scale: 1,}),
});
CREATE TABLE `table` (
`decimal_precision` real(1),
`decimal_precision_scale` real(1, 1),
);
double
import { double, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
double: double('double')
});
CREATE TABLE `table` (
`double` double,
);
import { double, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
doublePrecision: double('double_precision', { precision: 1,}),
doublePrecisionScale: double('double_precision_scale', { precision: 1, scale: 1,}),
});
CREATE TABLE `table` (
`double_precision` real(1),
`double_precision_scale` real(1, 1),
);
float
import { float, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
float: float('float')
});
CREATE TABLE `table` (
`float` float,
);
serial
SERIAL
is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
import { serial, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
serial: serial('serial')
});
CREATE TABLE `table` (
`serial` serial AUTO_INCREMENT,
);
binary
import { binary, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
binary: binary('binary')
});
CREATE TABLE `table` (
`binary` binary,
);
varbinary
import { varbinary, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
varbinary: varbinary('varbinary', { length: 2}),
});
CREATE TABLE `table` (
`varbinary` varbinary(2),
);
char
import { char, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
char: char('char'),
});
CREATE TABLE `table` (
`char` char,
);
varchar
You can define { enum: ["value1", "value2"] }
config to infer insert
and select
types, it won't check runtime values
import { varchar, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
varchar: varchar('varchar', { length: 2 }),
});
// will be inferred as text: "value1" | "value2" | null
varchar: varchar('varchar', { length: 6, enum: ["value1", "value2"] })
CREATE TABLE `table` (
`varchar` varchar(2),
);
text
You can define { enum: ["value1", "value2"] }
config to infer insert
and select
types, it won't check runtime values
import { text, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
text: text('text'),
});
// will be inferred as text: "value1" | "value2" | null
text: text('text', { enum: ["value1", "value2"] });
CREATE TABLE `table` (
`text` text,
);
boolean
import { boolean, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
boolean: boolean('boolean'),
});
CREATE TABLE `table` (
`boolean` boolean,
);
date
import { boolean, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
date: date('date'),
});
CREATE TABLE `table` (
`date` date,
);
datetime
import { datetime, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
datetime: datetime('datetime'),
});
datetime('...', { mode: 'date' | "string"}),
datetime('...', { fsp : 0..6}),
CREATE TABLE `table` (
`datetime` datetime,
);
import { datetime, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
datetime: datetime('datetime', { mode: 'date', fsp: 6 }),
});
CREATE TABLE `table` (
`datetime` datetime(6),
);
time
import { time, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
time: time('time'),
timefsp: time('time_fsp', { fsp: 6 }),
});
time('...', { fsp: 0..6 }),
CREATE TABLE `table` (
`time` time,
`time_fsp` time(6),
);
year
import { year, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
year: year('year'),
});
CREATE TABLE `table` (
`year` year,
);
timestamp
import { timestamp, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
timestamp: timestamp('timestamp'),
});
timestamp('...', { mode: 'date' | "string"}),
timestamp('...', { fsp : 0..6}),
CREATE TABLE `table` (
`timestamp` timestamp,
);
import { timestamp, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
timestamp: timestamp('timestamp', { mode: 'date', fsp: 6 }),
});
CREATE TABLE `table` (
`timestamp` timestamp(6),
);
import { timestamp, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
timestamp: timestamp('timestamp').defaultNow(),
});
CREATE TABLE `table` (
`timestamp` timestamp DEFAULT (now()),
);
json
import { json, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
json: json('json'),
});
CREATE TABLE `table` (
`json` json,
);
You can specify .$type<..>()
for json object inference, it won't check runtime values.
It provides compile time protection for default values, insert and select schemas.
// will be infered as { foo: string }
json: json('json').$type<{ foo: string }>();
// will be infered as string[]
json: json('json').$type<string[]>();
// won't compile
json: json('json').$type<string[]>().default({});
enumColumn
import { mysqlEnum, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
mysqlEnum: mysqlEnum('popularity', ['unknown', 'known', 'popular']),
});
CREATE TABLE `table` (
`popularity` enum('unknown','known','popular'),
);
Customizing column data type
Every column builder has a .$type()
method, which allows you to customize the data type of the column. This is useful, for example, with unknown or branded types.
type UserId = number & { __brand: 'user_id' };
type Data = {
foo: string;
bar: number;
};
const users = mysqlTable('users', {
id: int('id').$type<UserId>().primaryKey(),
jsonField: json('json_field').$type<Data>(),
});
Columns constraints
Not null
NOT NULL
constraint dictates that the associated column may not contain a NULL
value
import { int, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
int: int('int').notNull(),
});
CREATE TABLE `table` (
`int` int NOT NULL,
);
Default value
The DEFAULT
clause specifies a default value to use for the column if no value
is explicitly provided by the user when doing an INSERT
.
If there is no explicit DEFAULT
clause attached to a column definition,
then the default value of the column is NULL
.
An explicit DEFAULT
clause may specify that the default value is NULL
,
a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses.
import { int, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
int: int('int').default(3),
});
CREATE TABLE `table` (
`int` int DEFAULT 3,
);
When using $default()
or $defaultFn()
, which are simply different aliases for the same function,
you can generate defaults at runtime and use these values in all insert queries.
These functions can assist you in utilizing various implementations such as uuid
, cuid
, cuid2
, and many more
Note: This value does not affect the drizzle-kit behavior, it is only used at runtime in drizzle-orm
import { varchar, mysqlTable } from "drizzle-orm/mysql-core";
import { createId } from '@paralleldrive/cuid2';
const table = mysqlTable('table', {
id: varchar('id', { length: 128 }).$defaultFn(() => createId()),
});
Primary key
import { int, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
int: int('int').primaryKey(),
});
CREATE TABLE `table` (
`int` int PRIMARY KEY NOT NULL,
);
Auto increment
import { int, mysqlTable } from "drizzle-orm/mysql-core";
const table = mysqlTable('table', {
int: int('int').autoincrement(),
});
CREATE TABLE `table` (
`int` int AUTO_INCREMENT
);