C#•5mo ago

Best practice for Database EF Migrations (Auto v Manual)

Hi everyone, I have written an API in ASP.Net that connects to a postgresql database. I have so far setup Github Actions to automatically push new releases to DockerHub, and have my host run checks for new versions to pull down. The next part I am looking at is the database, if I add an EF Core migration, what is the best way to apply this to the database? I understand I can have this migration compiled into a standalone binary that connects to the DB and applies it, or can have it deployed as a .sql script. I am unsure what is the best way to go about A) SQL script or standalone binary to apply migration, and B) do I set it up to apply migrations entirely automated whenever a new one is required, or is that bad practice in production? I currently have the API configured to apply the migration to the DB on startup, but given I am trying to design this as if it is a production system that has multiple instances of the API running, I think it would be bad practice for them to all try and apply a migration at once.

16 Replies

Jimmacle•5mo ago

you can also migrate in your application itself dbContext.Database.MigrateAsync() which is what i do in prod for a small application and haven't hurt myself yet

FraznoFire•5mo ago

yes, my post mentions that, but I don't like the idea of it, as I'm trying to design this app with the intention of multiple instances of the API running. having 10+ containers all try and update a database schema at once.. 😬

Pobiega•5mo ago

hm, we do in-app migration at work and it works fine with ~6 running instances. I'll double check if we do any kind of logic for "assigning" the pod that migrates

FraznoFire•5mo ago

Ok, I've decided I will go ahead with using bundles to export the actual migration to a binary, and to apply it in production in my docker compose, I will have it setup as a service and tell my api to wait for service completed successfully using depends on in docker compose. Was looking for something similar in Kubernetes, ie can I make a container that starts and finished before my actual app, and turns out, I can. They're called init containers. So in short I will have my CI/CD push out a migration bundle whenever new migrations are made (should be quite rare), and have my docker/kubernetes run a single instance of this before starting any instances of my API. Thanks guys, I guess I just needed to post here as like a rubber ducky type thing.

Applying Migrations - EF Core

Strategies for applying schema migrations to production and development databases using Entity Framework Core

Pobiega•5mo ago

ah yes, we use MSSQL and the "do migrations" job aquires a database lock before running so if we have 6 instances, they all do that but only one secures the lock

FraznoFire•5mo ago

oh interesting

Pobiega•5mo ago

 private static void CreateMigrationDbLock(DbContext dbContext)
    {
        _logger.Debug("Aquire EF Migrations distributed lock");

        dbContext.Database.OpenConnection();
        var connection = dbContext.Database.GetDbConnection();

        var command = connection.CreateCommand();
        command.CommandText = "sp_getapplock";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(new[]
        {
            new SqlParameter("Resource", $"EntityFrameworkCoreMigrations.{dbContext.GetType().Name}"),
            new SqlParameter("LockOwner", "Session"),
            new SqlParameter("LockMode", "Exclusive"),
            new SqlParameter("LockTimeout", 1000 * 60 * 60), // 1 Hour to wait for lock
            new SqlParameter("DbPrincipal", "Public"),
            new SqlParameter("ReturnValue", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue },
        });

        command.ExecuteNonQuery();
        var result = (int?)command.Parameters["ReturnValue"].Value;
        if (!result.HasValue || (result.Value != 0 && result.Value != 1))
        {
            throw new DbMigrationException($"Failed to aquire db migration lock, result: {(result.HasValue ? result.Value : "null")}", result);
        }

        _logger.Debug("Distributed lock aquired");
    }

 private static void CreateMigrationDbLock(DbContext dbContext)
    {
        _logger.Debug("Aquire EF Migrations distributed lock");

        dbContext.Database.OpenConnection();
        var connection = dbContext.Database.GetDbConnection();

        var command = connection.CreateCommand();
        command.CommandText = "sp_getapplock";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(new[]
        {
            new SqlParameter("Resource", $"EntityFrameworkCoreMigrations.{dbContext.GetType().Name}"),
            new SqlParameter("LockOwner", "Session"),
            new SqlParameter("LockMode", "Exclusive"),
            new SqlParameter("LockTimeout", 1000 * 60 * 60), // 1 Hour to wait for lock
            new SqlParameter("DbPrincipal", "Public"),
            new SqlParameter("ReturnValue", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue },
        });

        command.ExecuteNonQuery();
        var result = (int?)command.Parameters["ReturnValue"].Value;
        if (!result.HasValue || (result.Value != 0 && result.Value != 1))
        {
            throw new DbMigrationException($"Failed to aquire db migration lock, result: {(result.HasValue ? result.Value : "null")}", result);
        }

        _logger.Debug("Distributed lock aquired");
    }

FraznoFire•5mo ago

Oh interesting, so does that wait for current DB transactions to finish up? or would it fail if anything else is talking to the DB at that time?

Pobiega•5mo ago

nah doesnt wait. we use k8s + helm in production and when we do a release, it kills all running pods before replacing them its only ~10 seconds of downtime or so but your bundles + init containers idea sounds interesting too

FraznoFire•5mo ago

Man I wish the apps I dealt with at work were built like that In Aus, medical apps in particular are very behind the times. Of the ~4/5 prevalent ones we deal with they were pretty much all written 20 years ago with no real changes since. They work "fine" from a user standpoint, but when we have to update them.. it's a 2-3 hour manual process.

Pobiega•5mo ago

mhm, I know the pain.

FraznoFire•5mo ago

aah but now I'm worried about using bundles, the Microsoft documentation mentions that the sql script means you can visually inspect what it will do so there's (hopefully) no unexpected surprises. Not sure if it is possible to get that level of transparency from the bundle

Pobiega•5mo ago

so make that part of your release verification step generate the actual migration scripts, have someone (yourself?) go over them and give them a manual approval without manual approval of scripts, dont push to prod

FraznoFire•5mo ago

And those scripts are the exact steps the bundle executable would use?

Pobiega•5mo ago

yes

FraznoFire•5mo ago

Aaah ok Now I’m wondering if I should just have an init container that has an sql client and runs the .sql script, rather than output the sql script and the compiled bundle

Gaming

Programming

Best practice for Database EF Migrations (Auto v Manual)