Programmatically storing your Database for Source Control

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
views/
mainview.view.sql
sprocs/
getmytables.sproc.sql
data/
mytable1.data.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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.