Setting Up a Local Database for Integration Testing in F# with Dapper

I was recently tasked with setting up a local database for integration testing. This code was in F#, and we chose Dapper, a lightweight ORM, for our database needs.

After doing some research and consulting with my teammates, we came up with an overview of what’s needed for data related integration tests:

1. Set up a database connection server.
2. Create a test database (if not there) according to the Redgate Schema folder.
3. Update the test database if there were any database structure changes.
4. Purge all existing test data and restore any static data.

Ideally, we want one function call for developers to use that will encompass all of the above. The setup function lays out all the logic, but helper functions carry out the work.

1. Set Up the Server

To set up a local database connection server, the key is to not specify a database just yet. A specific connection can be returned once everything is properly set up.

Let connectionString = “Server=(localdb)\MSSQLLocalDB:Trusted_Connection=True;”
Let connection = new sqlConnection(connectionString)

2. Create a Local Database

I looked up many ways to check if a database exists, and the cleanest way I found was through an ExecuteScalar call.

Let databaseExists = connection.ExecuteScalar<bool>(sprintf “select db_id(‘%s’)” databasename)

After checking, it’s a simple command to create the desired test database.

connection.Execute(sprintf "create database %s" databaseName) |> ignore

3. Update the Database

The quickest way to check if an update is needed for the local database is through a timestamp. Inside the isUpdateNeeded function, our logic is as follows:

• Create a .gitignore file marked with a timestamp.
• Check the timestamp against all Redgate database schema file timestamps.
• Call SQLCompare to make an update to the local database if outdated.
• Update the timestamp file if an update was done.

Process.Start is used to call SQLCompare inside our F# code. It is important to note the Redgate file path.

Let args = sprintf “/Scripts1:\"%s\" \"/Server2:(localdb)\MSSQLLocalDB\" /Database2:%s /Options:IgnoreCollations /Include:Identical /Synchronize" redgatePath databaseName”

Let proc = Process.Start(“SQLCompare”, args)
proc.WaitForExit()
If proc.ExitCode <> 0 then
   Failwith (sprintf “SQLCompare exited with code %d” proc.ExitCode

4. Purge All Data & Restore Static data

Now that the local database exists and is up to date according to our schema, we need to ensure that every integration test has a clean slate to work with. During my research, I was pleasantly surprised that there were many objects in the SQL Server that are used internally by Microsoft — and sp_MSForEachTable is one of them. It’s a quick and easy way to delete all the data in the local database. Of course, don’t forget to disable and enable referential integrity!

connection.Execute("""
EXEC sp_MSForEachTable ‘Alter Table ? nocheck constraint all’
EXEC sp_MSForEachTable ‘Delete FROM ?’ 
EXEC sp_MSForEachTable ‘Alter Table ? with check check constraint all'
""")|> ignore

Last but not least, restore any static data that was there to begin with! This can be done through an iteration of static data files and executing all SQL commands within each file.

This setup creates an up-to-date local database that is ready for developers to use. I hope this is helpful to you!