📦
Bunty

Database Migrations

Database migrations provide version control for your database schema, allowing you to evolve your database structure over time while keeping track of changes across different environments.

Overview

Migrations are scripts that define incremental changes to your database schema. Each migration has:

  • Up method: Applies the migration (creates tables, adds columns, etc.)
  • Down method: Reverses the migration (drops tables, removes columns, etc.)
  • Timestamp: Unique identifier for ordering migrations

Creating Migrations

Basic Migration Structure

Create a migration file with a timestamp prefix:

// migrations/001_create_users_table.ts
import { Migration, MysqlClient } from '@bunty/orm';

export default class CreateUsersTable extends Migration {
  async up(db: MysqlClient): Promise<void> {
    await db.schema.createTable('users', (table) => {
      table.bigint('id').primaryKey().autoIncrement();
      table.varchar('name', { size: 100 }).notNullable();
      table.varchar('email', { size: 255 }).notNullable().unique();
      table.varchar('password_hash', { size: 255 }).notNullable();
      table.boolean('email_verified').default(false);
      table.timestamp('email_verified_at').nullable();
      table.timestamp('created_at').notNullable().defaultNow();
      table.timestamp('updated_at').notNullable().onUpdateCurrentTimestamp();
    });
  }

  async down(db: MysqlClient): Promise<void> {
    await db.schema.dropTable('users');
  }
}

Migration Naming Convention

Use descriptive names with timestamps:

// Good migration names:
001_create_users_table.ts
002_create_posts_table.ts
003_add_category_to_posts.ts
004_create_indexes_on_posts.ts
005_alter_users_add_avatar_column.ts
006_create_user_sessions_table.ts

Schema Builder API

Creating Tables

Use the schema builder to define table structures:

export default class CreateProductsTable extends Migration {
  async up(db: MysqlClient): Promise<void> {
    await db.schema.createTable('products', (table) => {
      // Primary key
      table.bigint('id').primaryKey().autoIncrement();
      
      // Basic product info
      table.varchar('sku', { size: 50 }).notNullable().unique();
      table.varchar('name', { size: 255 }).notNullable();
      table.text('description').nullable();
      
      // Pricing
      table.decimal('price', { precision: 10, scale: 2 }).notNullable();
      table.decimal('compare_at_price', { precision: 10, scale: 2 }).nullable();
      table.decimal('cost', { precision: 10, scale: 2 }).nullable();
      
      // Inventory
      table.int('quantity').unsigned().default(0);
      table.int('reserved_quantity').unsigned().default(0);
      table.boolean('track_inventory').default(true);
      
      // Attributes
      table.decimal('weight', { precision: 8, scale: 3 }).nullable();
      table.varchar('weight_unit', { size: 10 }).default('kg');
      table.enumerable('status', ['draft', 'active', 'archived']).default('draft');
      table.boolean('requires_shipping').default(true);
      
      // Metadata
      table.json('options').nullable();
      table.json('seo_data').nullable();
      
      // Timestamps
      table.timestamp('created_at').notNullable().defaultNow();
      table.timestamp('updated_at').notNullable().onUpdateCurrentTimestamp();
      
      // Indexes
      table.index(['status', 'created_at']);
      table.index(['sku']);
    })
      .engine('InnoDB')
      .charset('utf8mb4')
      .comment('Product catalog');
  }

  async down(db: MysqlClient): Promise<void> {
    await db.schema.dropTable('products');
  }
}

Adding Columns

Add new columns to existing tables:

export default class AddAvatarToUsers extends Migration {
  async up(db: MysqlClient): Promise<void> {
    await db.schema.alterTable('users', (table) => {
      table.addColumn('avatar', 'varchar', { size: 500 }).nullable();
      table.addColumn('bio', 'text').nullable();
      table.addColumn('website', 'varchar', { size: 255 }).nullable();
      table.addColumn('location', 'varchar', { size: 100 }).nullable();
    });
  }

  async down(db: MysqlClient): Promise<void> {
    await db.schema.alterTable('users', (table) => {
      table.dropColumn('avatar');
      table.dropColumn('bio');
      table.dropColumn('website');
      table.dropColumn('location');
    });
  }
}

Creating Indexes

Add database indexes for performance:

export default class CreateIndexesOnPosts extends Migration {
  async up(db: MysqlClient): Promise<void> {
    await db.schema.alterTable('posts', (table) => {
      // Single column indexes
      table.index(['status']);
      table.index(['published_at']);
      table.index(['author_id']);
      
      // Composite indexes
      table.index(['status', 'published_at'], 'idx_posts_status_published');
      table.index(['author_id', 'status'], 'idx_posts_author_status');
      
      // Full-text search index
      table.fullTextIndex(['title', 'content'], 'ft_posts_search');
    });
  }

  async down(db: MysqlClient): Promise<void> {
    await db.schema.alterTable('posts', (table) => {
      table.dropIndex('idx_posts_status_published');
      table.dropIndex('idx_posts_author_status');
      table.dropIndex('ft_posts_search');
      table.dropIndex(['status']);
      table.dropIndex(['published_at']);
      table.dropIndex(['author_id']);
    });
  }
}

Foreign Key Constraints

Define relationships between tables:

export default class CreatePostsTable extends Migration {
  async up(db: MysqlClient): Promise<void> {
    await db.schema.createTable('posts', (table) => {
      table.bigint('id').primaryKey().autoIncrement();
      table.bigint('author_id').notNullable();
      table.bigint('category_id').nullable();
      table.varchar('title', { size: 255 }).notNullable();
      table.text('content').notNullable();
      table.enumerable('status', ['draft', 'published', 'archived']).default('draft');
      table.timestamp('created_at').notNullable().defaultNow();
      table.timestamp('updated_at').notNullable().onUpdateCurrentTimestamp();
      
      // Foreign key constraints
      table.foreignKey('author_id')
        .references('users', 'id')
        .onDelete('CASCADE')
        .onUpdate('CASCADE');
        
      table.foreignKey('category_id')
        .references('categories', 'id')
        .onDelete('SET NULL')
        .onUpdate('CASCADE');
    });
  }

  async down(db: MysqlClient): Promise<void> {
    await db.schema.dropTable('posts');
  }
}

Running Migrations

Migration Runner

Create a migration runner to execute migrations:

// scripts/migrate.ts
import { MysqlClient } from '@bunty/orm';
import { readdirSync } from 'fs';
import path from 'path';

interface MigrationRecord {
  id: number;
  name: string;
  executed_at: Date;
}

class MigrationRunner {
  constructor(private db: MysqlClient) {}

  // Ensure migrations table exists
  async ensureMigrationsTable(): Promise<void> {
    await this.db.schema.createTableIfNotExists('migrations', (table) => {
      table.int('id').primaryKey().autoIncrement();
      table.varchar('name', { size: 255 }).notNullable().unique();
      table.timestamp('executed_at').notNullable().defaultNow();
    });
  }

  // Get executed migrations
  async getExecutedMigrations(): Promise<MigrationRecord[]> {
    return await this.db.select()
      .from('migrations')
      .orderBy('name', 'ASC')
      .getMany();
  }

  // Get pending migrations
  async getPendingMigrations(): Promise<string[]> {
    const executed = await this.getExecutedMigrations();
    const executedNames = new Set(executed.map(m => m.name));
    
    const migrationFiles = readdirSync('./migrations')
      .filter(file => file.endsWith('.ts') || file.endsWith('.js'))
      .sort();
    
    return migrationFiles.filter(file => !executedNames.has(file));
  }

  // Run a single migration
  async runMigration(fileName: string): Promise<void> {
    const migrationPath = path.resolve('./migrations', fileName);
    const MigrationClass = (await import(migrationPath)).default;
    const migration = new MigrationClass();
    
    console.log(`Running migration: ${fileName}`);
    
    await migration.up(this.db);
    
    // Record migration as executed
    await this.db.insertInto('migrations')
      .values({ name: fileName })
      .execute();
    
    console.log(`✅ Completed migration: ${fileName}`);
  }

  // Run all pending migrations
  async migrate(): Promise<void> {
    await this.ensureMigrationsTable();
    
    const pending = await this.getPendingMigrations();
    
    if (pending.length === 0) {
      console.log('No pending migrations');
      return;
    }
    
    console.log(`Running ${pending.length} pending migrations...`);
    
    for (const fileName of pending) {
      await this.runMigration(fileName);
    }
    
    console.log('✅ All migrations completed');
  }

  // Rollback last migration
  async rollback(): Promise<void> {
    const executed = await this.getExecutedMigrations();
    
    if (executed.length === 0) {
      console.log('No migrations to rollback');
      return;
    }
    
    const lastMigration = executed[executed.length - 1];
    const migrationPath = path.resolve('./migrations', lastMigration.name);
    const MigrationClass = (await import(migrationPath)).default;
    const migration = new MigrationClass();
    
    console.log(`Rolling back migration: ${lastMigration.name}`);
    
    await migration.down(this.db);
    
    // Remove migration record
    await this.db.deleteFrom('migrations')
      .where('name', '=', lastMigration.name)
      .execute();
    
    console.log(`✅ Rolled back migration: ${lastMigration.name}`);
  }
}

// CLI usage
async function main() {
  const db = new MysqlClient(process.env.DATABASE_URL!);
  const runner = new MigrationRunner(db);
  
  const command = process.argv[2];
  
  try {
    switch (command) {
      case 'migrate':
        await runner.migrate();
        break;
      case 'rollback':
        await runner.rollback();
        break;
      case 'status':
        const pending = await runner.getPendingMigrations();
        const executed = await runner.getExecutedMigrations();
        console.log(`Executed: ${executed.length}`);
        console.log(`Pending: ${pending.length}`);
        break;
      default:
        console.log('Usage: bun migrate.ts [migrate|rollback|status]');
    }
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  } finally {
    await db.$sql.end();
  }
}

if (import.meta.main) {
  main();
}

Running Migrations

Execute migrations from the command line:

# Run all pending migrations
bun run scripts/migrate.ts migrate

# Rollback the last migration
bun run scripts/migrate.ts rollback

# Check migration status
bun run scripts/migrate.ts status

Data Migrations

Migrating Existing Data

Sometimes you need to migrate data along with schema changes:

export default class MigrateUserNameFormat extends Migration {
  async up(db: MysqlClient): Promise<void> {
    // 1. Add new columns
    await db.schema.alterTable('users', (table) => {
      table.addColumn('first_name', 'varchar', { size: 100 }).nullable();
      table.addColumn('last_name', 'varchar', { size: 100 }).nullable();
    });
    
    // 2. Migrate existing data
    const users = await db.select(['id', 'name']).from('users').getMany();
    
    for (const user of users) {
      const nameParts = user.name.split(' ');
      const firstName = nameParts[0] || '';
      const lastName = nameParts.slice(1).join(' ') || '';
      
      await db.updateTable('users')
        .set({
          first_name: firstName,
          last_name: lastName
        })
        .where('id', '=', user.id)
        .execute();
    }
    
    // 3. Make new columns not nullable
    await db.schema.alterTable('users', (table) => {
      table.alterColumn('first_name').notNullable();
      table.alterColumn('last_name').notNullable();
    });
    
    // 4. Drop old column
    await db.schema.alterTable('users', (table) => {
      table.dropColumn('name');
    });
  }

  async down(db: MysqlClient): Promise<void> {
    // 1. Add back the old column
    await db.schema.alterTable('users', (table) => {
      table.addColumn('name', 'varchar', { size: 200 }).nullable();
    });
    
    // 2. Migrate data back
    const users = await db.select(['id', 'first_name', 'last_name'])
      .from('users')
      .getMany();
    
    for (const user of users) {
      const fullName = `${user.first_name} ${user.last_name}`.trim();
      
      await db.updateTable('users')
        .set('name', fullName)
        .where('id', '=', user.id)
        .execute();
    }
    
    // 3. Make name column not nullable
    await db.schema.alterTable('users', (table) => {
      table.alterColumn('name').notNullable();
    });
    
    // 4. Drop new columns
    await db.schema.alterTable('users', (table) => {
      table.dropColumn('first_name');
      table.dropColumn('last_name');
    });
  }
}

Seeding Data

Create migrations to seed initial data:

export default class SeedInitialData extends Migration {
  async up(db: MysqlClient): Promise<void> {
    // Create default categories
    await db.insertInto('categories')
      .values([
        {
          name: 'Technology',
          slug: 'technology',
          description: 'Posts about technology and programming',
          sort_order: 1
        },
        {
          name: 'Design',
          slug: 'design',
          description: 'Posts about design and UX',
          sort_order: 2
        },
        {
          name: 'Business',
          slug: 'business',
          description: 'Posts about business and entrepreneurship',
          sort_order: 3
        }
      ])
      .execute();
    
    // Create admin user
    await db.insertInto('users')
      .values({
        name: 'Admin User',
        email: 'admin@example.com',
        password_hash: await hashPassword('admin123'),
        role: 'admin',
        email_verified: true,
        email_verified_at: new Date()
      })
      .execute();
  }

  async down(db: MysqlClient): Promise<void> {
    // Remove seeded data
    await db.deleteFrom('categories')
      .where('slug', 'IN', ['technology', 'design', 'business'])
      .execute();
    
    await db.deleteFrom('users')
      .where('email', '=', 'admin@example.com')
      .execute();
  }
}

Environment-Specific Migrations

Development vs Production

Handle different environments:

// migrations/005_add_debug_columns.ts
export default class AddDebugColumns extends Migration {
  async up(db: MysqlClient): Promise<void> {
    // Only add debug columns in development
    if (process.env.NODE_ENV === 'development') {
      await db.schema.alterTable('users', (table) => {
        table.addColumn('debug_info', 'json').nullable();
        table.addColumn('test_flag', 'boolean').default(false);
      });
    }
  }

  async down(db: MysqlClient): Promise<void> {
    if (process.env.NODE_ENV === 'development') {
      await db.schema.alterTable('users', (table) => {
        table.dropColumn('debug_info');
        table.dropColumn('test_flag');
      });
    }
  }
}

Best Practices

Migration Guidelines

  1. Always include down methods: Every migration should be reversible
  2. Make migrations atomic: Each migration should be a single logical change
  3. Test migrations: Test both up and down methods before deploying
  4. Backup before production: Always backup before running migrations in production
  5. Never edit existing migrations: Create new migrations for changes

Safe Schema Changes

// ✅ Safe: Adding nullable columns
table.addColumn('new_field', 'varchar', { size: 100 }).nullable();

// ✅ Safe: Adding columns with defaults
table.addColumn('status', 'varchar', { size: 20 }).default('active');

// ⚠️ Requires care: Adding non-nullable columns to existing data
// 1. Add as nullable first
table.addColumn('required_field', 'varchar', { size: 50 }).nullable();
// 2. Update existing records
// 3. Make not nullable in a separate migration

// ❌ Dangerous: Dropping columns immediately
// Instead, deprecate first, then drop in later migration

Production Deployment

// scripts/production-migrate.ts
async function productionMigrate() {
  console.log('Starting production migration...');
  
  // 1. Backup database
  console.log('Creating backup...');
  await createBackup();
  
  // 2. Run migrations
  console.log('Running migrations...');
  const runner = new MigrationRunner(db);
  await runner.migrate();
  
  // 3. Verify data integrity
  console.log('Verifying data...');
  await verifyDataIntegrity();
  
  console.log('✅ Production migration completed');
}

This comprehensive guide covers everything you need to know about database migrations with the Bunty ORM. Migrations ensure your database schema evolves safely and consistently across all environments.

Next Steps

Have questions? Join our Discord community
Found an issue? Edit this page on GitHub