Database Versioning:

It Doesn't Have
To Be Hard

By this guy:

@MichaelKropat

Two Part Talk

1. Why version the database?
2. How I've done it in the past

(a.k.a poor man's db versioning)

Three Problems When You Don't Version

• Actually there are more reasons
• But these are the 3 ones I care about

First Problem:

Have To Be a Detective

(Any time a db change is made)

Scenario

• I'm happily developing my feature in a branch
• I merge in the latest code from master
• My local dev website now starts giving an error
• The error says database table Foo is missing
• Now what?

Scenario (Cont.)

I now have a few options:

1. Blow away my dev database and re-run initialization script, or...
2. Grep the source code for Foo

   (And hope I find a migration script for Foo)

3. Ask around until I find out who made the change

   (and get them to fix it)

First Problem:

Have to be a detective

(Any time a db change is made)

We Want:

A Defined Upgrade Path

Second Problem:

Db Initialization Script Is Unreliable

Scenario

• I'm happily developing my feature in a branch
• I need to create a new database table Bar
• I write a migration .sql script to create the table
• I remember to check the migration script in, yet...
• Next person to init the db gets an error running the site... oops

Second Problem:

Db Initialization Script Is Unreliable

We Want:

An Always Up-To-Date Db Initializaiton Procedure

Third Problem:

Tables Disappear Without a Trace

True Story

• I'm testing out someone's branch
• Done with that, I checkout my feature branch
• Site no longer runs
• Gives error: Invalid column name 'sharePointStorage'
• No references to sharePointStorage anywhere :/

Third Problem:

Tables Disappear Without a Trace

We Want:

A History Of Changes

What Versioning Gives Us

1. A Defined Upgrade Path
2. An Always Up-To-Date Db Initializaiton Procedure
3. A History Of Changes

Products Exist

• Visual Studio Database Projects
• Liquibase
• Redgate

(I don't have experience with any of these)

Products Exist (Cont.)

I want to show is how easy it is to version

How I've Done Versioning In The Past

(a.k.a poor man's db versioning)

Start With Migration Scripts

20150221-01-create-facts.sql
20150221-02-populate-facts.sql
20150222-disallow-nulls.sql

The Algorithm

$db = Open-Database $Server $Database
$original = Get-DbVersion $db

$migrations = Get-SchemaMigrations $SchemaDir |
    where { $_.Version -gt $original } |
    sort -Property Version
    
Invoke-Migrations $db $migrations

function Invoke-Migrations($Database, $Migrations) {
    try {
        foreach ($m in $Migrations) {
            Invoke-Migration $Database $m
        }
    }
    catch {
        throw $_.Exception
    }
}

function Invoke-Migration($Database, $Migration) {
    $conn = Get-DbConnection $Database
    $script = Get-Content $Migration.Path -Raw
    
    $conn.BeginTransaction()
    
        $Database.ExecuteNonQuery($script)
        Set-DbVersion $Database $Migration.Version
        
    $conn.CommitTransaction()
}

Source

Proof of concept, if you want it:

https://gist.github.com/mkropat/1ba7ffd1d14f55f63fb3

Rollbacks

• Not supported in proof of concept
• Straightforward to add
• (Create rollback .sql script for each migration)

You Can Do It

(It's not hard)

There is NO reason not to version

(However you choose to do it)

Questions?