Writing Complex Migrations with Drizzle ORM

Introduction

Database migrations evolve schemas safely. Drizzle ORM’s migration tool allows complex changes like data transformations and conditional operations.

Prerequisites

• Drizzle ORM setup
• drizzle-kit installed

Step 1: Install Drizzle Kit

npm install --save-dev drizzle-kit
npx drizzle-kit init

Generates drizzle.config.ts.

Step 2: Create Migration File

npx drizzle-kit generate:migration --name add-user-roles

This creates a new migration file in drizzle directory.

Step 3: Write Migration Logic

Edit migration file:

import { Kysely, sql } from 'kysely';

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema.alterTable('users', (table) =>
    table.addColumn('role', 'text').notNull().defaultTo(sql`'user'`)
  );

  // Update existing users based on conditions
  await db
    .updateTable('users')
    .set({ role: 'admin' })
    .where('email', 'like', '%@admin.com')
    .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.alterTable('users', (table) => table.dropColumn('role'));
}

Step 4: Run Migrations

npx drizzle-kit migrate

Step 5: Conditional Logic

Use raw SQL or JS logic:

if (process.env.NODE_ENV === 'production') {
  await db.schema.alterTable('orders', (t) => t.addColumn('archived', 'boolean').defaultTo(false));
}

Summary

Drizzle migrations leverage Kysely for schema alterations and programmatic data updates. Include conditional logic for environment-specific changes.