It is essential to treat database schema as source code and apply all the norms and best practices applicable to it. From what I hear and read, database schema version control is non-existent in too many projects.
Various techniques to accomplish version control of database schemas have been discussed and published. Martin Fowler and Pramod Sadagale has written a comprehensive article on Evolutionary Database Design. It is a must-read for everyone involved with database development.
K. Scott Allen writes in detail about an excellent system here – Versioning Databases – Change Scripts. This approach is sufficiently light-weight (for my taste) and provides complete control over the whole process.
I am providing a free tool here that you can use to implement this system. It will even create the SchemaVersionsLog table for your database, if it doesn’t exist. If you are using this tool with MSSQL Server, you can get started with very little effort. If you are using a different DBMS, you can still use this tool. It uses NHibernate to access the SchemaVersionsLog table. So, any DBMS supported by NHibernate is acceptable, as long as it comes with a command line tool to execute the sql scripts.
Download DbUpdater here.
5 Comments
Hello,
I’ve tried the tool and it runs forever after it says “executing baseline.sql”. I’m running it against a sql2000 server and a 400kb baseline.sql.
Any ideas why?
Thanks
Interesting tool…I’m trying to implement this at my current employer and would be interested in seeing the source code (if you are willing to share). I’m trying to get dba buy-in on the process and it would be helpful to articulate everything that is going to be done to the db.
We’re trying to use your DbUpdater, but when I have indexes and foreign keys scripted in our baseline.sql file, the exe hangs.
The baseline.sql file runs perfectly fine in SQL Management Studio. DbUpdater will work if I remove all the indexes and foreign keys from baseline.sql and move them to post.X.sql scripts.
We’ve also noticed that if you only have 1 db changes file, DbUpdater will not recognize that there are any changes. We have had to create 2 db changes files before the DbUpdater would notice the updates.
DbUpdater version 1.4 fixes two bugs -
1. If you only have 1 db changes file, DbUpdater will not recognize that there are any changes.
2. When you have indexes and foreign keys scripted in our baseline.sql file, the exe hangs.
Adrian and Cory, Thanks for your feedback.
Hi there!
Some months ago I searched tool for versioning MySQL schema. I found many useful tools, like Doctrine migration, RoR migration, some tools writen in Java and Python.
But no one of them was satisfied my requirements.
My requirements:
1. No requirements , exclude PHP and MySQL
2. No schema configuration files, like schema.yml in Doctrine
3. Able to read current schema from connection and create new migration script, than represent identical schema in other installations of application.
I started to write my migration tool, and today I have beta version.
Please, try it, if you have an interest in this topic.
Please send me future requests and bugreports.
Source code: bitbucket.org/idler/mmp/src
Overview in English: bitbucket.org/idler/mmp/wiki/Home
Overview in Russian: antonoff.info/development/mysql-migration-with-php-project