Migration-based database development

Version controlling your database alongside your code provides a number of benefits (not discussed in this blog post).

There are two major approaches to version-controlled database development: state-based and migration-based. In a state-based approach, a developer describes their desired database schema and uses a tool that can alter an existing database to match that schema (or create a fresh database matching that schema). In a migration-based approach, a developer instead captures every schema change as a separate script, and uses a tool to run the appropriate scripts against existing databases.

Difficulties with a state-based approach

We've tried using a state-based approach with SSDT database projects. To apply changes we build the project to create a DACPAC file, and publish it using sqlpackage. We like this approach because it's declarative, and gives us highlighting, code completion, and fast feedback via project compilation. However, it also poses some problems:

• The executed SQL may differ per environment, which lowers our confidence that it will work in production.
• Some changes require publishing multiple times. For example, to add a column without a default to a table containing data requires publishing once to add it with a default and later publishing again to remove the default.
• Some changes, which would be easy to do in multiple steps, are hard to do in one go. For example, we had some difficulty turning on system versioning for an existing table, because we hit an edge case where the generated SQL was erroring.

Benefits of a migration-based approach

Using a migration-based approach could solve these issues:

• The executed SQL is precisely what the developers committed to the repo.
• The same SQL is executed in every environment, increasing confidence that it will work.
• Multiple steps can be applied in a single script. This makes it easy to, for example, add a column with a default and remove that default.

In addition, there are some other nice possibilities, such as being able to easily add reference data to reference tables as part of the scripts.

grate

DbUp is a popular choice for migration-based database development. However, in this post I'm going to focus on a different tool: grate. What I particularly like about grate over DbUp is that it can be installed and run as a dotnet tool, whereas DbUp requires using the Nuget package to write a console app.

There are a lot of other things to like about grate too:

• Not everything is forced into single-execution update scripts. For example there are anytime scripts that are executed whenever they change, useful for stored procedure definitions for example.
• It makes it easy to write scripts targeting specific environments, which might be useful for seeding test data for example.
• It has clear conventions for where scripts should go based on their purpose.

Although grate is relatively new, for years its main developer has been the principal maintainer of RoundhousE, an established tool for migration-based database development (grate was created because RoundhousE is showing its age).

Demo

In a new directory, install grate as a local dotnet tool:

dotnet new tool-manifest
dotnet tool install grate
dotnet tool restore

Add a script called 0001_SettlementType.sql in the ./up directory and run grate.

CREATE TABLE SettlementType (
    ID INT PRIMARY KEY,
    Name NVARCHAR(10)
)

INSERT INTO SettlementType
VALUES
    (1, 'Hamlet'),
    (2, 'Village'),
    (3, 'Town'),
    (4, 'City')

dotnet grate --connectionstring='Data Source=localhost;User=sa;Password=yourStrong(!)Password;Database=grate_test;TrustServerCertificate=True' --silent --version 1.0.0

The database is as expected:

SELECT *
FROM SettlementType

Add another script in the ./up directory called 0002_SettlementType_Code.sql, and run grate again (passing in a new version number via the CLI). The name of the file is important so that grate knows it is intended to be run after the previous one (because it comes later alphabetically).

ALTER TABLE SettlementType
ADD Code CHAR(1) NULL
GO

UPDATE SettlementType
SET Code = LEFT(Name, 1)
GO

ALTER TABLE SettlementType
ALTER COLUMN Code CHAR(1) NOT NULL

dotnet grate --connectionstring='Data Source=localhost;User=sa;Password=yourStrong(!)Password;Database=grate_test;TrustServerCertificate=True' --silent --version 1.1.0

Again, the database has the expected data:

SELECT *
FROM SettlementType

Note that grate has its own tables for keeping track of the scripts that have been run, what type they are and their contents:

Grate has clear conventions it uses to determine which scripts will be run and the order they will be run in, while its use of alphabetical ordering allows you the flexibility to come up with script naming conventions that work for you.

Check out our grate-demo Git repo if you want to see all the code together.

Summary

That was a brief dive into the concept of migration-based database development and grate, one of many available tools to use that approach. I hope you found the post interesting and consider giving grate a try—we'll certainly be experimenting further within Compositional IT.