debuggable

 
Contact Us
 

How Table Migrations In CakePHP 1.2 Can Save Your Life

Posted on 2/7/07 by Tim Koschützki

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. :)

 
&nsbp;

You can skip to the end and add a comment.

[...] PHP-Coding-Practices.com, Tim Koschuetzki has posted a new tutorial that just might save your life - well, at least the life of your CakePHP application - using table [...]

Joel Moss said on Jul 04, 2007:

Tim, great little tutorial. Thanks for the mention.

I should tell you that if you want the latest and greatest version of Cake Migrations, then you should subscribe to my Switchboard at http://joelmoss.info. All new revisions are now posted in CakeForge at http://cakeforge.org/snippet/detail.php?type=snippet&id=165

Also, you mentioned fixtures, I also created a fixtures shell that works alongside migrations. It's a lot easier than Cake's built in fixtures. That can also be found at Cakeforge at http://cakeforge.org/snippet/detail.php?type=snippet&id=166

But again, thanks for the mention.

Tim Koschuetzki said on Jul 04, 2007:

Hey Joel!

Thanks for stopping by. :] Will definately subscribe to the switchboard.

My article on fixtures is planned for this weekend, so it would be cool to check out your latest revisions for the fixtures shell. Good times!

Do you have MSN/AIM/ICQ ? Would like to talk to you about a projects of ours (I just noticed you are working for the same company as I do :]).

Joel Moss said on Jul 04, 2007:

You can catch me on MSIM using my email: joel[at]joelmoss.info

I am planning on releasing a screencast on migrations and fixtures, so make sure you subscribe to my Switchboard.

Speak to ya soon.

php blog said on Jul 17, 2007:

PHP4 dying and Cake die.

Tim Koschuetzki said on Jul 18, 2007:

Why do you want Cake to die?

Sam Figueroa said on Jul 19, 2007:

Great to see this RubyOnRails feature come to Cake. I work with RoR at home but with CakePHP at work. So this will in the future keep me from rambling around that we should rather use RoR at least concerning migrations.

Tim Koschuetzki said on Jul 19, 2007:

Hehe, yeah!

There are many other cool features going to be ported over to Cake. Also the migration feature is going to get a lift up, enabling it to retrieve database migration data from remote systems. Pretty kewl.

Joel Moss said on Jul 19, 2007:

Yeah, but read my latest post and you might not like what is in store. then again, you might.... http://joelmoss.info/switchboard/blog/2562:Migrations_making_their_way_into_the_CakePHP_core

brian  said on Aug 22, 2007:

I'm a bit of a newbie and haven't worked with RoR at all. So what are the advantages to using migrations versus simply performing a SQL query to do the same thing?

Joel Moss said on Aug 22, 2007:

@Brian: it means you don't have to write any SQL at all, making it faster and easier to make changes to your DB. Its a version control system for your DB, so you can migrate up and down at any time. Just try it and you will love it.

Tim Koschuetzki said on Aug 22, 2007:

You really will, brian.

brian  said on Aug 22, 2007:

@Joel and Tim: ok...but I'm still not seeing how Table Migrations can be applied to simplify development. Can y'all highlight some examples where it simplifies development over using SQL?

And maybe also clarify what you mean when you say "migrate up or down at any time"

I'm sure there's a pearl here but I just see the oyster.

Joel Moss said on Aug 22, 2007:

The best thing would be for you to take a look at my screencast at http://joelmoss.info/switchboard/blog/2539:Screencast_How_to_use_CakePHP_Database_Migrations

That should help you understand it and see its benefits.

brian  said on Aug 22, 2007:

Thanks Joel, I'll take a look at it and let you know if it clears things up for me.

hiutopor said on Sep 17, 2007:

Hello

Very interesting information! Thanks!

G'night

roryy  said on Jan 03, 2008:

Hi i have a question about the migrate tool of Joel Moss.

When I want to migrate (example 001_users.yml first and then 002_users.yml)
then the script is migrating 001 succesfully but 002 gives a error: Table

`users` already exists. Does someone know how I can avoid that problem? Alter_table doesn't work :(

Tim Koschuetzki said on Jan 04, 2008:

Please show me the contents of your yml files.

Daniel Vecchiato said on Mar 07, 2008:

I wrote a shell to generate yaml files easily: Yammy!

Check it out here:

http://www.4webby.com/blog/posts/view/3/yammy_db_to_yaml_shell_migrations_made_easy_in_cakephp

Dan

forrestgump said on Sep 29, 2008:

lil something to get things started.... http://techcrate.wordpress.com/

mito said on Feb 12, 2009:

What about using PHP code in migrations? It is often very useful to execute custom logic over data that are used in migrations.

This post is too old. We do not allow comments here anymore in order to fight spam. If you have real feedback or questions for the post, please contact us.