Single Database for Multiple Microservices with FluentMigrator

If you have multiple microservices, it is common to use a separate database for each of them. But recently, we faced the following problem. Our price plan on database hosting provider includes only a limited number of databases. We can't create a new database for each microservice as it is too expensive. How can we solve this problem?

High-level Approach

In this article, I'll use SQL Server as my database. In general, the solution is very simple. All microservices will use the same database. But how can we be sure that there will be no conflicts? We will use schemas. Each microservice will create database objects (tables, views, stored procedures, ...) only in some particular database schema which is unique across all microservices. In order to avoid problems with access to data of another microservice, we'll create a separate login and user and give them rights only for one schema.

For example, for microservice to work with orders, we can do it like this:

CREATE LOGIN [orders_login] WITH PASSWORD='p@ssw0rd'

execute('CREATE SCHEMA [orders]')

CREATE USER [orders_user] FOR LOGIN [orders_login] WITH DEFAULT_SCHEMA=[orders]

GRANT CREATE TABLE to [orders_user]
GRANT ALTER,DELETE,SELECT,UPDATE,INSERT,REFERENCES ON SCHEMA :: [orders] to [orders_user]

Now we are ready to create database objects.

FluentMigrator

I will use FluentMigrator NuGet package to modify structure of my database. It is very simple to use. First configure it:

var serviceProvider = new ServiceCollection()
    .AddFluentMigratorCore()
    .ConfigureRunner(
        builder =>
        {
            builder
                .AddSqlServer2016()
                .WithGlobalConnectionString(connectionString)
                .ScanIn(typeof(Database).Assembly).For.Migrations();
        })
    .BuildServiceProvider();

Here, we use SQL Server 2016 or later. The connectionString variable contains connection string to our database. Type Database can be any type inside an assembly with your migrations. Wait! But what are migrations?

This is how we describe changes we want to make to our database. Each migration is a simple class that inherits Migration:

[Migration(1)]
public class FirstMigration : Migration
{
    public const string TableName = "orders";   

    public override void Up()
    {
        Create.Table(TableName)
            .WithColumn("id").AsInt32().PrimaryKey().Identity()
            .WithColumn("code").AsString(100).NotNullable();
    }

    public override void Down()
    {
        Delete.Table(TableName);
    }
}

Inside Up and Down methods, you describe what you want to do on applying and rollbacking the migration. Attribute Migration contains a number which specifies order in which your migrations will be applied.

Now, it is very simple to apply your migrations to a database:

var runner = serviceProvider.GetRequiredService<IMigrationRunner>();

runner.MigrateUp();

That's all. All your migrations must be applied to the database now. FluentMigrator will also create VersionInfo table that contains information about all currently applied migrations. With the help of this table, FluentMigrator will know next time which migrations should be additionally applied to the database.

Unfortunately, it does not work that way for our use case. There are two problems.

First of all, VersionInfo table is created in the dbo schema by default. But it is unacceptable for us. Each microservice must have its own VersionInfo table inside its own schema.

The second problem is the following. Consider this code of a migration:

Create.Table("orders")

Unfortunately, this code creates table orders also inside dbo schema. Of course, we can specify schema explicitly:

Create.Table("orders").InSchema("orders")

But I'd prefer to avoid this. Somebody will forget to write this schema and we may have an error. I'd like to replace default schema for an entire microservice.

Schema for VersionInfo Table

It is very easy to set custom schema for VersionInfo table:

var serviceProvider = new ServiceCollection()
    .AddSingleton<IConventionSet>(new DefaultConventionSet("orders", null))
    .AddFluentMigratorCore()
    .ConfigureRunner(
        builder =>
        {
            builder
                .AddSqlServer2016()
                .WithGlobalConnectionString(connectionString)
                .ScanIn(typeof(Database).Assembly).For.Migrations();
        })
    .BuildServiceProvider();

Here, we just register a new instance of DefaultConventionSet class for IConventionSet interface with corresponding schema. Now our VersionInfo table will be created inside orders schema.

Default Schema for Database Objects

Unfortunately, it is not so easy to understand how we can replace default schema for other database objects. It took me some time. Let's start from the code of AddSqlServer2016 method. It registers instance of SqlServer2008Quoter class. This class inherits QuoteSchemaName method from SqlServer2005Quoter class. Here, you can see where the default schema comes from.

We'll replace this Quoter class with our own:

sealed class Quoter : SqlServer2008Quoter
{
    private readonly string _defaultSchemaName;

    public Quoter(string defaultSchemaName)
    {
        if (string.IsNullOrWhiteSpace(defaultSchemaName))
            throw new ArgumentException("Value cannot be null or whitespace.", 
                                         nameof(defaultSchemaName));
        _defaultSchemaName = defaultSchemaName;
    }           

    public override string QuoteSchemaName(string schemaName)
    {
        if (string.IsNullOrEmpty(schemaName))
            return $"[{_defaultSchemaName}]";
        return base.QuoteSchemaName(schemaName);
    }
}

As you can see, it is very simple. Implementation is almost the same as in SqlServer2005Quoter class, but instead of dbo, we use our custom schema.

Now we just must register this class:

var serviceProvider = new ServiceCollection()
    .AddSingleton<IConventionSet>(new DefaultConventionSet("orders", null))
    .AddFluentMigratorCore()
    .ConfigureRunner(
        builder =>
        {
            builder
                .AddSqlServer2016()
                .WithGlobalConnectionString(connectionString)
                .ScanIn(typeof(Database).Assembly).For.Migrations();

            builder.Services.RemoveAll<SqlServer2008Quoter>()
                .AddSingleton<SqlServer2008Quoter>(new Quoter("orders"));
        })
    .BuildServiceProvider();

And everything works fine as we've expected.

Conclusion

I hope this article is useful for you. It was surprisingly hard to understand how to change default schema for database objects. I hope I saved you some time. Good luck!

If you like my articles, you can read more in my blog.

History

• 3^rd June, 2022: Initial version