Rewiring

Better .NET DB First development workflow for SQL Server

Using EF Power Tools CLI in conjuction with MSBuild.Sdk.SqlProj provides a smooth workflow for Database First development when targeting SQL Server databases

Have you ever experienced the snowball effect of discovering one thing, leading to another, and another, until you've learned about a whole new world? I seem to get that quite often, which probably means I'm not as well-read as I believed... Oh well, at least I'm constantly learning new things!

One such snowball of mine was a recent task in a "DB First" project that had me feeling dissatisfied with the workflow. DB First means that your data storage is the first and foremost layer of development, and everything else follows. Contrast that with Code First, which is the default when working with EF Core, where you define your data model as code and later generate the database based on that code. My job has mainly been doing Code First, but especially for the legacy upgrades going DB first is a very sensible choice.

Note: The .sqlproj and the SDK showcased here are unfortunately only available for SQL Server, so no luck for postgres... EFPT works well there too though

The default for DB First in dotnet land is having a SQL Database Project (.sqlproj), which was earlier known as a Visual Studio Database Project. As you can probably guess, it's specific to MS SQL and its Azure variants. It provides easy access to the Schema Compare tool straight from Visual Studio (and Rider!) making it a bit easier to safely deploy database changes, but mostly it's just a container for all your SQL scripts.

Obviously being tied to MS SQL reduces it's usability and the .sqlproj has caused some (not insurmountable) issues in e.g. build pipelines, but mostly it's been a solid enough solution for our needs.

Until now. Until the snowball started rolling. The catalyst was EFPT CLI.

Entity Framework Core Power Tools

When it comes to making the job easier, EF Core Power Tools is easily one of my favorite open source tools. It's indispensable for performing database reverse engineering into EF code and has some nice other features besides. ErikEJ, the author, is a key contributor in a bunch of .NET data access repositories and well worth a follow!

Earlier it was only a Visual Studio extension, but some time ago a CLI was released, which really piqued my interest. Firstly, since EFPT is pretty much my only reason for booting Visual Studio, I could drop that, but also - what automation possibilities could it provide?

Reverse engineering with the cli is simple; using the dotnet tool, call dotnet efcpt "<connection string>" mssql (or just efcpt if you installed the tool globally) in a directory with the config file, and you're done. Unfortunately, migrating from the VS extension to the CLI takes a bit of work, since the configuration files are different. It wasn't too bad though, since the process was essentially

  1. run the tool
  2. examine the results
  3. if it did not match the original, tweak the config, go back to 1

After a few rounds, I had an easily runnable "refresh my models" tool in my hands. It's good enough when you point it to a live database, but what if you could just update the SQL script in the project and check the effects in code? Turns out it's possible to reverse engineer from a .dacpac file just as well as a connection string. Great! Now just to get a .dacpac from .sqlproj!.

First roll of the snowball completed.

MSBuild.Sdk.SqlProj

On my quest to find a way to generate a .dacpac from an .sqlproj via the CLI I stumbled upon MSBuild.Sdk.SqlProj, which provides a bunch of interesting SQL features. The main promise of is easy generation of .dacpacs and SQL scripts, so the question then becomes: can it be combined with .sqlproj or must .sqlproj be dumped completely?

Turns out that combining works beautifully, so one can gain these new capabilities while keeping the familiar tools available.

Note: in an upcoming post, which talks about integration testing against a real database, I'll set up an example repo with these goodies included. Stay tuned!

First, define a new .csproj that acts as the "build target". Let's assume that you have a MyDatabase.sqlproj that contains your db definitions in paths such as definitions/Tables/MyTable.sql and definitions/Views/MyView.sql.

With that, you could create a build/MyDatabase.Build.csproj (name it as you wish) with these kinds of contents:

<Project Sdk="MSBuild.Sdk.SqlProj/3.1.1">

    <PropertyGroup>
        <TargetFramework>netstandard2.1</TargetFramework>
        <SqlServerVersion>Sql150</SqlServerVersion>
        <GenerateCreateScript>True</GenerateCreateScript>
        <IncludeCompositeObjects>True</IncludeCompositeObjects>
        <TargetDatabaseName>MyAwesomeDatabase</TargetDatabaseName>
    </PropertyGroup>

    <ItemGroup>
        <Content Include="..\definitions\**\*.sql"/>
    </ItemGroup>

</Project>

The key parts:

Now, when you build the .csproj, .dacpac and .sql files are generated in the output directory. You can point it to your target directory by dotnet build build/MyDatabase.Build.csproj -o outputs

Easy, trivial even. Now you have both a .dacpac file for doing the EFPT reverse engineering with dotnet efcpt "path/to/outputs/MyDatabase.Build.dacpac" mssql as well as Create_MyDatabase.sql containing a script for initializing the full database for e.g. your integration tests. The SDK also supports publishing to a database with dotnet publish.

All in all, the SDK seems to be a gold mine for DB First functionality, like static SQL analysis and I look forward to learning all it has to offer. I bet it could straight up replace .sqlproj, and I'll definitely look into it later, but let's keep this ball of snow from ballooning too much in one go.

Will the snowball lay waste to everything?

With these rather easy modifications I have reached a level of DB First capabilities I would not have dreamt of just a week earlier. If you have a database project in a separate repository, using these tools via git submodules is really straightforward. The SDK especially has alluring possibilities for doing database deployments from CD (after they've been approved by a person! I don't trust database manipulation to the machines just yet..).

Getting static analysis on the SQL seems like a much needed addition to improve the quality of our database. With some build trigger trickery it seems easy enough to achieve a really smooth dev experience of "modify table, build, observe the changes, run integration tests, done".

All in all, these discoveries further increase the value - at least in my eyes - of going DB First. While this stuff has always been trivial in Code First, as the great poet said:

You know what's a good tool for designing a database? A fucking database

If you know who it was that actually laid that golden egg, let me know!

Thoughts, comments? Send me an email!

#dotnet #entityframework #sql #tech