Database Migrations

Table of contents

Introduction

As the application evolves, database tables are created, dropped and changed.

On the machines of other developers, in the testing environment and in the production environment, it wouldn't be practical to constantly apply all these changes to the database manually.

Database migrations are essentially a version control for databases, a way to keep track of the changes and apply them to the database of other environments.

Every set of changes is stored in a "migration" PHP file.

The phinx migration library is framework-independent, straightforward, uses pure PHP and SQL and has a great documentation.

With phinx, the migrations have to be created manually, and there is no command that automatically creates them from a previous database schema or migration file.

For this reason, the library odan/phinx-migrations-generator comes in handy. It helps greatly to generate migration files by comparing the current database with the schema information from the previous version.

This is the only required dependency, it automatically installs phinx too.

composer require odan/phinx-migrations-generator --dev

Configuration

The following directories have to be created for phinx:

• resources/migrations for the migration files
• resources/schema for the schema.php and schema.sql files
• resources/seeds for the database fillers with default data

Phinx configuration

The configuration for phinx is in the file config/defaults.php:

$settings['phinx'] = [
    'paths' => [
        'migrations' => $settings['root_dir'] . '/resources/migrations',
        'seeds' => $settings['root_dir'] . '/resources/seeds',
    ],
    'schema_file' => $settings['root_dir'] . '/resources/schema/schema.php',
    'default_migration_prefix' => 'db_change_',
    'generate_migration_name' => true,
    'environments' => [
        // Table that keeps track of the migrations
        'default_migration_table' => 'phinx_migration_log',
        'default_environment' => 'local',
        'local' => [
            /* Environment specifics such as db credentials from the secret config are added in env.phinx.php */
        ],
    ],
];

Environment configuration

The configuration file that is responsible for setting up the environment for phinx to run migrations is env.phinx.php.

First, it instantiates the app, which makes the DI container available with all the necessary configurations.

Then it retrieves the PDO instance, gathers the phinx configurations and appends the database name and the PDO connection to the 'local' key before returning the phinx configuration array.

This file is invoked by the migration commands.

File: config/env/env.phinx.php

use Cake\Core\Configure;
use Slim\App;

/** @var App $app */
$app = require __DIR__ . '/../bootstrap.php';

Configure::write('App.namespace', 'App');

$container = $app->getContainer();
$pdo = $container->get(PDO::class);
$config = $container->get('settings');
$database = $config['db']['database'];

$phinxConfig = $config['phinx'];

$phinxConfig['environments']['local'] = [
    // Set database name
    'name' => $database,
    'connection' => $pdo,
];

return $phinxConfig;

Migration command setup

To execute the migration commands with easier aliases, they are added to the "scripts" section of the composer.json file.

The following scripts are used in combination with database migrations. Documentation on the generation of the SQL schema: Test Setup.

File: composer.json

{
  "scripts": {
    "migration:generate": [
      "phinx-migrations generate --overwrite -c config/env/env.phinx.php --ansi",
      "@schema:generate"
    ],
    "migrate:prod": "phinx migrate -c config/env/env.phinx.php --ansi -vvv",
    "migrate": [
      "@migrate:prod",
      "@schema:generate"
    ],
    "schema:generate": [
      "php bin/console.php DatabaseSqlSchemaGenerator generateMySqlSchema",
      "@add-migrations-to-git"
    ],
    "add-migrations-to-git": "git add resources/migrations/* && git add resources/schema/*",
    "seed:minimal": "php vendor/bin/phinx seed:run -c config/env/env.phinx.php -s ClientStatusSeeder -s UserRoleSeeder -s AdminUserSeeder",
    "seed": "php vendor/bin/phinx seed:run -c config/env/env.phinx.php"
  }
}

Usage

Changes to the database can be made directly in the local development database. When satisfied with the changes, a migration file can be created.

Creating a migration

The command to create a new migration based on the changes between the current database and the previous version of the database which is stored in the schema.php file is:

composer migration:generate

A new migration file will be created in the resources/migrations directory and the schema.php file will be updated with the new database schema or creates a new one if it doesn't exist yet.

A new entry is made in the phinx_migration_log table with the name of the migration indicating that the current database is already up-to-date and this migration won't have to be executed on that database when running the migrations in the future.

It will also automatically generate a new schema.sql file with the current database schema and add it to git.

Executing a migration

To update the current database to the latest version, there are two possible commands.

For a local development environment, where tests are run and git is used, the command will automatically update the schema.sql file after the migration is done.
This is for the tests to have the latest database schema without having to generate the schema.sql file manually.

composer migrate

When only the migration should be run without updating the schema.sql file, like it's the case in a production environment, the command is:

composer migrate:prod

During the migration, the files in resources/migrations are executed in the order they were created.

When a migration is executed, the phinx_migration_log table is updated so that this migration file is skipped in the future.

Cheatsheet — when to execute which command

After every database change, to be able to run tests with the current database, the schema.sql can be updated with:

composer schema:generate

When satisfied with the changes and before committing, a new migration file has to be created with:

composer migration:generate 

This will create the new migration, update schema.php and schema.sql which can be committed to the version control and deployed.

When deploying, after uploading all files, the migration on the server has to be executed with:

composer migrate:prod

If code is pulled from remote and other devs made database changes, the local dev database and schema.sql have to be updated with:

composer migrate

Limitations

The phinx-migrations-generator is an amazing tool, but it can't do everything.

Column renaming

When renaming a column, the migration generator sees a column that is gone and one that is new as it works by comparing the current database with the previous version.

The migration file has to be edited manually to prevent losing data upon executing the migration on a different database.

In the generated migration file, a column rename result in something like this:

<?php

use Phinx\Db\Adapter\MysqlAdapter;

class DbChange160867212671a2f06cfbf3 extends Phinx\Migration\AbstractMigration
{
    public function change()
    {
        $this->table('table_name', [ 
            // ... 
            ])
            ->addColumn('new_column_name', 'string', [
                'null' => true,
                'default' => null,
                // ...
            ])
            ->removeColumn('old_column_name')
            ->save();
    }
}

The functions ->addColumn() with the new column name and ->removeColumn() with the old name can simply be replaced with

    ->renameColumn('old_column_name', 'new_column_name');

Breaking changes in phinx upgrade

When upgrading phinx, there could be breaking changes that require manual intervention in the migration files.

For example, previously the key 'identity' accepted the value 'enable' but the most recent version requires either null or true.

If a migration fails after upgrading phinx, a close look at the error message has to be taken and the migration files updated accordingly with search and replace. But this should only rarely be required.

Seeding

Database seeding is the process of inserting initial or default data into a database.

The seeders are classes that extend the AbstractSeed class and are stored in the resources/seeds directory.

Seeder class

The following is a template of a seeder file to put in the resources/seeds directory.

The name of the seeder class should end with the word Seeder to make it clear what it is.

The getDependencies() function is optional and can be used to run and insert data from other seeders before the function run() is executed. This is needed if the data of the current seeder depends on the data of another seeder such as a foreign key.

By extending the AbstractSeed class, $this->table() is available and used to define the table in which the data should be inserted.
The insert() method prepares the data to insert and saveData() executes the query.

File: resources/seeds/TemplateSeeder.php

<?php

use Phinx\Seed\AbstractSeed;

class TemplateSeeder extends AbstractSeed
{

    /**
     * Retrieve the dependencies for this seeder.
     * The seeders returned by this function will be executed before this one.
     *
     * @return string[]
     */
    public function getDependencies(): array
    {
        return [
            // Strings with the names of the seeder classes that this seeder depends on
            'OtherSeeder',
        ];
    }

    /**
     * Run Method.
     *
     * Write your database seeder using this method.
     *
     * More information on writing seeders is available here:
     * https://book.cakephp.org/phinx/0/en/seeding.html
     */
    public function run(): void
    {
        $rows = [
            [
                // 'field' => 'value',
            ],
        ];

        $table = $this->table('table_name');
        $table->insert($rows)->saveData();
    }
}

A new empty seeder file can also be created via this command:

php vendor/bin/phinx seed:create -c config/env/env.phinx.php NameOfTheSeeder

This will create a new seeder class in the resources/seeds directory containing only the run method.

Running all seeders

To run all seeders, the following command is used:

php vendor/bin/phinx seed:run -c config/env/env.phinx.php

Or the composer script:

composer seed

Running a specific seeder

To run a specific seeder, the command is the same as above but with -s and the name of the seeder class. To insert more than one seeder, the -s option can be repeated separated by a space. This will run the seeders in the order they are specified.

php vendor/bin/phinx seed:run -c config/env/env.phinx.php -s NameOfTheSeeder -s SecondSeeder