Documentation Index
Fetch the complete documentation index at: https://mintlify.com/drizzle-team/drizzle-orm/llms.txt
Use this file to discover all available pages before exploring further.
Define MySQL tables using the mysqlTable function. Tables are the foundation of your database schema.
Table Definition
mysqlTable()
Defines a MySQL table with columns and constraints.
mysqlTable<TTableName extends string, TColumnsMap extends Record<string, MySqlColumnBuilderBase>>(
name: TTableName,
columns: TColumnsMap | ((columnTypes: MySqlColumnBuilders) => TColumnsMap),
extraConfig?: (self: BuildColumns<TTableName, TColumnsMap>) => MySqlTableExtraConfigValue[]
): MySqlTableWithColumns
The name of the table in the database.
columns
object | function
required
Column definitions. Can be an object of column builders or a function that receives column type helpers.
Function to define indexes, foreign keys, and other constraints. Should return an array of constraint builders.
Examples
import { mysqlTable, int, varchar, timestamp } from 'drizzle-orm/mysql-core';
import { index, primaryKey, foreignKey } from 'drizzle-orm/mysql-core';
// Basic table
export const users = mysqlTable('users', {
id: int().primaryKey().autoincrement(),
name: varchar({ length: 255 }).notNull(),
email: varchar({ length: 255 }).notNull().unique(),
createdAt: timestamp().notNull().defaultNow()
});
// Table with column type helpers
export const posts = mysqlTable('posts', (t) => ({
id: t.int().primaryKey().autoincrement(),
title: t.varchar({ length: 255 }).notNull(),
content: t.text(),
authorId: t.int().notNull()
}));
// Table with indexes and constraints
export const orders = mysqlTable('orders', {
id: int().primaryKey().autoincrement(),
userId: int().notNull(),
productId: int().notNull(),
quantity: int().notNull(),
createdAt: timestamp().notNull().defaultNow()
}, (table) => [
index('user_idx').on(table.userId),
index('product_idx').on(table.productId),
index('created_idx').on(table.createdAt),
foreignKey({
columns: [table.userId],
foreignColumns: [users.id]
}).onDelete('cascade')
]);
// Composite primary key
export const userRoles = mysqlTable('user_roles', {
userId: int().notNull(),
roleId: int().notNull(),
assignedAt: timestamp().notNull().defaultNow()
}, (table) => [
primaryKey({ columns: [table.userId, table.roleId] })
]);
mysqlTableCreator()
Creates a custom table creator with a name transformation function. Useful for adding prefixes or custom naming conventions.
mysqlTableCreator(
customizeTableName: (name: string) => string
): MySqlTableFn
Function that transforms table names. Receives the provided name and returns the actual database table name.
Examples
import { mysqlTableCreator } from 'drizzle-orm/mysql-core';
// Add prefix to all tables
const mysqlTable = mysqlTableCreator((name) => `myapp_${name}`);
export const users = mysqlTable('users', {
id: int().primaryKey()
});
// Creates table named 'myapp_users'
// Environment-based naming
const env = process.env.NODE_ENV;
const mysqlTable = mysqlTableCreator(
(name) => `${env}_${name}`
);
export const products = mysqlTable('products', {
id: int().primaryKey()
});
// Creates 'production_products' or 'development_products'
Schema Organization
mysqlTableWithSchema()
Defines a table within a specific MySQL schema/database.
mysqlTableWithSchema<TTableName extends string, TSchemaName extends string>(
name: TTableName,
columns: TColumnsMap,
extraConfig: (self: BuildColumns) => MySqlTableExtraConfigValue[],
schema: TSchemaName,
baseName?: string
): MySqlTableWithColumns
Constraints and indexes configuration.
The schema/database name.
Base name for internal reference. Defaults to name.
Examples
import { mysqlTableWithSchema, int, varchar } from 'drizzle-orm/mysql-core';
// Table in analytics schema
export const events = mysqlTableWithSchema(
'events',
{
id: int().primaryKey().autoincrement(),
eventName: varchar({ length: 255 }).notNull(),
userId: int()
},
undefined,
'analytics'
);
// Creates table in 'analytics.events'
// Multiple schemas in one application
export const authUsers = mysqlTableWithSchema(
'users',
{ id: int().primaryKey() },
undefined,
'auth',
'auth_users'
);
export const appUsers = mysqlTableWithSchema(
'users',
{ id: int().primaryKey() },
undefined,
'app',
'app_users'
);
Table Constraints
Constraints are defined in the third parameter (extraConfig) of mysqlTable.
Indexes
import { index, uniqueIndex } from 'drizzle-orm/mysql-core';
export const users = mysqlTable('users', {
id: int().primaryKey(),
email: varchar({ length: 255 }),
name: varchar({ length: 255 }),
createdAt: timestamp()
}, (table) => [
// Simple index
index('email_idx').on(table.email),
// Unique index
uniqueIndex('email_unique_idx').on(table.email),
// Composite index
index('name_created_idx').on(table.name, table.createdAt),
// Named index with algorithm
index('name_idx').on(table.name).using('btree')
]);
Primary Keys
import { primaryKey } from 'drizzle-orm/mysql-core';
// Composite primary key
export const userPermissions = mysqlTable('user_permissions', {
userId: int().notNull(),
permissionId: int().notNull(),
grantedAt: timestamp()
}, (table) => [
primaryKey({ columns: [table.userId, table.permissionId] })
]);
// Single column primary key (preferred on column)
export const products = mysqlTable('products', {
id: int().primaryKey().autoincrement(),
name: varchar({ length: 255 })
});
Foreign Keys
import { foreignKey } from 'drizzle-orm/mysql-core';
export const posts = mysqlTable('posts', {
id: int().primaryKey(),
authorId: int().notNull(),
categoryId: int()
}, (table) => [
// Basic foreign key
foreignKey({
columns: [table.authorId],
foreignColumns: [users.id]
}),
// Foreign key with actions
foreignKey({
columns: [table.authorId],
foreignColumns: [users.id]
}).onDelete('cascade').onUpdate('restrict'),
// Composite foreign key
foreignKey({
columns: [table.categoryId, table.authorId],
foreignColumns: [categories.id, categories.ownerId]
}),
// Named foreign key
foreignKey({
columns: [table.authorId],
foreignColumns: [users.id],
name: 'posts_author_fk'
})
]);
Unique Constraints
import { unique } from 'drizzle-orm/mysql-core';
export const users = mysqlTable('users', {
id: int().primaryKey(),
email: varchar({ length: 255 }),
username: varchar({ length: 100 }),
tenantId: int()
}, (table) => [
// Single column unique
unique().on(table.email),
// Named unique constraint
unique('username_unique').on(table.username),
// Composite unique constraint
unique('tenant_email_unique').on(table.tenantId, table.email)
]);
Check Constraints
import { check } from 'drizzle-orm/mysql-core';
import { sql } from 'drizzle-orm';
export const products = mysqlTable('products', {
id: int().primaryKey(),
price: decimal({ precision: 10, scale: 2 }),
discount: decimal({ precision: 5, scale: 2 }),
stock: int()
}, (table) => [
// Check constraint
check('price_positive', sql`${table.price} > 0`),
// Multiple conditions
check('valid_discount', sql`${table.discount} >= 0 AND ${table.discount} <= 100`),
// Stock constraint
check('stock_non_negative', sql`${table.stock} >= 0`)
]);
Type Helpers
MySqlTable
The base table class. All tables defined with mysqlTable() are instances of this class.
class MySqlTable<T extends TableConfig>
InferModel
Infer TypeScript types from table definitions.
import { type InferSelectModel, type InferInsertModel } from 'drizzle-orm';
export const users = mysqlTable('users', {
id: int().primaryKey().autoincrement(),
name: varchar({ length: 255 }).notNull(),
email: varchar({ length: 255 }).notNull(),
createdAt: timestamp().notNull().defaultNow()
});
// Type for SELECT queries
type User = InferSelectModel<typeof users>;
// { id: number; name: string; email: string; createdAt: Date }
// Type for INSERT queries
type NewUser = InferInsertModel<typeof users>;
// { id?: number; name: string; email: string; createdAt?: Date }
Table Configuration
Union type of all possible table configuration values.
type MySqlTableExtraConfigValue =
| IndexBuilder
| CheckBuilder
| ForeignKeyBuilder
| PrimaryKeyBuilder
| UniqueConstraintBuilder
Best Practices
Column-level constraints: Define simple constraints (like .notNull(), .unique(), .primaryKey()) directly on columns for better readability.
Table-level constraints: Use the extraConfig parameter for composite indexes, composite primary keys, and foreign keys.
Reserved words: Avoid using MySQL reserved keywords as table or column names. If necessary, they will be automatically quoted.
Naming conventions: Choose consistent naming for tables (e.g., plural form like users, posts) and stick with it throughout your schema.