SqlJuxt – Using disposable in F# to drop a test database

The integration tests in SqlJuxt need to do the following:

• Create two databases
• Set the databases up in a certain state (create tables, views etc)
• Compare them
• Assert the result of the comparison
• Clean up the databases at the end (drop them)

Before I dive in to how I went about making this work I think its good to take a look at how the finished test looks:

[<Fact>]
let ``should return identical when two tables are the same``() =
    use left = createDatabase()
    use right = createDatabase()
     
    let table = CreateTable "TestTable"
                    |> WithNullableInt "Column1"
                    |> Build 

    runScript left table
    runScript right table
        
    loadSchema left.ConnectionString
            |> compareWith right.ConnectionString
            |> should equal IsMatch

I think that test reads really well. In fact you can tell exactly what is going on in the test from the code which is one of the key ingredients of a good test. The test is creating two databases, then creating the same table on both of the databases. It then compares them using the SqlJuxt library and expects the databases to match. The result of the comparison is a discriminate union which I will talk about more in an upcoming post. For now you can read that it says “should equal IsMatch” which is really clear.

The astute reader will notice that nowhere in the test does it mention dropping the databases so you might wonder how this is done. The secret behind this is IDisposable. I got the idea from reading this page on let, use and do on the F# for fun and profit site. Scott talks about using the dispose to stop a timer so I thought it would be neat to use it to drop the database.

To make this work the createDatabase function returns a class that implements IDisposable. Notice that the variables left and right are declared using the ‘use’ keyword and not the ‘let’ keyword. This means that when the variables go out of scope Dispose is automatically called.

This is how the disposable database type is defined:

type DisposableDatabase(name, connectionString) =

    member this.ConnectionString = connectionString
    member this.Name = name

    interface System.IDisposable with 
        member this.Dispose() = 
            dropDatabase this.Name

The code is a little bit clunky as we have to declare a class to implement IDisposable but the cool part is that we do not have to explicitly drop the database in our test it just happens for us.

If you want to check out the full source code and delve deeper feel free to check out the SqlJuxt GitHub repository.