C#•2y 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.

Jimmacle•6/8/24, 12:31 PM

you can also migrate in your application itself

Jimmacle•6/8/24, 12:31 PM

dbContext.Database.MigrateAsync()

dbContext.Database.MigrateAsync()

Jimmacle•6/8/24, 12:32 PM

which is what i do in prod for a small application and haven't hurt myself yet

FraznoFireOP•6/8/24, 12:34 PM

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.

FraznoFireOP•6/8/24, 12:34 PM

having 10+ containers all try and update a database schema at once..

Pobiega•6/8/24, 12:45 PM

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

FraznoFireOP•6/8/24, 12:47 PM

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

service completed successfully

using

depends on

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•6/8/24, 12:47 PM

ah yes, we use MSSQL and the "do migrations" job aquires a database lock before running

Pobiega•6/8/24, 12:47 PM

so if we have 6 instances, they all do that but only one secures the lock

FraznoFireOP•6/8/24, 12:47 PM

oh interesting

Pobiega•6/8/24, 12:47 PM

 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");
    }

FraznoFireOP•6/8/24, 12:48 PM

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•6/8/24, 12:50 PM

nah doesnt wait. we use k8s + helm in production and when we do a release, it kills all running pods before replacing them

Pobiega•6/8/24, 12:50 PM

its only ~10 seconds of downtime or so

Pobiega•6/8/24, 12:51 PM

but your bundles + init containers idea sounds interesting too

FraznoFireOP•6/8/24, 12:53 PM

Man I wish the apps I dealt with at work were built like that

FraznoFireOP•6/8/24, 12:55 PM

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•6/8/24, 12:56 PM

mhm, I know the pain.

FraznoFireOP•6/8/24, 1:10 PM

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•6/8/24, 1:28 PM

so make that part of your release verification step

Pobiega•6/8/24, 1:28 PM

generate the actual migration scripts, have someone (yourself?) go over them and give them a manual approval

Pobiega•6/8/24, 1:29 PM

without manual approval of scripts, dont push to prod

PPobiega generate the actual migration scripts, have someone (yourself?) go over them and...

FraznoFireOP•6/8/24, 2:00 PM

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

Pobiega•6/8/24, 2:01 PM

yes

FraznoFireOP•6/8/24, 10:50 PM

Aaah ok

FraznoFireOP•6/8/24, 10:51 PM

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