Build templates on Azure DevOps

Last time we created a gated check-in build for .NET Core app. It works very well, but we did there one thing which is in general a bad practice in our proficiency. We duplicated build steps for building and testing .NET Core app. We can do better than that, we can use templates. Following the documentation:

Templates let you define reusable content, logic, and parameters. Templates function in two ways. You can insert reusable content with a template or you can use a template to control what is allowed in a pipeline.

We will do both things.

More …

Gated check-in build on Azure DevOps for .NET Core app

Standard CI build checks integrity of committed code after merge. This approach can lead to a state when code is not deployable due to failing tests or even failing compilation. Gated check-in helps to protect the integrity by verifying state before the merge. In that way, you can protect your master branch and avoid build breaks. In that way, you can ensure that your master branch is always deployable (what is crucial in GitHub flow) and you will not interrupt your colleagues with your obvious mistakes. Gated check-in feature was originally introduced in TFS 2010. It can also be easily adopted in Azure DevOps YAML based builds.

More …

Caching (not only) NuGet packages on Azure DevOps

The goal of Cache@2 task is improving build performance by caching files between pipeline runs. It supports multiple types of packages like

  • Bundler gems
  • npm packages
  • Yarn packages
  • NuGet packages
  • Maven artifacts
  • Gradle artifacts
  • ccache artifacts

Further, we will focus on caching NuGet packages however in a similar manner we can configure this task for other types.

More …

DB deployments with Fake.Sql.SqlPackage

In my previous post, I presented how we can deploy multiple visual studio database projects using Fake build tool. Here, I will present another option which is possible with version 5.19.0.

This version has a new module called Fake.Sql.SqlPackage, which is a redesign of previous Fake.Sql.DacPac module. The reason, why the previous module needed redesign, it was a missing option (from my point of view crucial option) - publish profiles. I raised that point on GitHub here and solved it with this pull request. If you are interested in SqlPackage itself you should check the documentation.

To sum up this a bit long introduction - all that means that I had a pleasure to be Fake build contributor.

Fake build feature release list

New publish target

All code can be found here and is a continuation of the previous post. The logic of DeployDb target doesn’t change and thus I will skip it here. I will focus only on publish function which is used by this target.

Previously we used MSBuild tool to publish database projects.

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 "DbDeploy-Output: "

In this approach, we build and publish the database. It means that we build project twice (once in BuildDb target and once in DeployDb target). With Fake.Sql.SqlPackage we can avoid that and use dacpac files created in BuildDb target as it is presented below.

let publishWithSqlPackageModule env (projectDirectoryPair:string * string) =
    let project, directory = projectDirectoryPair
    let dacPacPath = sprintf "./build/%s/%s.dacpac" directory directory
    let profile = sprintf "./%s/Profiles/%s.publish.xml" directory env

    Fake.Sql.SqlPackage.deployDb (fun args -> { args  with Source = dacPacPath; Profile = profile })

The function itself is simpler (in both version we just invoke Fake modules, but here we need to provide fewer parameters), but what is important even more. It is faster too. Publish with MSBuild takes 78 seconds.

Deployments statistics with MS Build

While deployment with SqlPackage takes 66 seconds.

Deployments statistics with SqlPackage

It may not seem like much, but SqlPackage version is 15.5% faster just on this example project. I made a similar switch in my company projects, which has a more complex database structure than this here. Numbers are much better - 172 seconds with MSBuild version versus 90 seconds with SqlPackage version. What gives 47.7% speedup! And it saves not only my time but everyone’s time as we use it on our CI server.

Summary

The conclusion will be really short. It is not the end of the world if open source software doesn’t have needed feature. It can be your opportunity to pay back to the community.

DB deployments with FAKE tool

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:

Build DB target result

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

Build DB target failed result

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:

Deploy DB target 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.

There is another way of deploying databases in FAKE. Since we already created DACPAC files in BuildDB target, we can use SqlPackage tool (which is delivered with VisualStudio). We can use this tool via [Fake.DacPac](https://fake.build/sql-dacpac.html) module. However, I found an issue there with the defaults argument. We can pass plenty of parameters to SqlPackage via command line or we can put them in a publish profile. In the case where we set the same parameter in the command line and a publish profile, the first one wins. This is the same with Fake.DacPac, but there is one difference. If we do not pass for instance _block on possible data loss_ parameter, Fake.DacPac set its own defaults. In that case, even if we have this parameter set in publish profile and we don't set it directly in Fake.DacPac it will be overridden by Fake.DacPac default. I already created an issue on GitHub and hopefully, I should fix it soon

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.