The Easiest Way To Manage Database Migrations in .NET

Hello, everybody. I'm Nick, and in this video, I will show you how I use DbUp, a very popular migrations Nuget package, to manage RDBMS migrations.

Don't forget to comment, like and subscribe :)

Social Media:

#csharp #dotnet

[MatinDevs]

Nick told me that he will pin this comment

[OlleHellman]

I like that DBup is very strait forward and easy to explain for anyone that writes their own SQL upgrade scripts.

[ChrisBrandsma]

I’ve been using Fluent Migraines for a long time. It allows me to use the same syntax for multiple databases and it also works if you have to update multiple databases (multi-tenant databases, microservice databases, etc)

[isnotnull]

A weird solution to use an external library for the case you can perfectly do with in-built solution.
First of all, EF Core does allow you to generate sql migration scripts. So after creating an inital migration with ef core and after every other migration cs file created you just run `dotnet ef migrations script` command. Then embed the newly generated script as it shown in the video and then use native `migrationBuilder.Sql(sql); ` to execute sql. In that way you have all .cs classes for every migration you have. You may revert to any migration if you want to. At a project we worked on we also manually changed generated scripts in cases when it could lead to data loss (for instance changing a column data type). So ef core generated a script as it did always then we checked the script, changing if needed and executed with ef core migrations natively using migrations table and other ef core stuff.

[TristanGoetz]

I have been using DbUp for a while now and I have always wondered if the way I was using it was "right". I am glad to see that I am not completely off my rocker, but I would LOVE to see a video that shows how you handle DbUp and migrations on a larger scale, like you were mentioning with the separate container. I also would like to know how that would be achieved without using containers, as clients don't always want docker installed on their systems

[bboyadzhiev]

I used DbUp a couple of years back and I think that a core example of migration process is missed in this video - the "Db Down" (or reverse migration).
For every "Up" script we used to have a "Down" script.
Furthermore there is the differentiation between structural migration SQLs (the DB structure, as presented here), predefined data SQLs (any hard-coded data, enums, etc.) and sample data SQL (test cases, only for DEV/UAT).
All these types can be preset to run in DbUp on their own (structural and hard-coded data - only once per environment), sample data - flashed every time :).
It was a pain-in-the-a** to write the "Down" scripts every time, but it was worth it ant the end.

[river]

I prefer to use a sql project, it gives me clear insight in what my database looks like.

[brakara]

Good video. I usually use DbUp with Dapper unless the team I'm on want to use EF. There are some times when you want to mitigate the complexity of the SQL scripts by using EF, but in my experience that tends to come back and bite you in the ass. If you know what you're doing you'll most of the time be fine with using DbUp and a lightweight ORM like Dapper or just SQL queries directly.

[GiswaldCA]

Could you maybe make a video for this "run migrations" before starting the main application? eg in context for kubernetes with init_container or something else?

[edmistond]

Nice overview - I used DbUp at a previous job and I'm generally a fan. As you noted, running migrations on startup isn't ideal; in our case we threw it into a separate CLI app project and ran our migrations in their own CI/CD pipeline instead.

One thing I'd personally note: I think it's still worthwhile to add those "if not exists" checks around tables/fields that you're adding, and we in fact had a requirement that all our migration scripts had to be idempotent. One of the reasons we picked DbUp was that it didn't try to be cute about figuring out which migrations had or hadn't run based on a timestamp, and our app had a number of working branches where things might get merged in unpredictably.

Having a lot of working branches in flight wasn't ideal and was something we were working to fix at a team level, but having that safety check to make sure we didn't blow up our CI/CD pipeline, or that we could delete a couple of "this migration ran" records and re-run them if necessary, was helpful in a couple of deployments.

[michaelrall]

I often use Jetbrains DataGrip and it's DDL-Script-Export-and-compare features where we only have one production database. In other projects I use DevExpress-XPO which has it's own (EF-Core like) DB-Migrator. I also have one or two projects where I placed the update-scripts in the startup-code like in your first example. All depends on scope, age and environment of the project.

[haxi]

I hand rolled a very similar solution for one of the projects' I'm working on now. Works very well with test containers.

[garodrameryan]

Actually, for the past 10 years I'm using Red-Gate Sql Compare for migration scripts and add them to source control. I just couldn't get used to the idea of DB schema modiying application code. This way worked me so far...

[DevelTime]

Thank you for sharing. But if I understand correctly while with EF Core you can get "connection" between your models (in C#) and the database structure, with the approach you present you get "disconnected" data -- for example you could drop some column in DB (like LastName from a Person), while your model happily assume you have property LastName in Person (on C# side). Despite all EF Core shortcomings this binding give me some peace of mind and I like it 🙂

[MelikPehlivanov]

Hey Nick, it would be great if you could create a tutorial on the "best" way to handle logging and tracing in .NET. Specifically, how to manage all the logs and tracing data while masking sensitive information like PII and then easily display it somewhere in order to debug easily on PROD. This would be very beneficial as many people might face this issue and wonder about the best approach. For example, whether to use OpenTelemetry for both tracing and logging, or a combination like OpenTelemetry for tracing and Serilog for logging, etc.

[antonmartyniuk]

I like Fluent Migrator, which is the best tool if you need your software with a single codebase to be able to migrate different SQL databases, depending on the customer. One use Postgres, others use MS SQL, Oracle, MySQL and so on.
If I know that my software only needs to support single type of database - I go with EF Core migrations.

[tehj]

If using SQL, I really prefer making a SQL project so changes to a single object are tracked to that file in git.

[thusr]

I am using evolve, its very similar to dbup. I like to have the full control of migrations.

[Valeriano.A.R]

I didn't know the existence of DbUp. In our case we implemented the migration logic; migration table, check execution, different connection string with admin permissions.It's working like a charm in our case. But we were changing from manually executing migration script to this, so any change was great.

[browny]

I use EFCore migrations with (My)SQL just because it is much more difficult to make mistakes with it.
For one mongoDB project I did create my own migration implementation because we rarely needed it (mongoDB can deal with "columns" not existing) and if we did, we usually had to integrate external apis so C# code was required to fill in empty fields (e.g. to switch from integer user IDs to string UPNs)