PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

Database Version Control

Note: This article was originally published at Planet PHP on 11 January 2011.
Planet PHP

Version control for source code is used by most development teams today. It gives you a history of all the changes you made, you can use it to share your code with others and you can work on an experimental feature without polluting the stable product. These are just some of the reasons why version control software is considered indispensable today. But where does our database fit in all this? How can we share the changes we want to make to the database with our colleagues, and eventually with our production environment?

Database version control is something that most developers have to deal with regularly, yet only a few have actually thought about what solution might be best for them. Most people have a solution that sort of works for them, but when you ask them about the subject they are pretty convinced that there must be some better way to manage database changes, they're just not entirely sure what that solution is - but the silver bullet must be out there somewhere, right?

This article will give you a bit more insight into the theory behind database version control, and gives you several suggestions on how you could deal with the problem. We will look at simple solutions and also take a look at some of the tools available to help.

Writing your own script

A common starting point is to have a custom script to apply database patches (sometimes also called aodeltasa or aomigrationsa) to your database. This approach is widely used and works quite well for a lot of people. Most of the available tools are enhanced versions of this approach and as a result they are subject to the same limitations, as we will see later! The idea behind this approach is simple: we store patch files in a directory in our project. These patch files are checked into version control alongside our source code.

patch files directory

Each of these patch files contains some SQL detailing a database change. So, a patch file might look something like this:

CREATE TABLE `user` (`id` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR(255) NOT NULL, `firstname` VARCHAR(255) NOT NULL, `lastname` VARCHAR(255) NOT NULL, PRIMARY KEY(`id`));

Using this approach, whenever a developer makes a change to the database schema they must add the SQL patch file to this directory describing the change. The patch file is then checked in to version control together with the source code. When anyone updates a copy of the code, they will receive patch files as well as source code changes, and these patches need to be applied to the database to bring it to the version expected by the code.

In order to automate this using a script, our script basically needs to do two things

  • Run database patches on the databa

Truncated by Planet PHP, read more at the original (another 24786 bytes)