Database Migrations

Migrations are a convenient way for you to alter your database in astructured and organized manner. You could edit fragments of SQL by handbut you would then be responsible for telling other developers that theyneed to go and run them. You would also have to keep track of which changesneed to be run against the production machines next time you deploy.

The database table migration tracks which migrations have already beenrun so all you have to do is make sure your migrations are in place andcall $migration->latest() to bring the database up to the most recentstate. You can also use $migration->setNamespace(null)->progess() toinclude migrations from all namespaces.

Migration file names

Each Migration is run in numeric order forward or backwards depending on themethod taken. Each migration is numbered using the timestamp when the migrationwas created, in YYYYMMDDHHIISS format (e.g. 20121031100537). Thishelps prevent numbering conflicts when working in a team environment.

Prefix your migration files with the migration number followed by an underscoreand a descriptive name for the migration. The year, month, and date can be separatedfrom each other by dashes, underscores, or not at all. For example:

  • 20121031100537_add_blog.php
  • 2012-10-31-100538_alter_blog_track_views.php
  • 2012_10_31_100539_alter_blog_add_translations.php

Create a Migration

This will be the first migration for a new site which has a blog. Allmigrations go in the app/Database/Migrations/ directory and have names suchas 20121031100537_add_blog.php.

  1. <?php namespace App\Database\Migrations;
  2.  
  3. class AddBlog extends \CodeIgniter\Database\Migration {
  4.  
  5.         public function up()
  6.         {
  7.                 $this->forge->addField([
  8.                         'blog_id'          => [
  9.                                 'type'           => 'INT',
  10.                                 'constraint'     => 5,
  11.                                 'unsigned'       => TRUE,
  12.                                 'auto_increment' => TRUE
  13.                         ],
  14.                         'blog_title'       => [
  15.                                 'type'           => 'VARCHAR',
  16.                                 'constraint'     => '100',
  17.                         ],
  18.                         'blog_description' => [
  19.                                 'type'           => 'TEXT',
  20.                                 'null'           => TRUE,
  21.                         ],
  22.                 ]);
  23.                 $this->forge->addKey('blog_id', TRUE);
  24.                 $this->forge->createTable('blog');
  25.         }
  26.  
  27.         public function down()
  28.         {
  29.                 $this->forge->dropTable('blog');
  30.         }
  31. }

The database connection and the database Forge class are both available to you through$this->db and $this->forge, respectively.

Alternatively, you can use a command-line call to generate a skeleton migration file. Seebelow for more details.

Foreign Keys

When your tables include Foreign Keys, migrations can often cause problems as you attempt to drop tables and columns.To temporarily bypass the foreign key checks while running migrations, use the disableForeignKeyChecks() andenableForeignKeyChecks() methods on the database connection.

  1. public function up()
  2. {
  3.     $this->db->disableForeignKeyChecks();
  4.  
  5.     // Migration rules would go here...
  6.  
  7.     $this->db->enableForeignKeyChecks();
  8. }

Database Groups

A migration will only be run against a single database group. If you have multiple groups defined inapp/Config/Database.php, then it will run against the $defaultGroup as specifiedin that same configuration file. There may be times when you need different schemas for differentdatabase groups. Perhaps you have one database that is used for all general site information, whileanother database is used for mission critical data. You can ensure that migrations are run onlyagainst the proper group by setting the $DBGroup property on your migration. This name mustmatch the name of the database group exactly:

  1. <?php namespace App\Database\Migrations;
  2.  
  3. class AddBlog extends \CodeIgniter\Database\Migration
  4. {
  5.     protected $DBGroup = 'alternate_db_group';
  6.  
  7.     public function up() { . . . }
  8.  
  9.     public function down() { . . . }
  10. }

Namespaces

The migration library can automatically scan all namespaces you have defined withinapp/Config/Autoload.php or loaded from an external source like Composer, usingthe $psr4 property for matching directory names. It will include all migrationsit finds in Database/Migrations.

Each namespace has it’s own version sequence, this will help you upgrade and downgrade each module (namespace) without affecting other namespaces.

For example, assume that we have the following namespaces defined in our Autoloadconfiguration file:

  1. $psr4 = [
  2.         'App'       => APPPATH,
  3.         'MyCompany' => ROOTPATH.'MyCompany'
  4. ];

This will look for any migrations located at both APPPATH/Database/Migrations andROOTPATH/MyCompany/Database/Migrations. This makes it simple to include migrations in yourre-usable, modular code suites.

Usage Example

In this example some simple code is placed in app/Controllers/Migrate.phpto update the schema:

  1. <?php namespace App\Controllers;
  2.  
  3.     class Migrate extends \CodeIgniter\Controller
  4.     {
  5.  
  6.             public function index()
  7.             {
  8.                     $migrate = \Config\Services::migrations();
  9.  
  10.                     try
  11.                     {
  12.                       $migrate->latest();
  13.                     }
  14.                     catch (\Exception $e)
  15.                     {
  16.                       // Do something with the error here...
  17.                     }
  18.             }
  19.  
  20.     }

Command-Line Tools

CodeIgniter ships with several commands that are available from the command line to helpyou work with migrations. These tools are not required to use migrations but might make things easier for those of youthat wish to use them. The tools primarily provide access to the same methods that are available within the MigrationRunner class.

migrate

Migrates a database group with all available migrations:

  1. > php spark migrate

You can use (migrate) with the following options:

  • (-g) to chose database group, otherwise default database group will be used.
  • (-n) to choose namespace, otherwise (App) namespace will be used.
  • (-all) to migrate all namespaces to the latest migration

This example will migrate Blog namespace with any new migrations on the test database group:

  1. > php spark migrate -g test -n Blog

When using the -all option, it will scan through all namespaces attempting to find any migrations that havenot been run. These will all be collected and then sorted as a group by date created. This should helpto minimize any potential conflicts between the main application and any modules.

rollback

Rolls back all migrations, taking the database group to a blank slate, effectively migration 0:

  1. > php spark migrate:rollback

You can use (rollback) with the following options:

  • (-g) to choose database group, otherwise default database group will be used.
  • (-b) to choose a batch: natural numbers specify the batch, negatives indicate a relative batch

refresh

Refreshes the database state by first rolling back all migrations, and then migrating all:

  1. > php spark migrate:refresh

You can use (refresh) with the following options:

  • (-g) to choose database group, otherwise default database group will be used.
  • (-n) to choose namespace, otherwise (App) namespace will be used.
  • (-all) to refresh all namespaces

status

Displays a list of all migrations and the date and time they ran, or ‘–’ if they have not been run:

  1. > php spark migrate:status
  2. Filename               Migrated On
  3. First_migration.php    2016-04-25 04:44:22

You can use (status) with the following options:

  • (-g) to choose database group, otherwise default database group will be used.

create

Creates a skeleton migration file in app/Database/Migrations.It automatically prepends the current timestamp. The class name itcreates is the Pascal case version of the filename.

> php spark migrate:create [filename]

You can use (create) with the following options:

  • (-n) to choose namespace, otherwise (App) namespace will be used.

Migration Preferences

The following is a table of all the config options for migrations, available in app/Config/Migrations.php.

PreferenceDefaultOptionsDescription
enabledTRUETRUE / FALSEEnable or disable migrations.
tablemigrationsNoneThe table name for storing the schema version number.
timestampFormatY-m-d-His_ The format to use for timestamps when creating a migration.

Class Reference

  • CodeIgniter\Database\MigrationRunner
    • findMigrations()

Returns:An array of migration filesReturn type:array

An array of migration filenames are returned that are found in the path property.

  • latest($group)

Parameters:

  1. - **$group** (_mixed_) – database group name, if null default database group will be used.Returns:

TRUE on success, FALSE on failureReturn type:bool

This locates migrations for a namespace (or all namespaces), determines which migrationshave not yet been run, and runs them in order of their version (namespaces intermingled).

  • regress($batch, $group)

Parameters:

  1. - **$batch** (_mixed_) – previous batch to migrate down to; 1+ specifies the batch, 0 reverts all, negative refers to the relative batch (e.g. -3 means “three batches back”)
  2. - **$group** (_mixed_) – database group name, if null default database group will be used.Returns:

TRUE on success, FALSE on failure or no migrations are foundReturn type:bool

Regress can be used to roll back changes to a previous state, batch by batch.

  1. $migration->batch(5);
  2. $migration->batch(-1);
  • force($path, $namespace, $group)

Parameters:

  1. - **$path** (_mixed_) – path to a valid migration file.
  2. - **$namespace** (_mixed_) – namespace of the provided migration.
  3. - **$group** (_mixed_) – database group name, if null default database group will be used.Returns:

TRUE on success, FALSE on failureReturn type:bool

This forces a single file to migrate regardless of order or batches. Method “up” or “down” is detected based on whether it has already been migrated. Note: This method is recommended only for testing and could cause data consistency issues.

  • setNamespace($namespace)

Parameters:

  1. - **$namespace** (_string_) – application namespace.Returns:

The current MigrationRunner instanceReturn type:CodeIgniterDatabaseMigrationRunner

Sets the path the library should look for migration files:

  1. $migration->setNamespace($path)
  2.           ->latest();
  • setGroup($group)

Parameters:

  1. - **$group** (_string_) – database group name.Returns:

The current MigrationRunner instanceReturn type:CodeIgniterDatabaseMigrationRunner

Sets the path the library should look for migration files:

  1. $migration->setNamespace($path)
  2.           ->latest();