Migrations are a wonderful technique to keep your database in sync with your code. When working on a project as a team, migrations can save your life. Please join me and get a quick introduction to migrations in cakephp and become a happier coder. :]

Yes, they are finally here! Migrations in cake! They are so powerful - especially in a team programming environment. However, what are they in the first place?

What Are Migrations In CakePHP?

Migrations are .yml files that contain information about how to construct a new SQL table and how to drop it. A typical migration file looks as follows:

[code]
UP:
create_table:
posts:
user_id:
type: integer
title:
type: text
length: 255
body:
type: text
highlighted:
type: boolean
default: false
flagged:
type: boolean
default: false
resolved:
type: boolean
default: false

DOWN:
drop_table: [posts]
[/code]

Notice the "UP" and "DOWN" in the code. They represent what to do when you want to migrate "up" to the next database scheme version and what to do when you want to go down. The code above does not need much explanation I think. When migrating up, we create a table named posts, that has six fields with some default, type and length restrictions. When migrating "down", we drop that table again.

How Does One Execute Migrations?

For this to work you will need PEAR installed along with its MDB2 Package. Installing pear is as easy as executing a bat file on windows. Too bad I am a WAMP guy and haven't done much yet with Linux. However, detailed instructions on how to install pear under linux can be found here.

The second step is altering your php.ini's include_path to look in your pear installation, too, or else it will not be able to find the MDB2 package. My include_path directive looks like the following:

[code]
; UNIX: "/path1:/path2"
;include_path = ".:/php/includes"
;
; Windows: "\path1;\path2"
include_path = ".;C:\wamp\php\pear;"
[/code]

Note, that "." stands for "current directory". On windows you separate directories with a semicolon and on *nix with a colon. Php.ini's include_path is a powerful thing, allowing you to customise your php installation to a great deal. However, don't mess with it. :]

Important: PHP's command line interface will grab the ini file that is in the same directory as your php.exe or php-cli.exe (respectively). Set that one up correctly, especially enable the mysql module. Without mysql support, migrations will be useless. :]

The next step is to grab Joel Moss' Migration Shell Code. Place it into app/vendors/shells/ and then start up your shell. Navigate to your project folder and then into /cake/console.

Now type in php cake.php migrate and it will migrate to the latest version. With php cake.php migrate -v 3 you will migrate to the third migration and so on.

Your command could look like "php5 cake.php migrate", too. On windows you can register any php.exe with your "Path" environment variable.

A Bit More On Migration Files

Okay, now that we got it working, here is a bit more about the construction of migration files.

First thing to note is that migrations can only keep your code in sync with the database scheme. To insert some default data into your tables you will have to rely on Cakephp's fixtures, which I will write about in a later article.

Your migration files have to be placed in app/config/migrations and they need to include some kind of number at the beginning of the file name. This is to express the version number of the migration. Here is a possible migration folder:

[code]
001_initial_tables.yml
002_network_table.yml
003_posts_table.yml
003_comments_table.yml
004_tags_table.yml
[/code]

You can also include more than one table setup within one migration. Here is an example migratiion file:

[code]
#
# migration YAML file
#
UP:
create_tables:
tip_categories:
name:
fkey: parent
tips:
title:
content:
type: text
fkey: category
DOWN:
drop_tables: [tip_categories, tips]
[/code]

Should be self-explaining. Instead of the "create_table:" command we use "create_tables:" and go through the scheme data one table at a time.

Conclusion

Migrations are extremely powerful. They will save you much time, energey and prevent you from getting hairloss. Having a working codebase with the correct information in the database is all happy programmer needs on his local test installation, no? :]

If you run into problems getting migrations working, please let me know by commenting on this article. I will try my best to help you out.

Have a good one! By the way, that migraine is actually inspiring me to blog. :)