Database creation, migration and how it works with source control (SVN) and projects.

Database version control has been something that used to cause me problems, a lot of problems, because it wasn't within the normal controlled sources. This had to change - and it isn't something that is easy to do as it requires discipline because the databases don't really integrate with any source control system. (Correct me if I'm wrong - I'll be very pleased).

My development sources have been controlled since the early 90's, but when Delphi came along I was genuinely stunned - it had a database, a real one, included with it. It was great - I'd been using databases since the late 1980's - but they always seemed to need a large machine (part of the reason I got myself a VAX 3250 - but that's another story).

Now, I've been a huge fan of source control since I first discovered it in 1988 with RCS - it was stunning and when we managed to get the entire system to build from SCCS in around 1989 that was unbelievable - because it was controlled and traceable.

Enter the database and it all started to go astray - not at first - but all of the old problems of versioning started happening with the databases, it hurt and it had to be stopped.

In reality the solution seemed simple. We'd keep an exported copy of the database within the source tree and in theory it should all be cool. Except that it didn't quite work like that - problems started to appear between versions - and eventually the unthinkable happened in that the database became out of sync with the source tree - and worse still it was unrecoverable. This was a major problem - the database that we had on the development server was fine - but the database that was controlled was broken. Despite the changelogs it was impossible to find out what actually happened.

So, a new approach was called for, and after many iterations this is how I would control the database.

Starting from the firsrt moment of development and continuing for ever there is a script - db-create.sql - which does exactly what you'd imagine (starting with DROP DATABASE).

During development up until the first live deployment this is run often - and modifying the DB via other tools is permissible - but under caution to update the script, as otherwise changes will be lost. By ensuring that db-create is run often (and by definition it must create the database in a usable state) we have solved the problem of quick patches to tables and stored procedures.

Once the system reaches a significant milestone (usually deployment or release), then the db-create script is frozen - ensuring that the version matches with the releae version.

After this point any changes have to be made via a new script, equally imaginatively entitled db-migrate.sql. This script is required to take the database from the version of db-create and make it match the requirements for the current version under control. Again it will be executed frequently - except that a database restore from the release version is performed first - the concept here is to ensure that the development database is mirroring the released version and the the migrationprocess works and is acutally usable.

In practice the two scripts often get polluted with auto-generated SQL from one of the many tools that allow database manipulation, whilst this is annoying that's all it is, and can easily be solved by more those more expert with SQL.

The key part of the process is to ensure that the scripts are run frequently - in fact it often helps the development process precisely because the database is a lot cleaner.

Also it is often worth having a db_version table that tracks the current database version, and use your judgement to decide if you want to keep track of the changes that have been applied here - for example in a seperate table - or in the version table.