Database Migrations

Package git repository

https://github.com/phalcon/migrations

Requirements

• PHP >= 8.2
• The PDO extension and the PDO driver for your database: pdo_mysql, pdo_pgsql or pdo_sqlite
• The yaml extension, only if you use a YAML configuration file
• The posix extension (optional, Linux and macOS). Without it, the output has no colors.

Supported databases

The adapter value is not case-sensitive.

Installing via Composer

composer require --dev phalcon/migrations

Using the PHAR

Each release on GitHub has a phalcon-migrations.phar file. Download it from the releases page. Run it with PHP:

php phalcon-migrations.phar list

The PHAR accepts the same arguments and options as vendor/bin/phalcon-migrations.

Quick start

What you need for a quick start:

• Configuration file in the root of your project (you can also pass them as parameters inside CLI environment)
• Create database table structure
• Execute command to generate migrations

After that, you can execute that migration (run) in another environment to create the same DB structure.

Create the configuration file

The configuration file is a PHP file that returns an array. You can use any file name, for example migrations.php.

<?php

return [
    'database'    => [
        'adapter'  => 'mysql',
        'host'     => '127.0.0.1',
        'port'     => 3306,
        'username' => 'root',
        'password' => '',
        'dbname'   => 'db-name',
    ],
    'application' => [
        'logInDb'              => true,
        'migrationsDir'        => 'db/migrations',
        // true - TIMESTAMP, false - versions
        'migrationsTsBased'    => true,
        'exportDataFromTables' => [
            // Tables names
        ],
    ],
];

For all the configuration keys, see Configuration.

Generate migrations

Basic generation

vendor/bin/phalcon-migrations generate

Generate a specific table and export data from it

vendor/bin/phalcon-migrations generate \
    --config=migrations.php \
    --table=users \
    --data=oncreate

The --table option accepts:

• One table name: --table=users
• A comma-separated list of table names: --table=users,roles
• A table prefix with an asterisk: --table=user*

For the data options, see Export data.

Run migrations

vendor/bin/phalcon-migrations run

Roll back migrations

To roll back, use run with --version. Set --version to a version that is earlier than the current version:

vendor/bin/phalcon-migrations run --version=1.0.1

The package goes back one version at a time. For each table, it calls the down() and afterDown() methods of the version that it removes. Then it calls the morph() method of the previous version. See Migration methods.

List existing migrations

vendor/bin/phalcon-migrations list

The command shows each version and if it was applied:

┌───────────────────────────┬──────────────┐
│ Version                   │  Was applied │
├───────────────────────────┼──────────────┤
│ 1.0.0                     │            Y │
│ 1.0.1                     │            N │
└───────────────────────────┴──────────────┘

Configuration

The configuration has two sections: database and application.

Database options

Application options

Other configuration formats

The package also reads .ini, .json, .yaml and .yml files. These files use the same database and application sections. A YAML file needs the yaml PHP extension.

[database]
adapter  = mysql
host     = 127.0.0.1
username = root
password =
dbname   = db-name

[application]
logInDb              = true
migrationsDir        = db/migrations
migrationsTsBased    = true
exportDataFromTables = users,roles

Project directory

The project directory is the value of the --directory option. The default is the current directory.

These paths are relative to the project directory:

• The --config file
• A relative --migrations or migrationsDir directory

Configuration file discovery

If you do not use --config, the package looks for a configuration file in the project directory. It looks in app/config/ first, then in config/. In each directory, it looks for these files, in this order:

1. config.ini
2. config.php
3. config.json
4. config.yaml
5. config.yml

The package uses the first file that it finds. If it finds no file, the command stops with an error.

Default migrations directory

If you do not set --migrations or migrationsDir, the package uses the first of these directories that applies:

1. app/migrations, if the app directory exists
2. apps/migrations, if the apps directory exists
3. migrations

If you set more than one migrations directory, generate asks which directory to use.

Migrations log

The package records each version that it applies. It uses the log to find the versions to run, and to show the status in list.

• File (default). The log is the .phalcon/migration-version file in the project directory.
• Database. Set logInDb to true, or use the --log-in-db option. The log is the phalcon_migrations table. If the table does not exist, the package creates it. The table has the columns version, start_time and end_time.

Usage example

Run migrations from specific migrations directory

<?php

use Phalcon\Migrations\Migrations;
use Phalcon\Migrations\Utils\Config;

$config = Config::fromArray([
    'database' => [
        'adapter'  => 'mysql',
        'host'     => 'phalcon-db-mysql',
        'username' => 'root',
        'password' => 'root',
        'dbname'   => 'vokuro',
    ],
]);

Migrations::run([
    'directory'     => __DIR__,
    'migrationsDir' => [
        __DIR__ . '/migrations',
    ],
    'config'        => $config,
]);

The config value must be a Phalcon\Migrations\Utils\Config object. Use Config::fromArray() to create it from an array with the database and application sections. If you use a plain array, Migrations::run() throws an exception with the message Internal error. Config should be an instance of Phalcon\Migrations\Utils\Config.

Migrations::run() accepts these options:

Migration files

Each version is a directory in the migrations directory. The version directory has one PHP file for each table. Each file has one class, which extends Phalcon\Migrations\Mvc\Model\Migration.

The class name is the table name in CamelCase, then Migration_, then the version stamp:

• For an incremental version X.Y.Z, the stamp is X * 100 + Y * 10 + Z.
• For a timestamp based version, the stamp is the timestamp part of the name.

For example, the class for the users table in version 1.0.0 is UsersMigration_100.

A generated file is similar to this (shortened) example:

<?php

use Phalcon\Migrations\Db\Column;
use Phalcon\Migrations\Db\Index;
use Phalcon\Migrations\Db\Reference;
use Phalcon\Migrations\Mvc\Model\Migration;

/**
 * Class UsersMigration_100
 */
class UsersMigration_100 extends Migration
{
    /**
     * Define the table structure
     *
     * @return void
     * @throws Exception
     */
    public function morph(): void
    {
        $this->morphTable('users', [
            'columns' => [
                new Column(
                    'id',
                    [
                        'type' => Column::TYPE_INTEGER,
                        'unsigned' => true,
                        'notNull' => true,
                        'autoIncrement' => true,
                        'size' => 10,
                        'first' => true
                    ]
                ),
                new Column(
                    'email',
                    [
                        'type' => Column::TYPE_VARCHAR,
                        'notNull' => true,
                        'size' => 255,
                        'after' => 'id'
                    ]
                ),
            ],
            'indexes' => [
                new Index('PRIMARY', ['id'], 'PRIMARY'),
            ],
        ]);
    }

    /**
     * Run the migrations
     *
     * @return void
     */
    public function up(): void
    {
    }

    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down(): void
    {
    }
}

Migration methods

Each migration is a separate class that works as an entity for a specific database table. Inside each class, different methods can occur during migration running.

Each migration file (and class) can implement specific methods, that will be executed based on the operation requested. There are no restrictions on the logic encapsulated in each method.

The tables below show the Migration Class methods. They are stored by order of execution, earliest to latest.

Running to up

Running to down

Helper methods

The Phalcon\Migrations\Mvc\Model\Migration class has these methods, which you can use in your migration methods:

Export data

generate can export the data of a table. It writes the rows to a <table>.dat file (CSV format) in the version directory. The --data option controls how the migration imports the data:

The tables in exportDataFromTables (or --exportDataFromTables) get the same code as always.

CLI Arguments and options

Arguments

Options

Incremental versions

By default, generate creates incremental versions. The first version is 1.0.0. Each new version adds one to the last number: 1.0.1, 1.0.2 and so on.

To set the version name, use --version:

vendor/bin/phalcon-migrations generate --version=2.0.0

If the version already exists, the command stops with an error. To overwrite the files of the existing version, add --force.

Timestamp based migrations

Using this approach is useful when more than one developer is participating in the database structure management.

• To generate a timestamp based version, set a description with the --descr option, or with descr in the config file.
• To run or list timestamp based versions, use 'migrationsTsBased' => true in the config file, or the --ts-based option.

The description can contain only lowercase letters, digits and underscores (_). If it has other characters, the command stops with an error. For example:

vendor/bin/phalcon-migrations generate --descr=create_users

The command creates a version directory with a name like this:

* 1582539287636860_create_users

The name is the current Unix time in microseconds, then _, then the description.

Migrations will be executed from oldest to newest.

Upgrade from 3.x to 4.0

Version 4.0.0 removes the dependency on the Phalcon extension. To upgrade:

1. Use PHP 8.2 or later.
2. Change your PHP configuration file to return an array. See Create the configuration file.
3. If you call Migrations::run() in your code, use Config::fromArray() for the config option. See Usage example.
4. Update your migration files with the migrate-files command.

First, see the files that the command changes:

vendor/bin/phalcon-migrations migrate-files --migrations=db/migrations --dry-run

Then change the files:

vendor/bin/phalcon-migrations migrate-files --migrations=db/migrations

The --migrations option is required. The path is relative to the current directory. The command reads all the .php files in the directory and its subdirectories, and replaces these lines:

Migration files that you do not update continue to work if the Phalcon\Db classes are available, for example from the Phalcon extension. The package converts the Phalcon\Db\Column, Phalcon\Db\Index and Phalcon\Db\Reference objects when the migration runs.