How to keep your database under version control?

I've been looking for a solution to this problem from quite a few days now, and I did find it quite interesting that there's no standard way to do this. So I decided to ask the CakePHP community, while there are many tools available to do the things, but I'm going to share the best solution of all, which is also Cake based. CakePHP has something known as Schema shell, which helps you solving the problem in efficient way.

Let me describe the problem first briefly. There was this comic I saw few days back (can't find it right now), which inspired me to solve this problem. Here goes the text from that cartoon:

Alex: Ok, we're ready, let's sync our work on this project today to show to client.

Rob: Ok sure, let me see the database changes and put it to server.

Alex: Oh yea, let me do it too.

Rob: Hey, I made this change, is it yours? And what about this? Where are you using it?

Alex: Yes-No-Yes-….

Boss: And we're screwed.

That was happening all the time with our team too, before I found this way.

Here's how it works:

1. Setup

Assuming that you already have a working project to implement db versioning on, start command line console from your 'app' directory. And then run 'cake schema help' to make sure you have the shell, or for necessary instructions.

2. Generate first Schema

After you see it working fine, lets output our whole DB structure into a dump file, it's not SQL dump file, its schema file which is in Cake-friendly format. You can play with other commands like 'schema view' to make sure things works. Ok, let's generate our schema with 'cake schema generate'.

Ok, schema.php generated, it would be inside your app/config/sql

Here's how the file will look:

Looks good (with the sample db). Now as you have a file for the whole database, you can put it in a repository under version control along with your project.

3. Update Schema

Since, there will be changes in database throughout the development of the project, and they will be done by many other developers. We'll need to keep a track of it, to do so, we'll make sure schema files remains updated always – this is something you'll have to instruct your team about.

Let's make a sample change and see how it goes. In my users table, I am going to add a new field called 'password' and then regenerate my schema file.

ALTER TABLE `users` ADD `password` VARCHAR( 20 ) NOT NULL ;

Lets regenerate schema now:

CakePHP's Schema shell had detected the change automatically, and prompted me if I want to over-write the current schema or snapshot it (create a new one). In my case, I want to overwrite it as I already have it in version control. Let's see what new schema looks like:

You see the 'password' field was reflected here as well. Now you can keep your whole DB structure under this schema file with version controlling. However, we've yet to sync the database across different machines. Let's say after updating (svn up) on my local setup, I got a new schema which I would like to implement on my database. Here's how you'll do that in next step.

4. Syncing

Let's assume the other user has added a field 'address' in his database, and regenerated the schema. Now I want same change to reflect on my local database. Here's new updated schema looks like (it's only a part of the schema.php file):

Now there are normally 2 options to update your database:

• you drop all tables and then import fresh DB structure from schema.
• Just update (We need this one)

Here's what you'll do:

And done, our database is now synced. You can run the same process across different machines to never worry about manual syncing. [Please note by 'sync' I mean only the structure not the records.]

Hope you enjoyed it!

Abhimanyu Grover