DBDeploy - Get your database under control
Keeping track of your database schema is always problematic, recently I have started using DBDeploy to help track changes to our database under version control.
We use DBDeploy as part of our build process now and so far its working very well, still waiting for those anti-patterns to occur somewhere down the line though hehe but that life aye...
The basic principle of DBDeploy is that you first create a baseline, this forms the base from which all changes are built upon. Then we use delta files to sequentially apply changes, these changes are then logged in a special changelog table in the schema.
For what I was doing I already had a schema so I went about creating a set of sql scripts that would create the baseline for me, this was achieved by using mysqldump. If you read the mysqldump docs you should be able to get it to produce exactly what you want. For me I wanted it to create files that were not schema specific so I could import that baseline into any schema, e.g. mysql -umute -p dash_trunk < baseline.sql, this way I can have multiple versions of my db for my branches etc... I also added in all the default data that the system required, my users, acl permissions etc.
With that all done, I could then do.
- Create a new schema in mysql
- Import the baseline
- Run build and have DBDeploy add any changes!
Magic a reliable way to setup my database, and good for testing too...
The Delta Files
Yeah they are a bit like X-Files but with less aliens... So a delta file contains changes/additions that you make to your schema, so they are simply just a bunch of sql statements. One important thing that I do is not to add deltas in branches, DBDeploy runs deltas in sequence 1,2,3... so if you merge a branch into the trunk say and there is 3.sql and 3.sql it will merge it and potentially cause you all sorts of mischief!
Test Data
Now once I had done all this I had a pretty empty application, so now I need test data! 24dash has a massive 50k articles and now I have none what todo??? Well I didn't really come up with a very easy solution, again mysqldump did a lot of the work for me and I got a cron going to export a load of stuff nightly for me. Which is fine until you are working on something that makes changes to the tables and you cant import it anymore! I am still working on a way to fix this for now we just have to think what we are doing before we need to import test data!
Conclusion
Personally I am really impressed with DBDeploy so far its working perfectly and I feel much happier knowing what my DB is doing, if you do use something like DBDeploy I would say you must use local build before commit and seriously consider using continuous integration! ( its all about developer communication )
Posted on 23/06/2008 at 04:37 PM
