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.
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.
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.
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.
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.
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.
While deployment with SqlPackage takes 66 seconds.
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.
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.
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!
Let’s create build.fsx file at the solution level. Once we have it we can define our first target and build our solution.
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.
Now is time to publish our databases. I skipped here BuildDb target details for readability.
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.
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
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.
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.
As our last modification fo this script I want to add a clean step to clear build directory before main targets are executed.
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.