database-migrations.md

Database Migrations

Table of Contents

1. Introduction
2. Creating Migrations
3. Migrator
4. Running Migrations
5. Logging Migrations
6. Rolling Back Migrations
7. Fixing Migrations in 1.1

Introduction

As your product changes, chances are you'll need to make changes to the structure of your database tables. Migrations give you the ability to make these changes atomically, and roll them back in case of an issue.

Creating Migrations

Migrations implement Opulence\Databases\Migrations\IMigration (Migration comes built-in). It defines two methods:

• down()
  • Performs a rollback of the migration
• up()
  • Runs the migration

To create a migration, you can run

php apex make:migration MIGRATION_NAME

This will generate a file in src/Infrastructure/Databases/Migrations. For example, running

php apex make:migration AddUserTable

will generate a class like the following:

class AddUserTable extends Migration
{
    public static function getCreationDate() : DateTime
    {
        return DateTime::createFromFormat(DateTime::ATOM, '2017-01-01T12:00:00+00:00');
    }

    public function down() : void
    {
        // 
    }

    public function up() : void
    {
        // 
    }
}

Note: getCreationDate() is used to arrange the migrations in the order they'll be run.

$this->connection contains an instance of Opulence\Databases\IConnection. You can use this to execute your migration:

public function down() : void
{
    $sql = 'DROP TABLE IF EXISTS users';
    $statement = $this->connection->prepare($sql);
    $statement->execute();
}

public function up() : void
{
    $sql = 'CREATE TABLE users (id serial primary key, email text)';
    $statement = $this->connection->prepare($sql);
    $statement->execute();
}

Note: There is no need to create database transactions in your migrations - one is created automatically by the migrator.

Migrator

Migrations are discovered, run, and rolled back by an instance of Opulence\Databases\Migrations\IMigrator (Migrator comes built-in). Migration classes are discovered by FileMigrationFinder, which recursively finds all classes that implement IMigration in a particular path or paths. Those migrations are ordered by their creation dates.

If you're using the skeleton project, you can configure the path to search for migration classes in config/paths.php (defaults to src/Infrastructure/Databases/Migrations).

Running Migrations

To run migrations, call

php apex migrations:up

This will call the up() methods on any un-executed migrations. If you'd like to run them outside of the console, you can call IMigrator::runMigrations().

Logging Migrations

Once you run a migration, it is added to some sort of storage - typically a database. This is done via IExecutedMigrationRepository (SqlExecutedMigrationRepository comes built-in if you're using the skeleton project).

Note: Migrations are keyed by name in the database. If you ever want to re-namespace your migrations or change their class names, be sure to run a migration beforehand updating the names of the migrations in the database.

Rolling Back Migrations

Sometimes, migrations don't go quite as planned, and it'd be nice to roll them back. You can do so using

php apex migrations:down --number NUMBER_TO_ROLL_BACK

This will call all the down() methods in your executed migrations. If you'd like to roll back all your migrations, simply don't pass the --number option. If you'd like to roll back migrations outside of the console, you can call IMigrator::rollBackMigrations().

Using MySQL

The migrator will use the PostgreSQL database adapter by default. If you need MySQL configured, you need ensure that the DB_DRIVER environment variable is set correctly. If you're using opulence/project, then the relevant part setting is in config/environment/.env.app.php:

Environment::setVar('DB_DRIVER', \Opulence\Databases\Adapters\Pdo\MySql\Driver::class);

Fixing Migrations in 1.1

A bug in Opulence 1.1 (resolved in 1.1.9) caused migrations to be sorted by the date they were run. However, running multiple migrations at one time could cause identical timestamps for the migrations, leading to ambiguity in the migration order. To fix the issue, you can run the following command once:

php apex migrations:fix

Note: This is only necessary if you used database migrations between versions 1.1.0 and 1.1.8.