CakePHP 3 Database Migrations

Written by James McDonald

December 17, 2017

You have a database with a heap of tables already running.

You want to start from that base and then be able to migrate from that running database through the addition of new fields and then migrate those changes to your production database

Here is how I believe you do it with CakePHP 3.x

Firstly so I don’t have old migration cruft I delete the current migrations and drop the phinxlog table

rm config/Migrations/*

In MYSQL delete the phinxlog table which gets rid of old migration cruft

# in mysql 
DROP TABLE `clamDev`.`phinxlog`; 

Next you want to create a base migration snapshot. This captures your database as it is in the beginning

bin/cake bake migration_snapshot Base
# output
Creating file /Users/jmcd/sites/clam/config/Migrations/20171216230425_Base.php
Wrote`/Users/jmcd/sites/clam/config/Migrations/20171216230425_Base.php`
Marking the migration 20171216230425_Base as migrated...

The Base migration image is a PHP description of the entire database as at this link 20171216230425_Base

The next step is using Mysql client or Mysql Workbench (or whatever you use to dev your database) to add new tables and columns to your database which is not shown here.

Once you have made your database modifications. You then need to create a diff. A diff describes only the changes you have made since your last migration_shapshot.  You create a diff as follows


bin/cake bake migration_diff AddAuxilarySchoolToAssigned

# you will see out put similar to the following as the changes are capture by migration_diff

Creating file /Users/jmcd/sites/clam/config/Migrations/20171216232044_AddAuxilarySchoolToAssigned.php
Wrote `/Users/jmcd/sites/clam/config/Migrations/20171216232044_AddAuxilarySchoolToAssigned.php`
Marking the migration 20171216232044_AddAuxilarySchoolToAssigned as migrated...
using migration paths 
- /Users/jmcd/sites/clam/config/Migrations
using seed paths 
- /Users/jmcd/sites/clam/config/Seeds
Migration `20171216232044` successfully marked migrated !
Creating a dump of the new database state...
using migration paths 
- /Users/jmcd/sites/clam/config/Migrations
using seed paths 
- /Users/jmcd/sites/clam/config/Seeds
Writing dump file `/Users/jmcd/sites/clam/config/Migrations/schema-dump-default.lock`...
Dump file `/Users/jmcd/sites/clam/config/Migrations/schema-dump-default.lock` was successfully written

Here is an example of the migration_diff created

<?php
use Migrations\AbstractMigration;

class AddAuxilarySchoolToAssigned extends AbstractMigration
{

public function up()
{

$this->table('assigned')
->addColumn('aux_person_id', 'integer', [
'after' => 'assistant_id',
'default' => null,
'length' => 11,
'null' => true,
])
->addColumn('aux_assistant_id', 'integer', [
'after' => 'aux_person_id',
'default' => null,
'length' => 11,
'null' => true,
])
->update();
}

public function down()
{

$this->table('assigned')
->removeColumn('aux_person_id')
->removeColumn('aux_assistant_id')
->update();
}
}

You will be able to see the migration status by running the following

bin/cake migrations status
using migration paths 
 - /Users/jmcd/sites/clam/config/Migrations
using seed paths 
 - /Users/jmcd/sites/clam/config/Seeds
using environment default

 Status  Migration ID    Migration Name 
-----------------------------------------
     up  20171216230425  Base                        
     up  20171216232044  AddAuxilarySchoolToAssigned 

The above shows that you have two stages of changes and both have been applied

To revert the changes of the last diff

bin/cake migrations rollback

 

Be careful with rollbacks: If you rollback too many times it will delete all your tables (e.g. the database will be in the state before the Base migration which is no tables)

To specifically choose the migration to apply use the -t timestamp option

bin/cake migrations migrate -t 20171216232044

 

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

You May Also Like…