1. php
  2. /database
  3. /migrations

Database Migrations in PHP

Introduction to Database Migrations

Database migrations are version control for your database schema. They allow you to modify your database structure in a structured, repeatable way while keeping track of changes over time. Migrations enable teams to synchronize database changes and deploy schema updates safely across different environments.

Benefits of Database Migrations

  1. Version Control: Track database schema changes over time
  2. Team Collaboration: Share schema changes across development teams
  3. Environment Consistency: Maintain identical schemas across environments
  4. Rollback Capability: Undo problematic changes safely
  5. Automated Deployment: Deploy database changes with application code
  6. Change Documentation: Maintain a history of schema modifications

Basic Migration System

Migration Structure

<?php
abstract class Migration
{
    protected $pdo;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    abstract public function up(): void;
    abstract public function down(): void;
    
    protected function execute(string $sql): void
    {
        $this->pdo->exec($sql);
    }
    
    protected function query(string $sql, array $params = []): PDOStatement
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
}
?>

Migration Manager

<?php
class MigrationManager
{
    private $pdo;
    private $migrationsPath;
    private $migrationsTable = 'migrations';
    
    public function __construct(PDO $pdo, string $migrationsPath)
    {
        $this->pdo = $pdo;
        $this->migrationsPath = rtrim($migrationsPath, '/');
        $this->ensureMigrationsTable();
    }
    
    private function ensureMigrationsTable(): void
    {
        $sql = "CREATE TABLE IF NOT EXISTS {$this->migrationsTable} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            migration VARCHAR(255) NOT NULL UNIQUE,
            executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )";
        $this->pdo->exec($sql);
    }
    
    public function migrate(): void
    {
        $pendingMigrations = $this->getPendingMigrations();
        
        if (empty($pendingMigrations)) {
            echo "No pending migrations.\n";
            return;
        }
        
        foreach ($pendingMigrations as $migration) {
            $this->executeMigration($migration, 'up');
            echo "Migrated: {$migration}\n";
        }
    }
    
    public function rollback(int $steps = 1): void
    {
        $executedMigrations = $this->getExecutedMigrations();
        $migrationsToRollback = array_slice($executedMigrations, -$steps);
        
        foreach (array_reverse($migrationsToRollback) as $migration) {
            $this->executeMigration($migration, 'down');
            $this->removeMigrationRecord($migration);
            echo "Rolled back: {$migration}\n";
        }
    }
    
    public function reset(): void
    {
        $executedMigrations = $this->getExecutedMigrations();
        
        foreach (array_reverse($executedMigrations) as $migration) {
            $this->executeMigration($migration, 'down');
            echo "Rolled back: {$migration}\n";
        }
        
        $this->pdo->exec("DELETE FROM {$this->migrationsTable}");
        echo "All migrations rolled back.\n";
    }
    
    public function status(): void
    {
        $allMigrations = $this->getAllMigrationFiles();
        $executedMigrations = $this->getExecutedMigrations();
        
        echo "Migration Status:\n";
        echo str_repeat('-', 50) . "\n";
        
        foreach ($allMigrations as $migration) {
            $status = in_array($migration, $executedMigrations) ? 'Migrated' : 'Pending';
            echo sprintf("%-30s %s\n", $migration, $status);
        }
    }
    
    private function getPendingMigrations(): array
    {
        $allMigrations = $this->getAllMigrationFiles();
        $executedMigrations = $this->getExecutedMigrations();
        
        return array_diff($allMigrations, $executedMigrations);
    }
    
    private function getExecutedMigrations(): array
    {
        $stmt = $this->pdo->query("SELECT migration FROM {$this->migrationsTable} ORDER BY id");
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }
    
    private function getAllMigrationFiles(): array
    {
        $files = glob($this->migrationsPath . '/*.php');
        $migrations = [];
        
        foreach ($files as $file) {
            $filename = basename($file, '.php');
            if (preg_match('/^\d{14}_/', $filename)) {
                $migrations[] = $filename;
            }
        }
        
        sort($migrations);
        return $migrations;
    }
    
    private function executeMigration(string $migration, string $direction): void
    {
        $filePath = $this->migrationsPath . '/' . $migration . '.php';
        
        if (!file_exists($filePath)) {
            throw new Exception("Migration file not found: {$filePath}");
        }
        
        require_once $filePath;
        
        $className = $this->getClassNameFromFile($migration);
        
        if (!class_exists($className)) {
            throw new Exception("Migration class not found: {$className}");
        }
        
        $migrationInstance = new $className($this->pdo);
        
        $this->pdo->beginTransaction();
        
        try {
            if ($direction === 'up') {
                $migrationInstance->up();
                $this->recordMigration($migration);
            } else {
                $migrationInstance->down();
            }
            
            $this->pdo->commit();
        } catch (Exception $e) {
            $this->pdo->rollBack();
            throw new Exception("Migration failed: {$migration}. Error: " . $e->getMessage());
        }
    }
    
    private function recordMigration(string $migration): void
    {
        $stmt = $this->pdo->prepare("INSERT INTO {$this->migrationsTable} (migration) VALUES (?)");
        $stmt->execute([$migration]);
    }
    
    private function removeMigrationRecord(string $migration): void
    {
        $stmt = $this->pdo->prepare("DELETE FROM {$this->migrationsTable} WHERE migration = ?");
        $stmt->execute([$migration]);
    }
    
    private function getClassNameFromFile(string $filename): string
    {
        // Convert filename to class name
        // e.g., 20231201123000_create_users_table -> CreateUsersTable
        $parts = explode('_', substr($filename, 15)); // Remove timestamp
        return implode('', array_map('ucfirst', $parts));
    }
}
?>

Creating Migrations

Migration File Generator

<?php
class MigrationGenerator
{
    private $migrationsPath;
    
    public function __construct(string $migrationsPath)
    {
        $this->migrationsPath = rtrim($migrationsPath, '/');
        
        if (!is_dir($this->migrationsPath)) {
            mkdir($this->migrationsPath, 0755, true);
        }
    }
    
    public function create(string $name): string
    {
        $timestamp = date('YmdHis');
        $filename = "{$timestamp}_{$name}.php";
        $className = $this->getClassName($name);
        
        $content = $this->getTemplate($className);
        
        $filePath = $this->migrationsPath . '/' . $filename;
        file_put_contents($filePath, $content);
        
        echo "Created migration: {$filename}\n";
        return $filePath;
    }
    
    private function getClassName(string $name): string
    {
        $parts = explode('_', $name);
        return implode('', array_map('ucfirst', $parts));
    }
    
    private function getTemplate(string $className): string
    {
        return <<<PHP
<?php

class {$className} extends Migration
{
    public function up(): void
    {
        // Add your migration logic here
        \$sql = "
            -- Add your SQL here
        ";
        \$this->execute(\$sql);
    }
    
    public function down(): void
    {
        // Add your rollback logic here
        \$sql = "
            -- Add your rollback SQL here
        ";
        \$this->execute(\$sql);
    }
}
PHP;
    }
}
?>

Common Migration Patterns

Creating Tables

<?php
class CreateUsersTable extends Migration
{
    public function up(): void
    {
        $sql = "
            CREATE TABLE users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255) NOT NULL UNIQUE,
                email_verified_at TIMESTAMP NULL,
                password VARCHAR(255) NOT NULL,
                remember_token VARCHAR(100) NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        ";
        $this->execute($sql);
    }
    
    public function down(): void
    {
        $this->execute("DROP TABLE IF EXISTS users");
    }
}
?>

Adding Columns

<?php
class AddPhoneToUsersTable extends Migration
{
    public function up(): void
    {
        $sql = "ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email";
        $this->execute($sql);
    }
    
    public function down(): void
    {
        $sql = "ALTER TABLE users DROP COLUMN phone";
        $this->execute($sql);
    }
}
?>

Modifying Columns

<?php
class ModifyUsersEmailColumn extends Migration
{
    public function up(): void
    {
        // Make email column longer and add index
        $sql = "
            ALTER TABLE users 
            MODIFY COLUMN email VARCHAR(320) NOT NULL,
            ADD INDEX idx_users_email (email)
        ";
        $this->execute($sql);
    }
    
    public function down(): void
    {
        $sql = "
            ALTER TABLE users 
            DROP INDEX idx_users_email,
            MODIFY COLUMN email VARCHAR(255) NOT NULL
        ";
        $this->execute($sql);
    }
}
?>

Creating Indexes

<?php
class AddIndexesToUsersTable extends Migration
{
    public function up(): void
    {
        $sql = "
            ALTER TABLE users 
            ADD INDEX idx_users_created_at (created_at),
            ADD INDEX idx_users_email_verified (email_verified_at),
            ADD UNIQUE INDEX idx_users_email_unique (email)
        ";
        $this->execute($sql);
    }
    
    public function down(): void
    {
        $sql = "
            ALTER TABLE users 
            DROP INDEX idx_users_created_at,
            DROP INDEX idx_users_email_verified,
            DROP INDEX idx_users_email_unique
        ";
        $this->execute($sql);
    }
}
?>

Foreign Key Constraints

<?php
class CreatePostsTable extends Migration
{
    public function up(): void
    {
        $sql = "
            CREATE TABLE posts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                title VARCHAR(255) NOT NULL,
                content TEXT,
                published_at TIMESTAMP NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                
                INDEX idx_posts_user_id (user_id),
                INDEX idx_posts_published_at (published_at),
                
                CONSTRAINT fk_posts_user_id 
                    FOREIGN KEY (user_id) 
                    REFERENCES users(id) 
                    ON DELETE CASCADE ON UPDATE CASCADE
            )
        ";
        $this->execute($sql);
    }
    
    public function down(): void
    {
        $this->execute("DROP TABLE IF EXISTS posts");
    }
}
?>

Advanced Migration Features

Schema Builder Helper

<?php
class SchemaBuilder
{
    private $pdo;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    public function create(string $table, callable $callback): void
    {
        $tableBuilder = new TableBuilder($table);
        $callback($tableBuilder);
        
        $sql = $tableBuilder->toCreateSql();
        $this->pdo->exec($sql);
    }
    
    public function table(string $table, callable $callback): void
    {
        $tableBuilder = new TableBuilder($table, false);
        $callback($tableBuilder);
        
        $statements = $tableBuilder->toAlterSql();
        foreach ($statements as $sql) {
            $this->pdo->exec($sql);
        }
    }
    
    public function drop(string $table): void
    {
        $this->pdo->exec("DROP TABLE IF EXISTS {$table}");
    }
    
    public function dropIfExists(string $table): void
    {
        $this->pdo->exec("DROP TABLE IF EXISTS {$table}");
    }
}

class TableBuilder
{
    private $table;
    private $columns = [];
    private $indexes = [];
    private $constraints = [];
    private $isCreate;
    
    public function __construct(string $table, bool $isCreate = true)
    {
        $this->table = $table;
        $this->isCreate = $isCreate;
    }
    
    public function id(string $name = 'id'): self
    {
        $this->columns[] = "{$name} INT AUTO_INCREMENT PRIMARY KEY";
        return $this;
    }
    
    public function string(string $name, int $length = 255): self
    {
        $this->columns[] = "{$name} VARCHAR({$length})";
        return $this;
    }
    
    public function text(string $name): self
    {
        $this->columns[] = "{$name} TEXT";
        return $this;
    }
    
    public function integer(string $name): self
    {
        $this->columns[] = "{$name} INT";
        return $this;
    }
    
    public function boolean(string $name): self
    {
        $this->columns[] = "{$name} BOOLEAN DEFAULT FALSE";
        return $this;
    }
    
    public function timestamp(string $name): self
    {
        $this->columns[] = "{$name} TIMESTAMP";
        return $this;
    }
    
    public function timestamps(): self
    {
        $this->columns[] = "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP";
        $this->columns[] = "updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
        return $this;
    }
    
    public function nullable(): self
    {
        $lastIndex = count($this->columns) - 1;
        $this->columns[$lastIndex] .= " NULL";
        return $this;
    }
    
    public function notNull(): self
    {
        $lastIndex = count($this->columns) - 1;
        $this->columns[$lastIndex] .= " NOT NULL";
        return $this;
    }
    
    public function unique(): self
    {
        $lastIndex = count($this->columns) - 1;
        $this->columns[$lastIndex] .= " UNIQUE";
        return $this;
    }
    
    public function default($value): self
    {
        $lastIndex = count($this->columns) - 1;
        $defaultValue = is_string($value) ? "'{$value}'" : $value;
        $this->columns[$lastIndex] .= " DEFAULT {$defaultValue}";
        return $this;
    }
    
    public function index(string $column, string $name = null): self
    {
        $indexName = $name ?: "idx_{$this->table}_{$column}";
        $this->indexes[] = "INDEX {$indexName} ({$column})";
        return $this;
    }
    
    public function foreign(string $column): ForeignKeyBuilder
    {
        return new ForeignKeyBuilder($this, $column);
    }
    
    public function addConstraint(string $constraint): self
    {
        $this->constraints[] = $constraint;
        return $this;
    }
    
    public function toCreateSql(): string
    {
        $elements = array_merge($this->columns, $this->indexes, $this->constraints);
        $sql = "CREATE TABLE {$this->table} (\n";
        $sql .= "    " . implode(",\n    ", $elements);
        $sql .= "\n)";
        
        return $sql;
    }
    
    public function toAlterSql(): array
    {
        $statements = [];
        
        foreach ($this->columns as $column) {
            $statements[] = "ALTER TABLE {$this->table} ADD COLUMN {$column}";
        }
        
        foreach ($this->indexes as $index) {
            $statements[] = "ALTER TABLE {$this->table} ADD {$index}";
        }
        
        foreach ($this->constraints as $constraint) {
            $statements[] = "ALTER TABLE {$this->table} ADD {$constraint}";
        }
        
        return $statements;
    }
}

class ForeignKeyBuilder
{
    private $tableBuilder;
    private $column;
    private $references;
    private $onDelete = 'RESTRICT';
    private $onUpdate = 'RESTRICT';
    
    public function __construct(TableBuilder $tableBuilder, string $column)
    {
        $this->tableBuilder = $tableBuilder;
        $this->column = $column;
    }
    
    public function references(string $column): self
    {
        $this->references = $column;
        return $this;
    }
    
    public function on(string $table): self
    {
        $this->table = $table;
        return $this;
    }
    
    public function onDelete(string $action): self
    {
        $this->onDelete = $action;
        return $this;
    }
    
    public function onUpdate(string $action): self
    {
        $this->onUpdate = $action;
        return $this;
    }
    
    public function cascade(): self
    {
        $this->onDelete = 'CASCADE';
        $this->onUpdate = 'CASCADE';
        return $this;
    }
    
    public function __destruct()
    {
        $constraint = "CONSTRAINT fk_{$this->tableBuilder->table}_{$this->column} ";
        $constraint .= "FOREIGN KEY ({$this->column}) ";
        $constraint .= "REFERENCES {$this->table}({$this->references}) ";
        $constraint .= "ON DELETE {$this->onDelete} ON UPDATE {$this->onUpdate}";
        
        $this->tableBuilder->addConstraint($constraint);
    }
}
?>

Using Schema Builder

<?php
class CreateUsersTableWithBuilder extends Migration
{
    public function up(): void
    {
        $schema = new SchemaBuilder($this->pdo);
        
        $schema->create('users', function(TableBuilder $table) {
            $table->id();
            $table->string('name')->notNull();
            $table->string('email')->notNull()->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password')->notNull();
            $table->string('remember_token', 100)->nullable();
            $table->timestamps();
            
            $table->index('email');
            $table->index('created_at');
        });
    }
    
    public function down(): void
    {
        $schema = new SchemaBuilder($this->pdo);
        $schema->drop('users');
    }
}
?>

Data Migrations

Seeding Data

<?php
class SeedDefaultUsers extends Migration
{
    public function up(): void
    {
        $users = [
            [
                'name' => 'Administrator',
                'email' => '[email protected]',
                'password' => password_hash('admin123', PASSWORD_DEFAULT),
                'email_verified_at' => date('Y-m-d H:i:s')
            ],
            [
                'name' => 'Test User',
                'email' => '[email protected]',
                'password' => password_hash('test123', PASSWORD_DEFAULT),
                'email_verified_at' => date('Y-m-d H:i:s')
            ]
        ];
        
        foreach ($users as $user) {
            $stmt = $this->pdo->prepare("
                INSERT INTO users (name, email, password, email_verified_at, created_at)
                VALUES (:name, :email, :password, :email_verified_at, NOW())
            ");
            $stmt->execute($user);
        }
    }
    
    public function down(): void
    {
        $this->execute("DELETE FROM users WHERE email IN ('[email protected]', '[email protected]')");
    }
}
?>

Data Transformation

<?php
class MigrateUserPhoneNumbers extends Migration
{
    public function up(): void
    {
        // Add new column
        $this->execute("ALTER TABLE users ADD COLUMN phone_formatted VARCHAR(20) AFTER phone");
        
        // Format existing phone numbers
        $stmt = $this->query("SELECT id, phone FROM users WHERE phone IS NOT NULL");
        $updateStmt = $this->pdo->prepare("UPDATE users SET phone_formatted = ? WHERE id = ?");
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $formattedPhone = $this->formatPhoneNumber($row['phone']);
            $updateStmt->execute([$formattedPhone, $row['id']]);
        }
        
        // Remove old column
        $this->execute("ALTER TABLE users DROP COLUMN phone");
        
        // Rename new column
        $this->execute("ALTER TABLE users CHANGE phone_formatted phone VARCHAR(20)");
    }
    
    public function down(): void
    {
        // Add back original column
        $this->execute("ALTER TABLE users ADD COLUMN phone_original VARCHAR(50) AFTER phone");
        
        // Copy data back (this might lose some formatting)
        $this->execute("UPDATE users SET phone_original = phone");
        
        // Drop formatted column and rename
        $this->execute("ALTER TABLE users DROP COLUMN phone");
        $this->execute("ALTER TABLE users CHANGE phone_original phone VARCHAR(50)");
    }
    
    private function formatPhoneNumber(string $phone): string
    {
        // Simple phone formatting logic
        $cleaned = preg_replace('/[^0-9]/', '', $phone);
        if (strlen($cleaned) === 10) {
            return sprintf('(%s) %s-%s', 
                substr($cleaned, 0, 3),
                substr($cleaned, 3, 3),
                substr($cleaned, 6)
            );
        }
        return $phone; // Return original if can't format
    }
}
?>

Deployment and Environment Management

Environment-Specific Migrations

<?php
class CreateDevelopmentData extends Migration
{
    public function up(): void
    {
        // Only run in development
        if (getenv('APP_ENV') !== 'development') {
            return;
        }
        
        // Create test data for development
        $this->createTestUsers();
        $this->createTestPosts();
    }
    
    public function down(): void
    {
        if (getenv('APP_ENV') !== 'development') {
            return;
        }
        
        $this->execute("DELETE FROM posts WHERE title LIKE 'Test Post%'");
        $this->execute("DELETE FROM users WHERE email LIKE '%test.dev'");
    }
    
    private function createTestUsers(): void
    {
        for ($i = 1; $i <= 50; $i++) {
            $stmt = $this->pdo->prepare("
                INSERT INTO users (name, email, password, created_at)
                VALUES (?, ?, ?, NOW())
            ");
            $stmt->execute([
                "Test User {$i}",
                "user{$i}@test.dev",
                password_hash('password', PASSWORD_DEFAULT)
            ]);
        }
    }
    
    private function createTestPosts(): void
    {
        $userIds = $this->query("SELECT id FROM users WHERE email LIKE '%test.dev'")->fetchAll(PDO::FETCH_COLUMN);
        
        foreach ($userIds as $userId) {
            for ($i = 1; $i <= 5; $i++) {
                $stmt = $this->pdo->prepare("
                    INSERT INTO posts (user_id, title, content, published_at, created_at)
                    VALUES (?, ?, ?, NOW(), NOW())
                ");
                $stmt->execute([
                    $userId,
                    "Test Post {$i} by User {$userId}",
                    "This is test content for post {$i}"
                ]);
            }
        }
    }
}
?>

Command Line Interface

<?php
// migrate.php - Command line interface
require_once 'vendor/autoload.php';

$pdo = new PDO(
    "mysql:host={$_ENV['DB_HOST']};dbname={$_ENV['DB_NAME']}",
    $_ENV['DB_USER'],
    $_ENV['DB_PASS']
);

$migrationManager = new MigrationManager($pdo, __DIR__ . '/migrations');
$migrationGenerator = new MigrationGenerator(__DIR__ . '/migrations');

$command = $argv[1] ?? 'help';

switch ($command) {
    case 'migrate':
        $migrationManager->migrate();
        break;
    
    case 'rollback':
        $steps = (int)($argv[2] ?? 1);
        $migrationManager->rollback($steps);
        break;
    
    case 'reset':
        $migrationManager->reset();
        break;
    
    case 'status':
        $migrationManager->status();
        break;
    
    case 'make':
        if (!isset($argv[2])) {
            echo "Usage: php migrate.php make migration_name\n";
            exit(1);
        }
        $migrationGenerator->create($argv[2]);
        break;
    
    case 'help':
    default:
        echo "Available commands:\n";
        echo "  migrate              Run pending migrations\n";
        echo "  rollback [steps]     Rollback migrations (default: 1 step)\n";
        echo "  reset                Rollback all migrations\n";
        echo "  status               Show migration status\n";
        echo "  make <name>          Create new migration\n";
        break;
}
?>

Best Practices

  1. Atomic Migrations: Each migration should be atomic and reversible
  2. Backup Before Migration: Always backup production data before migrating
  3. Test Rollbacks: Test both up and down migrations in development
  4. Small Changes: Keep migrations small and focused on single changes
  5. Descriptive Names: Use clear, descriptive names for migrations
  6. Documentation: Comment complex migrations and data transformations
  7. Environment Testing: Test migrations in staging before production
  8. Rollback Plans: Always have a rollback plan for production deployments

Database migrations are essential for maintaining database schema integrity across environments and enabling safe, repeatable deployments. They provide version control for your database structure and enable collaborative development with proper change tracking.