EF Core Migration - multiple databases

Question

Is there a way to run EF Core migrations on multiple databases having the same set of tables. This is for multi-tenancy architecture where there's a master database (has metadata of all tenant databases including the tenant database connection string) and one database per tenant having the same set of database objects. We need to be able to run these migrations when a new tenant database is created automatically in SaaS model and also run these migrations whenever there are changes to the database structure (new columns, data type changes, new indexes etc.)

Trillby

Answer 1

I've posted this exact same question on EF Core's GitHub.

The answer is, it can't be done at design time. You basically need to run your migration scripts manually on each tenant's database.

Executing migrations at runtime, however, is easy. You can instantiate a dbContext for each of your connection strings when your app launches (before WebHost.Run() if it's a web app) and execute your migrations like this: dbContext.Database.Migrate();

This is not ideal, of course, because it makes it harder for you to rollback your migrations to a certain point using Visual Studio Package Manager Console or CLI using dotnet ef commands.

Appoint

Is this good to be used in production to update all the databases? – Rigatoni

Yeah, absolutely. We've been using this approach to apply DB migrations across all our tenant databases in Production. – Appoint

@Appoint So, is this approach rely on iis restart/refresh to update tenant databases? (Because Webhost.Run() is in Program.c and it will only be executed on app re-launches as you said) – Identity

Not so much the Restart/Refresh as the initial start of the app. After your code gets deployed to the server, one of the first things your app will do as part of its start-up routine is to apply all DB migration scripts. If everything goes smoothly, subsequent app restarts won't have any effect because all DB migrations would have already been applied across all tenant databases. – Appoint

Answer 2

The CLI command can be provided a connection string. So you could run it once per db, providing the connection string for each.

The command would look like this:
dotnet ef database update --connection "Server=client1.db;Database=client1"

Rottweiler

Answer 3

Our team has about 10 developers, our application is one front-end connect to 20 databases(same scheme), and new database will be add when there is new client. Time to time someone will need update DB scheme, we end up doing this.

• if you need scheme change, create SQL script and create the change request by email
• only one person in the team run those script, and update database access layer
• git push
• tell the team dinner is ready

The person doing this created a EXE project for DB migration, he keep adding script to a folder, so the folder will contain all the script

0001.InitTables.sql
0002.MoreTabels.sql
0003.UpdateDropdowns.sql
.
.
.

then he use a library like DbUp (https://dbup.readthedocs.io/en/latest/) help him track those scripts and run on DB server.

He will run for DEV server first, on the release date, he will run this for production.

  List<string> connectionStrings=new List<string>{
      "ConStr1","ConStr2", "ConStr3" 
    };
    foreach(var conStr in connectionStrings){
        var upgrader = DeployChanges.To
                .SqlDatabase(conStr)        
                .WithScriptsEmbeddedInAssembly(
                   Assembly.GetExecutingAssembly()
                 )
                .LogToConsole()
                .Build();
    
        var result = upgrader.PerformUpgrade();
    }
    

Dudgeon

How do you create those scripts for the scheme-changes in the first place? Do you for example use EF migrations and generate the scripts in the package manager console for each database-instance? – Humbug

I always thinking EF migrations is not a good idea. 1. It is only for developers, when you have DB admin, it become a problem. 2. Also, when .NET is not the only language for the system, none .NET developer need a way to update the database. Bottom line, it creates limitation, and I don't get too much benefit from it, other solution looks better for me. – Dudgeon

@cpt.oneeye, we are not force developer to use any method to create the SQL script, they can use some generation software, and write by hand. – Dudgeon