Database Migration

Name: database-migration
Rating: 78
Author: RIMTHAN-LAB

Purpose

Create and apply database migrations using Drizzle ORM and Testcontainers for testing.

When to Use

•Adding new tables or columns to database
•Modifying existing database schema
•Creating indexes or constraints
•Seeding initial data

Capabilities

•Creates Drizzle schema with proper types in packages/db-[domain]
•Adds indexes for common queries
•Creates foreign key relationships
•Generates up/down migration files
•Runs migration against database
•Creates seed data if needed
•Adds Testcontainers tests for migration verification

Multi-Database Pattern

This monorepo uses a multi-database DDD pattern:

code

packages/
├── db-main/     # Shared entities (organizations, settings)
├── db-auth/     # Auth domain (users, sessions, credentials)
└── db-[domain]/ # Per-domain schemas

Each domain owns its database schema package.

Migration Workflow

bash

# 1. Add schema to packages/db-[domain]/src/schema/
# 2. Export from package index
# 3. Generate and apply
pnpm db:generate && pnpm db:migrate

Schema Example

typescript

// packages/db-main/src/schema/organizations.ts
import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const organizations = pgTable('organizations', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// packages/db-main/src/index.ts
export * from './schema/organizations';

Testcontainers Test

typescript

import { PostgreSqlContainer } from '@testcontainers/postgresql';
import { migrate } from 'drizzle-kit/node';

describe('Database Migration', () => {
  let postgres;
  before(async () => {
    postgres = await new PostgreSqlContainer('postgres:18').start();
  });
  after(async () => {
    await postgres.stop();
  });
  test('should apply migration successfully', async () => {
    await migrate({ connectionString: postgres.getConnectionUri() });
  });
});

Commands Reference

Command	Purpose
`pnpm db:generate [db-package]`	Generate migration files
`pnpm db:migrate [db-package]`	Apply migration to database
`pnpm db:push [db-package]`	Push schema directly (dev only)
`pnpm db:studio [db-package]`	Open Drizzle Studio

Rules

•Always test migrations with Testcontainers before applying
•Use domain-specific packages - don't mix domains in one schema
•Export types for tRPC inference
•Add indexes for frequently queried columns
•Use transactions for multi-table changes

Configuration Example

Each database package has its own drizzle.config.ts:

typescript

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/schema',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;