/create-database-migrations

Create Database Migrations

Create and manage database migrations

Instructions

1. **Migration Strategy and Planning**

• Analyze current database schema and target changes
• Plan migration strategy for zero-downtime deployments
• Define rollback procedures and data safety measures
• Assess migration complexity and potential risks
• Plan for data transformation and validation

2. **Migration Framework Setup**

• Set up comprehensive migration framework:

**Node.js Migration Framework:**

   // migrations/migration-framework.js
   const fs = require('fs').promises;
   const path = require('path');
   const { Pool } = require('pg');

   class MigrationManager {
     constructor(databaseConfig) {
       this.pool = new Pool(databaseConfig);
       this.migrationsDir = path.join(__dirname, 'migrations');
       this.lockTimeout = 30000; // 30 seconds
     }

     async initialize() {
       // Create migrations tracking table
       await this.pool.query(`
         CREATE TABLE IF NOT EXISTS schema_migrations (
           id SERIAL PRIMARY KEY,
           version VARCHAR(255) UNIQUE NOT NULL,
           name VARCHAR(255) NOT NULL,
           executed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
           execution_time_ms INTEGER,
           checksum VARCHAR(64),
           rollback_sql TEXT,
           batch_number INTEGER
         );
         
         CREATE INDEX IF NOT EXISTS idx_schema_migrations_version 
         ON schema_migrations(version);
         
         CREATE INDEX IF NOT EXISTS idx_schema_migrations_batch 
         ON schema_migrations(batch_number);
       `);

       // Create migration lock table
       await this.pool.query(`
         CREATE TABLE IF NOT EXISTS migration_lock (
           id INTEGER PRIMARY KEY DEFAULT 1,
           is_locked BOOLEAN DEFAULT FALSE,
           locked_at TIMESTAMP WITH TIME ZONE,
           locked_by VARCHAR(255),
           CHECK (id = 1)
         );
         
         INSERT INTO migration_lock (id, is_locked) 
         VALUES (1, FALSE) 
         ON CONFLICT (id) DO NOTHING;
       `);
     }

     async acquireLock(lockId = 'migration') {
       const client = await this.pool.connect();
       try {
         const result = await client.query(`
           UPDATE migration_lock 
           SET is_locked = TRUE, locked_at = CURRENT_TIMESTAMP, locked_by = $1
           WHERE id = 1 AND (is_locked = FALSE OR locked_at < CURRENT_TIMESTAMP - INTERVAL '${this.lockTimeout} milliseconds')
           RETURNING is_locked;
         `, [lockId]);

         if (result.rows.length === 0) {
           throw new Error('Could not acquire migration lock - another migration may be running');
         }

         return client;
       } catch (error) {
         client.release();
         throw error;
       }
     }

     async releaseLock(client) {
       try {
         await client.query(`
           UPDATE migration_lock 
           SET is_locked = FALSE, locked_at = NULL, locked_by = NULL 
           WHERE id = 1;
         `);
       } finally {
         client.release();
       }
     }

     async getPendingMigrations() {
       const files = await fs.readdir(this.migrationsDir);
       const migrationFiles = files
         .filter(file => file.endsWith('.sql') || file.endsWith('.js'))
         .sort();

       const executedMigrations = await this.pool.query(
         'SELECT version FROM schema_migrations ORDER BY version'
       );
       const executedVersions = new Set(executedMigrations.rows.map(row => row.version));

       return migrationFiles
         .map(file => {
           const version = this.extractVersion(file);
           return { file, version, executed: executedVersions.has(version) };
         })
         .filter(migration => !migration.executed);
     }

     extractVersion(filename) {
       const match = filename.match(/^(\d{14})/);
       if (!match) {
         throw new Error(`Invalid migration filename format: ${filename}`);
       }
       return match[1];
     }

     async runMigration(migrationFile) {
       const version = this.extractVersion(migrationFile.file);
       const filePath = path.join(this.migrationsDir, migrationFile.file);
       const startTime = Date.now();

       console.log(`Running migration: ${migrationFile.file}`);

       const client = await this.pool.connect();
       try {
         await client.query('BEGIN');

         let migrationContent;
         let rollbackSql = '';

         if (migrationFile.file.endsWith('.js')) {
           // JavaScript migration
           const migration = require(filePath);
           await migration.up(client);
           rollbackSql = migration.down ? migration.down.toString() : '';
         } else {
           // SQL migration
           migrationContent = await fs.readFile(filePath, 'utf8');
           const { upSql, downSql } = this.parseSqlMigration(migrationContent);
           
           await client.query(upSql);
           rollbackSql = downSql;
         }

         const executionTime = Date.now() - startTime;
         const checksum = this.generateChecksum(migrationContent || migrationFile.file);
         const batchNumber = await this.getNextBatchNumber();

         // Record migration execution
         await client.query(`
           INSERT INTO schema_migrations (version, name, execution_time_ms, checksum, rollback_sql, batch_number)
           VALUES ($1, $2, $3, $4, $5, $6)
         `, [version, migrationFile.file, executionTime, checksum, rollbackSql, batchNumber]);

         await client.query('COMMIT');
         console.log(`✓ Migration ${migrationFile.file} completed in ${executionTime}ms`);

       } catch (error) {
         await client.query('ROLLBACK');
         console.error(`✗ Migration ${migrationFile.file} failed:`, error.message);
         throw error;
       } finally {
         client.release();
       }
     }

     parseSqlMigration(content) {
       const lines = content.spli