This post was re-blogged with permission of Leif Battermann from here.
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.
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
- In Visual Studio Create a blank Solution
- Add a database project and name it
TestDbadd a new table named
Table1and replace the
Table1.sqlfile contents with:
CREATE TABLE [dbo].[Table1] ( [Id] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(MAX) NOT NULL )
Configuring the project
- In the properties of the project check the “Create Script (.sql file)” check box under project settings.
- 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.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.
- Enable the “Enable NuGet Package Restore” option on the solution.
- In the solution’s root directory add a new file named
- Add the following content to
@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
// 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"
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"
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
To do this we first have to add a reference to
System.IO in the
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
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"
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"
The code from this post can be found here on GitHub.