Database project của SQL Server

Một database project (dự án cơ sở dữ liệu) của SQL Server (SSDT, hoặc Microsoft.Build.Sql kiểu SDK) lưu mỗi object trong một file. Các object phụ thuộc lẫn nhau giữa các thư mục, theo cả hai chiều: một table trong dbo/Tables có computed column gọi một function trong dbo/Functions, và function đó đọc một table khác trong dbo/Tables.

Không thứ tự file nào áp dụng được schema như vậy, kể cả OrderedDirectories. Project build ra một .dacpac, và DbTest.MsSql.Dacpac deploy nó bằng DacFx, theo đúng thứ tự phụ thuộc.

dotnet add package DbTest.MsSql.Dacpac

Ghi chú

DacpacSchemaSource chỉ deploy lên SQL Server. Với bất kỳ engine nào khác, bước setup ném DbTestException trước khi container khởi động.

Dùng dacpac làm schema

Override SchemaSource thay vì SchemaRoot, và trả về một 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 ...
}

Truyền đường dẫn dacpac qua AppContext.BaseDirectory. Đường dẫn đưa cho một schema source được resolve theo thư mục hiện tại, khác với SchemaRoot resolve theo thư mục của test binary.

Build và copy dacpac

Tham chiếu database project từ test project, để dotnet test build ra dacpac. Sau đó copy dacpac vào cạnh test binary, cùng với mọi dacpac nằm cạnh nó: các dacpac mà nó tham chiếu (master.dacpac từ Microsoft.SqlServer.Dacpacs.Master, hoặc dacpac của project khác) được resolve từ thư mục mà nó được deploy.

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>

Bản thân database project:

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>

Thư mục samples/MsSqlDacpac là một ví dụ hoàn chỉnh, chạy được: một database project, các test của nó và phần build wiring. CI của db-test chạy nó ở mỗi thay đổi.

Ghim SDK

Đặt phiên bản Microsoft.Build.Sql trong global.json dưới msbuild-sdks, và viết <Project Sdk="Microsoft.Build.Sql"> không kèm phiên bản, để mọi project build với cùng một phiên bản.

global.json

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

Những điều cần biết

Thiếu dacpac thì fail trước khi container khởi động

Source kiểm tra cấu hình trước khi container khởi động, nên lỗi hiện ra trong vài mili giây thay vì sau khi pull image và boot server. Thiếu dacpac sẽ ném DbTestException kèm đường dẫn đầy đủ mà nó đã tìm:

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

Dacpac mà SQL Server từ chối thì khác: setup fail với SchemaApplyException nêu tên dacpac và lỗi của server. Mật khẩu đã được loại khỏi thông báo.

SchemaSource và SchemaRoot loại trừ lẫn nhau

Một schema source mang theo vị trí và options của riêng nó. Đặt SchemaSource cùng với SchemaRoot hoặc SchemaLoadOptions sẽ ném InvalidOperationException khi test đầu tiên khởi tạo, thay vì bỏ qua một trong hai. Chỉ override SchemaSource.

Để áp dụng một thư mục qua mô hình source, dùng DirectorySchemaSource.

Deploy options

Khi không truyền options, source deploy với các mặc định sau:

• không xóa gì cả (DropObjectsNotInSource = false);
• khả năng mất dữ liệu không chặn deploy (BlockOnPossibleDataLoss = false);
• không tạo database mới, vì engine đã tạo rồi (CreateNewDatabase = false);
• các thiết lập mức database như recovery model và filegroup bị bỏ qua (ScriptDatabaseOptions = false), vì container dùng một lần không cần chúng và chúng là nguyên nhân dễ gây lỗi deploy nhất ở đây;
• mọi command bị giới hạn bởi command timeout của engine (CommandTimeout và LongRunningCommandTimeout).

Options bạn truyền vào được dùng đúng như đã cho, và một new DacDeployOptions() trống hoàn toàn không có timeout. Hãy bắt đầu từ các mặc định:

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

Chú ý

Đừng thay đổi options sau khi đã tạo source. Key của nó được tính lúc khởi tạo, và việc áp dụng options đã bị đổi sẽ ném InvalidOperationException, thay vì để hai schema khác nhau dùng chung một container.

Các test class có dacpac hoặc options khác nhau sẽ có container riêng.

CleanAsync cũng xóa cả reference data

CleanAsync() làm trống mọi table, kể cả reference data mà post-deployment script của project đã seed. Hãy chạy lại script sau khi dọn dữ liệu. Sample làm việc này trong [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);
}

Cách này hoạt động miễn là script idempotent và không dùng cú pháp SQLCMD. Script của sample:

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 gửi telemetry sử dụng về Microsoft trừ khi biến môi trường DACFX_TELEMETRY_OPTOUT là 1. Đặt <DacFxTelemetryEnabled>false</DacFxTelemetryEnabled> trong .sqlproj để tắt nó cả lúc build. Repository của chính db-test làm cả hai.

Tiếp theo

• Shape guard và dọn dữ liệu hoạt động với schema dacpac giống như với file SQL.
• Viết nguồn schema cho các định dạng schema khác.