Introducing Weasel for Database Development

An unheralded, but vital foundational piece of the “Critter Stack” is the Weasel family of libraries that both Marten and Wolverine use quite heavily for a range of database development utilities. For the moment, we have Weasel packages with similar functionality for PostgreSQL and Sql Server.

We’re certainly not opposed to adding other database engines like MySQL or even Oracle, but those two databases were the obvious places to start.

I’m just giving a little bit of an overview of some of the functionality in the Weasel libraries.

Extension Methods for Less Painful ADO.Net

The “Back to the Future” aspect of working so heavily with first Marten, then database centric features in Wolverine has been doing a lot of low level ADO.Net development after years of more or less relying on ORMs. At one point in the late 00’s I had a quote in my blog something to the effect of:

Even when you need to have finer grained control over SQL generation in your codebase, I think you’re maybe a little better off at least using a micro-ORM like Dapper.

ADO.Net has a very tedious API out of the box, so Weasel alleviates that with quite a few extension methods to make your code a little quicker to write and hopefully much easier to read later as well.

Here’s a sample method from the Sql Server-backed node tracking from the Wolverine codebase that shows off several Weasel utility extension methods:

    public async Task RemoveAssignmentAsync(Guid nodeId, Uri agentUri, CancellationToken cancellationToken)
    {
        await using var conn = new SqlConnection(_settings.ConnectionString);

        // CreateCommand is an extension method in Weasel
        await conn.CreateCommand($"delete from {_assignmentTable} where id = @id and node_id = @node")
            .With("id", agentUri.ToString())
            .With("node", nodeId)
            
            // Also an extension method in weasel that opens the connection,
            // executes the command, and closes the connection in sequence
            .ExecuteOnce(cancellationToken);
    }

This isn’t particularly very innovative, and I’ve seen several other one off libraries where folks have done something very similar. I still like having these methods though, and I appreciate these utilities not being copy and pasted between Marten, Weasel, and other work.

Batched Commands

I don’t want to oversimplify things too much, but in the world of enterprise software development the one of the most common sources of poor performance is being too chatty between technical layers as network round trips can be very expensive. I did a lot of experimentation very early on in Marten development, and what we found quite clearly was that there was a massive performance benefit in batching up database commands and even queries to the database.

Weasel has a utility called CommandBuilder (there’s one for Sql Server, one for PostgreSQL, and a third flavor that targets the generic DbCommand abstractions) that we use quite heavily for building batched database queries. Here’s a usage from the PostgreSQL backed node management code in Wolverine:

        await using var conn = new NpgsqlConnection(_settings.ConnectionString);
        await conn.OpenAsync(cancellationToken);

        var builder = new CommandBuilder();
        var nodeParameter = builder.AddNamedParameter("node", nodeId, NpgsqlDbType.Uuid);

        foreach (var agent in agents)
        {
            var parameter = builder.AddParameter(agent.ToString());
            builder.Append(
                $"insert into {_assignmentTable} (id, node_id) values (:{parameter.ParameterName}, :{nodeParameter.ParameterName}) on conflict (id) do update set node_id = :{nodeParameter.ParameterName};");
        }

        await builder.ExecuteNonQueryAsync(conn, cancellationToken);


        await conn.CloseAsync();

Behind the scenes, CommandBuilder is using a StringBuilder to more efficiently append strings for what eventually becomes the data for a DbCommand.CommandText. It’s also helping to build as many database parameters as you need with the pattern “p0, p1, p#” as well as letting you use shared, named parameters.

Database Schema Management

A crucial feature in both Marten and Wolverine is the ability to quietly put your backing database into the proper, configured state that your application requires. This part of Weasel is a little more involved than I have ambition to adequately demonstrate here, but here’s a taste. In Wolverine’s new Sql Server messaging transport, there’s a separate table for each named queue to track scheduled messages that’s configured in code like this:

using Weasel.Core;
using Weasel.SqlServer.Tables;
using Wolverine.RDBMS;

namespace Wolverine.SqlServer.Transport;

internal class ScheduledMessageTable : Table
{
    public ScheduledMessageTable(DatabaseSettings settings, string tableName) : base(
        new DbObjectName(settings.SchemaName, tableName))
    {
        AddColumn<Guid>(DatabaseConstants.Id).AsPrimaryKey();
        AddColumn(DatabaseConstants.Body, "varbinary(max)").NotNull();
        AddColumn(DatabaseConstants.MessageType, "varchar(250)").NotNull();
        AddColumn<DateTimeOffset>(DatabaseConstants.ExecutionTime).NotNull();
        AddColumn<DateTimeOffset>(DatabaseConstants.KeepUntil);
        AddColumn<DateTimeOffset>("timestamp").DefaultValueByExpression("SYSDATETIMEOFFSET()");
        
        // Definitely want to index the execution time. Far more reads than writes. We think. 
        Indexes.Add(new IndexDefinition($"idx_{tableName}_execution_time")
        {
            Columns = new string[]{DatabaseConstants.ExecutionTime}
        });
    }
}

What you see above is the support for database tables in Sql Server. This model helps the critter stack tools be able to make database migrations on the fly, including:

• Building missing tables
• Creating missing database schemas
• Adding additional columns that are part of the configured table model, but not present in the database (Marten uses this quite heavily, and this all originally came out of early Marten)
• Removing columns that are in the existing database table, but don’t exist in the configuration
• Adding, removing, or modifying indexes to make the database reflect the configured table model (this has been a permutation hell undertaking and a frequent source of bugs over time with Weasel)

The schema management and migration subsystem of Weasel also supports change management of functions, stored procedures, and PostgreSQL sequences or extensions. This model also underpins all of Marten’s database command line management in the Marten.CommandLine package (but all of it is completely available in Weasel.CommandLine as well to support Wolverine).

The command line support adds command line options to your .NET application to:

• Generate database schema creation scripts
• Create database migration files including rollback scripts by comparing the existing database to the configured database schema objects in your system
• Applying all required database changes on demand