Database Migrations with Drizzle ORM in TypeScript

Drizzle ORM provides a type-safe, SQL-first approach to database migrations. This guide covers schema definition, migration generation, version control, and production deployment.

Why Drizzle ORM?

Drizzle combines the flexibility of SQL with type safety of TypeScript. Its migration system generates SQL statements automatically while maintaining full control over database changes. Perfect for teams that want explicit, reviewable migrations.

Prerequisites

Node.js 18+ with TypeScript
PostgreSQL 12+ (or MySQL, SQLite)
Git for version control
Drizzle CLI installed

Step 1: Install Drizzle ORM and Dependencies

npm install drizzle-orm pg dotenv
npm install -D drizzle-kit typescript ts-node tsx

For MySQL:

npm install mysql2

For SQLite:

npm install better-sqlite3

Step 2: Configure Drizzle Project

Create drizzle.config.ts:

import type { Config } from 'drizzle-kit';
import * as dotenv from 'dotenv';

dotenv.config();

export default {
  schema: './src/db/schema.ts',
  out: './src/db/migrations',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL || '',
  },
  verbose: true,
  strict: true,
} satisfies Config;

Create .env:

DATABASE_URL=postgresql://user:password@localhost:5432/mydb
NODE_ENV=development

Step 3: Define Database Schema

Create src/db/schema.ts:

import {
  pgTable,
  serial,
  varchar,
  text,
  integer,
  boolean,
  timestamp,
  decimal,
  uniqueIndex,
  index,
  foreignKey,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import { relations } from 'drizzle-orm';

// Users table
export const users = pgTable(
  'users',
  {
    id: serial('id').primaryKey(),
    email: varchar('email', { length: 255 }).unique().notNull(),
    username: varchar('username', { length: 100 }).unique().notNull(),
    passwordHash: varchar('password_hash', { length: 255 }).notNull(),
    firstName: varchar('first_name', { length: 100 }),
    lastName: varchar('last_name', { length: 100 }),
    bio: text('bio'),
    isVerified: boolean('is_verified').default(false).notNull(),
    isActive: boolean('is_active').default(true).notNull(),
    role: varchar('role', { length: 50 }).default('user').notNull(),
    lastLoginAt: timestamp('last_login_at'),
    createdAt: timestamp('created_at').default(sql`CURRENT_TIMESTAMP`).notNull(),
    updatedAt: timestamp('updated_at').default(sql`CURRENT_TIMESTAMP`).notNull(),
  },
  (table) => ({
    emailIdx: uniqueIndex('idx_users_email').on(table.email),
    usernameIdx: index('idx_users_username').on(table.username),
    isActiveIdx: index('idx_users_is_active').on(table.isActive),
  })
);

// Posts table
export const posts = pgTable(
  'posts',
  {
    id: serial('id').primaryKey(),
    userId: integer('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    title: varchar('title', { length: 255 }).notNull(),
    slug: varchar('slug', { length: 255 }).unique().notNull(),
    content: text('content').notNull(),
    excerpt: text('excerpt'),
    published: boolean('published').default(false).notNull(),
    viewCount: integer('view_count').default(0).notNull(),
    createdAt: timestamp('created_at').default(sql`CURRENT_TIMESTAMP`).notNull(),
    updatedAt: timestamp('updated_at').default(sql`CURRENT_TIMESTAMP`).notNull(),
    publishedAt: timestamp('published_at'),
  },
  (table) => ({
    userIdIdx: index('idx_posts_user_id').on(table.userId),
    slugIdx: index('idx_posts_slug').on(table.slug),
    publishedIdx: index('idx_posts_published').on(table.published),
    createdAtIdx: index('idx_posts_created_at').on(table.createdAt),
  })
);

// Comments table
export const comments = pgTable(
  'comments',
  {
    id: serial('id').primaryKey(),
    postId: integer('post_id')
      .notNull()
      .references(() => posts.id, { onDelete: 'cascade' }),
    userId: integer('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    content: text('content').notNull(),
    likes: integer('likes').default(0).notNull(),
    createdAt: timestamp('created_at').default(sql`CURRENT_TIMESTAMP`).notNull(),
    updatedAt: timestamp('updated_at').default(sql`CURRENT_TIMESTAMP`).notNull(),
  },
  (table) => ({
    postIdIdx: index('idx_comments_post_id').on(table.postId),
    userIdIdx: index('idx_comments_user_id').on(table.userId),
  })
);

// Tags table
export const tags = pgTable(
  'tags',
  {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 100 }).unique().notNull(),
    slug: varchar('slug', { length: 100 }).unique().notNull(),
    createdAt: timestamp('created_at').default(sql`CURRENT_TIMESTAMP`).notNull(),
  },
  (table) => ({
    slugIdx: index('idx_tags_slug').on(table.slug),
  })
);

// Posts to Tags junction table
export const postsTags = pgTable(
  'posts_tags',
  {
    postId: integer('post_id')
      .notNull()
      .references(() => posts.id, { onDelete: 'cascade' }),
    tagId: integer('tag_id')
      .notNull()
      .references(() => tags.id, { onDelete: 'cascade' }),
  },
  (table) => ({
    pk: table.primaryKey({ columns: [table.postId, table.tagId] }),
  })
);

// Define relationships
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
  comments: many(comments),
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.userId],
    references: [users.id],
  }),
  comments: many(comments),
  tags: many(postsTags),
}));

export const commentsRelations = relations(comments, ({ one }) => ({
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
  }),
  author: one(users, {
    fields: [comments.userId],
    references: [users.id],
  }),
}));

Step 4: Generate Initial Migration

Generate migration files from schema:

npx drizzle-kit generate:pg

This creates migration files in src/db/migrations/ with SQL statements.

Example migration output (0000_initial.sql):

CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "email" VARCHAR(255) UNIQUE NOT NULL,
  "username" VARCHAR(100) UNIQUE NOT NULL,
  "password_hash" VARCHAR(255) NOT NULL,
  ...
);

CREATE INDEX "idx_users_email" on "users" ("email");

Step 5: Run Migrations

Create migration runner (src/db/migrate.ts):

import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { Pool } from 'pg';
import path from 'path';
import * as dotenv from 'dotenv';

dotenv.config();

const runMigrations = async () => {
  const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
  });

  const db = drizzle(pool);

  console.log('Running migrations...');

  await migrate(db, {
    migrationsFolder: path.join(__dirname, './migrations'),
  });

  console.log('Migrations completed');
  await pool.end();
};

runMigrations().catch((err) => {
  console.error('Migration failed:', err);
  process.exit(1);
});

Add to package.json:

{
  "scripts": {
    "db:migrate": "ts-node src/db/migrate.ts",
    "db:generate": "drizzle-kit generate:pg",
    "db:drop": "drizzle-kit drop"
  }
}

Run migrations:

npm run db:migrate

Step 6: Create Database Client

Create src/db/index.ts:

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';
import * as dotenv from 'dotenv';

dotenv.config();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

export const db = drizzle(pool, { schema });

// Graceful shutdown
process.on('SIGINT', async () => {
  console.log('Closing database connection...');
  await pool.end();
  process.exit(0);
});

export { pool };

Step 7: Use Database in Application

Example query (src/services/userService.ts):

import { db } from '../db';
import { users, posts } from '../db/schema';
import { eq, desc, and } from 'drizzle-orm';

export async function getUserWithPosts(userId: number) {
  return await db.query.users.findFirst({
    where: eq(users.id, userId),
    with: {
      posts: {
        orderBy: desc(posts.createdAt),
      },
    },
  });
}

export async function createUser(userData: {
  email: string;
  username: string;
  passwordHash: string;
}) {
  const result = await db.insert(users).values(userData).returning();
  return result[0];
}

export async function updateUserLastLogin(userId: number) {
  await db
    .update(users)
    .set({ lastLoginAt: new Date() })
    .where(eq(users.id, userId));
}

export async function getPublishedPosts(limit: number = 10) {
  return await db.query.posts.findMany({
    where: eq(posts.published, true),
    orderBy: desc(posts.createdAt),
    limit,
    with: {
      author: true,
      comments: {
        limit: 3,
      },
    },
  });
}

Step 8: Schema Modifications

When you need to modify the schema, update schema.ts then generate migration:

Add New Column

// In schema.ts - add to users table
avatar: varchar('avatar', { length: 255 }),

Generate migration:

npm run db:generate

Review generated migration, then run:

npm run db:migrate

Rename Column

Drizzle requires explicit renaming:

// Old column name
oldColumnName: varchar('old_name'),

// Update to
columnNameRenamed: varchar('column_name_renamed'),

In migration file, add manual SQL:

ALTER TABLE "users" RENAME COLUMN "old_name" TO "column_name_renamed";

Add Foreign Key

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  // ...
});

Step 9: Production Migration Strategy

Create safe migration script (scripts/migrate-production.sh):

#!/bin/bash

set -e

echo "Starting production migration..."
echo "Database: $DATABASE_URL"

# Create backup
echo "Creating backup..."
pg_dump $DATABASE_URL > backup-$(date +%Y%m%d-%H%M%S).sql

# Run migrations
echo "Running migrations..."
npm run db:migrate

echo "Migration completed successfully"

Make executable:

chmod +x scripts/migrate-production.sh

Step 10: Seed Database

Create seed script (src/db/seed.ts):

import { db } from './index';
import { users, tags, posts } from './schema';
import * as dotenv from 'dotenv';

dotenv.config();

async function seed() {
  console.log('Seeding database...');

  // Create users
  const createdUsers = await db
    .insert(users)
    .values([
      {
        email: 'alice@example.com',
        username: 'alice',
        passwordHash: '$2a$10$...',
        firstName: 'Alice',
      },
      {
        email: 'bob@example.com',
        username: 'bob',
        passwordHash: '$2a$10$...',
        firstName: 'Bob',
      },
    ])
    .returning();

  // Create tags
  const createdTags = await db
    .insert(tags)
    .values([
      { name: 'TypeScript', slug: 'typescript' },
      { name: 'Node.js', slug: 'nodejs' },
      { name: 'Databases', slug: 'databases' },
    ])
    .returning();

  console.log('Database seeded successfully');
}

seed().catch((err) => {
  console.error('Seed failed:', err);
  process.exit(1);
});

Add to package.json:

{
  "scripts": {
    "db:seed": "ts-node src/db/seed.ts"
  }
}

Migration Type	Purpose	Risk Level
Add Column	Extend schema with new fields	Low
Drop Column	Remove unused fields	High
Rename Column	Update field names	Medium
Create Index	Improve query performance	Low
Add Foreign Key	Establish relationships	Medium

Best Practices

Always Review Migrations: Before applying to production

# Preview migration SQL
cat src/db/migrations/0001_migrations.sql

Test on Staging First: Always test migrations on staging environment

DATABASE_URL=postgresql://staging-db npm run db:migrate

Backup Before Major Changes: For production deployments

pg_dump $DATABASE_URL > backup.sql

Use Transactions: Ensure atomic operations

await db.transaction(async (tx) => {
  await tx.update(users).set({ role: 'admin' }).where(...);
  await tx.insert(auditLog).values(...);
});

Version Control: Commit migrations alongside code changes

git add src/db/schema.ts src/db/migrations/
git commit -m "feat: add user profiles table"

Troubleshooting

Migration Already Applied Error:

# Check migration status
npx drizzle-kit generate:pg --casing camelCase

# Reset if needed (caution!)
npx drizzle-kit drop

Type Errors in Queries:

Ensure schema exports are updated:

npm run db:generate

Connection Pooling Issues:

Verify connection string:

psql $DATABASE_URL -c "SELECT 1"

Useful Resources

Conclusion

Drizzle ORM provides a type-safe, SQL-first migration system perfect for production applications. Combine with version control, testing, and backup strategies for safe, auditable database evolution.