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 'dotenv/config';
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/schema.ts',
  out: './drizzle',
  driver: 'mysql2', // 'pg' | 'mysql2' | 'better-sqlite' | 'libsql' | 'turso'
  dbCredentials: {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
  },
} satisfies Config;

Generate the migration

pnpm drizzle-kit generate:mysql

This 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.ts

Configurations

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

Benchmarks

Studio

Become a Gold Sponsor