Migrations
The most important thing about Drizzle is that you can use its schema as a source of truth for everything else.
drizzle-kit is a CLI companion for Drizzle ORM which you can use to generate SQL migrations automatically based on your schema changes
or apply those changes directly to the database.
See detailed docs for extended examples and walk-throughs.
Quick start
Declare your schema
import { index, int, mysqlTable, bigint, varchar } from 'drizzle-orm/mysql-core';
export const users = mysqlTable('users', {
id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
fullName: varchar('full_name', { length: 256 }),
}, (users) => ({
nameIdx: index('name_idx').on(users.fullName),
}));
export const authOtps = mysqlTable('auth_otp', {
id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
phone: varchar('phone', { length: 256 }),
userId: int('user_id').references(() => users.id),
});The schema files SHOULD NOT contain any runtime logic besides defining your DB schema. In particular, your DB connection should be defined separately. Otherwise, that logic will be executed whenever you run any drizzle-kit commands.
Schema-related type definitions, on the other hand, are allowed and even encouraged, as they are not executed at runtime.
Create the config
Create a drizzle.config.ts file in your project root:
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
schema: './src/schema.ts',
out: './drizzle',
dialect: 'mysql', // 'postgresql' | 'mysql' | 'sqlite'
dbCredentials: {
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
},
});Generate the migration
pnpm drizzle-kit generateThis will generate a migration SQL file:
CREATE TABLE `users` (
`id` bigint primary key auto_increment,
`full_name` varchar(256)
);
CREATE TABLE `auth_otp` (
`id` bigint primary key auto_increment,
`phone` varchar(256),
`user_id` int
);
ALTER TABLE auth_otp ADD CONSTRAINT auth_otp_user_id_users_id_fk FOREIGN KEY (`user_id`) REFERENCES users(`id`) ;
CREATE INDEX name_idx ON users (`full_name`);Run the migrations
Drizzle ORM is designed to be an opt-in solution at any point of your development flow. You can either run the generated migrations via Drizzle, or treat them as generic SQL migrations and run them with any other tool.
To run the migrations with Drizzle, you can use the migrate() helper, available for every supported driver:
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as schema from './schema';
export const connection = await mysql.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
multipleStatements: true,
});
export const db = drizzle(connection, { schema });import 'dotenv/config';
import { migrate } from 'drizzle-orm/mysql2/migrator';
import { db, connection } from './db';
// This will run migrations on the database, skipping the ones already applied
await migrate(db, { migrationsFolder: './drizzle' });
// Don't forget to close the connection, otherwise the script will hang
await connection.end();pnpm tsx src/migrate.tsConfigurations
- Custom table for migrations
By default, all information about executed migrations will be stored in the database inside the __drizzle_migrations table,
and for PostgreSQL, inside the drizzle schema. However, you can configure where to store those records.
To add a custom table name for migrations stored inside your database, you should use the migrationsTable option
Usage example
await migrate(db, { migrationsFolder: './drizzle', migrationsTable: 'my_migrations' });- Custom schema for migrations
Works only with PostgreSQL databases
To add a custom schema name for migrations stored inside your database, you should use the migrationsSchema option
Usage example
await migrate(db, { migrationsFolder: './drizzle', migrationsSchema: 'custom' });
