Nowadays, source control is something that is not a question in the software development world. But when you deal with database development, it can be a more tricky question than it seems for the first sight. In this post, I going to show the two most common method about version controlling database-related code: by storing upgrade scripts or database state. After that, I am trying to show the pros and cons of each solution.

Great old legacy way

Before we dig into the modern solutions, let’s do a little flashback to the great old legacy ways. By legacy, I mean, it supposed to not exist today but there is a great chance to face with it when we work on old systems.

The worst possible: nothing at all

Joe was the person who took care of the database for 10 years. Joe left the company last week and nobody has information about the database at all. Maybe Joe saved some scripts on his already wiped hard drive.

That is the point when you are in trouble. No way to make this situation better. The only thing that can help you is to script out your current database state as soon as it is possible and save it. After you made that kind of database structure back-up, you can decide how you will do the source control issues.

Congratulations, you became Joe. From this day, you are the database expert at your company!

Expectations from the version control system

There are several things that you, as a developer can expect from your version control system:

  • Able to restore a functional database in a computer after checking out the repository
  • Able to restore an old state of the database by a given version or by date
  • Review changes by using your source control system
  • +1: it is possible to build a CI/CD pipeline based on your repository

Seed scripts: it must mention that special kind of scripts. These are basically insert scripts that fill tables with records that are necessary for your application to run.

Two ways: upgrade scripts or store states

Upgrade scripts or change scripts: you store the exact SQL scripts that you are going to upload. For example, when you want to add a new column for a table, there will be a script in your version control system that contains an alter table statement.

Storing states: your version control stores the current state of your database. When you upload, you have some kind of software that compares the previous state with the state that you want to reach and generates an upgrade script (often called migration scripts) that will run during the release.

Pros – upgrade scriptsCons – upgrade scripts
Release scripts are versionedThe actual database structure is hided
You can easily add rollback scripts
Easy to support 7/24 databases
Pros – storing statesCons – storing states
The current database structure is always visibleRelease scripts are auto-generated
Easy to understand
Can generate database schema comparisation
based on version control information

Common tools

FlyWay: commercial software that uses migration scripts. Multiple databases are supported. Rollback scripts (undo migrations) are supported.

SQL Source Control: commercial software by Red Gate. Stores database states. Dedicated to SQL Server and has Management Studio integration. Supports shared and dedicated developer databases.

DBUp: .Net library that supports migration scripts. Free to commercial use. Supports multiple databases.

Worth to mentions:

SSDT (SQL Server Data Tools): Visual studio integration features. Generates it’s own XML schema from the database and stores database state. Free to use, SQL Server only.

Entity Framework: Open Source ORM tool created by Microsoft. It can generate up and down scripts with a CLI. Supports multiple databases. Supports both XML Schema mapping and POCOs with mapping attributes.

Closing thoughts

Soon or later but if you have a database, you must take care of your version control.

Migration scripts are fine if rollback scripts and performance are musts. I would recommend to use them at large enterprise applications when it is mandatory to minimalize downtime and prepare to rollbacks in case of error occurs.

In another hand, I would recommend using a method that stores states and generate upload scripts to start-ups or small projects. They are really easy to understand but you loos the chance to well define the script that you are going to upload.


InvisibleProgrammer

A remote senior software engineer.

0 Comments

Leave a Reply

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