Seed your database with ease through idempotent data-as-code scripts and SQL DACPACs

As with all good projects, we want to source control our database. SQL projects are a great way to do this, as they allow us to define our tables, views, and stored procedures. We can then compile DACPACs to give us an interface to publish the definitions to our target database manually or via our source control process, such as GitHub Actions or Azure DevOps pipelines.

If we want to automatically add data to these database tables as part of our CI/CD lifecycle, how should we best do this?

This article will demonstrate some thinking used to split the code we wish to deploy to avoid dependency issues, reduce the chance of merge conflicts in Git, and produce idempotent deployments with little maintenance effort.

Common challenges

Managing SQL database code can be challenging at the best of times. The temptation to make changes directly in the database can result in drift between what's in the database and what's in source control.

The overhead of maintaining source-controlled schemas can reinforce the desire to open SSMS and make changes directly — often in dev, but sometimes in prod.

SQL project file structures can grow messy quickly, further incentivizing the continuation of bad practices.

Even if we do follow good development practices, teams of more than one person working and committing code in parallel can end up with unfortunate merge conflicts when editing the same files.

Solving these two challenges is my motivation for this article, so let's get into the thinking.

Our example

I spend most of my working time with metadata-driven platforms, using a SQL Database to house this metadata. I won't get into the what's and why's here, but consider reading our Whitepaper if you wish to know more!

In our example we've got the following:

• Azure SQL Database

• GitHub as our source control system

• A SQL database project with tables, views, and stored procedures

• Some post-deployment scripts to deploy data to some of the tables.

Our post-deployment scripts concern adding data to just three tables for now.

Defining our design approach

First and foremost, we need to consider a possible anti-pattern we want to improve upon, so let's draw that out.

We've got a SQL Project with all of our object definitions and we've written a script to perform any SQL queries we wish to run after publishing these objects to our database.

This Post-Deployment Script might look like the following:

-- Clear out current records
DELETE FROM [metadata].[attributes];
GO
DELETE FROM [metadata].[datasets];
GO
DELETE FROM [metadata].[connections];
GO

-- Reset identity column values
DBCC CHECKIDENT ('metadata.attributes', RESEED, 1);
GO
DBCC CHECKIDENT ('metadata.datasets', RESEED, 1);
GO
DBCC CHECKIDENT ('metadata.connections', RESEED, 1);
GO

-- Insert our source system connection metadata
INSERT INTO [metadata].[connections] (ConnectionName, ConnectionLocation, Enabled)
VALUES 
    ('AdventureWorks', 'adventureworksdb.database.windows.net', 1),
    ('WideWorldImporters', 'wideworldimportersdb.database.windows.net', 1);
GO

-- Insert our source system dataset metadata
INSERT INTO [metadata].[datasets] (ConnectionId, DatasetName, Enabled)
VALUES
    (1, 'Product', 1), 
    (2, 'StockItem', 1);
GO

-- Insert our source system attribute metadata
INSERT INTO [metadata].[attributes] (DatasetId, AttributeName, AttributeType, Enabled)
VALUES
    (1, 'ProductId', 'INT', 1), 
    (1, 'ProductName', 'VARCHAR(100)', 1), 
    -- Many more rows!
    (2, 'StockItemId', 'INT', 1),
    (2, 'StockItemName', 'VARCHAR(100)', 1);
GO

This is a crude first pass, but gives us an idea of what we want to do: add data to the tables in the desired order and assume foreign key values based on the identity columns being re-seeded each time.

Identifying the flaws

Keeping it to the point:

• We're doing a delete so we can re-insert data. A merge statement might be more appropriate.

• Hard-coded foreign keys are brittle, and could lead to incorrect relations between tables.

• Everything in one file — this is already quite a lot of code for 2 datasets. As it grows, it will be harder to find what needs changing.

• It is brittle to concurrent updates. Multiple users working on the same file, potentially editing the same lines.

An example of the last point is as follows.

1. User one changes makes a change on the left, adding the TaxiData data source.

2. User two makes a similar change, to the right for the Fireworks data source. They're working on different domains and want to work independently.

-- Insert our source system connection metadata
INSERT INTO [metadata].[connections] (ConnectionName, ConnectionLocation, Enabled)
VALUES 
    ('AdventureWorks', 'adventureworksdb.database.windows.net', 1),
    ('WideWorldImporters', 'wideworldimportersdb.database.windows.net', 1),
    ('TaxiData', 'taxidb.database.windows.net', 1);
GO

-- Insert our source system connection metadata
INSERT INTO [metadata].[connections] (ConnectionName, ConnectionLocation, Enabled)
VALUES 
    ('AdventureWorks', 'adventureworksdb.database.windows.net', 1),
    ('WideWorldImporters', 'wideworldimportersdb.database.windows.net', 1),
    ('Fireworks', 'fireworksdb.database.windows.net', 1);
GO

At a first glance, this might look okay — we're only adding one row each, right?

Well, both commits will actually edit the WideWorldImporters row, which previously had a semicolon, replacing it with a comma.

They're both changing that line, and also both adding a subsequent line. Git doesn't handle this well at the point of merging the second commit into the base branch:

Both additions are correct, but both modify the previous line at different commit histories, so Git becomes a bit confused. Let's help mitigate this with some better code.

A second pass at things

We've identified a few issues, and are keen to improve! We create some new stored procedures and script files to split out our workloads.

An example of one of our stored procedures to idempotently merge data into one of the target tables might look like the following:

metadata/Stored Procedures/AddDataset.sql

CREATE PROCEDURE [metadata].[adddataset] (
    @ConnectionName VARCHAR(100),
    @DatasetName    VARCHAR(100),
    @Enabled        BIT
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Datasets TABLE (
        ConnectionId   INT NOT NULL,
        ConnectionName VARCHAR(100),
        DatasetName    VARCHAR(100),
        Enabled        BIT
    );

    INSERT INTO @Datasets (ConnectionId, ConnectionName, DatasetName, Enabled)
    VALUES (-1, @ConnectionName, @DatasetName, @Enabled);

    UPDATE d
    SET d.ConnectionId = c.ConnectionId
    FROM @Datasets AS d
    INNER JOIN [metadata].[connections] AS c
        ON c.ConnectionName = d.ConnectionName;

    MERGE INTO [metadata].[datasets] AS Target
    USING @Datasets AS Source
        ON Source.ConnectionId = Target.ConnectionId
        AND Source.DatasetName = Target.DatasetName

    -- INSERT CLAUSE
    WHEN NOT MATCHED THEN
        INSERT (ConnectionId, DatasetName, Enabled)
        VALUES (Source.ConnectionId, Source.DatasetName, Source.Enabled)

    -- UPDATE CLAUSE
    WHEN MATCHED THEN UPDATE SET
        Target.DatasetName = Source.DatasetName,
        Target.Enabled     = Source.Enabled;
END;

Moving away from delete then insert logic to merging allows us to better control the upsert process to our target tables.

Scripts/AddConnections.sql

-- Merge into our source system connection metadata
EXEC [metadata].[addconnection] 
    @ConnectionName     = 'AdventureWorks',
    @ConnectionLocation = 'adventureworksdb.database.windows.net', 
    @Enabled            = 1;
GO

EXEC [metadata].[addconnection] 
    @ConnectionName     = 'WideWorldImporters',
    @ConnectionLocation = 'wideworldimportersdb.database.windows.net', 
    @Enabled            = 1;
GO

Scripts/AddDatasets.sql

EXEC [metadata].[adddataset] 
    @ConnectionName = 'AdventureWorks',
    @DatasetName    = 'Product', 
    @Enabled        = 1;
GO

EXEC [metadata].[adddataset] 
    @ConnectionName = 'WideWorldImporters',
    @DatasetName    = 'StockItem', 
    @Enabled        = 1;
GO

Scripts/AddAttributes.sql

-- Merge into our source system attribute metadata
EXEC [metadata].[addattribute] 
    @DatasetName   = 'Product', 
    @AttributeName = 'ProductId', 
    @AttributeType = 'INT', 
    @Enabled       = 1;
GO

EXEC [metadata].[addattribute] 
    @DatasetName   = 'Product', 
    @AttributeName = 'ProductName', 
    @AttributeType = 'VARCHAR(100)', 
    @Enabled       = 1;
GO

EXEC [metadata].[addattribute] 
    @DatasetName   = 'StockItem', 
    @AttributeName = 'StockItemId', 
    @AttributeType = 'INT', 
    @Enabled       = 1;
GO

EXEC [metadata].[addattribute] 
    @DatasetName   = 'StockItem', 
    @AttributeName = 'StockItemName', 
    @AttributeType = 'VARCHAR(100)', 
    @Enabled       = 1;
GO

Scripts/Script.PostDeployment.sql

:r .\AddConnections.sql
:r .\AddDatasets.sql
:r .\AddAttributes.sql

This is an improvement as we've split code into more manageable files, and we've solved the idempotency challenge through using some stored procedures.

This design addresses most of the earlier flaws, but we still might end up with quite large files being worked on by many members of the team. An example scenario of 100 datasets may lead us again to a situation where a few people are modifying the AddDatasets.sql and AddAttributes.sql scripts concurrently.

Final Scripts

A better way to handle this would be to break our scripts down even further. By flipping the boundary of what a script contains from "target-table-based" to "dataset-based", we can separate the same logic into independent blocks of code - Each script capturing all logic for a single object in isolation.

We might have a domain script, which focuses on populating the values for a single domain we want to put into the connections table:

Scripts/Domains/AdventureWorks.sql

-- Merge into our source system connection metadata
EXEC [metadata].[addconnection] 
    @ConnectionName     = 'AdventureWorks',
    @ConnectionLocation = 'adventureworksdb.database.windows.net', 
    @Enabled            = 1;
GO

Scripts/Domains/WideWorldImporters.sql

EXEC [metadata].[addconnection] 
    @ConnectionName     = 'WideWorldImporters',
    @ConnectionLocation = 'wideworldimportersdb.database.windows.net', 
    @Enabled            = 1;
GO

These are fairly static, so it may seem like overkill to split them out, but it fits the pattern we want to follow.

Now that this is established, let's add the datasets one at a time. Starting with the AdventureWorks Product table, this encapsulates the dataset itself, and all of the associated attributes. Putting everything in one place makes it easy to define and find, and means that one engineer can work on this script separate to the engineer working on another dataset.

Scripts/Datasets/AdventureWorks/Product.sql

-- Merge into our source system dataset metadata
EXEC [metadata].[adddataset] 
    @ConnectionName = 'AdventureWorks',
    @DatasetName    = 'Product', 
    @Enabled        = 1;
GO

-- Merge into our source system attribute metadata
EXEC [metadata].[addattribute] 
    @DatasetName   = 'Product', 
    @AttributeName = 'ProductId', 
    @AttributeType = 'INT', 
    @Enabled       = 1;
GO

EXEC [metadata].[addattribute] 
    @DatasetName   = 'Product', 
    @AttributeName = 'ProductName', 
    @AttributeType = 'VARCHAR(100)', 
    @Enabled       = 1;
GO

Scripts/Datasets/WideWorldImporters/StockItem.sql

-- Merge into our source system dataset metadata
EXEC [metadata].[adddataset] 
    @ConnectionName = 'WideWorldImporters',
    @DatasetName    = 'StockItem', 
    @Enabled        = 1;
GO

-- Merge into our source system attribute metadata
EXEC [metadata].[addattribute] 
    @DatasetName   = 'StockItem', 
    @AttributeName = 'StockItemId', 
    @AttributeType = 'INT', 
    @Enabled       = 1;
GO

EXEC [metadata].[addattribute] 
    @DatasetName   = 'StockItem', 
    @AttributeName = 'StockItemName', 
    @AttributeType = 'VARCHAR(100)', 
    @Enabled       = 1;
GO

Scripts/Script.PostDeployment.sql

-- AdventureWorks metadata
:r .\Domains\AdventureWorks.sql
:r .\Datasets\AdventureWorks\Product.sql

-- WideWorldImporters metadata
:r .\Domains\WideWorldImporters.sql
:r .\Datasets\WideWorldImporters\StockItem.sql

Final Bottleneck

We've separated our scripts into self-contained units that largely prevent engineers' changes from conflicting with one another, but there is one remaining bottleneck: the Scripts/Script.PostDeployment.sql file.

We can overcome this with some nifty configurations within our database.sqlproj file though!

SQL Project File

We're going to extend our database.sqlproj file to simplify things, leveraging the following features:

• Streamlined SDK project

• Leverage SQLCMD variables for parameterization and future CI/CD support

• Automatically generate the post-deployment scripts to run with build actions

If you haven't seen an SDK-based sqlproj file before, they're worth checking out. The screenshot below shows the difference between the old XML-style and the new SDK-style sqlproj — the SDK version is instantly easier to read and requires far less manual declaration of included files.

I've included a side-by-side comparison of the blank sqlproj files using the different formats so you can see the compactness gained by the new SDK style.

It is also worth noting that you don't need to "include" any of the objects when writing this - which again makes it more readable and reduces the chance of merge conflicts if users are creating separate tables concurrently!

SQLCMD variables are another great feature for applying different configurations when publishing to different environments.

For example, by specifying a SQLCMD variable for AdventureWorksConnectionLocation in the database.sqlproj file:

<!-- =========================
       SQLCMD Variables
       ========================= -->
  <ItemGroup>
    <SqlCmdVariable Include="AdventureWorksConnectionLocation" Value="$(SqlCmdVar__1)" />
  </ItemGroup>

We can then reference it in our Scripts/AddConnections.sql script using the $(VariableName) syntax:

Scripts/AddConnections.sql

-- Merge into our source system connection metadata
EXEC [metadata].[addconnection] 
    @ConnectionName     = 'AdventureWorks',
    @ConnectionLocation = '$(AdventureWorksConnectionLocation)', 
    @Enabled            = 1;
GO

When publishing to the target database, this value is resolved at runtime — it can be set manually, scripted, or passed through a CI/CD pipeline.

The most interesting feature we will look at, though, is a build target that uses MSBuild's WriteLinesToFile task to auto-generate our include files at build time.

<!-- =========================
       Build Targets: Auto-generate include files
       ========================= -->
  <!-- Generate Scripts\Domains\_AutoInclude.sql -->
  <Target Name="GenerateDomainScripts" BeforeTargets="BeforeBuild">
    <ItemGroup>
      <DomainScripts Include="Scripts\Domains\*.sql" Exclude="Scripts\Domains\_AutoInclude.sql" />
    </ItemGroup>
    <WriteLinesToFile File="Scripts\Domains\_AutoInclude.sql" Lines="@(DomainScripts->':r .\%(RecursiveDir)%(Filename)%(Extension)')" Overwrite="true" />
  </Target>

This block of code looks for all .sql files matching the Include path pattern — wildcards are supported, which will prove very useful. At build time, any matching files are automatically written to Scripts\Domains\_AutoInclude.sql, preserving any recursive folder structure.

The same pattern applies to the Datasets folder. The key difference is the **\ wildcard, which recurses into sub-folders — necessary here because each dataset lives under its own connection sub-folder (e.g. Datasets\AdventureWorks\, Datasets\WideWorldImporters\):

<!-- Generate Scripts\Datasets\_AutoInclude.sql -->
  <Target Name="GenerateDatasetScripts" BeforeTargets="BeforeBuild">
    <ItemGroup>
      <DatasetScripts Include="Scripts\Datasets\**\*.sql" Exclude="Scripts\Datasets\_AutoInclude.sql" />
    </ItemGroup>
    <WriteLinesToFile File="Scripts\Datasets\_AutoInclude.sql" Lines="@(DatasetScripts->':r .\%(RecursiveDir)%(Filename)%(Extension)')" Overwrite="true" />
  </Target>

Why this matters

We've now got a way of telling our DACPAC to include all files in certain subfolders, without needing to manually list them in the sqlproj file (the SDK-style project auto-discovers included files) or in Script.PostDeployment.sql. We can instead point it to the _AutoInclude.sql files as shown below:

Scripts/Script.PostDeployment.sql

:r .\Domains\_AutoInclude.sql
:r .\Datasets\_AutoInclude.sql

These _AutoInclude.sql files are generated automatically at build time by the MSBuild target — you do not write or maintain them manually. An example of what gets generated is as follows:

Scripts\Domains\_AutoInclude.sql

:r .\AdventureWorks.sql
:r .\WideWorldImporters.sql

Scripts\Datasets\_AutoInclude.sql

:r .\AdventureWorks\Product.sql
:r .\WideWorldImporters\StockItem.sql

This means we no longer need to worry about engineers clashing when adding new scripts.

Since they are generated on each build, feel free to add the _AutoInclude.sql files to your .gitignore file. This removes another potential source of merge conflicts.

Caveats

There are some considerations with this approach.

• File ordering is alphabetical, meaning that scripts will be written to the _AutoInclude.sql filein this order. This can be problematic if you have execution order dependencies.

• Separate into multiple _AutoInclude.sql targets if needed. These can then be referenced in your Script.PostDeployment.sql file in the required execution order.

• As this example implementation does not have any clean up, orphaned data removed from the scripts is not cleaned up. Maintaining the scripts with Enabled = 0 flags, or utilising a clean-up script can be used to achieve this.

Wrap up

We haven't touched on the CI/CD element of this, as that would go off-topic. However, with this pattern we do accommodate CI/CD and environment differences quite easily, with the option to use SQLCMD variables to apply environment-specific parameterization for things like our [metadata].[connections] table's ConnectionLocation, which might differ between development, test, and production environments. These could be stored as part of a publish profile you've got configured for each environment, or if they contain sensitive information, as part of your repository's secrets (e.g., GitHub Actions secrets).

Have you used a similar approach to handling code deployments in your databases? What have we missed? Let us know in the discussion below.

Thanks for reading.