Simple migrations with PHPixie Migrate

Published on November 14, 2016

Simple migrations with PHPixie Migrate

  • Tutorial
imageThe launch of the console extension a few weeks ago allowed to expand the range of tasks solved with the help of PHPixie and its components. And now I am pleased to introduce you to PHPixie Migrate, a database migration utility. Like other components, it can work completely independently, and at the end of the article I will give an example of how to run it without a framework.

Upgrading existing projects
Если вы уже используете PHPixie то сделать апгрейд для использования миграций совсем просто.

1. Обновить конфиг соединения с БД (database.php)

PHPixie теперь поддерживает альтернативный синтаксис где вместо одной строки соединения используются дополнительные параметры, например:

Вместо старого:
return array(
    'default' => array(
        'connection' => 'mysql:dbname=phpixie',
        'user'     => 'phpixie',
        'password' => 'phpixie',
        'driver'   => 'pdo'
    )
);

теперь используется

return array(
    'default' => array(
        'database' => 'phpixie',
        'user'     => 'phpixie',
        'password' => 'phpixie',
        'adapter'  => 'mysql', // one of: mysql, pgsql, sqlite
        'driver'   => 'pdo'
    )
);

PHPixie и дальше будет поддерживать старую конфигурацию для запросов, но для работы Migrate важно чтобы конфиг был в новом виде.

2. Скопировать папку /assets/migrate и конфиг файл /assets/config/migrate.php со скелета в свой проект.

Вот и все.

Configuration

Consider the /assets/config/migrate.php config :

<?php
return array(
    // настройки миграций
    'migrations' => array(
        'default' => array(
            // имя соединения с database.php
            'connection' => 'default',
            // путь в котором хранятся миграции, относительно папки /assets/migrate/
            'path'       => 'migrations',
            // не обязательно:
            // имя таблицы в которой хранить миграции
            'migrationTable' => '__migrate',
            // имя поля в таблице миграций
            'lastMigrationField' => 'lastMigration'
        )
    ),
    // настройки сидирования (об этом позже)
    'seeds' => array(
        'default' => array(
            // имя соединения с database.php
            'connection' => 'default',
            // путь в котором хранятся сиды, относительно папки /assets/migrate/
            'path' => 'seeds'
        )
    )
);

Most likely you will not need to change anything in this config, unless you have two or more bases, or you need different seeds for the same database.

Creating and deleting a database

Now you can create and delete a database directly from the console, this is done by the new framework: database command :

framework:database ACTION [ CONFIG ]
Create or drop a database
Arguments:
ACTION    Either 'create' or 'drop'
CONFIG    Migration configuration name, defaults to 'default'

That is, the console framework: database create will check if the database exists, and if not, it will create it, and the console framework: database drop will delete it.

Migrations


Well, now about the most important thing. First, a short introduction for those who have not used anything like this yet.

Migrations make it possible to store changes in the structure of the database in code, which is much more convenient than transferring ready-made dumps and then manually changing the database on production. The principle of operation is simple: the name of the last migration is stored in the database and when the command is launched, all migrations will be applied that are "larger" in natsort () order , that is, if we have the files 1.sql, 2.sql ... 22.sql, and the last in the database 13.sql then everything from 14 to 22 will be executed, and then 22 will be saved in the database as the name of the latter. They can be in .sql or .php format.

SQL migration


Everything is simple here, it’s just an SQL file in which individual expressions are separated by a "- statement" separator, for example:

CREATE TABLE fairies(
    id int NOT NULL,
    name VARCHAR(255)
);
-- statement
CREATE TABLE flowers(
    id int NOT NULL,
    name VARCHAR(255)
);

Php migration


This is just a PHP file with the ability to execute queries and even access to PHPixie Database queries:

$this->execute("CREATE TABLE fairies(
    id int NOT NULL,
    name VARCHAR(255)
)");
$this->message("Какое-то сообщение в консоль");
// привычные запросы
$this->connection()->updateQuery()
    ->table('users')
    ->set(['role' => 'user'])
    ->execute();

By the way, I highly recommend writing a short description in the names of migrations and not just numbers. Since the natsort order is used, you can safely write comments after the _ sign , for example 33_fairies_table.sql

Here you should immediately answer 2 questions:

Why there are no down migrations for rollback:

If you think from the point of view of the database itself, then there is no such thing as a rollback. Rollback is just another migration forward that cancels what the previous ones did. In addition, such a rollback is not always even possible, since if you deleted the table in one migration, then the rollback could recreate it but certainly not restore the data.

Why are changes made by raw SQL queries and not by universal methods like createTable ()?

The problem with universal methods is that they miss out on the subtleties in the differences between different databases, and many things have to be guessed. In addition, the option is possible when the libraries are updated and begin to create fields and tables in a slightly different way, and then the production base with old migrations will be different from the new one where the same migrations were launched several months later. Plus, there are already so many graphical utilities for creating SQL for tables and converting from one database to another that making helper methods for this seems rather superfluous.

Sides


Sides are data that can fill the database. For example, it can be some default users, product categories, etc. They can also be used to fill the database with test data for functional tests. The file name must match the table name, the available formats are .php and .json. For example:

// /assets/migrate/seeds/fairies.php
<?php
return array(
    array(
        'id'   => 1,
        'name' => 'Pixie'
    ),
    array(
        'id'   => 2,
        'name' => 'Trixie'
    ),
);

// /assets/migrate/seeds/flowers.json
[
    {
        "id": 1,
        "name": "daisy"
    },
    {
        "id": 2,
        "name": "Rose"
    },
]

In the case of .php, in addition to returning the data array, it is also possible to do everything by hand using a database connection:

// /assets/migrate/seeds/fairies.php
<?php
$this->connection()->insertQuery()
    ->data([
        'id'   => 1,
        'name' => 'Pixie'
     ])
     ->execute();

To insert seeds, use the framework: seed command :

framework:seed [ --truncate ] [ CONFIG ]
Seed the database with data
Options:
truncate    Truncate the tables before inserting the data.
Arguments:
CONFIG    Seed configuration name, defaults to 'default'

If data already exists in the table, this will lead to an error. In order to clear the table before inserting, you can use the --truncate parameter .

Obviously, for the same database connection, you can specify several seed profiles in the configuration file.

Use without a framework


Like all other PHPixie Migrate components, you can use it separately from the framework, something like this:

$slice = new \PHPixie\Slice();
$database = new \PHPixie\Database($slice->arrayData(array(
    'default' => array(
        'database' => 'phpixie',
        'user'     => 'phpixie',
        'password' => 'phpixie',
        'adapter'  => 'mysql', // one of: mysql, pgsql, sqlite
        'driver'   => 'pdo'
    )
)));
$filesystem = new \PHPixie\Filesystem();
$migrate = new \PHPixie\Migrate(
    $filesystem->root(__DIR__.'/assets/migrate'),
    $database,
    $slice->arrayData(array(
    'migrations' => array(
        'default' => array(
            'connection' => 'default',
            'path'       => 'migrations',
        )
    ),
    'seeds' => array(
        'default' => array(
            'connection' => 'default',
            'path' => 'seeds'
        )
    )
)));
$cli = new \PHPixie\CLI();
$console = new \PHPixie\Console($slice, $cli, $migrate->consoleCommands());
$console->runCommand();

Here the command names will be run , seed , database without the framework prefix .

There are of course a few more features that I would like to add, but I hope the component will appeal to those users who have already been waiting for migrations for the 3rd PHPixie.