EF Core 2.x Migrations with one DbContext but multiple Databases

[nzdjigit]

Hi,

I've been looking everywhere for an answer, but can't seem to find any.

We are developing a multi-tenanted SaaS app that will have a separate database for each customer/tenant. Each database's schema will be identical to each other. The idea is to have only one dbContext that gets instantiated at runtime for each Http Request with the correct connection string. This works fine at runtime, including migrations, all migrations get correctly applied to all tenant databases at runtime without any issues.

What we can't figure out is how to run different migration commands ('update-database', 'remove-migration' etc.) at design time using the VS Package Manager Console across all databases, but having only one dbContext?

We've investigated the IDesignTimeDbContextFactory approach, but that didn't work, it only works with one instance of our dbContext for one connection string only.

I would imagine, this is not such an unusual scenario, especially considering there are Microsoft articles describing having a separate DB for each tenant as one of the viable approaches to achieve multi-tenancy. It would be good to know how to achieve this using Microsoft's own tooling.

Thanks

[ajcvickers]

@nzdjigit The commands are designed to execute once against a single context instance with a single connection. To execute against multiple databases, you could try either:

• Run the commands multiple times (e.g. in a script) with a different connection string each time
• Or generate a SQL script from the migrations and execute this against multiple databases using a script of some other tool.

[nzdjigit]

@ajcvickers Thanks for the quick response. We did contemplate the options you've listed, but we were hoping there was a way to achieve this using default EF Core tools.

I accept that our case is not that common, but it's fair to say that in this day and age SaaS multi-tenanted systems are becoming more wide spread, therefore making systems operating on many identical databases using one dbContext more and more popular.

I don't know much about the inner workings of EF Core and its tooling, so forgive me if I oversimplify things, but would it not be possible to add another method to IDesignTimeDbContextFactory (for example CreateDbContexts) that returns a collection of dbContext instances (potentially of the same type but using different connection strings)? So, in the end it would loop through all dbContexts in the collection and execute the migration commands at Design time.

Thanks

[ajcvickers]

@nzdjigit Thanks for the additional info. We discussed in triage and decided this isn't something we want to add to the main product. While it is true that multi-tenancy is common, it's also the case that there multiple different ways it can be implemented. We don't have any hard data on it, but having multiple tenants sharing the same database but using, for example, different schemas seems more common than identical duplicate databases.

As always, we could consider changing this decision based on feedback from others. Also, you might consider releasing your solution as a NuGet package if you think it would be very useful to others.

See also #10750

[vodkadom75]

As data-sovrieignty becomes a greater requirement I too think that we'll need to find a tenant-per-database solution to the migration issue

[839928622]

I stucked on migrating one dbcontext with multi database . as you said:"This works fine at runtime, including migrations, all migrations get correctly applied to all tenant databases at runtime without any issues", could you please share more on how to make it ?

[Perustaja]

I stucked on migrating one dbcontext with multi database . as you said:"This works fine at runtime, including migrations, all migrations get correctly applied to all tenant databases at runtime without any issues", could you please share more on how to make it ?

I believe he essentially means to create some sort of synchronous function that applies the migrations to each database based on a collection connection strings at startup? I may be mistaken though.

[A30006565]

I stucked on migrating one dbcontext with multi database . as you said:"This works fine at runtime, including migrations, all migrations get correctly applied to all tenant databases at runtime without any issues", could you please share more on how to make it ?

I believe he essentially means to create some sort of synchronous function that applies the migrations to each database based on a collection connection strings at startup? I may be mistaken though.

Spot on. That's what we ended up doing. At program launch we would first build the config provider (which is wired up to the KeyVault) and straight after that we would execute a routine that would loop through each tenant's DB connection string, construct a DbContext and apply DB migrations.

[fabercs]

I stucked on migrating one dbcontext with multi database . as you said:"This works fine at runtime, including migrations, all migrations get correctly applied to all tenant databases at runtime without any issues", could you please share more on how to make it ?

I believe he essentially means to create some sort of synchronous function that applies the migrations to each database based on a collection connection strings at startup? I may be mistaken though.

Spot on. That's what we ended up doing. At program launch we would first build the config provider (which is wired up to the KeyVault) and straight after that we would execute a routine that would loop through each tenant's DB connection string, construct a DbContext and apply DB migrations.

So, any pending migration for the tenants is applied when the application pool (IIS) is recycled, right?

[xuejmnet]

i'm resloved
EFCoreMigrateMultiDatabase
this is my demo,need replace service IMigrationsScaffolder and IMigrationsAssembly support different database with single dbcontext to migration