Schema Management with Marten (Why document databases rock)

This blog post describes the preliminary support and thinking behind how we’ll manage schema changes to production using Marten. I’m writing this to further the conversation one of our teams is having about how best to accomplish this. Expect the details of how this works to change after we face real world usages for awhile;)

Some of our projects at work are transitioning from RavenDb to an OSS project I help lead called Marten that uses Postgresql as a fully fledged document database. Among the very large advantages of document databases over relational databases is how much simpler it is to evolve a system over time because it takes so much less mechanical work to keep your document database synchronized to the application code.

Exhibit #1 in the case against relational databases is the need for laboriously tracking database migrations (assuming you give a damn about halfway decent software engineering in regards to your database).

Let’s compare the steps in adding a new property to one of your persisted objects in your system. Using a relational database with any kind of ORM (even if it describes itself as “micro” or “simple”), your steps in some order would be to:

1. Add the new property
2. Add a migration script that adds a new column to your database schema
3. Change your ORM mapping or SQL statements to reflect the new property

Using a document database approach like Marten’s, you’d:

1. Add the new property and continue on with your day

Notice which list is clearly shorter and simpler — not to mention less error prone for that matter.

Marten does still need to create matching schema objects in your Postgresql database, and it’s unlikely that any self-respecting DBA is going to allow your application to have rights to execute schema changes programmatically, so we’re stuck needing some kind of migration strategy as we add document types, Javascript transformations, and retrofit indexes. Fortunately, we’ve got a decent start on doing just that that’s demonstrated below:

 

Just Get Stuff Done in Development!

As long as you have rights to alter your Postgresql database, you can happily set up Marten in one of the “AutoCreate” modes and not worry about schema changes at all as you happily code new features and change existing document types:

var store = DocumentStore.For(_ =>
{
    // Marten will create any new objects that are missing,
    // attempt to update tables if it can, but drop and replace
    // tables that it cannot patch. 
    _.AutoCreateSchemaObjects = AutoCreate.All;


    // Marten will create any new objects that are missing or
    // attempt to update tables if it can. Will *never* drop
    // any existing objects, so no data loss
    _.AutoCreateSchemaObjects = AutoCreate.CreateOrUpdate;


    // Marten will create missing objects on demand, but
    // will not change any existing schema objects
    _.AutoCreateSchemaObjects = AutoCreate.CreateOnly;
});

As long as you’re using a permissive auto creation mode, you should be able to code in your application model and let Marten change your development database as needed behind the scenes.

Patching Production Databases

In the next section, I demonstrate how to dump the entire data definition language (DDL) that matches your Marten configuration as if you were starting from an empty database, but first, I want to focus on how to make incremental changes between production or staging releases.

In the real world, you’re generally not going to allow your application to willy nilly make changes to the running schema and you’ll be forced into this setting:

var store = DocumentStore.For(_ =>
{
    // Marten will not create or update any schema objects 
    // and throws an exception in the case of a schema object
    // not reflecting the Marten confi
guration
    _.AutoCreateSchemaObjects = AutoCreate.None;
});

This leaves us with the problem of how to get our production database matching however we’ve configured Marten in our application code. At this point, our theory is that we’ll use the “WritePatch” feature to generate delta DDL files:

IDocumentStore.Schema.WritePatch(string file);

When this is executed against a configured Marten document store, it will loop through all of the known document types, javascript transforms, the event store usage, and check the configured storage against the actual database. Marten writes two files, one to move your schema “up” to match the configured document store, and a second “drop” file that would rollback your database schema to reverse the changes in the “up” file.

The patching today is able to:

1. Add all new tables, indexes, and functions
2. Detect when a generated function has changed and rebuild it after dropping the old version
3. Determine which indexes are new or modified and generate the necessary DDL to match
4. Add the event store schema objects if they’re active and missing
5. Add the database objects Marten needs for its “Hilo” identity strategy

This is very preliminary, but my concept of how we’ll use this in real life (admittedly with some gaps) is to:

• Use “AutoCreateSchemaObjects = AutoCreate.All” in development and CI and basically not worry at all about incremental schema changes.
• For each deployment to staging or production, we’ll use the WritePatch() method shown above to generate a patch SQL file that will then be committed to Git.
• I’m assuming that the patch SQL files generated by Marten could feed into a real database migration tool like RoundhousE, and we would incorporate RoundhousE into our automated deployments to execute the “up” scripts to the most current database version.

 

 

Dump all the Sql

If you just want a database script that will build all the necessary schema objects for your Marten configuration, you can export either a single file:

// Export the SQL to a file
store.Schema.WriteDDL("my_database.sql");

Or write a SQL file for each document type and functional area of Marten to a directory like this:

// Or instead, write a separate sql script
// to the named directory
// for each type of document
store.Schema.WriteDDLByType("some folder");

In the second usage, Marten also writes a file called “all.sql” that executes the constituent sql files in the correct order just in case you’re using Marten’s support for foreign keys between document types.

The SQL dumps from the two methods shown above will write out every possible database schema object necessary to support your Marten configuration (document types, the event store, and a few other things) including tables, the generated functions, indexes, and even a stray sequence or two.

Relational Databases are the Buggy Whips of Software Development

I think that there’s going to be a day when you tell your children stories about how we built systems against relational databases with ORM’s or stored procedures or hand written SQL and they’re going to be appalled at how bad we had it, much like I did when my grandfather told me stories about ploughing with a horse during the Great Depression.