This story has begun quite long ago. I got a chance to work on projects without the automatic deployment process. It was strange a bit because we had deployments process for both front-end and back-end projects, but not for databases. For databases, we were generating SQL scripts from Visual Studio, and then we executed them in our Test environment. This was a perfect place to save our time and FAKE did the right job here.

We use Visual Studio SQL Server DB projects to handle SQL scripts. And exactly this kind of project we are going to deploy using FAKE. I prepared a solution with two DB projects based on AdventureWorks db. The code you can find in my repo FsharpAdventCalendar2019. In each of these projects, I added two publish profile in Profiles folders. One for Dev and one for Test env. In my example, they are almost the same. There is only one difference. Dev file has Block incremental deployment if data loss occurs option enabled and Test file disabled. This is only to prove that we can parameterize selecting the profile.

Dev publish profile for FsharpAdventureWorks db

Test publish profile for FsharpAdventureWorksDW db

Our goal is to deploy two databases at once!

Build target

Let’s create build.fsx file at the solution level. Once we have it we can define our first target and build our solution.

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

// Helper methods
let getProjectName (fullPath: string) =
    fullPath.Split('\\').Last().Replace(".sqlproj", "")

let build (projectDirectoryPair:string * string) =
    let project, projectName = projectDirectoryPair
    MSBuild.runRelease  (fun p ->
        { p with Properties = [ ("DeployOnBuild", "false") ] } ) (buildDir + projectName + "/") "Build" (Seq.singleton project)
       |> Trace.logItems "DbBuild-Output: "

Target.create "BuildDb" (fun _ -> 

    !! "**/*.sqlproj" 
    |> Seq.map (fun x -> (x, getProjectName x))
    |> Seq.iter build
)

// start build
Target.runOrDefault "BuildDb"

BuildDb target gets all project files (files with sqlproj extension), extracts project name from the file name, runs MSBuild (via MSBuild module) for each and saves DACPAC files in the build directory. Just a few lines of code and we have our solution build. Let’s run fake build command and check result:

Let’s check what happens if we make a not-compilable mistake in table declaration.

Great, that proves that build target works.

Publish target

Now is time to publish our databases. I skipped here BuildDb target details for readability.

let publish (projectDirectoryPair:string * string) =
    let project, projectName = projectDirectoryPair
    MSBuild.runRelease  (fun p ->
        { p with Properties = [ ("DeployOnBuild", "true"); ("SqlPublishProfilePath","./Profiles/DEV.publish.xml") ] } ) (buildDir + projectName + "/") "Publish" (Seq.singleton project)
       |> Trace.logItems "DbBuild-Output: "

Target.create "DeployDb" (fun _ -> 

    !! "**/*.sqlproj"
    |> Seq.map (fun x -> (x, getProjectName x))
    |> Seq.iter publish
)

"BuildDb"
    ==> "DeployDb"

// start build
Target.runOrDefault "DeployDb"

This step is actually pretty similar to Build database step. We just passed slightly different parameters to MSBuild tool:

• we have Publish target instead of Build
• we passed publish profiles

Let’s run fake build command this time and check the result:

In that way, we just deployed our databases. How cool is that? Just a few lines of code and we have all things done.

Targets parameterization

Scripts above are good, but they are rigid. They deploy always all databases and use only Dev publish profile. We can easily change it and get in that way a tool which can be used to deploy databases in our CI/CD pipeline. To add these parameters we need to slightly modify our code.

let publish env (projectDirectoryPair:string * string) =
    let project, projectName = projectDirectoryPair
    MSBuild.runRelease  (fun p ->
        { p with Properties = [ ("DeployOnBuild", "true"); ("SqlPublishProfilePath","./Profiles/" + env + ".publish.xml") ] } ) (buildDir + projectName + "/") "Publish" (Seq.singleton project)
       |> Trace.logItems "DbBuild-Output: "

// Targets

Target.create "BuildDb" (fun _ -> 

    let db = Fake.Core.Environment.environVarOrDefault "db" "*"

    !! (sprintf "**/%s.sqlproj" db)
    |> Seq.map (fun x -> (x, getProjectName x))
    |> Seq.iter build
)

Target.create "DeployDb" (fun _ -> 

    let env = Fake.Core.Environment.environVarOrDefault "env" "Dev"
    let db = Fake.Core.Environment.environVarOrDefault "db" "*"

    !! (sprintf "**/%s.sqlproj" db)
    |> Seq.map (fun x -> (x, getProjectName x))
    |> Seq.iter (publish env)
)

Now if we want to deploy using Test publish profile we need to run:

` fake build -e env=Test `

and if we want to deploy only FsharpAdventureWorks database:

` fake build -e db=FsharpAdventureWorks `

and in case when we want to deploy FsharpAdventureWorks database using Test profile:

` fake build -e db=FsharpAdventureWorks -e env=Test `

To achieve that I made only small changes in code. We only read parameters given in command line and pass them to functions to properly execute targets. This shows, how powerful FAKE can be. The code is really expressive and easy to reason about. Another benefit is having the same approach on DEV and other environments. Having such consistency we can faster catch any potential deployment issue.

Clean target

As our last modification fo this script I want to add a clean step to clear build directory before main targets are executed.

Target.create "Clean" (fun _ ->
    Shell.cleanDir buildDir
)

"Clean"
    ==> "BuildDb"
    ==> "DeployDb"

// start build
Target.runOrDefault "DeployDb"

The whole code you can find here.

Summary

In my opinion, FAKE is a really good tool and with plenty of options which we can use to reach our goal. And even if there is sth what doesn’t work as it should be it is open source and community there is more than friendly. I hope that soon and I will do my contribution to the community.

This post is part of F# Advent Calendar 2019. Thanks to Sergey Tihon for running that event.