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

src/schema.ts
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:

drizzle.config.ts
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:

drizzle/migrations/0000_better_than_prisma.sql
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:

src/db.ts
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 });
src/migrate.ts
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