Relational Schema Management
Creating and evolving the schema on PostgreSQL and SQL Server
💡 MongoDB users can skip this page. Mongo is schemaless — it creates collections and indexes lazily through its own provisioners and uses neither mode below.
Two Modes
The relational backends support two ways of creating and evolving their schema. Both use the same entities and the same shared EF model — only how the DDL reaches the database differs.
| Mode | Database:UseMigrations |
How schema is created |
|---|---|---|
| Model-driven (default) | false / unset |
GenerateCreateScript() lays down the current model's
tables and indexes. No history is kept.
|
| EF migrations | true |
Database.Migrate() applies the migration files in the provider
projects, tracked in __EFMigrationsHistory.
|
Model-driven only lays down tables on an empty schema and silently skips every later model change, so it suits development and first deploys — not a live database you intend to evolve.
Where Migrations Live
Migrations are provider-specific — Npgsql and SQL Server emit different DDL — and there is one set per DbContext, so there are four folders:
Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Postgres/Migrations/Platform
Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Postgres/Migrations/Tenant
Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Sqlserver/Migrations/Platform
Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Sqlserver/Migrations/Tenant
The shared contexts are built at design time by the factories in
Services/BlazorBlueprint.DbMigrator/Migrations/MigrationsDesignTime.cs. The
DbMigrator is the startup project for the tooling because it references
both provider projects and every plugin, so a generated migration includes all
plugin tables. The provider is chosen by the BB_MIGRATIONS_PROVIDER
environment variable.
What Adding a Plugin Costs
Plugins ship no migrations of their own. A plugin's entities are folded
into the shared contexts by PluginEntityModelContributor, so its tables
appear inside the host's migration as plugin_{plugin}_{Entity}.
This is deliberate. EF Core permits exactly one migrations assembly per DbContext, so a
plugin could only own its migrations by owning a DbContext — which would mean a separate
__EFMigrationsHistory per plugin per tenant, and a plugin that forgot the
tenancy interceptor silently writing every tenant's data into the platform schema. One
shared context with one version per tenant is the trade.
| Backend / mode | Adding a plugin | Changing a plugin's schema |
|---|---|---|
| MongoDB | drop in + restart | drop in + restart |
| Relational, model-driven | drop in + restart, plus the sweep for existing organisations | drop in + restart |
| Relational, EF migrations | drop in + restart, plus the sweep | regenerate migrations |
A restart alone only reaches organisations provisioned after it. New
organisations get the plugin's tables from the create script either way; reaching the
existing ones needs Database:AllowPluginSchemaDropIn plus
the sweep.
⚠️ A plugin's assembly must be named BlazorBlueprint.Plugins.*
for its tables to be created at all. The host loads plugins by what they implement,
not by filename, so a differently-named assembly runs happily — routes, services,
admin pages — while the model contributor skips its entities and the relational
backends create nothing for it, with no error. Mongo is unaffected. Name the assembly
accordingly, or the first query against the plugin fails on a missing relation.
The bottom-right cell surprises people. Under Database:UseMigrations=true,
adding a plugin entity the committed migrations don't know about makes the runtime model
differ from the snapshot, and Database.Migrate() throws
PendingModelChangesWarning — the host will not boot until
migrations are regenerated. That is the guard working: it is the same check that catches a
forgotten regeneration after any model change.
Dropping a Plugin Into a Running Deployment
Database:AllowPluginSchemaDropIn=true (default false) lets a
plugin be added to a running relational deployment by dropping its DLL in
and restarting — no rebuild, no regeneration — matching how plugins already behave on
MongoDB. When set:
- the runtime drift check is relaxed, since the model legitimately has entities the migrations predate;
-
provisioning runs an additive pass after
Migrate(), creating whatever tables the migrations didn't. Safe because the model declares no foreign keys and noALTERs, so its create script is nothing butCREATE TABLE/CREATE INDEX, each rewritten toIF NOT EXISTS; - existing tenants are reached through the sweep — run the DbMigrator,
or set
Database:MigrateTenantsOnStartup=trueon the BackgroundWorker.
⚠️ The plugin DLL must be visible to whichever process runs the sweep.
The DbMigrator and the BackgroundWorker build the model from the plugin assemblies they
can load — their own directory and a Plugins/ subtree beneath it. Dropping
the DLL only into the Web host's Plugins/ folder leaves
the migrator with a plugin-less model, so its additive pass creates nothing and it
still reports every tenant current and exits 0. Copy the plugin alongside each host
that needs it.
It creates tables; it cannot ALTER one. So it installs a
plugin, but does not upgrade a plugin whose own schema changed between versions — that
still needs a regeneration. Leave the flag off in your own repository and
in CI, where the drift check is the real guard and this flag would hide a genuinely
forgotten regeneration.
Generating and Regenerating
Requires EF tools matching EF Core 10:
dotnet tool update --global dotnet-ef --version 10.*
PG=Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Postgres
MS=Infrastructure/BlazorBlueprint.Infrastructure.Persistence.Sqlserver
SP=Services/BlazorBlueprint.DbMigrator # EF design-time startup project
# PostgreSQL
BB_MIGRATIONS_PROVIDER=postgres dotnet ef migrations add <Name> --project $PG --startup-project $SP --context PlatformDbContext -o Migrations/Platform
BB_MIGRATIONS_PROVIDER=postgres dotnet ef migrations add <Name> --project $PG --startup-project $SP --context TenantDbContext -o Migrations/Tenant
# SQL Server
BB_MIGRATIONS_PROVIDER=sqlserver dotnet ef migrations add <Name> --project $MS --startup-project $SP --context PlatformDbContext -o Migrations/Platform
BB_MIGRATIONS_PROVIDER=sqlserver dotnet ef migrations add <Name> --project $MS --startup-project $SP --context TenantDbContext -o Migrations/Tenant
Or use scripts/regen-migrations.sh, which deletes the four folders and
regenerates a clean InitialCreate for both providers across both contexts in
one go. Run it whenever you add a plugin — a dropped-in plugin's tables
only reach a relational deployment once its migration is generated.
Before launch, the committed InitialCreate migrations are a
starting point that goes stale as the model changes. Either keep regenerating them, or
ignore them entirely while UseMigrations stays false. Once the
model is frozen, generate a clean set, commit it, and switch migrations on.
Switching Migrations On
Set Database:UseMigrations=true (or
Database__UseMigrations=true). Then:
- Platform — the backend activator runs
Database.Migrate()on boot. Postgres pins the platform history to the platform schema; each tenant's history lives in its own schema (Postgres) or database (SQL Server), so tenants track their migration state independently. - New tenants — the provisioner ensures the schema exists, then migrates it to the latest.
- Existing tenants — applied at deploy time by the DbMigrator, which is idempotent (only pending migrations run). Deliberately not auto-run on every boot: for a large tenant count that would be a long unattended sweep.
The DbMigrator
Services/BlazorBlueprint.DbMigrator is a one-shot console app that applies
schema before the app takes traffic. It migrates the platform database
then every existing tenant, reconciles indexes on MongoDB, and exits 0 on
success, non-zero if any tenant failed — fail-loud, so a deploy gate blocks
rather than shipping an un-migrated fleet.
It applies schema only — data seeding stays app-side — and needs no Data Protection or Redis, because it never reads an encrypted value.
In production
The relational compose files run it as a one-shot
blazorblueprint-dbmigrator service; every app service gates on it via
depends_on: { condition: service_completed_successfully }. In
Kubernetes, run it as an init-container or Job that must complete before the rollout.
Give it a DDL-privileged connection; the app can run with a
lower-privileged one.
Emitting SQL instead of applying
For Flyway, DbUp or plain psql, --emit-sql writes idempotent
migration SQL to ./sql/ without opening a database connection. Apply
tenant.sql once per tenant schema or database.
⚠️ In the shipped container you must redirect the output. Every
compose service runs the image read_only: true under a non-root user, so
/app — and therefore the default ./sql — is immutable:
docker run --rm -v ./sql:/sql blazorblueprint-dbmigrator:latest --emit-sql --out /sqlWithout it the write fails; the migrator reports the reason and this command, and exits 1.
Runtime backstop
TenantSchemaVersionMiddleware returns a clean 503 "under maintenance" for any
tenant whose schema is still behind the app's migrations — one the migrator missed, or a
tenant created mid-rolling-deploy — instead of a raw SQL error. Cached, fail-open, inert
on Mongo and in model-driven mode. Kill switch:
Database:SchemaVersionGate:Enabled.
Expand/contract
Migrations must be additive — no dropping or renaming a column or table in
Up() — so a rolling deploy cannot break old app instances still serving
after the migrator ran. CI enforces this; defer drops to a later release.
Verified Against a Real Database
The integration suite runs this whole pipeline against a real PostgreSQL instance:
platform and tenant Migrate(), __EFMigrationsHistory landing in
the right schema (the platform schema and each tenant's own, never public),
and data round-trips.