Writing Migrations

Phinx relies on migrations in order to transform your database. Each migrationis represented by a PHP class in a unique file. It is preferred that you writeyour migrations using the Phinx PHP API, but raw SQL is also supported.

Creating a New Migration

Generating a skeleton migration file

Let’s start by creating a new Phinx migration. Run Phinx using the createcommand:

$ php vendor/bin/phinx create MyNewMigration

This will create a new migration in the formatYYYYMMDDHHMMSS_my_new_migration.php, where the first 14 characters arereplaced with the current timestamp down to the second.

If you have specified multiple migration paths, you will be asked to selectwhich path to create the new migration in.

Phinx automatically creates a skeleton migration file with a single method:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Change Method.
     *
     * Write your reversible migrations using this method.
     *
     * More information on writing migrations is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-abstractmigration-class
     *
     * The following commands can be used in this method and Phinx will
     * automatically reverse them when rolling back:
     *
     *    createTable
     *    renameTable
     *    addColumn
     *    renameColumn
     *    addIndex
     *    addForeignKey
     *
     * Remember to call "create()" or "update()" and NOT "save()" when working
     * with the Table class.
     */
    public function change()
    {
    }
}

All Phinx migrations extend from the AbstractMigration class. This classprovides the necessary support to create your database migrations. Databasemigrations can transform your database in many ways, such as creating newtables, inserting rows, adding indexes and modifying columns.

The Change Method

Phinx 0.2.0 introduced a new feature called reversible migrations. This featurehas now become the default migration method. With reversible migrations, youonly need to define the change logic, and Phinx can figure out how to migratedown automatically for you. For example:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class CreateUserLoginsTable extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-change-method
     *
     * Uncomment this method if you would like to use it.
     */
    public function change()
    {
        // create the table
        $table = $this->table('user_logins');
        $table->addColumn('user_id', 'integer')
              ->addColumn('created', 'datetime')
              ->create();
    }
 
    /**
     * Migrate Up.
     */
    public function up()
    {
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

When executing this migration, Phinx will create the user_logins table onthe way up and automatically figure out how to drop the table on the way down.Please be aware that when a change method exists, Phinx will automaticallyignore the up and down methods. If you need to use these methods it isrecommended to create a separate migration file.

Note

When creating or updating tables inside a change() method you must usethe Table create() and update() methods. Phinx cannot automaticallydetermine whether a save() call is creating a new table or modifying anexisting one.

Phinx can only reverse the following commands:

• createTable
• renameTable
• addColumn
• renameColumn
• addIndex
• addForeignKey

If a command cannot be reversed then Phinx will throw aIrreversibleMigrationException exception when it’s migrating down.

The Up Method

The up method is automatically run by Phinx when you are migrating up and itdetects the given migration hasn’t been executed previously. You should use theup method to transform the database with your intended changes.

The Down Method

The down method is automatically run by Phinx when you are migrating down andit detects the given migration has been executed in the past. You should usethe down method to reverse/undo the transformations described in the up method.

Executing Queries

Queries can be executed with the execute() and query() methods. Theexecute() method returns the number of affected rows whereas thequery() method returns the result as aPDOStatement:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        // execute()
        $count = $this->execute('DELETE FROM users'); // returns the number of affected rows
 
        // query()
        $stmt = $this->query('SELECT * FROM users'); // returns PDOStatement
        $rows = $stmt->fetchAll(); // returns the result as an array
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

Note

These commands run using the PHP Data Objects (PDO) extension whichdefines a lightweight, consistent interface for accessing databasesin PHP. Always make sure your queries abide with PDOs before usingthe execute() command. This is especially important when usingDELIMITERs during insertion of stored procedures or triggers whichdon’t support DELIMITERs.

Warning

When using execute() or query() with a batch of queries, PDO doesn’tthrow an exception if there is an issue with one or more of the queriesin the batch.

As such, the entire batch is assumed to have passed without issue.

If Phinx was to iterate any potential result sets, looking to see if onehad an error, then Phinx would be denying access to all the results as thereis no facility in PDO to get a previous result setnextRowset() -but no previousSet()).

So, as a consequence, due to the design decision in PDO to not throwan exception for batched queries, Phinx is unable to provide the fullestsupport for error handling when batches of queries are supplied.

Fortunately though, all the features of PDO are available, so multiple batchescan be controlled within the migration by calling uponnextRowset()and examining errorInfo.

Fetching Rows

There are two methods available to fetch rows. The fetchRow() method willfetch a single row, whilst the fetchAll() method will return multiple rows.Both methods accept raw SQL as their only parameter:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        // fetch a user
        $row = $this->fetchRow('SELECT * FROM users');
 
        // fetch an array of messages
        $rows = $this->fetchAll('SELECT * FROM messages');
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

Inserting Data

Phinx makes it easy to insert data into your tables. Whilst this feature isintended for the seed feature, you are also free to use theinsert methods in your migrations:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class NewStatus extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        // inserting only one row
        $singleRow = [
            'id'    => 1,
            'name'  => 'In Progress'
        ];
 
        $table = $this->table('status');
        $table->insert($singleRow);
        $table->saveData();
 
        // inserting multiple rows
        $rows = [
            [
              'id'    => 2,
              'name'  => 'Stopped'
            ],
            [
              'id'    => 3,
              'name'  => 'Queued'
            ]
        ];
 
        // this is a handy shortcut
        $this->insert('status', $rows);
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
        $this->execute('DELETE FROM status');
    }
}

Note

You cannot use the insert methods inside a change() method. Please use theup() and down() methods.

Working With Tables

The Table Object

The Table object is one of the most useful APIs provided by Phinx. It allowsyou to easily manipulate database tables using PHP code. You can retrieve aninstance of the Table object by calling the table() method from withinyour database migration:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('tableName');
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

You can then manipulate this table using the methods provided by the Tableobject.

The Save Method

When working with the Table object, Phinx stores certain operations in apending changes cache.

When in doubt, it is recommended you call this method. It will commit anypending changes to the database.

Creating a Table

Creating a table is really easy using the Table object. Let’s create a table tostore a collection of users:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $users = $this->table('users');
        $users->addColumn('username', 'string', ['limit' => 20])
              ->addColumn('password', 'string', ['limit' => 40])
              ->addColumn('password_salt', 'string', ['limit' => 40])
              ->addColumn('email', 'string', ['limit' => 100])
              ->addColumn('first_name', 'string', ['limit' => 30])
              ->addColumn('last_name', 'string', ['limit' => 30])
              ->addColumn('created', 'datetime')
              ->addColumn('updated', 'datetime', ['null' => true])
              ->addIndex(['username', 'email'], ['unique' => true])
              ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

Columns are added using the addColumn() method. We create a unique indexfor both the username and email columns using the addIndex() method.Finally calling save() commits the changes to the database.

Note

Phinx automatically creates an auto-incrementing primary key column called id for everytable.

The id option sets the name of the automatically created identity field, while the primary_keyoption selects the field or fields used for primary key. id will always override the primary_keyoption unless it’s set to false. If you don’t need a primary key set id to false withoutspecifying a primary_key, and no primary key will be created.

To specify an alternate primary key, you can specify the primary_key optionwhen accessing the Table object. Let’s disable the automatic id column andcreate a primary key using two columns instead:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('followers', ['id' => false, 'primary_key' => ['user_id', 'follower_id']]);
        $table->addColumn('user_id', 'integer')
              ->addColumn('follower_id', 'integer')
              ->addColumn('created', 'datetime')
              ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

Setting a single primary_key doesn’t enable the AUTO_INCREMENT option.To simply change the name of the primary key, we need to override the defaultid field name:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('followers', ['id' => 'user_id']);
        $table->addColumn('follower_id', 'integer')
              ->addColumn('created', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
              ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

In addition, the MySQL adapter supports following options:

By default the primary key is signed.To simply set it to unsigned just pass signed option with a falsevalue:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('followers', ['signed' => false]);
        $table->addColumn('follower_id', 'integer')
              ->addColumn('created', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
              ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

Valid Column Types

Column types are specified as strings and can be one of:

• biginteger
• binary
• boolean
• date
• datetime
• decimal
• float
• integer
• string
• text
• time
• timestamp
• uuid

In addition, the MySQL adapter supports enum, set, blob and jsoncolumn types. (json in MySQL 5.7 and above)

In addition, the Postgres adapter supports smallint, json, jsonb,uuid, cidr, inet and macaddr column types (PostgreSQL 9.3 andabove).

For valid options, see the ref:Valid Column Options below.

Determining Whether a Table Exists

You can determine whether or not a table exists by using the hasTable()method:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $exists = $this->hasTable('users');
        if ($exists) {
            // do something
        }
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

Dropping a Table

Tables can be dropped quite easily using the dropTable() method. It is agood idea to recreate the table again in the down() method:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $this->dropTable('users');
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
        $users = $this->table('users');
        $users->addColumn('username', 'string', ['limit' => 20])
              ->addColumn('password', 'string', ['limit' => 40])
              ->addColumn('password_salt', 'string', ['limit' => 40])
              ->addColumn('email', 'string', ['limit' => 100])
              ->addColumn('first_name', 'string', ['limit' => 30])
              ->addColumn('last_name', 'string', ['limit' => 30])
              ->addColumn('created', 'datetime')
              ->addColumn('updated', 'datetime', ['null' => true])
              ->addIndex(['username', 'email'], ['unique' => true])
              ->save();
    }
}

Renaming a Table

To rename a table access an instance of the Table object then call therename() method:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('users');
        $table->rename('legacy_users')
              ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
        $table = $this->table('legacy_users');
        $table->rename('users')
              ->save();
    }
}

Working With Columns

Valid Column Types

Column types are specified as strings and can be one of:

• biginteger
• binary
• boolean
• char
• date
• datetime
• decimal
• float
• integer
• string
• text
• time
• timestamp
• uuid

In addition, the MySQL adapter supports enum, set, blob and json column types. (json in MySQL 5.7 and above)

In addition, the Postgres adapter supports smallint, json, jsonb, uuid, cidr, inet and macaddr column types(PostgreSQL 9.3 and above).

Valid Column Options

The following are valid column options:

For any column type:

For decimal columns:

For enum and set columns:

For integer and biginteger columns:

For timestamp columns:

You can add created_at and updated_at timestamps to a table using theaddTimestamps() method. This method also allows you to supply alternativenames:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Change.
     */
    public function change()
    {
        // Override the 'updated_at' column name with 'amended_at'.
        $table = $this->table('users')->addTimestamps(null, 'amended_at')->create();
    }
}

For boolean columns:

For string and text columns:

For foreign key definitions:

You can pass one or more of these options to any column with the optionalthird argument array.

Limit Option and PostgreSQL

When using the PostgreSQL adapter, additional hinting of database column type can bemade for integer columns. Using limit with one the following options willmodify the column type accordingly:

use Phinx\Db\Adapter\PostgresAdapter;
 
//...
 
$table = $this->table('cart_items');
$table->addColumn('user_id', 'integer')
      ->addColumn('subtype_id', 'integer', ['limit' => PostgresAdapter::INT_SMALL])
      ->create();

Limit Option and MySQL

When using the MySQL adapter, additional hinting of database column type can bemade for integer, text and blob columns. Using limit withone the following options will modify the column type accordingly:

use Phinx\Db\Adapter\MysqlAdapter;
 
//...
 
$table = $this->table('cart_items');
$table->addColumn('user_id', 'integer')
      ->addColumn('product_id', 'integer', ['limit' => MysqlAdapter::INT_BIG])
      ->addColumn('subtype_id', 'integer', ['limit' => MysqlAdapter::INT_SMALL])
      ->addColumn('quantity', 'integer', ['limit' => MysqlAdapter::INT_TINY])
      ->create();

Get a column list

To retrieve all table columns, simply create a table object and call _getColumns()_method. This method will return an array of Column classes with basic info. Example below:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class ColumnListMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $columns = $this->table('users')->getColumns();
        ...
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
        ...
    }
}

Checking whether a column exists

You can check if a table already has a certain column by using thehasColumn() method:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Change Method.
     */
    public function change()
    {
        $table = $this->table('user');
        $column = $table->hasColumn('username');
 
        if ($column) {
            // do something
        }
 
    }
}

Renaming a Column

To rename a column, access an instance of the Table object then call therenameColumn() method:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('users');
        $table->renameColumn('bio', 'biography')
            ->update();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
        $table = $this->table('users');
        $table->renameColumn('biography', 'bio')
            ->update();
    }
}

Adding a Column After Another Column

When adding a column you can dictate its position using the after option:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Change Method.
     */
    public function change()
    {
        $table = $this->table('users');
        $table->addColumn('city', 'string', ['after' => 'email'])
            ->update();
    }
}

Dropping a Column

To drop a column, use the removeColumn() method:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate up.
     */
    public function up()
    {
        $table = $this->table('users');
        $table->removeColumn('short_name')
            ->save();
    }
}

Specifying a Column Limit

You can limit the maximum length of a column by using the limit option:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Change Method.
     */
    public function change()
    {
        $table = $this->table('tags');
        $table->addColumn('short_name', 'string', ['limit' => 30])
            ->update();
    }
}

Changing Column Attributes

To change column type or options on an existing column, use thechangeColumn() method. See Valid Column Types and Valid ColumnOptions for allowed values:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $users = $this->table('users');
        $users->changeColumn('email', 'string', ['limit' => 255])
            ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

Working With Indexes

To add an index to a table you can simply call the addIndex() method on thetable object:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('users');
        $table->addColumn('city', 'string')
            ->addIndex(['city'])
            ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

By default Phinx instructs the database adapter to create a normal index. Wecan pass an additional parameter unique to the addIndex() method tospecify a unique index. We can also explicitly specify a name for the indexusing the name parameter:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('users');
        $table->addColumn('email', 'string')
            ->addIndex(['email'], ['unique' => true, 'name' => 'idx_users_email'])
            ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

The MySQL adapter also supports fulltext indexes. If you are using a versionbefore 5.6 you must ensure the table uses the MyISAM engine:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    public function change()
    {
        $table = $this->table('users', ['engine' => 'MyISAM']);
        $table->addColumn('email', 'string')
            ->addIndex('email', ['type' => 'fulltext'])
            ->create();
    }
}

Removing indexes is as easy as calling the removeIndex() method. You mustcall this method for each index:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('users');
        $table->removeIndex(['email'])
            ->save();
 
        // alternatively, you can delete an index by its name, ie:
        $table->removeIndexByName('idx_users_email')
            ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

Working With Foreign Keys

Phinx has support for creating foreign key constraints on your database tables.Let’s add a foreign key to an example table:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('tags');
        $table->addColumn('tag_name', 'string')
            ->save();
 
        $refTable = $this->table('tag_relationships');
        $refTable->addColumn('tag_id', 'integer')
            ->addForeignKey('tag_id', 'tags', 'id', ['delete'=> 'SET_NULL', 'update'=> 'NO_ACTION'])
            ->save();
 
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

“On delete” and “On update” actions are defined with a ‘delete’ and ‘update’options array. Possibles values are ‘SET_NULL’, ‘NO_ACTION’, ‘CASCADE’ and‘RESTRICT’. Constraint name can be changed with the ‘constraint’ option.

It is also possible to pass addForeignKey() an array of columns. Thisallows us to establish a foreign key relationship to a table which usesa combined key:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('follower_events');
        $table->addColumn('user_id', 'integer')
            ->addColumn('follower_id', 'integer')
            ->addColumn('event_id', 'integer')
            ->addForeignKey(['user_id', 'follower_id'],
                'followers',
                ['user_id', 'follower_id'],
                ['delete'=> 'NO_ACTION', 'update'=> 'NO_ACTION', 'constraint' => 'user_follower_id'])
            ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

We can add named foreign keys using the constraint parameter. This featureis supported as of Phinx version 0.6.5:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('your_table');
        $table->addForeignKey('foreign_id', 'reference_table', ['id'],
                ['constraint'=>'your_foreign_key_name']);
            ->save();
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

We can also easily check if a foreign key exists:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('tag_relationships');
        $exists = $table->hasForeignKey('tag_id');
        if ($exists) {
            // do something
        }
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}

Finally, to delete a foreign key, use the dropForeignKey method:

<?php
 
use Phinx\Migration\AbstractMigration;
 
class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('tag_relationships');
        $table->dropForeignKey('tag_id');
    }
 
    /**
     * Migrate Down.
     */
    public function down()
    {
    }
}