Mysql – DrizzleORM

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

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

import { varchar, mysqlTable } from "drizzle-orm/mysql-core";
 
const table = mysqlTable('table', {
	varchar: varchar('varchar', { length: 2 }),
});

CREATE TABLE `table` (
	`varchar` varchar(2),
);

text

import { text, mysqlTable } from "drizzle-orm/mysql-core";
 
const table = mysqlTable('table', {
	text: text('text'),
});

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'),
});
 
json<...>('...');
json<string[]>('...')

CREATE TABLE `table` (
	`json` json,
);

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'),
);

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,
);

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

PostgreSQL SQLite