Simple migrations with PHPixie Migrate

The launch of the console extension a few weeks ago made it possible to significantly expand the range of tasks solved using PHPixie and its components. And now I’m happy to introduce you to PHPixie Migrate, a database migration utility. Like other components, it can work completely independently, and in the end I will give an example of how to run it without a framework.


Configuration

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

 <?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 seed for the same database.

Create and delete database

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

 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

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

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

SQL migration

Everything is simple, it's just a SQL file in which individual expressions are separated by a separator "- statement", 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 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 short descriptions in migration names and not just numbers. Since the natsort order is used, you can safely write a comment after the _ sign, for example 33_fairies_table.sql

Here you should immediately answer 2 questions:

Why not down migration for rollback:

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

Why changes are made by raw SQL queries and not by generic methods of type createTable ()?

The problem of universal methods is that they miss the subtleties in the differences between different bases, and many things have to be guessed. In addition, the option is possible when the libraries update and start creating fields and tables a little differently, and then the production base with the old migrations will be different from the new one where the same migrations were started a few months later. Plus, there are already so many graphical utilities for creating SQL for tables and converting from one database to another, that doing helper methods for this seems rather superfluous.

Seeds

Seeds are data that can fill the base. For example, it may be some default users, product categories, etc., you can also use them 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 manually using the database connection:

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

To insert seed, 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 there is already data in the table, this will lead to an error. In order to clear the table before insertion, you can use the --truncate parameter.

It is obvious that for the same connection to the database, you can set several profiles of the seed in the configuration file.

Use without framework

Like all other components of PHPixie Migrate, you can use it separately from the framework, 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 some 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.