Run migration SQL script for new Databases only

Question

I have project which is Multi-Tenant. So each tenant has their own database.

This project is database first, and I'm updating this to be a code-first solution.

Now I've reversed engineered the Context / model from the existing database by following the documentation on Microsoft Docs - https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/migrations/existing-database.

I've created an Empty Migration for all existing databases. But because the user(s) can create a tenant run-time of the application I need to Run the initial migration for all new Databases.

At the moment I've moved the initial database creation and initial seed data to an SQL File and I intend to call this Method in the initial Migration.

Something like this.

public partial class InitialCreate : DbMigration
{
    public override void Up()
    {
        this.Sql(TBCompany_Scripts.CreateTBCompanyDB_TB5);
    }

    public override void Down()
    {

    }
}

How would I skip this script from running for all existing databases - but run for new databases? As they already have the tables, schemas and seed data.

xander · Accepted Answer · 2019-11-30 01:14:21Z

Disclaimer: I haven't tried this strategy with EF migrations, but I have used it successfully with a homebrew migration tool built using similar concepts.

Since you've already got your initial create coded in SQL, you just need to make that script idempotent--meaning you can run it multiple times without ill effects.

Logically, what you're going for is something like...

if not exists (select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'SomeTableFromYourSchema')
begin
    -- create your schema here
end

If your test table exists, the migration script will do nothing. If it doesn't exist, the script will create the schema. Either way, EF is happy that the migration has been done.

That said, conditional sql can be tricky. Depending on what your "initial create" script does, making it idempotent might not be as simple as wrapping it in an if. Ask your favorite search engine about "idempotent sql migrations" to dive deeper.

Alternatively, if you have a way to know in code whether the schema needs to be created, you could put the conditional logic in the migration class.

public override void Up()
{
    if (IsNewDatabase())
        Sql(TBCompany_Scripts.CreateTBCompanyDB_TB5);
}

Collectives™ on Stack Overflow

Run migration SQL script for new Databases only

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related