by Leif

SQL Type Providers and Continuous Integration with FAKE

If you want to access a relational database from an F# (or C#) application, SQL F# type providers are commonly used. SQL type providers will provide all the types, properties and methods needed to access and interact with the tables of a SQL database, without having to write any extra boilerplate code. Everything is type checked and if the actual database schema gets out of synch with the database related code, compilation will fail. This is very valuable because it gives you high confidence in the application's data access code.

So at compile time the database has to be up to date and accessible. But how does this work in a continuous integration environment? Off course an option is to have the connection string of the type provider point to a development database on the network. Another solution would be to manually create or update a database on the build server before the build. But I don't really like this because I think the build server should be independent and self-sufficient. A solution to this scenario, that worked for me, is to deploy the database during the build process using Visual Studio database projects and FAKE - F# Make.

There might be other and maybe better solutions that I haven't come up with. I'd be curious to find out. Actually this might be one. The approach that I used, however, works out nicely, so I will give a quick walkthrough on how to set things up.

Prerequisites

SQL Server Express or SQL Server and SQL Server Data Tools for Visual Studio should be installed locally and on the build server.

Creating the Visual Studio Database Project

These are the steps to create and configure a very basic database project in Visual Studio.

Project and schema

  1. In Visual Studio Create a blank Solution TestSolution.
  2. Add a database project and name it TestDb.
  3. To TestDb add a new table named Table1 and replace the Table1.sql file contents with:
CREATE TABLE [dbo].[Table1]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(MAX) NOT NULL
)

Configuring the project

  1. In the properties of the project check the "Create Script (.sql file)" check box under project settings.
  2. As the target platform you might have to select "SQL Server 2012" depending on your SQL Server version.

Creating an F# project for the data access

Here are the steps to create an F# library for data access using the SqlDataConnection type provider.

First add a new F# library project to the solution and add references to FSharp.Data.TypeProviders, as well as System.Data, and System.Data.Linq as also described here.

Then add a new file to the project, named DataAcces.fs, with this content:

namespace Data

module DataAccess =

    open System
    open System.Data
    open System.Data.Linq
    open Microsoft.FSharp.Data.TypeProviders
    open Microsoft.FSharp.Linq

    [<Literal>]
    let ConnectionString = "Data Source=(localdb)\V11.0;Initial Catalog=TestDb;Integrated Security=SSPI;"
    type private DbSchema = SqlDataConnection<ConnectionString>

    let private db (connectionString:string) = DbSchema.GetDataContext(connectionString)

    let getNameById connectionstring id =
        let db = db ConnectionString
        let opt = db.Table1 |> Seq.tryFind (fun x -> x.Id = id)
        opt |> Option.map (fun x -> x.Name)

The solution won't compile at this point because the external source of the type provider (the database) doesn't exist yet.

Building the project with FAKE

Now we are going to configure the build process using FAKE - F# Make.

  1. Enable the "Enable NuGet Package Restore" option on the solution.
  2. In the solution's root directory add a new file named build.bat.
  3. Add the following content to build.bat:
@echo off
cls
".nuget\NuGet.exe" "Install" "FAKE" "-OutputDirectory" "packages" "-ExcludeVersion"
"packages\FAKE\tools\Fake.exe" build.fsx
exit /b %errorlevel%

Next add a new Item of type "F# script File" to the solution named build.fsx, and add the following code to build.fsx:

// include Fake lib
#r @"packages/FAKE/tools/FakeLib.dll"
open Fake

// Default target
Target "Default" (fun _ ->
    trace "Hello World from FAKE"
)

// start build
RunTargetOrDefault "Default"

Run build.bat from the Windows Command Prompt or from PowerShell. The build script will not do much so far but it should successfully terminate.

Adding a target to build the database project

Change the content of the build.fsx to this:

// include Fake lib
#r @"packages/FAKE/tools/FakeLib.dll"
open Fake

RestorePackages()

// Properties
let buildDir = "./build/"

// SQL database project references
let sqlDbProjRef = 
  !! "./**/*.sqlproj"

// Targets
Target "Clean" (fun _ ->
    CleanDirs [buildDir]
)

Target "BuildSqlProj" (fun _ ->
    sqlDbProjRef
      |> MSBuildRelease buildDir "Build"
      |> Log "Database-Build-Output: "
)

// Dependencies
"Clean"
  ==> "BuildSqlProj"

// start build
RunTargetOrDefault "BuildSqlProj"

Run build.bat.

Now the database project should be build and a create script TestDb_Create.sql should be generated inside the build directory.

Adding a target to run the create script

FAKE does support SQL Server related tasks with the Fake.SQL.dll. But unfortunately the create script that is generated by the database project will contain SQLCMD commands. And when running the scripts with FAKE it seems that they can't be run in SQLCMD Mode.

One way to run the script is to use the Fake process Helper to start SQLCMD.EXE.

To do this we first have to add a reference to System and System.IO in the build.fsx file.

Then we will define some properties:

// SQL Properties
let server = "\"(localdb)\\v11.0\""
let scriptPath = buildDir </> "TestDb_Create.sql"
let sqlcmdOutputPath = buildDir </> "TestDb_Create.out"

Now we define a function that will start SQLCMD.EXE with the needed parameters and a target that calls this function. To pipe the output back into FAKE, the output file contents will be read and logged with log (File.ReadAllText(output)).

let runSqlcmd server input output = 
    let result = ExecProcess (fun info -> 
        info.FileName <- "SQLCMD.EXE"
        info.Arguments <- "-S " + server + " -i " + input + " -o " + output) (TimeSpan.FromMinutes 5.0)

    if result <> 0 then failwithf "SQLCMD.EXE returned with a non-zero exit code"

    log (File.ReadAllText(output))

Target "RunCreateScript" (fun _ ->
    runSqlcmd server scriptPath sqlcmdOutputPath
)

Next we have to updated the build target dependencies:

// Dependencies
"Clean"
  ==> "BuildSqlProj"
  ==> "RunCreateScript"

// start build
RunTargetOrDefault "RunCreateScript"

If build.bat is executed the Database should be created now.

Building the application

Add a property appReferences to specify the file sets of the application:

let appReferences  = 
    !! "./**/*.fsproj"

Add a target which compiles the application:

Target "BuildApp" (fun _ ->
    appReferences
      |> MSBuildRelease buildDir "Build"
      |> Log "Build-Output: "
)

Update the dependencies:

// Dependencies
"Clean"
  ==> "BuildSqlProj"
  ==> "RunCreateScript"
  ==> "BuildApp"

// start build
RunTargetOrDefault "BuildApp"

Now the application should be successfully built.

Testing the application

To test the application we will simply insert a data set into the database using e.g. SQL Management Studio or Visual Studio:

INSERT INTO Table1 VALUES (1, 'hello world')

Now we can test the DataAccess.dll with a script like this:

#r @"../../build/DataAccess.dll"

open Data.DataAccess

let getNameById = getNameById @"Data Source=(localdb)\V11.0;Initial Catalog=TestDb;Integrated Security=SSPI;"

let name = getNameById 1

The output should be:

Some "hello world"

Source code

The code from this post can be found here on GitHub.