SQL Server database projects

A SQL Server database project (SSDT, or the SDK-style Microsoft.Build.Sql) keeps one object per file. The objects depend on each other across folders, in both directions: a table in dbo/Tables has a computed column calling a function in dbo/Functions, which reads another table in dbo/Tables.

No file order applies that, not even OrderedDirectories. The project builds into a .dacpac, and DbTest.MsSql.Dacpac deploys it with DacFx, in dependency order.

dotnet add package DbTest.MsSql.Dacpac

Note

DacpacSchemaSource deploys to SQL Server only. With any other engine, setup throws a DbTestException before a container starts.

Use the dacpac as the schema

Override SchemaSource instead of SchemaRoot, and return a DacpacSchemaSource:

ShopTests.cs

using DbTest.Core.Engine;
using DbTest.Core.Schema;
using DbTest.MsSql;
using DbTest.MsSql.Dacpac;
using DbTest.MSTest;

using Microsoft.Data.SqlClient;

namespace Sample.Database.Tests;

/// <summary>
/// Tests for the Sample.Database project, run against its own .dacpac deployed into a
/// throwaway SQL Server container.
/// </summary>
[TestClass]
public sealed class ShopTests : DbTestBase
{
    protected override DbEngine CreateEngine() => new MsSqlEngine();

    // The .dacpac is copied next to the test binaries by the CopyDacpacs target in the .csproj.
    protected override SchemaSource? SchemaSource =>
        new DacpacSchemaSource(Path.Join(AppContext.BaseDirectory, "Sample.Database.dacpac"));

    // ... the tests ...
}

Pass the dacpac path through AppContext.BaseDirectory. A path given to a schema source resolves against the current directory, unlike SchemaRoot, which resolves against the test binaries’ directory.

Build and copy the dacpac

Reference the database project from the test project, so dotnet test builds the dacpac. Then copy the dacpac next to the test binaries, with every dacpac beside it: the dacpacs it references (master.dacpac from Microsoft.SqlServer.Dacpacs.Master, another project’s) are resolved from the folder it is deployed from.

Sample.Database.Tests.csproj

<!--
  The database project. Referencing it makes `dotnet build` / `dotnet test` build the .dacpac
  first; it is not an assembly, so nothing is referenced or copied by the reference itself.
-->
<ItemGroup>
  <ProjectReference Include="..\Sample.Database\Sample.Database.sqlproj"
                    ReferenceOutputAssembly="false"
                    SkipGetTargetFrameworkProperties="true"
                    Private="false" />
</ItemGroup>

<!--
  Copy each referenced database project's .dacpac next to the test binaries, together with
  every .dacpac beside it: the dacpacs it references (master.dacpac, another project's) are
  resolved from the folder it is deployed from.
-->
<Target Name="CopyDacpacs" AfterTargets="Build">
  <MSBuild Projects="@(ProjectReference)"
           Condition="'%(Extension)' == '.sqlproj'"
           Targets="GetTargetPath"
           BuildInParallel="false">
    <Output TaskParameter="TargetOutputs" ItemName="_DatabaseProjectDacpac" />
  </MSBuild>
  <ItemGroup>
    <_DacpacToCopy Include="%(_DatabaseProjectDacpac.RootDir)%(_DatabaseProjectDacpac.Directory)*.dacpac" />
  </ItemGroup>
  <Copy SourceFiles="@(_DacpacToCopy)" DestinationFolder="$(OutDir)" SkipUnchangedFiles="true" />
</Target>

The database project itself:

Sample.Database.sqlproj

<Project Sdk="Microsoft.Build.Sql">

  <!--
    A SQL Server database project (SDK-style). `dotnet build` turns it into
    bin/<Configuration>/Sample.Database.dacpac, which the test project deploys into a throwaway
    container. The SDK version is pinned in the repository's global.json (msbuild-sdks).
  -->
  <PropertyGroup>
    <Name>Sample.Database</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <!-- A sample: never packed, whatever builds it. -->
    <IsPackable>false</IsPackable>
    <!-- No build-time usage telemetry from the SDK. -->
    <DacFxTelemetryEnabled>false</DacFxTelemetryEnabled>
  </PropertyGroup>

  <ItemGroup>
    <PostDeploy Include="Script.PostDeployment.sql" />
  </ItemGroup>

  <!--
    The master database model, so objects can use sys.* views. Most real projects carry this
    reference, and it is why the tests copy every .dacpac from this project's output, not only
    Sample.Database.dacpac: DacFx resolves master.dacpac next to the dacpac it deploys.
  -->
  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Dacpacs.Master" />
  </ItemGroup>

</Project>

The samples/MsSqlDacpac directory is a complete, runnable example: a database project, its tests and the build wiring. db-test’s CI runs it on every change.

Pin the SDK

Put the Microsoft.Build.Sql version in global.json under msbuild-sdks, and write <Project Sdk="Microsoft.Build.Sql"> without a version, so every project builds with the same one.

global.json

{
  "msbuild-sdks": {
    "Microsoft.Build.Sql": "2.3.0"
  }
}

What to know

A missing dacpac fails before any container starts

The source checks its configuration before the container starts, so a mistake fails in milliseconds instead of after an image pull and a server boot. A missing dacpac throws a DbTestException with the full path it looked for:

Dacpac not found at '<full path>'. Build the .sqlproj and copy its .dacpac next to the tests ...

A dacpac that SQL Server rejects is different: it fails setup with a SchemaApplyException naming the dacpac and the server’s error. The password is removed from the message.

SchemaSource and SchemaRoot are exclusive

A schema source carries its own location and options. Setting SchemaSource together with SchemaRoot or SchemaLoadOptions throws an InvalidOperationException when the first test initializes, instead of ignoring one of them. Override only SchemaSource.

To apply a directory through the source model, use DirectorySchemaSource.

Deploy options

Without options, the source deploys with these defaults:

• nothing is dropped (DropObjectsNotInSource = false);
• possible data loss does not block the deploy (BlockOnPossibleDataLoss = false);
• no new database is created, because the engine already created it (CreateNewDatabase = false);
• database-level settings such as the recovery model and filegroups are skipped (ScriptDatabaseOptions = false), because a throwaway container has no use for them and they are the likeliest deploy failure there;
• every command is bounded by the engine’s command timeout (CommandTimeout and LongRunningCommandTimeout).

Options you pass are used exactly as given, and a bare new DacDeployOptions() has no timeout at all. Start from the defaults:

var options = DacpacSchemaSource.CreateDefaultOptions(new MsSqlEngine());
options.SqlCommandVariableValues["Environment"] = "Test";
return new DacpacSchemaSource(path, options);

Caution

Do not change the options after creating the source. Its key is computed at construction, and applying changed options throws an InvalidOperationException rather than letting two different schemas share a container.

Test classes whose dacpac or options differ get separate containers.

CleanAsync also empties reference data

CleanAsync() empties every table, including reference data that the project’s post-deployment script seeded. Run the script again after cleaning. The sample does it in [TestInitialize]:

ShopTests.cs

/// <summary>
/// Starts every test from the deployed state: CleanAsync empties every table — including the
/// reference data the post-deployment script seeded — so the script runs again.
/// </summary>
[TestInitialize]
public async Task ResetAsync()
{
    await Harness.CleanAsync(TestContext.CancellationToken);

    var postDeployment = await File.ReadAllTextAsync(
        Path.Join(AppContext.BaseDirectory, "Script.PostDeployment.sql"),
        TestContext.CancellationToken);
    await Harness.ExecuteAsync(postDeployment, TestContext.CancellationToken);
}

This works as long as the script is idempotent and has no SQLCMD syntax. The sample’s script:

Script.PostDeployment.sql

-- Post-deployment script: seeds the reference data. Idempotent, so it can run after every
-- deploy, and free of SQLCMD syntax, so the tests can run it again after CleanAsync() has
-- emptied every table.
MERGE dbo.TaxRates AS target
USING (VALUES ('NL', 0.2100), ('DE', 0.1900), ('GB', 0.2000)) AS source (RegionCode, Rate)
    ON target.RegionCode = source.RegionCode
WHEN MATCHED AND target.Rate <> source.Rate THEN
    UPDATE SET Rate = source.Rate
WHEN NOT MATCHED THEN
    INSERT (RegionCode, Rate) VALUES (source.RegionCode, source.Rate);

Telemetry

DacFx sends usage telemetry to Microsoft unless the environment variable DACFX_TELEMETRY_OPTOUT is 1. Set <DacFxTelemetryEnabled>false</DacFxTelemetryEnabled> in the .sqlproj to switch it off at build time too. db-test’s own repository does both.

Next

• Shape guard and cleanup work with a dacpac schema as they do with SQL files.
• Writing a schema source for other schema formats.