Building a database migration tool from scratch - Part 1

Intro

Why? Why the hell not!

One of the things that has always fascinated me as a full-stack developer is the database tooling landscape. For some reason, it's always felt a bit archaic to me. Old UIs, new applications that die after a few seasons (looking at you, Azure Data Studio), plugins to let you perform functionality you'd think would be included in the IDE, and don't even get me started on import and export functionality.

Granted, it's probably my fault for always sticking to MSSQL instead of branching out to something shiny and modern like PostgreSQL (it's on the TODO list).

With that said, migrations have always fallen into the category of things I consider fundamental to the lifecycle of a database. Yet a lot of industry projects I've seen tend to follow very similar patterns. Either you use some form of proprietary software that your developers have to learn the ins and outs of, or you roll out your own novel in-house project that is built in a rush and then lost to time, with the original developer leaving and no one knowing how to work with it.

The third option I've seen is to go with a FOSS solution, but that often only lasts a few months before limitations are reached or frustrations arise due to a lack of support for certain features.

Now, I've been a fan of Redgate's Flyway for a while and have always wanted to get my hands on the paid-tier features (schema generation, drift reports, etc.). Unfortunately, it's not in the budget this year... or the next.

But also we need something ASAP because our current tool was written five years ago, needs an update, and we don't have the source code anymore.

So let's build something that will put proprietary software to shame!

The problem

What are we actually trying to solve?

At its core the problem seems simple. I have a database in state A and i want it to be in state B, then state C and so on. We need this process to be repeatable and predictable and ideally simple to use. Pretty straight forward, right?

Well... Specs change, we introduce bugs and sometimes we may incidentally run a script or two directly on a production server to fix urgent and forget about scripting that change out. Oops...

And what about stored procedures, views, functions, triggers and Schema binds? What about all the other objects we can create within SQL? Do we track them too? Or are we going to limit ourselves to just tables? (boring)

And what about data? There's plenty of times where we might need to drop a column, a table, an index or rename things? Should we stop the process? email a developer? require input for confirmation?

What if i want to keep track of a schema over time instead just migrations files. Keeping track of what the current schema is and being able to see it change over time without having to inspect every commit or migration folder would be good.

Where will we run? do we have users? or are we tooling in a CI/CD process? Maybe we need both incase something goes wrong?

What happens if developers create migrations touching the same object? How is it merged and in what order? Can we neatly tie code for a feature to a migration?

Our solution needs to account for all of these possibilities and or eventualities, while making sure the integrity of the database.

Requirements

With most of the problem established, lets build our specs and features

• Migration identity: Each migration needs a unique identity and predictable execution order.

• Migration tracking: We need to be able to see who or what ran a migration, what scripts did it run, how long it took and ideally what objects might have changed.

• Migration atomicity: Each migration should be atomic. It either all succeeds or the whole thing fails.

• Migration validation: We need a way to validate the SQL before it gets run, and some rules around what we consider to be a valid migration folder, migration file and schema change.

• Controlled execution: We need to be to avoid re-running migrations, select only pending, select by a date range and other selection methods.

• Failure handling: If a migration fails, we need useful error information and a clearly defined strategy for handling the resulting database state.

• Structured logging: We will need a lot of logging to make sure things are working correctly and so that we can trace why things go wrong.

• Configuration: To make some things easier, we should allow users to store connection string, behavioral settings and tweaks.

• Schema view: I want to be able to view a pure schema definition and its changes over time without searching every folder. Something like a git history.

Extra features

The above is what i would consider the foundation to the tool, this is what most FOSS and community editions essentially cover. Below are some of the more advanced features id like to get to:

• Schema drift detection: Compare two schemas and find differences.

• Schema script generation: Automatic migration file generation for comparisons or automatic flow.

• Rollback or recovery flow: Compare schema snapshots and generate a possible path back to an earlier state. This would be a recovery aid, not a guarantee of a safe rollback.

• UI: for developer flow integration

Some of these might turn out to be more complicated than they're worth, while others might become essential once we start using the tool in practice. I know I've probably missed some things too.

For now, the priority is getting a reliable migration engine working before worrying about all the shiny extras.

The libraries

I spent a bit of time doing research and found some useful libraries. Lucky for us Microsoft already provides much of the SQL Server tooling we need, so I don't have to write a parser or build a schema-comparison system from scratch. I have chosen to avoid this, you however may want to own the whole stack so i will list them below so you can look them up and see the complexity of these projects yourself.

The heavy lifters

• Microsoft.SqlServer.DacFx: the same engine behind SSDT and SqlPackage. A .sqlproj builds into a dacpac, which is a complete model of the database. DacFx can compare two dacpacs, or a dacpac against a live database, and spit out the script to get from one to the other. This is the library that gets us the "paid-tier" features: schema generation, drift reports and deploys.

• Microsoft.SqlServer.TransactSql.ScriptDom: Microsoft's T-SQL parser. It turns a script into a syntax tree you can walk with a visitor. We use it to validate SQL before it runs, work out which object a script touches, split generated scripts into one file per object, and safely rewrite scripts on the fly.

• git: not a library, but it's doing a lot of the work. The schema project lives in source control anyway, so instead of storing snapshots ourselves we let git be the schema history.

• git archive pulls the schema project out at any commit without touching your working tree, and git ls-tree tells us which migration folders existed at a given commit. That's what lets us generate a migration (or a rollback) by comparing two commits, no database needed.

Talking to the database

• Microsoft.Data.SqlClient: the official SQL Server driver.

• Dapper: a thin layer over ADO.NET for the journal queries. No ORM, no magic, just SQL I can read.

The CLI

• Spectre.Console.Cli: command parsing, help text, and the pretty tables, spinners and colours in the terminal.

• Serilog: structured logging to the console and to rolling log files, so we can trace why things went wrong after the fact.

• Microsoft.Extensions.*: dependency injection and layered JSON/environment configuration, the same stuff you'd use in an ASP.NET app.

Testing and the IDE

• xUnit v3: regression tests over Core.

• Microsoft.VisualStudio.Extensibility: the new out-of-process VS extension model, used for the tool window.

Project structure

Ill be using C# for this project. I wanted to use C++ or Rust but i couldn't find equivalent SQL graphing or parsing libraries or bindings.

Below is going to be my initial project structure:

• MigDb.Core: the main library that can implemented by whatever fronted we want to wrap it around.

• MigDb.CLI: so that it can be invoked without blocking in CI/CD.

• MigDb.Tests: for regression testing

• MigDb.VSExtension: This is a choice i know.

I want to explain the choice of using VS Extension before i go further. Our previous workflow used Git Hooks more precisely pre-commit hooks. You'd finish your commit and every single time regardless of whether you had database changes or not a form would be triggered where you were asked if database changes were made and you had to respond and fill out if you did.

I wanted to change the workflow to be more passive. If i knew i had database changes i could just click a button on my IDE and i would invoke it. I feel the responsibility should be on the developer to seek the tool instead of it being shoved in their face. So this little extension will be a window that can be pinned to the side or moved around until you need it.

MigDb
├── MigDb.CLI/
├── MigDb.Core/
├── MigDb.Test/
└── MigDb.VSExtension/

Conclusion

So that's the plan. We have a problem that looks simple until you start poking at it, a list of requirements that's probably longer than it should be, and a set of libraries that means we don't have to reinvent the hard parts. DacFx and ScriptDom do the heavy lifting on the SQL side, and git gives us the schema history for free.

The goal isn't to clone Flyway feature for feature. It's to build something small enough that the next developer can pick it up and understand it, but solid enough that we trust it with production databases.

In the next part we will dive deeper into our migration strategy and possible folder structures and how we can solve some of the problem questions.

If youve made it this far, thank you for taking the time to read this and ill catch you in the next one!