Migrations are a CakePHP plugin that is used for quickly and easily building your database structure. It can also help version your Database so you can keep the Database up to date with the code you deploy.

You can easily define tables, columns, foregin keys etc. to build your database. Theres alot of options for comments, default values, limits etc.

The advantage is not only for local development, where it helps you build your environment incase you have to set it up, and reset it incase you goofed something up but also (and especially) for servers, where you can just run the migrations to have a ready environment to do whatever you want on.

And it helps with versioning.

Lets say the deployed application version on the server is 1.0.0, where theres a users table and a comments table. Now you build functionality with a new “posts” table.

Without migrations you'd have to manually create the posts table exactly like you did locally and then deploy the changes.

With migrations you can simply deploy the changes and run the migrations, which replicates the exact database you made locally.

We used this for solving a problem with ERV BE.

We had the policies table which had a Policy Number. We needed to extract the policy number to a separate table with foreign key to the policies table.

But since we needed to deploy this on the server and couldnt just manually add all the numbers after we deployed the new table (since the new table wouldnt be filled but all the remaining policies needed their numbers), we used migrations to write a script that adds all the policy numbers to their assigned number in the policy table, which resulted in us simply being able to deploy the change normally.

Foreign keys in Migrations can be rather icky, especially when setting the action on delete and on update.

You first need to say the table you want to add the foreign key to, then call addForeignKey() and pass the foreign key you want to make, including the column, referenced table, referenced column, delete action, update action and name.

Looks like this:

$this->table('accounts')
	->addForeignKey(
		$this->foreignKey('customer_id')
			->setReferencedTable('customers')
			->setReferencedColumns('id')
			->setDelete(ForeignKey::SET_NULL)
			->setUpdate(ForeignKey::SET_NULL)
			->setName('FK_accounts_customer_id')
	)
	->update();

Important here is the ForeignKey::SET_NULL , there are 2 different ForeignKey classes that contain different class constants. Its important to use Migrations\Db\Table\ForeignKey for migrations, else it will not recognise the option.