Schema per tenant
View as MarkdownAll tenants share one database, and each has its own schema (tenant_acme.Orders, tenant_globex.Orders). There is
one database to back up and connect to, and the schema keeps each tenant's rows apart, so your entities need no tenant
column. Tenantry Core's query filter still applies to an entity that implements ITenantEntity<TKey>, as it does in
shared tables. It works on SQL Server and PostgreSQL. MySQL has no schemas
apart from databases, so use a database per tenant there.
What Tenantry.Pro provides
pro.UseSchemaPerTenant(o => o.GetSchemaName = …): the context that usesUseTenantry()gets the current tenant's schema as its default schema, with a compiled model per schema. YourDbContextnames no schema. With more than one such context, you list those that get it (Which contexts get the schema).pro.AddSchemaProvisioning<TContext>(): creates a tenant's schema when you provision it (see Tenant lifecycle).pro.AddMigrations<TContext>(): applies your EF Core migrations, generated without a schema, to each tenant's schema, with a migration history of its own there (see Tables and migrations).
What you provide
- Tenant resolution and a tenant store via
AddTenantry<TKey>(...)(Tenantry core). - The schema-name convention (
GetSchemaName). - Your
DbContext, registered against the shared database withUseTenantry(). - An explicit tenant-creation flow that provisions each new tenant.
Registration
using Microsoft.EntityFrameworkCore;
using Tenantry;
using Tenantry.Pro;
var connectionString = builder.Configuration.GetConnectionString("AppDb")!;
builder.Services.AddTenantry<string>(tenant =>
{
tenant.ResolveFromHeader("X-Tenant-Id");
tenant.UseStore<MyTenantStore>();
tenant.UsePro(pro =>
{
pro.UseSchemaPerTenant(opts => opts.GetSchemaName = t => $"tenant_{t.TenantId}");
// Optional: provisioning a new tenant creates its schema, in the database AppDbContext connects to.
pro.AddSchemaProvisioning<AppDbContext>();
});
});
// Your DbContext, against the shared database. UseTenantry() is what gives it the tenant's schema.
builder.Services.AddDbContext<AppDbContext>(options => options
.UseSqlServer(connectionString)
.UseTenantry());The context itself needs nothing for the schema:
public sealed class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
public DbSet<Order> Orders => Set<Order>();
}The tenant's schema becomes the model's default schema after OnModelCreating, so it applies to every
table that does not name a schema of its own. Tenants whose GetSchemaName gives the same name share a
schema and a compiled model.
A context used without a current tenant would use the database's default schema, so its first query, command or save
throws TenantNotResolvedException. EF Core fixes the schema when it first builds a context's model, so a context
keeps the schema of the tenant that was current then. Used under another tenant, it throws
TenantIsolationViolationException (Kind is TenantSchemaMismatch). Create a context for each tenant, in its
scope, as requests and ITenantScopeFactory do.
GetSchemaName is called often, so keep it fast, and it must give a tenant the same name every time. A
name that is empty, has control characters or is longer than the database allows (128 characters on SQL
Server, 63 bytes on PostgreSQL, which would otherwise cut it short without an error) fails the tenant's
queries with InvalidOperationException.
SQL Server compares schema names under the database's collation, which by default ignores case, so with string
tenant ids tenant_acme and tenant_ACME are one schema there, and the two tenants share it. PostgreSQL compares
them exactly, as EF Core quotes them. Give each tenant an id that differs from every other in more than case, as
Tenantry Core asks for its query filters
(String tenant ids and the database's collation).
Which contexts get the schema
With one context type that uses UseTenantry(), as above, that context gets the tenant's schema, and there is nothing
to list. With more than one (a second over reference data with ITenantEntity rows, say, which stays in the shared
schema), list those that get it:
pro.UseSchemaPerTenant(o =>
{
o.GetSchemaName = t => $"tenant_{t.TenantId}";
o.Contexts.Add(typeof(AppDbContext)); // and its subclasses; other contexts keep Tenantry's filters only
});Leave the list empty with more than one, and schema per tenant does not guess which of them get the schema: it fails
closed, with an InvalidOperationException that names the context types and asks you to list them in
SchemaPerTenantOptions.Contexts. It throws:
- When the host starts, for the contexts you register with
AddDbContext,AddDbContextFactory, their pooled forms or Tenantry Core'sAddDbContextPerTenantDatabase. The host builds each one's options, without a tenant, before any hosted service starts, so before migrations at startup and before any request. - Before
migrate-tenantsorITenantMigrationRunnermigrates a tenant, the same way, since a deployment step does not start the host. - Otherwise, when the second context's options are built: a context you build by hand, one whose options cannot
be built without a tenant, or one used by a tool that builds your host without starting it, such as
dotnet ef. From then on every schema-per-tenant context of the application throws too.
A context gets the tenant's schema only when its options call UseTenantry(); listing it does not replace that.
The same checks, when the host starts and before a tenant is migrated, throw InvalidOperationException for a
registered context that is listed and does not call it, and, with none listed, when no registered context calls it.
Without the call every tenant would read and write the database's default schema. The checks read the options a
created context has, so a call in OnConfiguring counts, and with none listed two contexts that call it there are
found as the host starts. A context the checks cannot create without a tenant (its constructor or OnConfiguring
needs one) is checked only when Pro creates it for a tenant, to migrate, provision or offboard it; the host logs a
warning that names it (SchemaPerTenantContextNotChecked, Telemetry). A context from
AddDbContextPerTenantDatabase that needs a tenant only to find its database is not logged, as it is always created
for a tenant. An application that registers other contexts and builds its schema-per-tenant context itself lists that
context's type.
A design-time factory (IDesignTimeDbContextFactory) that builds the options without your application's services,
as the samples' does, gets no schema per tenant and is not counted.
A context type that derives from another counts as that one, as it does in the list: a test's
TestAppDbContext : AppDbContext beside AppDbContext needs no list. Two contexts that derive from a common base
class, and not from one another, are two.
How many schemas stay compiled
EF Core compiles a model for each schema, and compiles each query again for each schema's model. Its own
cache holds the models of about 40 schemas (about 100 on EF Core 8), or about 500 compiled queries, in
all: beyond that, it evicts them and compiles them again as tenants take turns, which makes the first
request after each eviction slow. So each context type gets a cache of its own, with room for
MaxCachedSchemas schemas (500 by default) and MaxCompiledQueriesPerSchema compiled queries for each
(100 by default), on top of EF Core's default:
pro.UseSchemaPerTenant(o =>
{
o.GetSchemaName = t => $"tenant_{t.TenantId}";
o.MaxCachedSchemas = 2_000; // at least the schemas in use at once
o.MaxCompiledQueriesPerSchema = 200; // about the distinct queries your application runs
});Set them within your memory budget: each cached schema holds a compiled model and its compiled queries. Past
the limits, the least recently used entries are compiled again when next needed. The cache belongs to EF
Core's internal service provider for the context type, which every application in the process shares, so
UseMemoryCache, or replacing EF Core's IModelCacheKeyFactory or IMemoryCache with ReplaceService, on
these contexts' options is refused with InvalidOperationException: either would undo the cache, or let
tenants share a model. Each context type that uses UseTenantry() gets an internal service provider of its
own (and one more for each different pair of limits); EF Core throws once a process has built more than 20. It
builds one for each set of options, and the same options set in another order are another set. So in a test suite
that builds contexts by hand, set EF Core's own options first, as AddDbContext does (with
new DbContextOptionsBuilder<AppDbContext>().EnableServiceProviderCaching(), say), then the provider, then
UseTenantry(). A suite that needs more than 20 sets of options turns the error into a log entry with
ConfigureWarnings(w => w.Ignore(CoreEventId.ManyServiceProvidersCreatedWarning)) on its contexts.
The contexts that get the tenant's schema cannot be pooled. A pooled context keeps the model it was first built with, so every tenant
that leased it would query the first tenant's schema. A context that uses UseTenantry() and is registered
with AddDbContextPool, AddPooledDbContextFactory or Tenantry Core's AddDbContextPerTenantDatabase with
pooled: true throws InvalidOperationException; use AddDbContext or AddDbContextFactory.
Provisioning a new tenant
AddSchemaProvisioning<TContext>() adds creating the tenant's schema to tenant provisioning, as its first
step (CreateSchema). Add the tenant to your store, then provision it with ITenantProvisioner<TKey>:
public sealed class TenantAdminService(ITenantProvisioner<string> provisioner)
{
public async Task<bool> CreateAsync(ITenantDescriptor<string> tenant, CancellationToken ct) =>
(await provisioner.ProvisionAsync(tenant, ct)).Succeeded; // CREATE SCHEMA unless it exists, then your steps
}The step creates the schema with EF Core's own migrations SQL for the provider, against the database
TContext connects to in the tenant's scope. It checks for the schema first, so provisioning a tenant
again does nothing, and two runs for one tenant at once both succeed. The context's credentials must be
allowed to create schemas; if your application's are not, give provisioning a context of its own with
o.CreateContext:
pro.AddSchemaProvisioning<AppDbContext>(o => o.CreateContext = sp =>
new AppDbContext(new DbContextOptionsBuilder<AppDbContext>().UseSqlServer(adminConnectionString).Options));On a provider other than SQL Server or PostgreSQL the step fails with NotSupportedException.
AddSchemaProvisioning without UseSchemaPerTenant stops the application from starting.
To drop the tenant's schema, with its tables, when it leaves, add pro.AddSchemaDeprovisioning<TContext>() and call
ITenantDeprovisioner<TKey>: it refuses a schema another tenant has. See
Offboarding a tenant.
Tables and migrations
pro.AddMigrations<TContext>() applies your migrations to each tenant's schema: as the Migrations provisioning
step, right after CreateSchema, and for every tenant from the migration runner:
tenant.UsePro(pro => pro
.UseSchemaPerTenant(o => o.GetSchemaName = t => $"tenant_{t.TenantId}")
.AddSchemaProvisioning<AppDbContext>() // CREATE SCHEMA, unless it exists
.AddMigrations<AppDbContext>()); // then the migrations, in that schemaGenerate the migrations as usual, with dotnet ef migrations add. The tools create the context without a tenant, so
its model has no schema and neither do the migrations or the snapshot. When they are applied for a tenant, every
table, index, key and sequence they name without a schema gets the tenant's. The migration history table keeps its
name (__EFMigrationsHistory, or MigrationsHistoryTable if you set one) and is the one in the tenant's schema, so
each schema records its own migrations. EF Core 9 and later compare the snapshot with the model before migrating;
Tenantry gives the snapshot the tenant's schema too, so they match.
- Keep the schema out of the model: no
HasDefaultSchema, and no schema inToTablefor the tenant tables. A schema a migration names is kept, so that table would stay where it names. - SQL you add with
migrationBuilder.Sql(...)is applied as written: names in it are not put in the tenant's schema, so unqualified ones resolve to the database's default. Keep tenant migrations to EF Core's operations. dotnet ef database updatehas no tenant, so it throwsTenantNotResolvedException. Migrate tenants with the runner, as a deployment step (app.RunTenantMigrationsIfRequestedAsync(args), see Tenant migrations).
The SchemaPerTenant samples do this end to end.
Limitations
- EF Core holds one compiled model per schema in memory, up to
MaxCachedSchemasper context type (above). - The contexts that get the tenant's schema cannot be pooled.
See also
- Database providers · Mixed mode
- Tenant lifecycle: provision a schema, migrate it and seed it in one call.
- Tenant migrations: migrate every tenant's schema.