Embedding Database Migrations with Weasel

A woodworking weasel building a table, of course!

Let’s say that you’re building a system that needs to directly work with a handful of database tables. Or maybe more aptly, you’re building a redistributable class library that will expect to interact with a small number of database tables, functions, view, or sequences — and you’d love to make that class library be responsible for building those database objects as necessary at least at development time so your users can just get to work without any kind of laborious setup before hand.

If you’ve worked with the main “Critter Stack” tools (Marten and Wolverine), you’re familiar with how they can quietly set up your development or even your production database as necessary to reflect your system’s configuration. The actual work of database migrations built into these tools is done by the third member of the “Critter Stack, ” a helper library named Weasel.

You can also use Weasel in your own class library to do the same kind of automatic database migration — as long as you’re using either PostgreSQL or Sql Server (for now).

With Weasel, you can define the requirements for a new database table with a class originally named Table in the Weasel.Postgresql Nuget which exposes an API for just about anything you could do to configure a table including columns, primary keys, foreign key relationships to other tables, and indexes:

        var table = new Table("tables.people");
        table.AddColumn<int>("id").AsPrimaryKey();
        table.AddColumn<string>("first_name");
        table.AddColumn<string>("last_name");

Inside your code, you can at any time migrate the existing database to reflect your Table object with this convenience extension method added in Weasel 7.4:

var table = new Table("tables.people");
table.AddColumn<int>("id").AsPrimaryKey();
table.AddColumn<string>("first_name");
table.AddColumn<string>("last_name");

await using var conn = new NpgsqlConnection("some connection string");
await conn.OpenAsync();

// This will apply any necessary changes to make
// the database reflect the specified table structure
await table.MigrateAsync(conn);

Behind the scenes, Weasel reaches into the database to find the current status — if any — of the specified table. If the table doesn’t exist, Weasel creates it based on the in memory specification. If the table does already exist in the database, Weasel can figure out if there is any “delta” between the expected table from the Table specification and the actual database table. Weasel can issue SQL patches to:

• Add missing columns
• Remove columns in the database that are not part of the specification
• Modify the primary key if necessary
• Add missing indexes
• Remove indexes that are not reflected in the specification
• Deal with foreign keys

And of course, Weasel will do absolutely nothing else if it does not find any differences between the tables.

Likewise, Weasel supports functions and sequences for PostgreSQL. The Weasel.SqlServer has similar support for tables, stored procedures, and custom types (Wolverine uses quite a few user defined table types as an optimization to batch up updates and inserts with its Sql Server integration).

So Weasel definitely isn’t the best documented or visible library in the Critter Stack, but it is useful outside of Marten and Wolverine, and the documentation story might improve dramatically if there’s more demand for that.