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 limited number of databases. We can't create new database for each microservice as it is too expensive. How can we solve this problem?
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
- 3rd June, 2022: Initial version