Database Migrations in PHP: A Complete Guide to Tools and Libraries

Database Migrations in PHP: A Complete Guide to Tools and Libraries

Developing web applications in PHP is inextricably linked to managing the database structure. As a project evolves, the DB schema constantly changes: new tables are added, fields are modified, indexes are introduced. Manually executing SQL queries across different environments (local, staging, production) leads to errors, data loss, and confusion. The solution to this problem is database migrations — a programmatic way to version and apply schema changes.

What are migrations and why are they needed?

A migration is a PHP class containing two methods: up() (applying changes) and down() (reverting changes). Each migration has a unique identifier (usually a timestamp) and is executed sequentially. The main advantages of using migrations:

• DB schema versioning — the entire change history is stored in code (usually in Git).
• Deployment automation — just run one command to update the DB on any server.
• Safety — the ability to roll back unsuccessful changes.
• Team collaboration — all developers synchronize the DB structure automatically.

Popular libraries for migrations in PHP

Let's look at three most common tools: Phinx, Doctrine Migrations, and the built-in Laravel Migrations.

1. Phinx (robmorgan/phinx)

Phinx is a framework-independent tool for migrations and seeding (populating with test data). It supports MySQL, PostgreSQL, SQLite, SQL Server, and other DBMS. Installation is done via Composer:

composer require robmorgan/phinx

Example migration for creating a users table:

<?phpuse Phinx\\Migration\\AbstractMigration;

class CreateUsersTable extends AbstractMigration{    public function up()    {        $table = $this->table('users');        $table->addColumn('username', 'string', ['limit' => 50])              ->addColumn('email', 'string')              ->addColumn('password', 'string')              ->addColumn('created_at', 'datetime')              ->addIndex(['email'], ['unique' => true])              ->create();    }

    public function down()    {        $this->table('users')->drop()->save();    }}

Phinx provides a convenient DSL (Domain-Specific Language) for describing schemas, as well as built-in support for seeders. Main commands:

• phinx migrate — apply all new migrations.
• phinx rollback — roll back the last migration.
• phinx status — show migration status.
• phinx seed:run — populate the DB with test data.

2. Doctrine Migrations

Doctrine Migrations is part of the Doctrine ORM ecosystem, but can also be used without it. It features powerful integration with Doctrine DBAL (Database Abstraction Layer). Installation:

composer require doctrine/migrations

Example migration using Doctrine DBAL:

<?phpuse Doctrine\\DBAL\\Migrations\\AbstractMigration;use Doctrine\\DBAL\\Schema\\Schema;

class Version20240101000000 extends AbstractMigration{    public function up(Schema $schema)    {        $table = $schema->createTable('users');        $table->addColumn('id', 'integer', ['autoincrement' => true]);        $table->addColumn('username', 'string', ['length' => 50]);        $table->addColumn('email', 'string');        $table->setPrimaryKey(['id']);        $table->addUniqueIndex(['email']);    }

    public function down(Sc