Nick told me that he will pin this comment

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.

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

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)

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.

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

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

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.

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.

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?

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

A question, has any of you at some point, some time into the project tried creating a new "Initial" EF migration, just because there are too many of them or there is a feeling that the database model became more stable?

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.

Am I missing something or is this the exact same thing EF Core does, but without the "you need to write db scripts" hassle, which are replaced by running a command?

Would this require you to write your own downs? Or how is that handled

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 🙂

I'll stick with EF 😅

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...

Migrating one way (Up) is straightforward. Try doing Down migrations, that is where things get more complicated.

We use DbUp, but we write a lot of C# based code migrations. We use this to maintain files on our Azure storage accounts. Works really well, actually.

How this is superior to grate/RoundHousE? Ofc it is totally own db migration tool and doesn't interreact with code. But it also keeps on track which scripts have run. Is this better than those? Am I missing something or are we just talking about different tools. Ofc grate & RoundHousE is mainly for SQL Server, but it support also other db's

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.

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)

Keep the migration runner separate from the app. This allows you to follow the rule of least privilege and assign CRUD permissions for the app identity while giving the migration runner elevated permissions to add/alter tables etc. The migration runner should also run from your CD pipeline (or during GitOps) not as part of the execution flow of the app. Lookup the "admin process" item in the "12 factor app" guide.

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.

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.

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

Maybe it's just me, but I find using the designer in SSMS is very quick and easy. I have a couple hundred SQL queries in my project-- I FOR SURE spend more time recompiling my project because I've changed some css names in the markup, than anything I spend trying to align my DB with my project.

I use flyway for migrations. I like it because I do not need write any code for running migrations. Just write the scripts and run flyway.

I first used DB Up over a decade ago and its a nice way to manage your DB schema but I vastly prefer Entity Framework Code first to it. However if you can't use code first, or EF. Then DB up is pretty nice.

If I go DB First, I prefer to have my migrations as a "separate" project. Love Sqitch for that. Allows to easily manage deploy/revert/check. A lot more powerful than simply executing a few random SQL scripts in a folder.

SSDT Database project is still better. At least I see the whole picture, I see the state of the table, and I don't need to open each migration script to understand the state. In Bloody Enterprise, the number of migrations is in the thousands

i used to be very much in favor of dbup and avoid codefirst ef migration, especially because of the large ugly c# file being generated. way too much magic. I now prefer writing manual migrations in C# with ef core or fluent migrator, and if full control is needed, raw sql can also be used in these migrations.

In our most complicated project our team separated db schema create scripts and c# code. We wrote an internal tool for managing db schema and data patches (migrations), which sometimes were quite complicated SQL-wise. That's an old project however, started before EF even existed. I used EF migrations after that in a few projects, but I wasn't completely satisfied with it. It nicely couples db versions with the code that uses it, but you have to know exactly how it works, otherwise your db is going to be a mess and you are going to get a lot of frustrating migration error messages. The biggest problem IMHO is that it does not care about the real DB schema. It only reads the Migrations table which only contains the last serialized model. If the DB is not totally in sync with what is in the Migration table, you are going to have a hard time solving the problem.

I've made a separate Infrastructure.DbUp project with all SQL and run as an executable with a connection string, via my pipeline

Looks an interesting package. I use DACPAC on my current project with an approval step in the DevOps pipeline to output the generated change script to check what is going to change

Good luck in production with this tool: "Schema downgrading is not implemented in DbUp because it's error prone and hard to implement properly."

I know there is a place to manage your db via your c# project, but as a few others have said. I prefer to keep my database design (tables, sprocs, functions, etc) in a proper database project where you can easily design those items and track them in get. Plus you can more easily catch typos, errors etc. You can still use items like EF Core to talk to the database, but I don't think it should control the design the of the database. Never been a model first kind of developer.

I've used a lot DbUp, you could have explained also the rollback scripts also. cheers!

I've been using DbUp for a while and I added some customizations to mine. I have a folder with (1) Scripts (2) Test data, and I haven't done this yet, but I would like to have a folder for (3) DB Objects (like stored procs, which get updated each time if they were updated in the file). Then for the test data, I just have a command line arg that lets you run the test data as an option if you want. Haven't had any serious issues with this setup but still room for improvement with the objects files...

EF Core migrations have some subtle but significant shortcomings when you start to scale and try to maintain a nice DX. One is with squashing. For years the official work around for overcoming gaps in the configuration API was to hand-edit the migration files after generating them. Except, ensure created won't pick up any of that. And it makes squashing out old migrations a huge PITA. The idea that you need to keep all migrations forever in the age of source control and continuous deployment is a bit dated. But, the EF team is quite resource constrained and it's my understanding the person who wrote the bulk of the migration system is no longer there..

Maybe I am just too old school, but I prefer the DB-first approach, and just alter the context after. I don't see the point in letting the code control the database. It's not faster if your code base isn't a mess, and you are a lot more likely to have well performing code, if you understand and control the SQL well. I don't understand why there are so many frameworks that allow you to "not learn SQL", but eventually, you have to learn it, because you can't optimize a SQL database without it. So in the end, you have built a component that doesn't perform, to use a tool that lets you not learn a dependency, to finally, need to learn the tool properly to use it, but now it's full of junk, that you have to deal with on top... Just learn SQL first. No need for frameworks that hide SQL from you, and you can make some really optimized calls to the database, without worring if the "code to SQL" compiler is doing something unintentional, that will slow you down.

Hi Nick - Can you do a video where you walk through the "migrations via docker compose before main startup" that you spoke of?

Not a NuGet package, but I've used Flyway as a separate deployment step. Apart from that difference, it does something similar.

I typically use EF database first and SQL database project. Then, do schema compare to go from dev/test to production.

11:35 it’s a shame they don’t support down/rollback migrations. Makes it harder when swapping branches. When swapping branches while developing schema changes, either need to have a db per branch, drop the db, or restore from a backup.

I am sorry, but this is not the best way to run database migrations. This will end in chaos

I normaly use Evolve, but it looks like the concepts are about the same..

how do i revert to the previous version of db if i need to check something from an old commit?

I’d be interested in a video about your container orchestration for upgrades/migrations in a horizontally scaled service.

We created a similar solution customized based on the specific requirements. But, it's becomes really hard to maintain when number of the scripts exponentially increases. We are 200+ and counting.

I like the simplicity. At my last job we were using Fluent Migrator. This seems nicer as you can just write actual SQL. But, but, what if you start using a different database?!?!? Who changes their database all that often? And you would probably just start your migrations all over again if you did and have your old DB as the base DB. Now, if we can only get rid of EF!

Why would you do all this when NHibernate has been able to setup your database - with a high degree of control - without any of this maintenance intensive code?

Thanks for the video Nick, is this approach recommended in production? There are a lot of recommendations from the community for not using migrations from EF Core in production via code in Web scenarios.

hi, can you pls provide the table off the subscriptions that work? or what is the requirement to have that on visual studio subscription?

Is DbUp a decent solution for .NET MAUI migrations? I’m trying to find a good way to do it but can’t find any recommended approaches for MAUI. All examples / tutorials just show the built in way to create the tables initially based on the sqlite-net-pcl library.

Hi @Nick Chapsas. Thanks for the great video. I do have one question though. DbUp doesn't seem to support adding Down migrations. Do you think this is a problem? If so, how to overcome it in your opinion? Anyway thanks again !

I've been using 'flyway' on couple projects. Basically the same idea, you feed it with a new .sql file, it applies it and stores the versions in a separate table. Just containerize it and you are good to go. If you prefer to keep your migrations as a separate app, why write your own app at all?

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

How does it know which order to run the scripts in?

Hey Nick, I have a question: is there any orm-like tool that support Native AOT? Because EF Core is only partially supported

You can achieve something similar with Evolve also. I prefer to write sql for migrations directly instead of dealing with EF clunky syntax.

The second scripts has a bug, it will fail if data is already present, because field is not null and default value is not provided. Looks like dbup is not checking concurrency as well.

For private projects i forget about databases. Just a json file on my system, a background thread checking for differences and storing the shit to harddrive... all this database this is just a pain. and because of fine encapsulation and interface using the transition to database or rest or anything else as data store is very easy.

I am new sorry but how does it maintain order? how does it know CREATE table to run first? it it the naming? Also, what happens if there is data in the table and you add non-nullable field. wouldn't it fail?

Was not aware of dbup, nice. I have a hand rolled thing that does basically the same thing, but uses a version attribute for a class, this allows for class renames. Also dbup is limited (or not?) to pure sql, my thing is just csharp code so i can use csharp if needed.

Hey Nick, great video for my team, we're currently managing migrations manually. The only issue that I stumbled across is: How do you connect connecting to sql Server database. Given that I've got MS managed identity configured, the connection to my dB is managed via an azure active directory access token token rather that username and password. DbUp doesn't seem to expose an extension method to connect to the database in this way and this sucks.

I need to manage many installations and therefore many databases. For migrations nothing is automated, the only project that directly accesses the database does not perform migrations on its own. I add the migration and the script it. Sometimes I need to modify that SQL script if I need some values moved around, but usually the generated code is fine. Then I can run the migration against all of my databases with a cmd. I also have a create from scratch sql script so I copy the contents of the migration to the end of that.

Is this run in a transaction? I deploy to a webfarm and want this to be idempotent.

EF is simple with code first. If you dont mind raw performance too much stick with EF.

That is great, is there a way to rollback migrations as well?

To be honest I don't see the point. It is missing very important feature of EF.Core which is an option to generate migrations. Why would I write my own scripts when I have a tool to do it for me? All you have to do is verify them afterwards. And if there is a need to add some custom script which, for example, is moving some data between tables, you are able to do it with migrationBuilder.Sql method. So, just for the sake of not using C# classes, it is not enough.

What IDE you are using?

What about version rollbacks?

the script order is just inferred by the file names I'm assuming

discord server still exists for member ? Mine don't show up, my accounts are linked

I prefer NoSQL and LINQ with code-first; upgrade the code properly and never have to migrate.

If i run this in my application it means I need to be „dbOwner“ or a user with high privileges on the DB instance! I don’t like that… I use ef-core to generate migrations scripts and these are executed with CI-CD pipelines ! The application does not need than high privileges such as being able to create a DB! It just needs to have read/write access to its own db and tables.

You can use EF to do it this way too. You don't need to do code first / db first. My issue is that these packages aren't maintained nearly as much. The only reason these packages don't die is because there are dinosaurs out there who have PTSD from edmx files and would literally cut off their arm than use EF. Most devs "think" they know DB versioning, until you look at their DB 😂

Good video but this just shows how convenient the DevEx of using EF is

Awesome. Time to ditch EF Core.

Why use this over EF core's built in functionality which already give you a way to run migrations from code or generate SQL scripts? I actually use both because I also write tests that use an actual db. I also test the up and down of migrations in separate tests.

Why all this pain just to use dapper, when ef core with set default non tracking have the same speed, is simpler and you don't need to write this boilerplate since you get migrations for free.

Yooooo I wouldn't call this "the best way" in any way. With SQL server you can just define a database project that contains the schema you want the database to have. Then in the CI pipeline this will be applied, where it will figure out how to get to db to the desired state. Similar to using terraform, you only check the "schema" into git. Wouldn't ever want to go back to manually writing scripts like this.

DBup tries to reinvent the wheel but instead of has managed to become a more complex copy of EF. The only point to this library that I can see is if you require a migrator whilst using Dapper, or you maintain a system that does not (yet) have EF. Perhaps if this video specified its use case outside of EF more clearly it would have been interesting. Now it just looks like a poorly maintained ripoff.

No tnx to going back to my early developer years when we were all writing RAW SQL (back then with manual ORM mapping) in C#. EFCore's built-in DB schema management generally does very well for anything except maybe the most niche of circumstances. Heck, we've even got some very dynamic migrations going on that're built on the fly based on external metadata and I sure as heck wouldn't wanna generate manual SQL migrations for versioning UP/DOWN, etc. on this. Sure, I'm able to manually write SQL, I've done it for many years back in the day. But I really don't see the point for most scenario's, nor do I really want the hassle to be honest. Maybe if you're trying to generate DB's with a bunch of extra views, stored procedures, etc. rather than just the basic table management you generally do with EFCore. Then I could understand you scripting DB creation. However, I haven't used stored procedures in like 15 years to be honest.

Datatype TEXT ... OMG! Some might pickup like it's ok - it's awful.

I use dacpac

I am using efcore and not migrations, should I? I have the context and create the table with ensurecreated, of course i canot modify the models of the tables unless i delete and create again but other than that why should i use migrations?

Dacpacs ❤

EF migrations are quite good at dev time, especially when there's a lot of overlap between devs' work on the db. However, it's quite a nightmare if there are problems in the pipeline.

I just prefer to decouple db and app. And guess what, I also use stored procedures for my queries

I like it

"The Best Way To Run Database Migrations" the best way: dont use. Migrations suck bad. Use SQL scripts and revers engineer it to generate models.

first

Weird coincidence - this is basically the exact same thing @amantinband vlogged about a few days ago?!