Marten and the PostgreSQL Schema

Marten works by adding tables and functions (yes, Virginia, we've let stored procedures creep back into our life) to a PostgreSQL schema. Marten will generate and add a table and matching upsert function for each unique document type as needed. It also adds some other tables and functions for the event store functionality and HiLo id generation

In all cases, the Marten schema objects are all prefixed with mt_.

As of Marten v0.8, you have much finer grained ability to control the automatic generation or updates of schema objects through the StoreOptions.AutoCreateSchemaObjects like so:

var store = DocumentStore.For(opts =>
{
    // 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.
    opts.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
    opts.AutoCreateSchemaObjects = AutoCreate.CreateOrUpdate;

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

    // Marten will not create or update any schema objects
    // and throws an exception in the case of a schema object
    // not reflecting the Marten configuration
    opts.AutoCreateSchemaObjects = AutoCreate.None;
});

^{snippet source | anchor}

To prevent unnecessary loss of data, even in development, on the first usage of a document type, Marten will:

1. Compare the current schema table to what's configured for that document type
2. If the table matches, do nothing
3. If the table is missing, try to create the table depending on the auto create schema setting shown above
4. If the table has new, searchable columns, adds the new column and runs an "UPDATE" command to duplicate the information in the JsonB data field. Do note that this could be expensive for large tables. This is also impacted by the auto create schema mode shown above.

Our thought is that in development you probably run in the "All" mode, but in production use one of the more restrictive auto creation modes.

As of Marten v0.9.2, Marten will also check if the existing upsert function and any table indexes match what is configured in the document store, and attempts to update these objects if necessary based on the same All/None/CreateOnly/CreateOrUpdate rules as the table storage.

Overriding Schema Name

By default marten will use the default public database scheme to create the document tables and function. You may, however, choose to set a different document store database schema name, like so:

If you run code before schema changes and using opts.GeneratedCodeMode = TypeLoadMode.Auto; (by yourself or by OptimizeArtifactWorkflow() in dev env) schema won't change. You need to delete Internal folder manually to force regenerating code and schema changes. :::

StoreOptions.DatabaseSchemaName = "other";

The Hilo sequence table is always created in this document store database schema.

If you wish to assign certain document tables to different (new or existing) schemas, you can do so like that:

StoreOptions.Schema.For<User>().DatabaseSchemaName("other");

^{snippet source | anchor}

This will create the following tables in your database: other.mt_doc_user, overriden.mt_doc_issue and public.mt_doc_company. When a schema doesn't exist it will be generated in the database.

Event Store

The EventStore database object are by default created in the document store DatabaseSchemaName. This can be overridden by setting the DatabaseSchemaName property of the event store options.

StoreOptions.Events.DatabaseSchemaName = "event_store";

This will ensure that all EventStore tables (mt_stream, mt_events, ...) and functions (mt_apply_transform, mt_apply_aggregation, ...) are created in the event_store schema.

Create Database

WARNING

You will probably need to use the AddMarten().ApplyAllDatabaseChangesOnStartup() option to force Marten to check and build additional databases on start up times by registering an IHostedService into your system that will run on startup.

Marten can be configured to create (or drop & create) databases in case they do not exist. This is done via store options, through StoreOptions.CreateDatabasesForTenants. Note that this functionality is only available by bootstrapping Marten as part of an IHost and does not execute by merely building a DocumentStore. This change was made in V7 so the database connectivity could run with asynchronous code. You might need to add Persist Security Info Property=true to your connection string if there is any issue with PostgreSQL claiming that it is missing a password.

var maintenanceConnectionString = ConnectionSource.ConnectionString;
var applicationConnectionString = "";
var builder = Host.CreateApplicationBuilder();
builder.Services.AddMarten(options =>
{
    // This might be different than the maintenance connection string
    options.Connection(applicationConnectionString);

    options.CreateDatabasesForTenants(c =>
    {
        // Specify a db to which to connect in case database needs to be created.
        // If not specified, defaults to 'postgres' on the connection for a tenant.
        c.MaintenanceDatabase(maintenanceConnectionString);
        c.ForTenant()
            .CheckAgainstPgDatabase()

            .WithOwner("postgres")
            .WithEncoding("UTF-8")
            .ConnectionLimit(-1);
    });
});

using var host = builder.Build();

// NOTE: The new database will only be built upon the call to IHost.StartAsync()
await host.StartAsync();

^{snippet source | anchor}

Databases are checked for existence upon store initialization. By default, connection attempts are made against the databases specified for tenants. If a connection attempt results in an invalid catalog error (3D000), database creation is triggered. ITenantDatabaseCreationExpressions.CheckAgainstPgDatabase can be used to alter this behavior to check for database existence from pg_database.

Note that database creation requires the CREATEDB privilege. See PostgreSQL CREATE DATABASE documentation for more.