Whats the best build system for building a database?

Question

This is a problem that I come to on occasion and have yet to work out an answer that I'm happy with. I'm looking for a build system that works well for building a database - that is running all of the SQL files in the correct database instance as the correct user and in the correct order, and handling dependencies and the like properly.

I have a system that I hacked together using Gnu Make and it works, but it's not especially flexable and frankly can be a bit of a pain to work with in some situations. I've considered looking at things like SCons and CMake too, but I don't know how much better they are likely to be, or if there's a better system out there that already exists...

Answer 1

For SQL Server, I just use a batch file with SQLCMD.EXE and a bunch of .SQL files. It's not perfect, but it seems to work.

Answer 2

For my database, I use Migrator.NET This is a .NET framework which allows you to create classes in where you define your DDL statements. The framework comes with a command-line tool with which you can execute your 'migrations' in the correct order. It also has a msbuild - task, so you can integrate it in a continuous integration build as well.

Answer 3

Just a shell script that runs all the create statements and imports in the proper order. You may also find migrations (comes with rails) interesting. It provides a make like infrastructure that let's you maintain a database the structure of which evolves over time.

Say you add a new column to some table. In migrations you'd write a snippet of code which describes the requirements for adding the column and also to rollback the change so you can switch to different versions of your schema automatically.

I'm not a big fan of the tight integration with rails, though, but the principles behind it are very interesting.