Databases are often a critical part of most applications today. But how do you go about storing them for Source Control purposes?

There are a number of approaches – the method used may also vary based on how the database is being consumed by the application. One project I worked on had everything modeled in C#/Entity models with no scripted code. Another project had decorated C# classes that were turned into database commands via T4 templating.

Whatever method you use – I find the simplest methods are always the best. For me, I try and ensure all databases – however they’re implemented, are stored in raw sql scripting in Source Control.

So in the root of my Source Control I have the following directory structure:

databases/
mydb/
bootstrap.sql
tables/
mytable1.table.sql
mytable2.table.sql
mytable3.table.sql
views/
mainview.view.sql
sprocs/
getmytables.sproc.sql
data/
mytable1.data.sql
updates/
0001-mytable2-addcolumn.sql
0002-mytable1-addconstraint.sql
0003-mytable3-createtable.sql

So for each database there’s a bootstrap.sql which simply creates any users and security policies needed for the database and initiates the CREATE DATABASE command.

Then each object type in the database has its own directory. You will notice that each object is suffixed with it’s type and the .sql extension. There’s a method behind this;

1. If you accidentally save the mytable1.data.sql file to the tables/ directory, it won’t overwrite the table creation script (or vice versa).
2. You can run a command-line search to just retrieve all files of type “.table.sql” regardless of path from the databases/ directory, so you could have all databases made, then all tables made, then all views etc…

Scripting the database as plainly as this makes it perfect for Source Control management. You then only need a command line tool to iterate over the directory structure to push the content to the database server.

A note on Table Updates

You’ll see there is an updates/ directory containing sequentially numbered Updates. These sequences allow you to play a sequence of transactions on the database to bring it up to date. You should always store the latest schema in the SQL scripting, but when you have live production databases you can’t just drop and recreate them, so all updates to live databases should go in the updates/ directory.

And remember – when doing updates that add database tables or columns – test for their existence first with IF NOT EXISTS conditions or similar.