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:
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:
; UNIX: "/path1:/path2"
;include_path = ".:/php/includes"
; Windows: "\path1;\path2"
include_path = ".;C:\wamp\php\pear;"
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:
You can also include more than one table setup within one migration. Here is an example migratiion file:
# migration YAML file
drop_tables: [tip_categories, tips]
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.
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. :)
You can skip to the end and add a comment.
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.
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 :]).
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.
PHP4 dying and Cake die.
Why do you want Cake to die?
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.
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.
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
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?
@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.
You really will, brian.
@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.
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.
Thanks Joel, I'll take a look at it and let you know if it clears things up for me.
Very interesting information! Thanks!
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 :(
Please show me the contents of your yml files.
I wrote a shell to generate yaml files easily: Yammy!
Check it out here:
lil something to get things started.... http://techcrate.wordpress.com/
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.
[...] 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 [...]