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
- In Visual Studio Create a blank Solution
TestSolution
. - Add a database project and name it
TestDb
. - To
TestDb
add a new table namedTable1
and replace theTable1.sql
file 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
, 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.
- Enable the "Enable NuGet Package Restore" option on the solution.
- In the solution's root directory add a new file named
build.bat
. - 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.