United Kingdom: +44 (0)208 088 8978

Migration-based database development

Matt discusses migration-based database development and grate, a dotnet tool to make it easy.

We're hiring Software Developers

Click here to find out more

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.


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:

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).


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 (
    Name NVARCHAR(10)

INSERT INTO SettlementType
    (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:

FROM SettlementType
ID Name
1 Hamlet
2 Village
3 Town
4 City

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

UPDATE SettlementType
SET Code = LEFT(Name, 1)

ALTER TABLE SettlementType
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:

FROM SettlementType
ID Name Code
1 Hamlet H
2 Village V
3 Town T
4 City C

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

A screenshot from Azure Data Studio, showing tables in the grate_test database including some in the grate namespace

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.


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.