53 commentsVersion 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 "deltas" or "migrations") 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 database
- Remember which one we executed last, so next time we know which ones to execute
To keep track of which patch file we executed last, we could simply update a value in our database, first checking the current patch level and then running any later scripts. The code steps (this is psuedo code) would look something like this:
include "database-config"; $dbVersion = getCurrentPatchLevel(); while (patchfileExists(++$dbVersion)) { runPatchOnDatabase(); increaseDatabasePatchLevel(); }
This approach works well and is much better than making manual or undocumented database changes. We could create a simple script that updates our project from source control and then runs any patches automatically, and use this to update our projects on both development and production platforms.
As simple as this solution might look, similar solutions work quite well for many applications and these make a great starting point for implementing database version control in your own projects
Rollback
Of course you hope to never get into this situation, but sometimes you might want roll back a production update. This can for example happen when the application was insufficiently tested, or when the application behaves or performs differently on the production machines than expected. Rolling back your source code is usually quite simple: you can just change the symlink to the directory containing the previous version, or use your version control software to update the code base to an earlier revision. For databases though, reverting is a bit more complicated.
One possible approach is to maintain undo files. This would be a file that does exactly the opposite of what your patch file did, and can be used to revert an update whenever something went wrong. For example, to roll back the example patch file shown above, our script would look something like this:
DROP TABLE `user`;
Bear in mind though that some database operations are not reversible and in those situations this approach is less useful. Imagine a patch file that drops a column from a table and an undo file that creates the column again. Yes, your database schema would now be the same again, but the contents of the column are gone.
One of the main things I found out about database version control is that different projects require different approaches, and while I do not make use of undo files myself, other people rely on them and use them very frequently. Consider the options; does your project require rollback scripts? If you think so, include these in your process. They might one day save your life (or at least your deployment).
Write Your Patch First
No matter what solution you use, it is always a good idea to first create your patch and then run it on your own database using your usual script. This approach ensures that the script actually works, and the version number in your database will match the executed patches.
Typing your own patches instead of copy/pasting them from the phpMyAdmin output might be a bit more cumbersome, but it does encourage you to think about your schema changes thoroughly before actually applying them.
First Patch
I found it useful to have the first patch (patch-001.sql, in the example above) to create the initial schema for the database – created at the point where database patching was implemented. If you have an existing project and you want to start using database patches, then patch-001.sql is simply a dump of the database schema as it is at that moment. Now whenever the schema needs changing, add a patch. This way you can always rebuild your database structure quickly and easily dropping the existing one and then, using your script to run all your patches.
Another advantage of this approach is that the database patches directory in your version control repository will always describe the correct database structure. Since code and database structure are both under source control, we always know which database changes go with which code version.
Sample Data
A great addition to this approach is to maintain an SQL file that inserts dummy contents into your database. Insert some users, news articles, whatever data your application uses. This makes it even easier to just drop your database and replace it with a new one with data known to be sane - especially useful when you end up with lots of test data from debugging a particular issue!
Where Is My Silver Bullet?
You might think that there would be a better solution, to compare databases and synchronise any differences between them, for example. However, if you imagine for a moment that you want to create a tool to do just that. It all works fine when adding or removing stuff, but it gets tricky when renaming tables or columns. The tool would not be able to tell whether a column was renamed, or a column was dropped and another column was added (warning: the existing tools that do compare databases always go for the "drop and add" solution if you renamed something). Since such a tool wouldn't be able to tell the difference it would have to guess, and therefore may not work correctly in all situations.
So database version control is not as simple as making two schemas equal to each other; it is about remembering all changes you did in development and then repeating exactly those steps at all other locations. Exactly how you document those steps is not really important. So far we have used SQL, but as we will see later on, other tools use languages like XML or even PHP. No matter what language you use, you will always have to document all the steps you did, in order to repeat exactly those steps somewhere else. Documenting those steps is the part that developers consider to be cumbersome and painful, but it is absolutely required if you want to do database version control in a reliable way.
Branch Merging
Until now we have been naming our patch files as patch-001.sql, patch-002.sql and so on. This works fine while development is on one branch and everyone updates and commits frequently. However, with multiple branches you might have to reconsider this approach.
Imagine a branch containing patch-003 and patch-004. Meanwhile, development continued on trunk and that also contains patch-003 and patch-004. When we want to merge the branch back to trunk we have a problem since the files will collide. There are several solutions for this, commonly we either keep separate sequences for the separate branches until we merge them again, or we name patch files uniquely (or in a manner likely to be unique), even when we merge branches.
In the first case, we would put the patch files for our branch into a separate directory, or prefix our patches, naming them 'trunk-001.sql' and 'branchA-001.sql'. By changing our script so it can deal with two sequences, we can merge the branch to trunk or back without any conflicts, and run patches from both branches. This works well during development, but in the end the same problem exists: a lot of renaming and reordering of the patch files is needed. Basically this "solution" is a way of postponing the problem, not solving it.
The second solution entails giving your patch files unique names to avoid a conflict. For example, use the current time in your filename (e.g. patch-201012202028.sql), or include the current SVN revision in your filename. When you use this approach, be aware that it is not enough to simply record the last executed patch number. After all, when branches are merged, you will be adding patches "from the past" into the sequence. Our script would need to remember a list of all executed patches, rather than just one number. This way, merging back and forward and finally joining all patches together wouldn't be a problem.
These solutions reduce (or even eliminate) the chance on a conflict on file system level, but on database level there still could be some serious problems. Imagine somebody renaming a column in the branch, and somebody else changing the properties of the column in the trunk. Now all of a sudden, the order in which the patches are executed is important.
When it comes to branch merging, human intervention is often needed. When you merge branches, look at every single one of your patches and see where they fit in the target sequence. In what order should they be executed? The pain of merging can be reduced by using some of these tricks, but there may always problems you should be aware of.
Existing Tools
There are a lot of tools out there that can help you with database version control, and using existing software instead of writing your own script can save a lot of time. In this chapter I will mention a few tools; rather than giving lots of detail on how to use them, I will instead show some of the differences, the pitfalls, the advantages and the downsides of the various solutions. Links are included with each section so you can read more about any of the tools which interest you.
Phing + DBDeploy
Phing is a PHP-based build system, similar to Apache Ant. This means you can use it to automate a number of tasks; for example you could write a Phing script that runs 'svn up', then changes the permissions on certain folders and finally executes your database patches. You could use Phing to call your own script, but you could also use DBDeploy, a database version control tool that comes with Phing when you download it.
When you use DBDeploy you provide it with the connection parameters (host/database/username/password etc.) and point it to the folder containing your patch files. Your patch files are SQL files and they are named using a number, followed by a description (for example: 33-create-user-table.sql). When you now run your Phing task, DB deploy will check for any new patch files and will generate an SQL file containing all the SQL statements to update the database and to update DB Deploy's own meta data table. You can then have Phing execute the generated SQL file by calling your commandline SQL client, using the generated SQL file as input.
The tool is really simple and provides basically the functionality of the simple script shown earlier in this article. Getting started with Phing and DBDeploy is pretty easy, and since it's written in PHP you probably don't have to install any other software to run it.
If you would like a ready-made solution, and you want to have something not too complicated that works, you should definitely check out these tools. To get started I recommend you take a look at this blog post, by Dave Marshall, where he explains how to get started using Phing and DBDeploy.
Liquibase
A more advanced tool which is also available is LiquiBase, which comes with more options than DBDeploy. You can, for example, compare two databases and have a patch file generated to make the one equal to the other (but, as mentioned before, beware when you have renamed any columns), or generate a patch file from an existing database schema. The patches are written in XML, meaning the LiquiBase knows the context of your patches and can actually figure out the undo-version of the patch file by itself. This tool is really nifty, and having all patches in one big XML file means that merging patches is similar to merging source code. You might still encounter some problems, but Liquibase does everything possible to keep the pain to an absolute minimum.
Liquibase is the most complete tool I have seen so far for database patching, however there are some downsides. The patches are written in XML, so expect to be learning a new language to create patches (although it does support SQL snippets in the XML which could be useful). The biggest downside for PHP developers is that it is written in Java, so we need to learn how to work with a new platform to take advantage of this tool.
If you will be dealing with lots of branches and database migrations and you are willing to invest some time into getting familiar with the tool, in exchange for one of the most complete solutions out there, LiquiBase is something you should certainly take a look at. The documentation on the LiquiBase website is very good and extensive, and should contain all the information you need to get going. They also have some good training videos for those who prefer to learn that way.
Depending on the DBMS used you will probably have to install a separate JDBC connector and tell LiquiBase where this connector can be found. Jérôme Renard wrote a good blog post on how to do this using MacPorts, but I'm sure the yum or apt-get version of this howto is quite similar.
Doctrine Migrations
Doctrine is an ORM (Object-Relational Mapping) tool, and it comes with a migrations script built in. Patches are PHP classes with an up() and down() method, describing how to apply and roll back the patch respectively. Most of doctrine migration's functionality is quite similar to the tools above; you can run patches, roll them back, and so on.
The unique feature of this tool is that it lets you generate patches by comparing a YAML file, describing your database schema, with the actual database. This means that whenever you need to add a column, you can simply edit the file, then run the included script generate-migrations-diff, and your patches will be generated automatically. We are still comparing schemas however, so be aware of the pitfalls when renaming columns as described earlier.
A Doctrine Migrations YAML file describing your database can look something like this: (example taken from the Doctrine project)
User: columns: username: string(255) password: string(255) email_address: string(255) Phonenumber: columns: user_id: integer phonenumber: string(25) relations: User: onDelete: CASCADE foreignAlias: Phonenumbers
Maintaining a separate YAML file (and pulling the entire Doctrine project in) is quite cumbersome if you don't use the rest of Doctrine's functionality, however it is a really complete tool with a lot of features so I highly recommend it for existing Doctrine users.
Forging The Silver Bullet
Whether you write your own script, or use an existing tool, managing database versioning is a hard problem. Executing patches is not the real problem; this is more or less taken care of. However, the writing of the patches is still a time consuming task, requiring us to write, generate or merge patches ourselves. Last year I spoke to a lot of people at conferences about this subject, and I heard about some fantastic tools that are being built. New versions of the tools above, which solve many current problems, new approaches that were still too secret to tell - and even a tool monitoring the MySQL binary log on the development machine to detect schema changes and generate patches automatically. Hopefully we will see more and improved tools coming out in the future, but for now all we can do is build the best bullet we can - even if it's not silver!
How are you handling database versioning in your project? Using one of the tools above? If you know a really awesome tool that should have been in this list, please add a comment and explain why it should not be forgotten!
Resources
- Video of my talk "Database Version Control Without Pain" at PHPNW10
- Corresponding slides of my talk "Database Version Control Without Pain" at PHPNW10
- Phing
- DB Deploy
- LiquiBase
- Doctrine
- Blogpost introducing the Akrabat DB Schema Manager: a database version control solution for Zend Framework
- Keep our PostgreSQL databases syncronized with PHP, a blogpost by Gonzalo Ayuso about a tool he made to keep multiple PostgreSQL databases synchronized
53 Responses
Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.
Great summary =)
Great article, but the title should probably be "Database (schema) version control".
Im a big fan of Doctrine migrations myself - mainly because I work a lot with symfony and it comes built in.
I prefer the patch file approach described here, it gives you better control and less risk than any tool for automating this. It also gives you a nice history of changes, that you can refer to later.
I also use it for documenting data/rows that needs to be seeded in the DB from the beginning. Parameters, types and such.
Yesterday, I made a twitter post about this: a new PHP-Framework called FUEL has this feature of update and rollback built in.
This is some good information, I was looking for a solution the other day. Although, it is just so much easier to do a full table structure dump and overwrite a master "schema.sql" file for each commit.
great great information.
If you're on a Microsoft stack Visual Studio has some great tools to have the db schema in source control.
I use the patch way myself for most of my smaller projects ant the larger ones that was started before the VS tools were available.
We employ database level triggers that store all the metaschema changes & change scripts
A good alternative that I've found is to keep a full script of database objects which follows my class layout. One file per object. Each script creates the objects to the initial state.
The proces for patching the database is then:
a) Create a new database with the ''current" schema. Hand written (to ensure that objects are meaningfully named).
b) Use SQL Compare (from RedGate) on the new db and the old db, and let it generate a migration script.
c) Run said migration script.
Personally I find it better than keeping migration scripts, though they are compatible. You save the migration scripts to svn and then integrate into a build/patch system, but that's only worth doing if your team is of a certain size (i.e. > 2/3).
Wow, thanks for the great replies and compliments. Thank you for suggesting other tools to use, I'll definitely take a look at those.
Mohammad, maybe you're right and the title should change. On the other hand, usually there typically isn't much content to manage in the projects I work on. Content (as in news items, blogposts, whatever) is usually something that is entered by the client on the production environment.
However, most of the time when people talk about database version control for the content of databases, they are talking about systems that use the database to store information on how the website works technically, like CMS systems. Since there is no way for an external tool to tell which data in the database is content entered by the user and which data is about the technical workings of the site, an external tool just would never be able to manage it correctly. In such a case, I believe the system you are working with should in fact provide you with a solution, since they're the only ones that know which data should be changed and which data shouldn't be changed when you update.
The solutions that Renowned Media and Ryan provide (thank you) have the problem described in the chapter "Where Is My Silver Bullet?" - your solution won't be able to detect renamed columns and will therefore drop the column and re-add it in such a case. I think Renowned Media is even suggesting to every time just drop the entire database and create a new one using the schema.sql. That might work during development, but I wouldn't recommend it to update your production environment :)
But as I said, different projects require different solutions - and if these solutions work for you and you're happy with it I don't think you should change it, just as long as you're aware of the pro's and con's of your approach. And of course, if it works for you it might for others as well - therefore thanks for sharing!
We are working with an "application repository", so we have some database data (not only schema) that are part of the development. Currently we are developing a script that dumps all the database to text files, one file per table, one text line per field, and tries to submit such files to 'normal' version control, alongside the normal scripts. This way, we hope to have the merge process performed by the version control software, in the same way it does with the app scripts.
If using SQL Server use DB Ghost. One and done. Nuff said.
you can write liquibase schema changes in every lang (for the new version).
And why do you need to learn Java to use this tool? not correct. even for the older versions ...
IMO the first solution is the least cumbersome.
Two things that make it a little better:
1. Use the current date and time concatenated together for the file name, so branching is not a problem (as long as you don't come up with two files at the same second)
2. Keep track of all the imported files, not just the last one. This ensures that later commits will be imported just fine.
@Harrie -
It might be time to write my own blog post on this subject - there is quite a bit of things that tend to happen on these types of migrations that are missed everywhere. This has become one of those subjects that I care about quite a bit since we have to support them. Generally speaking - it is simply forgotten about during the beginning phases.
During a larger scale deployment you have a few operations that generally need to happen:
1. Schema Setup
2. Data Migration
3. Schema Correction
This ultimately relates to 3 different items:
1. What happens before the code is deployed to each server?
2. What should be happening while you deploy the code to each server?
3. What happens after the code is deployed to each server?
This ultimately leads to 3 separate "types" of migrations:
1. Pre Deployment
2. During Deployment
3. Post Deployment
Pre Deployment:
Generally speaking you are creating the schema and/or inserting data that the new code relies on but is written such that the old code is still supported.
During Deployment:
Here is your long running migrations - say you had to move a table out of the way and are now converting data for the new schema that just was added. Really this is conversions or UPDATE statements.
Post Deployment:
This is where you update the schema or insert data that supports the code going forward. Updating the data model so that certain columns are no longer null since you will not support null columns going forward but where needed before the code was pushed out to each server.
Doctrine Migrations and many others do not take this into account and do a single push to handle it.
We use DBDeploy and LiquiBase. DBDeploy is utilized where we deploy from the trunk and liquibase is where we have a merged branch (cherry picked revisions).
They each contain a pre and post deployment - we generally take a different approach to large migrations due to some table sizes and items such as that. But sometimes they are needed. The benefit is you do not need to build these in till you are ready.
Peter, thanks for your response. With the line "so we need to learn how to work with a new platform" I did not mean you would need to learn Java or anything. What I mean is that you will have to install JRE, get the correct JDBC connector and configure Liquibase how to use it, etc. Stuff that is probably obvious for someone familiar with Java, but being a PHP developer it literally took me a couple of hours to set everything up and actually get it running.
Mike, thanks for your extensive reply. It is easy to see that you care quite a lot about the subject :) Interesting approach, I had not looked at it in that way before. For a lot of smaller projects, with small database tables and little changes for the average deployment, just doing a single push will probably suffice. I would say your approach gets especially interesting when the size of your tables and/or deployment increases - and an update statement can easily take a couple of minutes to finish.
Thanks for sharing this approach. And yes, I think you should write your own blog post on the subject :)
A quick question... would you suggest using version control on a document-oriented databse such as MongoDB (where there really aren't any schemas per se)? If so, how would you go about that?
When it comes to document-oriented (or NOSQL) databases, database version control is probably a completely different story. As you say yourself, there isn't any schema - so there's not really anything to maintain. I can imagine you would maybe want to add some default properties to documents of a certain type after updating or something, and of course you could write a script for that which you run after updating your codebase. For doing so you could use a similar strategy as described in the "writing your own script" chapter, to keep track of which scripts you've already applied on the database.
However, I'm just guessing now. I'm afraid I'm not sure if version control for such databases would make sense and if so, what the best approach would be, as I haven't used such databases for any real life projects yet. It's an interesting topic though, definitely worth investigating.
One problem with all of these approaches is that while they can spot what needs changing and produce a script to do the change, they will usually cause major problems with a database containing a non-trivial amount of data.
The naive approach that many MySQL diff scripts use is to do an ALTER TABLE for each field that's changed. This typically means that if you change n fields it will take n times as long to do the migration. This could quite easily lock a table completely (and take your app offline) for DAYS! Even if you combine updates into a single alter table query it's likely to lock things for a fairly long time if you have a significant amount of data.
Another problem they often run into is foreign key constraints - some tools use drop/create or replace into for schema and/or data changes. That's fine for dumb tables like MyISAM, but if you're using tables with foreign key constraints (such as in InnoDB) they can end up cascade deleting a whole raft of other rows in other tables.
Some database systems (inc Oracle) allow schema changes to be done on the fly, but MySQL doesn't. Fortunately there are tools like OpenArk's oak-online-alter-table that provide a nice workaround, and I've used that (and the underlying idea manually) successfully on tables with a few hundred million rows without downtime. http://code.openark.org/blog/mysql/online-alter-table-now-available-in-openark-kit
As Harry mentioned in a comment above, automated migration tools like liquibase are great for dev environments, but I wouldn't let them near production infrastructure.
One important bit to note with the patch approach is that many RDBMS do not support transactional DDL. meaning in MySQL, Oracle etc if you mixed DDL and DML in your migrations and something goes haywire, then you cannot really rollback.
PostgreSQL, Firebird and MS SQL Server can rollback DDL though.
Have a look at SolidBase (URL: http://code.google.com/p/solidbase/).
Personally I have very good experiences using this tool. Just a few advantages:
* Uses SQL instead of XML
* All patches can be placed in a single patch script
* Ability to quickly import CSV files ("23 seconds to import 1 million records"; without indexes and constraints).
* Support "Multiple upgrade paths"; supports migrating to and from branches.
* Both ANT and Maven are supported
This time a working link to the SolidBase homepage:
http://code.google.com/p/solidbase/
Powerdesigner (though an expensive piece of software) has a nice support for creating delta files automatically, it also has a great schema version repository.
I also use MyBatis migrations, a nice thing
We're using a Data-Tier Application (dac pack) project for our VS2010/SQL Server database. It's got a 1.0 Microsoft feel about it (clunky round the edges) but used with standard version control it does most of what you are talking about.
I'm very surprised that no one mentioned this one that actually works amazingly well:
http://code.google.com/p/mybatis/
Check out their documentation on migrations
http://code.google.com/p/mybatis/downloads/detail?name=MyBatis-3.0.3-Migrations.pdf&can=2&q=
And their new efforts of making it into a Maven plugin:
http://code.google.com/p/mybatis/downloads/detail?name=maven-migration-plugin-1.0.0-reference.pdf
We use DBGhost in conjunction with AccuRev and it works very well
I am really happy to see that your post generated so much comments !
For this kind of needs (and a lot more other complex tasks related to db development / deployment), we are developing an open source GPL product called neXtep designer.
It is a brand new approach of this problematic and is database development environment where you work in a dedicated version control repository. The result is that you work directly on the model (no need to write delta scripts) so you stay focus on your database. All your changes are version controlled by the environment.
Then, you have the opportunity to :
- Synchronize your current workspace with a target database
- Generate the SQL between any 2 version
The generation of your SQL is automatized. Since this is a version control system, you can merge 2 branches of your development and generate a resulting delta.
The IDE is build on top of eclipse RCP and, in addition to the above features, propose : graphical data modeling, dependency management, powerful SQL editors (hyperlinks, documentation hovers, autocompletion, instant SQL execution, annotations), SQL client, reference data version control, structural version control. The product currently supports Oracle, MySql, PostgreSql and DB2 and runs on Windows, Linux and Mac (32 and 64 bits).
You should definitly try it and tell me what you think, post us suggestions, etc. We are trying to federate as many people as possible to gather feedbacks, suggestions, thoughts, problematics, in order to build a platform that can fully answer the needs.
Here are the link :
neXtep software site
neXtep designer wiki
Kind regards,
Christophe
In 2009 I was reached this problem in my project. I searched easy solution with minimal requirements writed in PHP. I didn't found.
I started to develop my own tool, and now I can propose it to You.
https://bitbucket.org/idler/mmp/wiki/Home
Script can:
create initial schema from existing database
load this schema into database (for example on new test or production server)
create migrations (as PHP-Classes)
Load migrations in database
Update to specified version(by time)
Try it!
Continuing the Discussion
Database Version Control - http://techportal.ibuildings.com/2011/01/11/database-version-control/
Database Version Control - http://techportal.ibuildings.com/2011/01/11/database-version-control/ #weblabor
Interessanter Artikel über Grundlagen bezüglich Datenbank-Versions-Kontrollen #php #mysql http://tinyurl.com/5waxofa
RT @tikaszvince: Database Version Control - http://techportal.ibuildings.com/2011/01/11/database-version-control/ #weblabor
Database version control http://techportal.ibuildings.com/2011/01/11/database-version-control/
Interessant artikel over database versiebeheer: http://techportal.ibuildings.com/2011/01/11/database-version-control/
@harrieverveer Did you commit that backend already? :P http://tinyurl.com/5waxofa
Database Version Control - http://techportal.ibuildings.com/2011/01/11/database-version-control/
Database Version Control - http://techportal.ibuildings.com/2011/01/11/database-version-control/ gotta try this!!
@ianbarber I wrote a techportal article on it as well :) http://tinyurl.com/5waxofa /cc @thecolejackson