Azure devops test database

Goal

I would like to have a solution, that among other things includes an SQL Server Database Project and a Unit Test Project. (+ integration test project?)

That solution is going to be source controlled by a git repository on azure devops, and ideally I would like to have a pipeline that starts a LocalDB (mssqllocaldb) database, runs the script/datpac of the database project, to get it up to speed, and then runs the unit tests that should be able to access to newly created database on the local server.

What I've got so far

The test I'm attempting is fairly simple: (currently using dapper, which along with the connection string will be moved to the api project as soon as I can get this test working):

[TestMethod]
public void TestDBConnection()
{
    var connString = "Server=(localdb)\\mssqllocaldb;Database=custom_db_name_here;Trusted_Connection=True;";
    using (var connection = new SqlConnection(connString))
    {
        var result = connection.Query<int>(sql: "select 1", commandType: CommandType.Text);
        Assert.AreEqual(result.Count(), 1);
        Assert.AreEqual(result.FirstOrDefault(), 1);
    }
}

I don't have a lot of experience with devops, this is the yaml file azure devops generated and I added the start mssqllocaldb task.

trigger:
- master

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
- task: NuGetToolInstaller@1

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

- task: VSBuild@1
  inputs:
    solution: '$(solution)'
    msbuildArgs: '/p:DeployOnBuild=true /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true /p:DesktopBuildPackageLocation="$(build.artifactStagingDirectory)\WebApp.zip" /p:DeployIisAppPath="Default Web Site"'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

- task: PowerShell@2
  displayName: 'start mssqllocaldb'
  inputs:
    targetType: 'inline'
    script: 'sqllocaldb start mssqllocaldb'

# Publish probably goes here, not sure how though?

- task: VSTest@2
  inputs:
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

Problem

I'm not sure how to create the new database, add all the structures (tables, procedures etc.) and data.

Use FluentMigrator (alternative to the dacpac approach)

Since you're using dapper, I would recommend that you use FluentMigrator to manage your migrations. This will come in handy as you manage migrations over time in your long running environments, such as prod.

There are two approaches you could take with to scaffold and seed your database for your tests. These two options are really just the two ways you can apply your migrations with FluentMigrator, in-process and out-of-process.

Option 1: As part of the test initialisation, in-process (My personal preference)

It appears that you're using the MSTest framework, so you would put in your [AssemblyInitialize] code which runs the migration scripts, and seeds any data. Since the data you're seeding for tests is not intended to be seeded into prod, I would keep this separate to the migration scripts. FluentMigrator gives you the ability to run the migrations in-process (i.e. from your C# code). You can find details in the FluentMigrator quickstart guide

Using this approach, your pipeline will not need to change. Another advantage of this is that any developers wanting to scaffold their local db can simply run the test suite after starting their local db.

In my experience, even though I would use this to scaffold the database for tests, I had still used the out-of-process approach for applying the migrations to long running environments.

Option 2: As a separate task in your pipeline, out-of-process

This is another option that you can find in the FluentMigrator quickstart guide. With this option, you'd create a task after starting LocalDB to run the migrations. I don't like this approach because when you're developing locally, you will have to know when to apply migrations by running the command, which is tedious.

Use SqlPackage (dacpac approach)

Azure Hosted Agents provide SqlPackage by default, once you have built your .dacpac file, add the following task to your pipeline, making sure to update the path/name of your produced dacpac

- script: SqlPackage /Action:Import /SourceFile:"$(Build.Repository.LocalPath)\dbproject\bin
debug\project.dacpac" /TargetConnectionString:"Data Source=(localdb)\v11.0;Initial Catalog=devdb; Integrated Security=true;"
  workingDirectory: C:\Program Files\Microsoft SQL Server0\DAC\bin\
  displayName: 'Import dacpac'

I'm adding this answer just so that the end result I went with is included here. The credit should go to Aaron Zhong and his answer.

I added an after build event that copies the created .dacpac to the root directory of the solution, so that it's easier to access it later. (Also had to add the .dacpac to .gitignore)

  <Target Name="AfterBuild">
    <Copy SourceFiles="$(OutputPath)$(MSBuildProjectName).dacpac" DestinationFolder="$(SolutionDir)" ContinueOnError="false" />
  </Target>

The final .yml file looks like this:

trigger:
- master

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
- task: NuGetToolInstaller@1

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

- task: VSBuild@1
  inputs:
    solution: '$(solution)'
    msbuildArgs: '/p:DeployOnBuild=true /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true /p:DesktopBuildPackageLocation="$(build.artifactStagingDirectory)\WebApp.zip" /p:DeployIisAppPath="Default Web Site"'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

- task: PowerShell@2
  displayName: 'Start SQL server'
  inputs:
    targetType: 'inline'
    script: 'sqllocaldb start mssqllocaldb'

- task: PowerShell@2
  displayName: 'Deploy DB dacpac'
  inputs:
    targetType: 'inline'
    script: '.\SqlPackage.exe /Action:Publish /SourceFile:"$(Build.Repository.LocalPath)$(DBNAME).dacpac" /TargetConnectionString:"Data Source=(localdb)\mssqllocaldb;Initial Catalog=$(DBNAME); Integrated Security=true;"'
    workingDirectory: 'C:\Program Files\Microsoft SQL Server0\DAC\bin\'

- task: VSTest@2
  inputs:
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

I used an environment variable ($(DBNAME)) for the database and the .dacpac file (they have the same name, so I only had to create 1 variable for both).

If you want to copy this solution the only thing you should have to change is the environment variable and possibly the trigger branch on the top of the .yml file.

I decided not to go with the scaffolding + migration approach, since although it pairs better with git, it feels a lot harder to read and maintain compared to a normal SQL schema.