Migrations – the CakePHP way(?)

Published on and tagged with cakephp  console  database  shell

A quite popular feature in Rails are migrations, a kind of version control system for database schemes. Inspired by this feature, Joel Moss started a while ago the CakePHP migrations project to bring this feature to the CakePHP world.

In the meantime there is also a migrations-like feature in the Cake core, even though it seems to be a bit half-baked (or maybe I’m too stupid to understand it *g*).

Anyway, let’s have a look at it by using the traditional blog example.

First, we create a file which will contain the definitions for our schema. We name the file blog.php and place it in app/config/sql. In this file we create a class BlogSchema:

class BlogSchema extends CakeSchema {
}

Each property of a schema class represents a table, i.e. the property name is the table name, and the property value contains the table definition as an array. The definition for our posts table looks like:

class BlogSchema extends CakeSchema {
    public $posts = array(
        'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'key' => 'primary'),
        'title' => array('type' => 'string', 'null' => false, 'length' => 100),
        'content' => array('type' => 'text', 'null' => false),
        'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1))
    );
}

Now, we can switch to the command line and make a test run to verify the schema script will execute the correct SQL statements:

cd cake/console
./cake schema run create blog -dry

---------------------------------------------------------------
Cake Schema Shell
---------------------------------------------------------------
Performing a dry run.

The following tables will drop.
posts

Are you sure you want to drop the tables? (y/n)
[n] > y
Dropping tables.
Dry run for posts :
DROP TABLE IF EXISTS `posts`;

The following tables will create.
posts

Are you sure you want to create the tables? (y/n)
[y] > y
Creating tables.
Dry run for posts :
CREATE TABLE `posts` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `title` varchar(100) NOT NULL,
        `content` text NOT NULL,
        PRIMARY KEY  (`id`)
);

End create.

Everything looks fine, so we can run the same command again, this time without the -dry parameter, and it should create the posts table.

Ok, now let’s add a new column “created” to our schema:

class BlogSchema extends CakeSchema {
    public $posts = array(
        'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'key' => 'primary'),
        'title' => array('type' => 'string', 'null' => false, 'length' => 100),
        'content' => array('type' => 'text', 'null' => false),
        'created' => array('type' => 'datetime'),
        'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1))
    );
}

To introduce the new column we could use the command from above, which drops the table and recreates it. Or we could use the update command, which performs an “ALTER TABLE” statement to synchronize the posts table in the database with the posts table from the schema file:

./cake schema run update blog

---------------------------------------------------------------
Cake Schema Shell
---------------------------------------------------------------
Comparing Database to Schema...

The following statements will run.
ALTER TABLE `posts`
        ADD `created` datetime DEFAULT NULL;

Are you sure you want to alter the tables? (y/n)
[n] > y

Updating Database...
posts updated.
End update.

With that, our blog database is up-to-date.

Unfortunately, the update command doesn’t work if you add a new table to the schema file, it doesn’t create this new table in the database. And so I think the migration process is currently not that useful in practice…

I used a question mark in the title of this article, as I am not sure if what I described here is really the correct way to use the schema feature. Please correct me, if I am wrong :)

33 comments baked

  • Dardo

    I usually use them this way:

    1. Update the tables in SQL (I really like the shell).
    2. Create/Update models (This is what you are missing for the new added tables to work).
    3. Update schema using cake shell
    4. Commit to SCM (We use GIT ;-)
    5. Tell other developers: “Hey guys, checkout (pull) my branch and update your schemas (using cake shell).”
    6. Everybody is on sync and happy!

    This is the workflow we use and is working for us.

    P.S.: I found your blog very useful and visually appealing.

  • Kyle Slattery

    Daniel, thanks for this writeup about managing schemas in Cake. I’ve found this to be one of my issues with CakePHP, and I never really found any good documentation on it (but then again, I may just be looking in the wrong places).

  • Nik Chankov

    It will be very useful if the schema is generated via bake script first. because database with 100+ tables for example will take forever to create this class :)

    So far I am using good old phpMyAdmin where I wipe out all tables and import them all. I had few times where I need to update live database and then I just manually created the import sql and put it into a file.

    Anyway, it’s good to know about this feature.

    Cheers Daniel.

  • nate

    Hi Daniel, it looks like you’re really missing out on how the Cake schema tools were designed to be used. Dardo’s approach, noted above, is generally the correct way to go about things.

    The fact is, as opposed to being “half-baked,” Cake’s schema tools are actually superior to Rails migrations, in that they solve several problems which Rails migrations create, and they’re more efficient for larger teams. They allow you to edit your databases in whatever tool you choose, and they leverage existing version control systems to prevent conflicts when schemas are being edited by multiple people in parallel, which is a common problem with Rails migrations.

  • cakebaker

    @all: Thanks for your comments!

    @dardo: I’m a bit surprised that your approach works ;-) If I create a model for a table which doesn’t exist yet, and try to run the update script (step 5 in your approach), then the script fails with a “missing database table” error…

    @Kyle: I think there is no documentation except the help text you get when you call:

    ./cake schema help
    

    @Nik: For existing databases you can use the following command to generate the schema file:

    ./cake schema generate
    

    @nate: I can’t say whether Cake’s approach is superior to Rails’ migrations. But if I can’t figure out how to use it resp. if it doesn’t work, then it doesn’t matter that Cake’s concept may be superior…

  • Beth

    Hey Daniel,

    You’ve hit the nail on the head in regards to your comments to Nate.

    If the code matched the doc’s, everything would be perfect.

  • chanon

    It would be great if there was a complete tutorial/documentation about the correct workflow for this somewhere.
    Reading Dardo’s comment, I still don’t understand the correct steps (ok maybe I’m not so smart).

  • chanon

    It would be great if there was a complete tutorial/documentation about the correct workflow for this somewhere.
    Reading Dardo’s comment, I still don’t understand the correct steps (ok maybe I’m too stupid).

  • cakebaker

    @Beth: Yes, the documentation for this feature could be better :)

    @chanon: You are not alone ;-) And yes, it would be cool to have the correct workflow documented.

  • Daniel

    Hey Daniel and “Bakers”,
    well I while ago (November 2007) I’ve developed a shell that autogenerates (in yaml format) the DB schemas on the fly, ready to be used by the migrations a-la-Joel-Moss.
    If you are interested you can download the shell and discover more in this article: YAMMY! DB to Yaml shell: migrations made easy in CakePHP

  • cakebaker

    @Daniel: Thanks for the link! Isn’t your script included in the CakePHP migrations, is it?

  • CakeSchema « वीर
  • nate

    @cakebaker, @beth, @chanon: Have any of you ever tried typing “cake schema create”? It’s not exactly rocket science.

    @cakebaker: I’m not asking you if it’s superior, I’m telling you.

  • nate

    Oops, should have been “cake schema help”.

  • cakebaker

    @nate: I know that command, I used it while writing this article…

  • TommyO

    My two cents (for what it’s worth, which is just a little shy of two cents):

    CakePHP is an open-source, community-driven project. To make a blog post about how to use a feature precludes one from complaining about a lack of documentation.

    If the amount of energy used to write an article in an arbitrary location was instead used to submit the exact same information to the documentation efforts, then the complaints about ‘lack of documentation’ would simply go away. The cry for documentation would be heard by exactly one person and then never heard again.

  • cakebaker

    @TommyO: In principle, you are right. But personally, I decided consciously against supporting the documentation efforts for several reasons. And in the end, I think it doesn’t matter, where the documentation is located, as with search engines it can be found anywhere.

  • Nater kane

    I seem to be getting a `Class ‘String’ not found in path\cake\libs\set.php on line 375` error when i run `schema generate` just wondering if anyone else has run into it. will post a ticket if i can.

  • cakebaker

    @Nater: Did you try it with the latest version from the repository?

  • Ziad

    I’d really like to use the schema tool as this seems like a very good way of managing database changes and for what its worth, I like the sound of the schema way of doing it as oppose to the migrations way of doing it because it works with your source control tool.

    I am having a problem using the schema tool though. I keep getting this error when trying to run a cake schema update:

    Comparing Database to Schema…
    PHP Warning: array_diff_assoc(): Argument #2 is not an array in /home/ziad/public_html/newmts/cake/libs/model/schema.php on line 396

    Any ideas? I am using postgres 8.2, is that the problem? Are there known issues with postgres?

    I did google around for answers but nothing much came up.

  • cakebaker

    @Ziad: I guess it is a bug, either in the postgres support of cake or in the schema tool. Did you try it with a MySQL database?

  • Ziad

    @cakebaker: Thanks for your suggestion. I tried it in MySQL. I used ‘mysqli’ driver first and got pretty much the same result. Then I tried ‘mysql’ driver and it worked!

    Then I did some digging around in the code and found that the problem was that the postgres and mysqli drivers don’t override the alterSchema method which is responsible for generating the SQL for altering the schema.

    Maybe I’ll do it, its quite a simple function in the mysql driver so shouldn’t be too difficult to do but for now I’m glad I got to the bottom of that one!

  • cakebaker

    @Ziad: I’m glad I could help. Using the non-MySQL drivers is always a bit risky as those drivers are not used that often and so probably contain more bugs than their MySQL counter part.

  • Aejaz

    Hi guys,

    nice tutorial for cake migrations but is there any way that i can insert some sample data into database with tables using the schema i tried a lot inserting queries in schema.php but no use its creating a table of that query.

    i need some default data to be inserted into database when i create the tables using schema.

    like in users table it create with name username and passwords as fields and as well as i want to insert data into the table

    username password

    xyz 123456
    abc xyz123

    like this?how can i do it using schema i know how to perform the above action using YAML file i have done but i want to do it using schema file?how

    pls help? i think this is the feature which is lacking in shell??

  • cakebaker

    @Aejaz: Thanks for your comment!

    As far as I understand the schema script it is only for managing schema changes, and so I think that what you try to accomplish is not supported by the schema script…

  • atomicguava

    I got all excited when I read this post about migrating schemas. I followed the instructions to the letter and it works fine on my development server (where I generated the schema.php file).

    However, when I add the schema.php to my GIT repository and push to my live server, I get the message: “Error: schema.php could not be loaded”.

    Exploring the problem further, I tried to generate a schema.php file on my live server – this worked fine.

    Comparing the two generated files, I think it’s got something to do with the way schema.php is generated – my local schema.php has the class name of ‘TestAppSchema’ whereas the one generated on my live server is ‘HttpdocsSchema’. Clearly, it’s using the name of the App root folder as the class so my question is:

    How can I get the class to be consistent so that the ‘cake schema run update’ command works in different environments?

  • cakebaker

    @atomicguava: Thanks for your comment!

    You can provide a schema name when you run ‘schema run’: ‘cake schema run update your_schema’

    Hope that helps!

  • atomicguava

    @cakebaker: Thanks for the advice, this got me over one hurdle – sadly I have another!

    When I put the site live, I manually added the SQL data into the database from a PHPMyAdmin dump file.

    Now, if I do a schema update, it seems to want to change every column in every database – to an identical field name and type.

    Update ends like this:
    Updating Database…
    activities: 1060: Duplicate column name ‘id’


    activities: 1060: Duplicate column name ‘id’

    (There is one of these ‘duplicate column’ messages for each DB table)

    The question is – should I have used the ‘schema create’ command to put the DB tables onto my production server? If so, that’s all well and good, but I’d still need to have a patch file populate with the initial row data – unless schema can also move rows from tables?

  • cakebaker

    @atomicguava: I’m sorry, but I don’t have an idea why you get those duplicate column errors. Maybe you will have more luck in the IRC channel or the Google Group…

    I’m not sure what you mean with “unless schema can also move rows from tables”, though as far as I know (I don’t use the schema script myself) the schema tool is only about structure and not about data. Maybe you can use the “after”-callback method for this purpose?

  • Don Kirkby

    The CakePHP documentation now includes a basic description of the schema shell:
    http://book.cakephp.org/view/734/Schema-management-and-migrations

    I haven’t decided yet whether to use schema shell or migrations. There’s also a descendant of Joel Moss’s migrations that includes YAML generation:
    http://bakery.cakephp.org/articles/view/cakephp-yaml-migrations-and-fixtures-without-pear

  • cakebaker

    @Don: Thanks for the links!

  • emilk2000

    as mark story says: Unfortunately, the database migrations features offered by schema shell are not very feature complete. There are however, more feature complete migration plugins available.

    source: http://cakephp.lighthouseapp.com/projects/42648/tickets/1699-errors-with-schema-update-s

  • Varun Kapoor

    @cakebaker: so did you found any solution to your problem. Right now i am in same fix and looking for help.

Bake a comment




(for code please use <code>...</code> [no escaping necessary])

© daniel hofstetter. Licensed under a Creative Commons License