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.
It is always a funny feeling when you read a book published at the year when you were born. And it means that the book is 31 years old. Why did I decide to reach for “Peopleware: Productive Projects and Teams” by Tom DeMarco and Tim Lister? There were two things. The first was 4.6 out of 5 starts and 201 reviews on Amazon. And the second was Joel Spolsky (Co-founder of Stack Overflow) opinion:
Peopleware is the one book that everyone who runs a software team needs to read and reread once a year. In the quarter century since the first edition appeared, it has become more important, not less, to think about the social and human issues in software development. This is the only way we’re going to make more humane, productive workplaces. Buy it, read it, and keep a stock on hand in the office supply closet.
Now when I’m after, I just can make the conclusion: I couldn’t agree more with Joel.