How to migrate database using Phinx in PHP

  •  
  •  
  •  
  • 1
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
    1
    Share

Phinx is a standalone tool to manage database migrations while working on PHP. This is the official migration plugin available for CakePHP written by Rob Morgan.

Phinx allows developers to alter and manipulate databases in convenient way. It avoids the use of writing SQL by hand and instead offers a powerful API for creating migrations using PHP code. Developers can then version these migrations using their preferred SCM system. This makes Phinx migrations portable between different database systems. Phinx keeps track of which migrations have been run, so you can worry less about the state of your database and instead focus on building better software

Benefits

  • Framework independent
  • Simple install Process
  • Easy to use
  • Can be integrated with other PHP tools

Prerequisite

  • Composer – Project should have composer installed into it. So that we can use package manager. If it is not installed then you will need to install the composer first.

Installation

Phinx is very simple to install, you can install it using composer, a dependency manager used in PHP. Installing Phinx using composer is just one command away as follows

$ php composer.phar require robmorgan/phinx

Install Phinx

After installations you will need to initialize it, Initialization created directory structure ‘db/migrations’ inside the project folder.

$ php vendor/bin/phinx init

Phinx init

Sometimes, it doesn’t create the structure, in that case you can create it manually and need to give the proper permission to let the Phinx create migrations files into it.

You will also need to edit the phinx.yml to define the database connections. By default, development environment is selected but you can also use staging and Production environment for migration on these servers.

Here I will tell about default environment i.e., development.

Creating Migration file

Let’s start by creating migration file using create command

$ php vendor/bin/phinx create TestMigration

Create Phinx migration

The above command created a new file named YYYYMMDDHHMMSS_test_migration.php. It is very important to know that you have to create migration named in CamelCase e.g., TestMigration. Because migration file will have classes named as same as the migration name you will give. If you are not providing this in CamelCase, error will be displayed.

Phinx error

Once file is created, you can open file to see basic structure of the migration class.

<?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()
{
}
}
?>

Now, you can change the file according to your need but method remains same.

<?php
use Phinx\Migration\AbstractMigration;
class TestMigration extends AbstractMigration
{
public function change()
{
}
      public function up()
{
   $this->execute("CREATE TABLE Users (
        user_id INT NOT NULL AUTO_INCREMENT,
        email VARCHAR(80) NOT NULL,
        display_name VARCHAR(50) NOT NULL,
        password CHAR(41) NOT NULL,
        PRIMARY KEY (user_id),
        UNIQUE INDEX (email)
      ) ENGINE=INNODB;
    ");
}
       public function down() {
              // code to revert the up
      }
}
?>

You can perform database operations like Fetch the data, Insert data into tables, delete the data, create new table, working with foreign keys, indexes etc. Some of the revertible operations are as follows.

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

Running a migration script

While running migration script it first check the file names which have been already executed and only executes that has not been executed already. You need to run

$ php vendor/bin/phinx migrate

Phinx Migration

This will execute the up() or change() function to make the database changes.

There are more to cover about Phinx. This is just an overview that can be used in PHP projects.

Save

Save