Proposed Marten Tooling for Database Management

This is an update to an earlier post on schema management using Marten.

At this point, I think the biggest challenges facing us at work for using Marten are strictly in the realm of database change management. To that end, we’re adding what will be a new package for command line tooling around Marten schema management and investigating possible usage of Sqitch to handle database migrations in our ecosystem. The command line usage shown in this post is in Marten master, but not pushed up to Nuget in any way yet. The Sqitch usage here is purely hypothetical.

When you’re using Marten, all the data definition language (DDL) for the underlying Postgresql database is generated to match by code within Marten. In development, you’d just run with the setting to auto-create database objects on the fly to match the code for faster iteration. For production deployment, however, you probably don’t get to do that and you’ll need some kind of database migration strategy to get the changes that your Marten application needs to the real database. That’s the gap that this post is trying to fill.

 

Command Line Tooling

My concept for supporting command line tooling suitable for build automation at this point is to publish a new library package called Marten.CommandLine that you can use to expose your own application and database through the command line. To use this tooling, follow these steps:

1. Create a new console application in your solution
2. Add the forthcoming Marten.CommandLine nuget
3. Add a reference to the projects in your system that would express the configuration for your Marten-enabled application
4. In the “Main()” entry point of your new console application, add code like this below to build up your Marten configuration via the StoreOptions class and then delegate to Marten to parse the command line arguments and execute the proper command:

    public class Program
    {
        public static int Main(string[] args)
        {
            var options = buildStoreOptions();

            return MartenCommands.Execute(options, args);
        }

        private static StoreOptions buildStoreOptions()
        {
            // build your own StoreOptions that 
            // establishes the configuration of your
            // Marten application
        }
    }

You can see an example of building the console application from the SampleConsoleApp project I used in the Marten codebase to test this functionality.

Once you have the code above, you’re actually ready to go. If you’re using the new dotnet CLI, running “dotnet run” in the root of your console application project yields this output listing the valid commands:

------------------------------------------------------------------------------------------------------------------------------------

  Available commands:
------------------------------------------------------------------------------------------------------------------------------------

   apply -> Applies all outstanding changes to the database based on the current configuration
  assert -> Assert that the existing database matches the current Marten configuration
    dump -> Dumps the entire DDL for the configured Marten database
   patch -> Evaluates the current configuration against the database and writes a patch and drop file if there are any differences
------------------------------------------------------------------------------------------------------------------------------------

 

If you’re not using the dotnet CLI yet, you’d just need to compile your new console application like you’ve always done and call the exe directly. If you’re familiar with the *nix style of command line interfaces ala Git, you should feel right at home with the command line usage in Marten.

For the sake of usability, let’s say that you stick a file named “marten.cmd” (or the *nix shell file equivalent) at the root of your codebase like so:

dotnet run --project src/MyConsoleApp %*

All the example above does is delegate any arguments to your console application. Once you have that file, some sample usages are shown below:

# Assert that the database matches the current database. This
# command will fail if there are differences
marten assert --log log.txt

# This command tries to update the database
# to reflect the application configuration
marten apply --log log.txt

# This dumps a single file named "database.sql" with 
# all the DDL necessary to build the database to
# match the application configuration
marten dump database.sql

# This dumps the DDL to separate files per document
# type to a folder named "scripts"
marten dump scripts --by-type

# Create a patch file called "patch1.sql" and
# the corresponding rollback file "patch.drop.sql" if any
# differences are found between the application configuration
# and the database
marten patch patch1.sql --drop patch1.drop.sql

In all cases, the commands expose usage help through “marten help [command].” Each of the commands also exposes a “–conn” (or “-c” if you prefer) flag to override the database connection string and a “–log” flag to record all the command output to a file.

 

My Current Thinking about Marten + Sqitch

Our team doing the RavenDb to Marten transition work has turned us on to using Sqitch for database migrations. From my point of view, I like this choice because Sqitch just uses script files in whatever the underlying database’s SQL dialect is. That means that Marten can use our existing “WritePatch()” schema management to tie into Sqitch’s migration scheme.

The way that I think this could work for us is first to have a Sqitch project established in our codebase with its folders for updates, rollbacks, and verify’s. In our build script that runs in our master continuous integration (CI) build, we would:

1. Call sqitch to update the CI database (or whatever database we declare to be the source of truth) with the latest known migrations
2. Call the “marten assert” command shown above to detect if there are outstanding differences between the application configuration and the database by examining the exit code from that command
3. If there are any differences detected, figure out what the next migration name would be based on our naming convention and use sqitch to start a new migration with that name
4. Run the “marten patch” command to write the update and rollback scripts to the file locations previously determined in steps 2 & 3
5. Commit the new migration file back to the underlying git repository

I’m insisting on doing this on our CI server instead of making developers do it locally because I think it’ll lead to less duplicated work and fewer problems from these migrations being created against work in progress feature branches.

For production (and staging/QA) deployments, we’d just use sqitch out of the box to bring the databases up to date.

I like this approach because it keeps the monotony of repetitive database change tracking out of our developer’s hair, while also allowing them to integrate database changes from outside of Marten objects into the database versioning.