# Database Migrations > **Skill Purpose:** Database schema migration patterns, version control, and deployment strategies --- ## Core Skill Pattern **Objective:** Establish comprehensive database migration patterns with version control, rollback procedures, and safe deployment strategies. **Universal Pattern:** 1. Define migration file structure and naming conventions 2. Create migration execution and rollback patterns 3. Set up database version tracking and state management 4. Establish migration testing and validation procedures 5. Create migration deployment and rollback procedures **Key Decisions (Project-Specific):** - Migration file format and structure - Version tracking and state management approach - Rollback strategy and safety procedures - Testing depth and validation requirements - Deployment automation and timing procedures --- ## Project-Specific Implementation Notes **Customize per project:** - Migration format based on database system and team preferences - Version tracking depth based on deployment complexity - Rollback safety based on data criticality and risk tolerance - Testing coverage based on schema complexity and impact - Deployment automation based on team size and release frequency --- ## Example Implementation (Database Migration Pattern) > **Note:** This is an example pattern for database migrations. Adapt migration format and procedures based on your specific database system and deployment requirements. ### Prerequisites (Example) - Database system selected and configured - Migration tooling identified and set up - Schema changes planned and documented - Deployment environment established --- ## Example: Database Migration Implementation > **Framework-Specific Example:** This demonstrates migration patterns using SQL-based migrations. Adapt for your database system and migration tooling. ### 1. Migration File Structure ```typescript // Migration file naming convention interface MigrationFile { version: string; // Format: YYYYMMDD_HHMMSS description: string; up: string; // SQL to apply migration down: string; // SQL to rollback migration dependencies?: string[]; // Previous migration versions } // Example migration file structure const migrationTemplate = ` -- Migration: {{version}} - {{description}} -- Dependencies: {{dependencies}} -- UP {{upSQL}} -- DOWN {{downSQL}} `; // Migration file example: 20240131_143000_create_users_table.sql const exampleMigration = ` -- Migration: 20240131_143000 - Create users table -- Dependencies: -- UP CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(255) NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); -- Create indexes CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_created_at ON users(created_at); -- DOWN DROP TABLE IF EXISTS users; `; ``` ### 2. Migration Management System ```typescript // Migration tracking and execution interface MigrationRecord { version: string; description: string; executed_at: Date; execution_time_ms: number; success: boolean; error_message?: string; } class MigrationManager { private migrations: MigrationFile[] = []; private executedMigrations: MigrationRecord[] = []; constructor(private db: Database) {} async loadMigrations(migrationPath: string): Promise { // Load migration files from directory const files = await this.readMigrationFiles(migrationPath); this.migrations = files.sort((a, b) => a.version.localeCompare(b.version)); } async executeMigrations(): Promise { // Get current migration state await this.loadExecutedMigrations(); // Find pending migrations const pendingMigrations = this.migrations.filter( migration => !this.executedMigrations.find(executed => executed.version === migration.version) ); // Execute pending migrations in order for (const migration of pendingMigrations) { await this.executeMigration(migration); } } private async executeMigration(migration: MigrationFile): Promise { const startTime = Date.now(); try { // Check dependencies await this.checkDependencies(migration); // Start transaction await this.db.query('BEGIN'); try { // Execute UP migration await this.db.query(migration.up); // Record migration await this.recordMigration(migration, true, Date.now() - startTime); // Commit transaction await this.db.query('COMMIT'); console.log(`✅ Migration ${migration.version} executed successfully`); } catch (error) { // Rollback transaction await this.db.query('ROLLBACK'); throw error; } } catch (error) { // Record failed migration await this.recordMigration(migration, false, Date.now() - startTime, error.message); console.error(`❌ Migration ${migration.version} failed:`, error); throw error; } } async rollbackMigration(version: string): Promise { const migration = this.migrations.find(m => m.version === version); if (!migration) { throw new Error(`Migration ${version} not found`); } // Check if migration was executed const executed = this.executedMigrations.find(m => m.version === version); if (!executed) { throw new Error(`Migration ${version} was not executed`); } try { // Start transaction await this.db.query('BEGIN'); try { // Execute DOWN migration await this.db.query(migration.down); // Remove migration record await this.removeMigrationRecord(version); // Commit transaction await this.db.query('COMMIT'); console.log(`✅ Migration ${version} rolled back successfully`); } catch (error) { // Rollback transaction await this.db.query('ROLLBACK'); throw error; } } catch (error) { console.error(`❌ Migration ${version} rollback failed:`, error); throw error; } } private async checkDependencies(migration: MigrationFile): Promise { if (!migration.dependencies) return; for (const dependency of migration.dependencies) { const executed = this.executedMigrations.find(m => m.version === dependency); if (!executed) { throw new Error(`Dependency ${dependency} not executed for migration ${migration.version}`); } } } private async recordMigration( migration: MigrationFile, success: boolean, executionTime: number, errorMessage?: string ): Promise { const record: MigrationRecord = { version: migration.version, description: migration.description, executed_at: new Date(), execution_time_ms: executionTime, success, error_message: errorMessage }; await this.db.query(` INSERT INTO schema_migrations (version, description, executed_at, execution_time_ms, success, error_message) VALUES ($1, $2, $3, $4, $5, $6) `, [record.version, record.description, record.executed_at, record.execution_time_ms, record.success, record.error_message]); } private async removeMigrationRecord(version: string): Promise { await this.db.query('DELETE FROM schema_migrations WHERE version = $1', [version]); } private async loadExecutedMigrations(): Promise { // Create migrations table if not exists await this.db.query(` CREATE TABLE IF NOT EXISTS schema_migrations ( version VARCHAR(255) PRIMARY KEY, description TEXT, executed_at TIMESTAMP WITH TIME ZONE, execution_time_ms INTEGER, success BOOLEAN, error_message TEXT ) `); // Load executed migrations const result = await this.db.query('SELECT * FROM schema_migrations ORDER BY version'); this.executedMigrations = result.rows; } private async readMigrationFiles(path: string): Promise { // Implementation for reading migration files // This would read from filesystem and parse migration files return []; } } ``` ### 3. Migration Testing Patterns ```typescript // Migration testing framework interface MigrationTest { name: string; migration: string; beforeEach?: string; // SQL to run before test afterEach?: string; // SQL to run after test assertions: TestAssertion[]; } interface TestAssertion { type: 'table_exists' | 'table_not_exists' | 'column_exists' | 'data_count' | 'data_valid'; target: string; expected?: any; query?: string; } class MigrationTester { private tests: MigrationTest[] = []; addTest(test: MigrationTest): void { this.tests.push(test); } async runTests(db: Database): Promise { const results: TestResult[] = []; for (const test of this.tests) { const result = await this.runSingleTest(test, db); results.push(result); } return { totalTests: this.tests.length, passedTests: results.filter(r => r.passed).length, failedTests: results.filter(r => !r.passed).length, results }; } private async runSingleTest(test: MigrationTest, db: Database): Promise { try { // Run beforeEach if (test.beforeEach) { await db.query(test.beforeEach); } // Run assertions for (const assertion of test.assertions) { const passed = await this.runAssertion(assertion, db); if (!passed) { return { testName: test.name, passed: false, error: `Assertion failed: ${assertion.type} on ${assertion.target}` }; } } return { testName: test.name, passed: true }; } catch (error) { return { testName: test.name, passed: false, error: error instanceof Error ? error.message : 'Unknown error' }; } finally { // Run afterEach if (test.afterEach) { await db.query(test.afterEach); } } } private async runAssertion(assertion: TestAssertion, db: Database): Promise { switch (assertion.type) { case 'table_exists': const tableExists = await db.query(` SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_name = $1 ) `, [assertion.target]); return tableExists.rows[0].exists; case 'table_not_exists': const tableNotExists = await db.query(` SELECT NOT EXISTS ( SELECT FROM information_schema.tables WHERE table_name = $1 ) `, [assertion.target]); return tableNotExists.rows[0].exists; case 'column_exists': const columnExists = await db.query(` SELECT EXISTS ( SELECT FROM information_schema.columns WHERE table_name = $1 AND column_name = $2 ) `, [assertion.target, assertion.expected]); return columnExists.rows[0].exists; case 'data_count': const countResult = await db.query(`SELECT COUNT(*) as count FROM ${assertion.target}`); return countResult.rows[0].count === assertion.expected; case 'data_valid': if (!assertion.query) throw new Error('Query required for data_valid assertion'); const validationResult = await db.query(assertion.query); return validationResult.rows.length > 0; default: throw new Error(`Unknown assertion type: ${assertion.type}`); } } } interface TestResult { testName: string; passed: boolean; error?: string; } interface TestResults { totalTests: number; passedTests: number; failedTests: number; results: TestResult[]; } ``` ### 4. Migration Deployment Patterns ```typescript // Migration deployment automation interface DeploymentConfig { environment: 'development' | 'staging' | 'production'; dryRun: boolean; backupRequired: boolean; approvalRequired: boolean; timeoutMs: number; } class MigrationDeployer { async deploy(config: DeploymentConfig): Promise { console.log(`🚀 Starting migration deployment to ${config.environment}`); // Pre-deployment checks await this.preDeploymentChecks(config); // Create backup if required if (config.backupRequired) { await this.createBackup(config.environment); } // Get approval if required if (config.approvalRequired) { await this.getApproval(config.environment); } // Deploy migrations const result = await this.executeDeployment(config); // Post-deployment verification await this.postDeploymentVerification(config); return result; } private async preDeploymentChecks(config: DeploymentConfig): Promise { console.log('🔍 Running pre-deployment checks...'); // Check database connection await this.checkDatabaseConnection(); // Check migration files await this.validateMigrationFiles(); // Check for conflicts await this.checkForConflicts(); console.log('✅ Pre-deployment checks passed'); } private async createBackup(environment: string): Promise { console.log(`💾 Creating backup for ${environment}...`); // Implementation for database backup // This would create a database backup before migration console.log('✅ Backup created successfully'); } private async getApproval(environment: string): Promise { if (environment === 'production') { console.log('👋 Waiting for production deployment approval...'); // Implementation for approval process // This could be manual approval or automated checks console.log('✅ Approval received'); } } private async executeDeployment(config: DeploymentConfig): Promise { const startTime = Date.now(); try { if (config.dryRun) { console.log('🔍 Dry run mode - showing what would be executed:'); return await this.dryRunExecution(); } else { console.log('⚡ Executing migrations...'); const migrationManager = new MigrationManager(this.db); await migrationManager.executeMigrations(); return { success: true, duration: Date.now() - startTime, migrationsExecuted: migrationManager.getExecutedCount() }; } } catch (error) { return { success: false, duration: Date.now() - startTime, error: error instanceof Error ? error.message : 'Unknown error' }; } } private async dryRunExecution(): Promise { // Implementation for dry run // This would show what migrations would be executed without actually running them return { success: true, duration: 0, migrationsExecuted: 0, dryRun: true }; } private async postDeploymentVerification(config: DeploymentConfig): Promise { console.log('🔍 Running post-deployment verification...'); // Run migration tests const tester = new MigrationTester(); const testResults = await tester.runTests(this.db); if (testResults.failedTests > 0) { throw new Error(`${testResults.failedTests} migration tests failed`); } console.log('✅ Post-deployment verification passed'); } private async checkDatabaseConnection(): Promise { // Implementation for database connection check } private async validateMigrationFiles(): Promise { // Implementation for migration file validation } private async checkForConflicts(): Promise { // Implementation for conflict checking } } interface DeploymentResult { success: boolean; duration: number; migrationsExecuted?: number; error?: string; dryRun?: boolean; } ``` ### 5. Migration Best Practices ```typescript // Migration best practices and guidelines interface MigrationGuidelines { naming: NamingGuidelines; structure: StructureGuidelines; safety: SafetyGuidelines; testing: TestingGuidelines; } const migrationGuidelines: MigrationGuidelines = { naming: { format: 'YYYYMMDD_HHMMSS_description', examples: [ '20240131_143000_create_users_table', '20240131_150000_add_email_index_to_users', '20240131_160000_drop_unused_column_from_orders' ], rules: [ 'Use timestamp prefix for ordering', 'Use snake_case for description', 'Be descriptive but concise', 'Include table name in table operations' ] }, structure: { sections: ['UP', 'DOWN'], requirements: [ 'Always include rollback logic', 'Use transaction-safe operations', 'Add comments for complex logic', 'Include index creation in separate migrations' ], prohibited: [ 'Data loss operations without backup', 'Long-running locks in production', 'Schema changes that break existing queries' ] }, safety: { preDeployment: [ 'Test in staging environment', 'Create database backup', 'Review with team members', 'Check for data dependencies' ], postDeployment: [ 'Verify application functionality', 'Monitor performance metrics', 'Check error logs', 'Validate data integrity' ] }, testing: { types: [ 'Schema validation tests', 'Data integrity tests', 'Performance tests', 'Rollback tests' ], coverage: [ 'Test all new tables and columns', 'Verify constraints and indexes', 'Test rollback procedures', 'Validate data migrations' ] } }; ``` --- ## Integration Patterns ### Data Department Integration - Schema design and migration coordination - Type generation synchronization - Documentation maintenance ### API Department Integration - API contract alignment with schema changes - Migration impact on API endpoints - Version coordination between schema and API ### QA Department Integration - Migration testing and validation - Test data synchronization - Environment consistency --- ## Stop Conditions **STOP migration operations and escalate if:** - Migration execution fails repeatedly - Rollback procedures fail - Data corruption detected - Performance degradation observed - Application functionality breaks **PAUSE operations if:** - Database in maintenance mode - High traffic periods - Backup operations in progress - Other migrations running --- ## Safety Procedures **Pre-Migration Safety:** - Database backup creation - Application downtime planning - Rollback procedure testing - Team notification and approval **Post-Migration Safety:** - Application functionality verification - Performance monitoring - Data integrity checks - Error log monitoring

Database Migrations

Install this skill

Comments