Hi Nick. Quick question: How many tables and services do you typically see in migrations? I worked at a company with 50+ tables in a giant monolith and they had to do migrations every once in a while. I'm starting my own LLC and want to be sure I'm not underestimating how many tables x microservices I'm going to need in the cloud.
@@nicholaspreston9586 there is no answer to that. it all highly depends on your domain. worked at a startup with monolith and 200-300 tables and a new migration every week or so then there was a company with like 10-15 tables per microservice, but our team's project was also like 100 tables don't care about how many tables you have, if business requires then it is needed, otherwise you might end up with a table that is used in 10 different ways for 10 different purposes. and don't forget about normalization if it is worth it in your case
@@nicholaspreston9586I've worked in medium project, where core, monolith service had some 200+ tables in db. Still, many other services existed with own dbs and part of content layed in nosql. I'd assume 1000 tables are not unseen
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)
Syntax is the same, sure, but the effect is not. Last year I ran Fluent migrations that was working fine for Postgres (small project with two or three tables) on a Sqlite and CockroachDB. Both threw errors - Sqlite because it doesnt support timezones properly, second because wire-compatibility with Postgres is not 100%. So Fluent Migrator is convenient, but not always practical. And you'd probably write raw SQL to migrate the data (e.g. move it to new column with some conversion logic).
Same here, we use FluentMigrator in the company, and it works really well for multi-tenant databases (plus 3 env dev, staging, prod). It simplifies database schema changes with a fluent API for defining migrations in code and helps maintain consistency across different environments. It's ideal for keeping your database evolution in sync!
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.
Totally agree. We used DbUp on one microservice project and it was just disaster (to use), because an average C# developer can't write proper migration SQL code. In addition, DbUp project has a lot of issues (see GitHub), some PRs are open for months with no response/comment, so I would bet on this project that will be around in 3-5 years from now.
You might have missed at the very beginning he said EF Core Migrations are great, but not everyone uses EF Core. This video is specifically for people NOT using EF Core and don't want to introduce such a huge dependency for one specific use case
Try to be open and that’s the reason why you watch this video. You can achieve one thing in many ways, just be inspired or ignore it. No need to debate which is good or bad.
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.
how was it worth it? in project’s ive been it we go with roll forward, including undoing changes. The same is concluded at stackoverflow years ago based on Nick Craver’s blog.
@@ruslan_yefimov But how? For instance let's say you created a new column in the Up migration. The only inverse action in the Down migration would be to drop the column (and thus lose the data). If you chose to do something like creating a secondary table and moving the data there before dropping, then that sounds like a forward migration to me, not a real reversal. And then imagine you are doing complex data updates on some migrations. You always gotta be ready / able to also write the inverse. That sounds like an immense pain.
I do migrations by hand and write both scripts. Then using dev db I run the up script, the down script, and the up script again. It helps to catch the potential errors before touching the prod db
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.
eh, I had the same opinion as you, but now I see it just as tradeoff. for example if there is a huge 15yo project with 1000 queries in sql and you need to migrate from oracle to postgres then well... hang in there. or if you have the same 15yo project with dogshit database structure and you need to fix or change something, navigate through ef code on top of this database will be a nightmare compared to slim orm. different pros and cons can be applied to new projects as well. everything will be cool if you like it and familiar with it. and everything will be shit if its unfortunate situation
@@cl8lf1wm8busually the biggest problem with codebases like that is the SQL cobbled together with lots of string concatenation. Where “common” SQL parts are extracted into different methods. Or maybe the select clause is dynamically built based user input. Good luck trying to rename/alter columns or tables without breaking something. At least ORMs provide a well-defined repeatable way to do the query building, and provide a well-typed and refactor-able definition.
What complexity you are talking about? EF is an awsome tool. You can turn off change tracker with one line and use it as plain ORM with LINQ and migration tool. The Unit of Work pattern is completely optional
@@janisozols5255 Complexity of the SQL scripts when you're doing it manually. Then you may want to use EF instead. I think you actually agree. ;) Now, if you want to use EF all the time always then more power to you. I'm not that guy though.
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.
I use it too, but I don't like the way renames are handled. Also some options feel risky, we have generated tables in teh database that should not be deleted for instance, you need to use an option for that. Also how it handles system data, or default values for new columns that should only be applied for existing columns is not that good. Still this is better than completely running your own sql scripts.
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...
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.
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.
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 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.
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
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 🙂
As long as your remember to update that 'connection', in reality a change like that would be requested through and you would follow the same steps as adding a column.
@@local9 Right, so I as I described, basically in EF Core you have to change one thing (*), while here change has to be on both sides. (*) in theory at least 🙂
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.
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.
Meant to say if using SQL Server. In visual studio you can create a database project. So if you had a customers table, and made a change it would only change one file then if using git the changes to a single file would be tracked there. Then normal you'd have deploy step in your pipeline.
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?
Init containers is not a good option here. You can run your application with many replicas, and doing so would run many instances of the init container, all in competition with one another. Build an image with your migrations, run them as a job and await its success before continuing with the deployment of your apps.
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)
Orchestration is not an issue, you do it just like on single instance app. Trouble is maintaining the backward compatibility during migrations across different running version (unless you can afford an outage, then it's easy)
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.
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.
@@jacobstamm You can use the community edition as its license iis the Apache 2.0. Entreprise Edition has more features, but for my use case the community edition is enough
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.
Tbh, many migration engines like roundhouse just don't support reverting once transaction is committed. It's generally a good practice to run them first on the copy of prod db and generating backup just before execution
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.
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?
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?
Do you understand or validate the SQL code generated by EF core before running it on a production database or do you just hope that it is good enough to cover for your laziness to learn SQL and the datastore you're using?
I’m not a huge fan of DbUp in a team scenario because it’s too easy to mess up the script creation, either naming the script incorrectly or forgetting to make the script an embedded resource, resulting in repeated deployments. If there was a way to automate script creation (with a command line tool like ‘dotnet ef migrations’ or a template or something) it would be perfect.
@@tedchirvasiu Yes and obviously. Do you know how to have a decent conversation without resorting to affront people that are of a different mindset than yours?
@@tedchirvasiu Yes, EF is smart enough to inform you of possible data loss with migrations so I don't see why you would be skeptical of the changes made. Also, if you are skeptical you can write you own migration scripts instead of having them generated, so using a different tool is pointless.
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..
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...
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 @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 !
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.
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!
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.
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.
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
Using Sql Server, SSDT is by far the best free tool ever. You can compare databases, generate code, block if data will lose, take care of so many things.
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.
@@bboyadzhiev the docs make a big whohar about it not being supported, and even goes as far as to suggest they are dangerous… I wouldn’t call that “support” lol. There is a separate DbUp.Downgrade library though.
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
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.
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.
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.
EF is hella fast. Almost on par with Dapper. The new bulkupdate and bulkdelete pretty much finish the perf issues prev version might have caused. If you really want to write your own SQL you can. But it's usually for very few edge cases.
@@pilotboba i always use ef no mater what. i dont care bulk update or other because i am not making projects with millions of users. and if you write your code well ef is fast. but if you making app with millions of user then performance maters and ef will be not choice. i always advice to people using ef if they are not making application with millions of user.
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?
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.
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?
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.
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?
You just run the db in a container locally.. Just create a new one to test some old version of it or smth. Also, make some seeders for test-data.. Not very clear what you want
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.
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.
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.
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.
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.
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.
I don't like putting my data through a black box. When things don't go the way I expect, then I have to troubleshoot and problem-solve. I'm going to p*ss everyone off by saying how easy it is to use GPT to generate whatever scripts or C# classes I need. I'm working on a site with a couple hundred tables and queries. At no point was managing my DB the bottleneck in development speed.
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 😂
I'm 26 and have been using DbUp professionally since I was 20 or 21, by my own choosing. Even in the edmx days I did DB first and had custom code for running SQL migrations. The idea of letting Jesus take the wheel through ORM abstractions for database schema changes is just crazy to me. If you're a noob or working on a hobby project, then fine, but handling data (which is the most valuable asset in most applications) without understanding the datastore or learning the SQL basics seems risky to me. The main selling point of EF code-first migrations is that you would write the code once and then run it on any database. That in my experience never actually works in practice apart from tutorial projects. Custom code usually still has to be written due to performance reasons or DB differences.
@@tedchirvasiui didn't say DbUp and rest aren't functional, indeed they are, but you do it EXACTLY the same way in EF... down to the version table. (without the need for code first etc). They think you're locked into EF if you use it for migrations. It's irrational. EDIT: we all handcraft our migrations scripts in big projects, that goes without saying.
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.
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.
If you use SQL Server you can use Sql Server database project to get the database changes and generate SQL scripts and DbUp to track and execute the migrations. It has the best of both worlds.
@@CabbageYe To many of my recently graduated colleagues, I'm pretty old myself though. 😅 Where I've worked, we've always been pretty comfortable going with the flow though. No getting stuck, keeping ancient .NET 2.0 stuff or something on life support, like some other workplaces I've been. And not jumping the gun on the latest & greatest either.
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?
Nick told me that he will pin this comment
Hi Nick. Quick question: How many tables and services do you typically see in migrations? I worked at a company with 50+ tables in a giant monolith and they had to do migrations every once in a while. I'm starting my own LLC and want to be sure I'm not underestimating how many tables x microservices I'm going to need in the cloud.
@@nicholaspreston9586 it depends on a lot of factors to be honest
@@nicholaspreston9586 there is no answer to that. it all highly depends on your domain. worked at a startup with monolith and 200-300 tables and a new migration every week or so
then there was a company with like 10-15 tables per microservice, but our team's project was also like 100 tables
don't care about how many tables you have, if business requires then it is needed, otherwise you might end up with a table that is used in 10 different ways for 10 different purposes. and don't forget about normalization if it is worth it in your case
@@nicholaspreston9586I've worked in medium project, where core, monolith service had some 200+ tables in db. Still, many other services existed with own dbs and part of content layed in nosql. I'd assume 1000 tables are not unseen
Salam matin
I like that DBup is very strait forward and easy to explain for anyone that writes their own SQL upgrade scripts.
You can super easily write your own upgrade scripts with EF migrations
For basic stuff it's usable, but for most stuff I prefer a SQL Project where I have my DB schema as code and actually review the changes in PRs.
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)
Fluent Migraines? lol
Syntax is the same, sure, but the effect is not. Last year I ran Fluent migrations that was working fine for Postgres (small project with two or three tables) on a Sqlite and CockroachDB.
Both threw errors - Sqlite because it doesnt support timezones properly, second because wire-compatibility with Postgres is not 100%.
So Fluent Migrator is convenient, but not always practical. And you'd probably write raw SQL to migrate the data (e.g. move it to new column with some conversion logic).
Fluent Migraines?? Sounds scary.
I can't believe I went to search on google "Fluent Migraines library"
Same here, we use FluentMigrator in the company, and it works really well for multi-tenant databases (plus 3 env dev, staging, prod). It simplifies database schema changes with a fluent API for defining migrations in code and helps maintain consistency across different environments. It's ideal for keeping your database evolution in sync!
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.
Totally agree. We used DbUp on one microservice project and it was just disaster (to use), because an average C# developer can't write proper migration SQL code.
In addition, DbUp project has a lot of issues (see GitHub), some PRs are open for months with no response/comment, so I would bet on this project that will be around in 3-5 years from now.
You might have missed at the very beginning he said EF Core Migrations are great, but not everyone uses EF Core. This video is specifically for people NOT using EF Core and don't want to introduce such a huge dependency for one specific use case
@@chris20077777 Incorrect, video was specific about being a more "end to end maintainable" alternative to EF.
You miss the point of this library. It's for people who don't like EF Core but would otherwise not have a good solution for migrations.
Try to be open and that’s the reason why you watch this video. You can achieve one thing in many ways, just be inspired or ignore it. No need to debate which is good or bad.
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.
how was it worth it? in project’s ive been it we go with roll forward, including undoing changes. The same is concluded at stackoverflow years ago based on Nick Craver’s blog.
@@Miggleness True, I never understood / found a real use for down scripts apart for testing environments.
@@tedchirvasiu Maybe they needed to be able to revert updates without loosing any (or almost any) of the new data..?
Sounds weird for me too tbh
@@ruslan_yefimov But how? For instance let's say you created a new column in the Up migration.
The only inverse action in the Down migration would be to drop the column (and thus lose the data).
If you chose to do something like creating a secondary table and moving the data there before dropping, then that sounds like a forward migration to me, not a real reversal.
And then imagine you are doing complex data updates on some migrations. You always gotta be ready / able to also write the inverse. That sounds like an immense pain.
I do migrations by hand and write both scripts. Then using dev db I run the up script, the down script, and the up script again. It helps to catch the potential errors before touching the prod db
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.
This, soooo much this right now.
eh, I had the same opinion as you, but now I see it just as tradeoff.
for example if there is a huge 15yo project with 1000 queries in sql and you need to migrate from oracle to postgres then well... hang in there. or if you have the same 15yo project with dogshit database structure and you need to fix or change something, navigate through ef code on top of this database will be a nightmare compared to slim orm. different pros and cons can be applied to new projects as well.
everything will be cool if you like it and familiar with it. and everything will be shit if its unfortunate situation
@@cl8lf1wm8busually the biggest problem with codebases like that is the SQL cobbled together with lots of string concatenation. Where “common” SQL parts are extracted into different methods. Or maybe the select clause is dynamically built based user input. Good luck trying to rename/alter columns or tables without breaking something. At least ORMs provide a well-defined repeatable way to do the query building, and provide a well-typed and refactor-able definition.
What complexity you are talking about? EF is an awsome tool. You can turn off change tracker with one line and use it as plain ORM with LINQ and migration tool. The Unit of Work pattern is completely optional
@@janisozols5255 Complexity of the SQL scripts when you're doing it manually. Then you may want to use EF instead. I think you actually agree. ;)
Now, if you want to use EF all the time always then more power to you. I'm not that guy though.
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.
I prefer to use a sql project, it gives me clear insight in what my database looks like.
Does this SQL project just contain like migrations? And gets run before all your applications start?
@@jjnrmason the project will create migrations for you. But you will need to deploy it your self.
I use it too, but I don't like the way renames are handled. Also some options feel risky, we have generated tables in teh database that should not be deleted for instance, you need to use an option for that. Also how it handles system data, or default values for new columns that should only be applied for existing columns is not that good. Still this is better than completely running your own sql scripts.
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...
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.
I hand rolled a very similar solution for one of the projects' I'm working on now. Works very well with test containers.
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.
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.
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 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.
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
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 🙂
As long as your remember to update that 'connection', in reality a change like that would be requested through and you would follow the same steps as adding a column.
@@local9 Right, so I as I described, basically in EF Core you have to change one thing (*), while here change has to be on both sides. (*) in theory at least 🙂
@@DevelTime you can always reverse engineer using EF
I've used a lot DbUp, you could have explained also the rollback scripts also. cheers!
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.
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.
If using SQL, I really prefer making a SQL project so changes to a single object are tracked to that file in git.
yes - and DB Update should run as a separate step during deployment, and not as part of the application.
Could you please elaborate a little? I didn't fully understand.
Meant to say if using SQL Server. In visual studio you can create a database project. So if you had a customers table, and made a change it would only change one file then if using git the changes to a single file would be tracked there. Then normal you'd have deploy step in your pipeline.
@@tehj1543 Thanks
Database project?
I'll stick with EF 😅
The video literally published 27 seconds ago, how the f
@@nickchapsas you human can't comprehend the advantages of the fourth dimension
@@mad_t haven't known that EF Core had an ability to throw a developer into 7 dimensions. That's dope
@@nickchapsas 🤣
@@nickchapsas Nothing like commenting on the title. :)
I am using evolve, its very similar to dbup. I like to have the full control of migrations.
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?
Probably easier to use a Job pod that simply runs a container that executes the migration instead
@@JollyGiant19 job approach looks like the better option in comparison with init containers
Init containers is not a good option here. You can run your application with many replicas, and doing so would run many instances of the init container, all in competition with one another.
Build an image with your migrations, run them as a job and await its success before continuing with the deployment of your apps.
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)
I’d be interested in a video about your container orchestration for upgrades/migrations in a horizontally scaled service.
Orchestration is not an issue, you do it just like on single instance app. Trouble is maintaining the backward compatibility during migrations across different running version (unless you can afford an outage, then it's easy)
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.
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.
Migrating one way (Up) is straightforward. Try doing Down migrations, that is where things get more complicated.
Hi Nick - Can you do a video where you walk through the "migrations via docker compose before main startup" that you spoke of?
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.
Is there still a viable free option for enterprise, or is that over since Redgate bought them?
@@jacobstamm You can use the community edition as its license iis the Apache 2.0. Entreprise Edition has more features, but for my use case the community edition is enough
Write the scripts, run and then fly away? I like this idea.
I've made a separate Infrastructure.DbUp project with all SQL and run as an executable with a connection string, via my pipeline
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.
Would this require you to write your own downs? Or how is that handled
Tbh, many migration engines like roundhouse just don't support reverting once transaction is committed. It's generally a good practice to run them first on the copy of prod db and generating backup just before execution
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.
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?
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?
Depending on the business this can be a lot simplier for smaller applications or smaller teams to manage.
Do you understand or validate the SQL code generated by EF core before running it on a production database or do you just hope that it is good enough to cover for your laziness to learn SQL and the datastore you're using?
I’m not a huge fan of DbUp in a team scenario because it’s too easy to mess up the script creation, either naming the script incorrectly or forgetting to make the script an embedded resource, resulting in repeated deployments. If there was a way to automate script creation (with a command line tool like ‘dotnet ef migrations’ or a template or something) it would be perfect.
@@tedchirvasiu Yes and obviously. Do you know how to have a decent conversation without resorting to affront people that are of a different mindset than yours?
@@tedchirvasiu Yes, EF is smart enough to inform you of possible data loss with migrations so I don't see why you would be skeptical of the changes made. Also, if you are skeptical you can write you own migration scripts instead of having them generated, so using a different tool is pointless.
I typically use EF database first and SQL database project. Then, do schema compare to go from dev/test to production.
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..
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...
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 @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 !
It DOES support it 😆
@@bboyadzhiev Can you show how to do it?
@@ajdinhusic2574 Apparently there is a separate package to suppose downgrading
The question is, is there ever a good use for down migrations?
Not a NuGet package, but I've used Flyway as a separate deployment step.
Apart from that difference, it does something similar.
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.
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!
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.
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.
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
Using Sql Server, SSDT is by far the best free tool ever. You can compare databases, generate code, block if data will lose, take care of so many things.
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.
it's called making things backwards compatible
DbUp supports this, it's just not presented here. For every "Up" you can have a"Down" migration.
@@bboyadzhiev the docs make a big whohar about it not being supported, and even goes as far as to suggest they are dangerous… I wouldn’t call that “support” lol. There is a separate DbUp.Downgrade library though.
You can achieve something similar with Evolve also. I prefer to write sql for migrations directly instead of dealing with EF clunky syntax.
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
Hey Nick, I have a question: is there any orm-like tool that support Native AOT? Because EF Core is only partially supported
I am sorry, but this is not the best way to run database migrations. This will end in chaos
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.
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.
What IDE you are using?
Awesome. Time to ditch EF Core.
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.
EF is simple with code first. If you dont mind raw performance too much stick with EF.
EF is hella fast. Almost on par with Dapper. The new bulkupdate and bulkdelete pretty much finish the perf issues prev version might have caused. If you really want to write your own SQL you can. But it's usually for very few edge cases.
@@pilotboba i always use ef no mater what. i dont care bulk update or other because i am not making projects with millions of users. and if you write your code well ef is fast. but if you making app with millions of user then performance maters and ef will be not choice. i always advice to people using ef if they are not making application with millions of user.
hi, can you pls provide the table off the subscriptions that work? or what is the requirement to have that on visual studio subscription?
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?
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.
That is great, is there a way to rollback migrations as well?
I feel this is kind of identical to what EF core does. Why change to this new one?
I normaly use Evolve, but it looks like the concepts are about the same..
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?
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.
I prefer NoSQL and LINQ with code-first; upgrade the code properly and never have to migrate.
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?
Good video but this just shows how convenient the DevEx of using EF is
how do i revert to the previous version of db if i need to check something from an old commit?
You just run the db in a container locally.. Just create a new one to test some old version of it or smth. Also, make some seeders for test-data.. Not very clear what you want
Dacpacs ❤
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.
What about version rollbacks?
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.
It is very useful if you are not using EF, for example Dapper.
@@emilzonjeronimo8898 yes, in that case I would agree
Is this run in a transaction? I deploy to a webfarm and want this to be idempotent.
discord server still exists for member ? Mine don't show up, my accounts are linked
I like it
How does it know which order to run the scripts in?
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.
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.
We hate EF (core), so we don't use EF in the compary, aka. we can't use does migration system. We rather use fluentmigrator. DbUp is a bit too simple.
@@Forshen Why do you hate EF?
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.
Weird coincidence - this is basically the exact same thing @amantinband vlogged about a few days ago?!
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.
the script order is just inferred by the file names I'm assuming
I use dacpac
Datatype TEXT ... OMG! Some might pickup like it's ok - it's awful.
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.
I don't like putting my data through a black box. When things don't go the way I expect, then I have to troubleshoot and problem-solve.
I'm going to p*ss everyone off by saying how easy it is to use GPT to generate whatever scripts or C# classes I need.
I'm working on a site with a couple hundred tables and queries. At no point was managing my DB the bottleneck in development speed.
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 😂
I'll give my left nut to never work with edmx files.
I'm 26 and have been using DbUp professionally since I was 20 or 21, by my own choosing. Even in the edmx days I did DB first and had custom code for running SQL migrations. The idea of letting Jesus take the wheel through ORM abstractions for database schema changes is just crazy to me.
If you're a noob or working on a hobby project, then fine, but handling data (which is the most valuable asset in most applications) without understanding the datastore or learning the SQL basics seems risky to me.
The main selling point of EF code-first migrations is that you would write the code once and then run it on any database. That in my experience never actually works in practice apart from tutorial projects. Custom code usually still has to be written due to performance reasons or DB differences.
@@tedchirvasiui didn't say DbUp and rest aren't functional, indeed they are, but you do it EXACTLY the same way in EF... down to the version table. (without the need for code first etc). They think you're locked into EF if you use it for migrations. It's irrational.
EDIT: we all handcraft our migrations scripts in big projects, that goes without saying.
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.
Yeah, this is my problem with complex black-box automations.
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.
If you use SQL Server you can use Sql Server database project to get the database changes and generate SQL scripts and DbUp to track and execute the migrations. It has the best of both worlds.
@@CabbageYe To many of my recently graduated colleagues, I'm pretty old myself though. 😅
Where I've worked, we've always been pretty comfortable going with the flow though. No getting stuck, keeping ancient .NET 2.0 stuff or something on life support, like some other workplaces I've been. And not jumping the gun on the latest & greatest either.
"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
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?
I just prefer to decouple db and app. And guess what, I also use stored procedures for my queries