My approach is to handle all DB stuff within SQL and not use migrations at all. Generated SQL-changes might be ok if it's smaller DB and you don't have to consider optimizations.
I do this as well. In fact, I have a SQL project in the solution where the design happens and get a DACPAC that I can validate with the DBA. It's also deployed to CI/CD for all or none approach. I like this better than magic migrations.
I am currently involved in a project that uses an existing SQL Server database, which includes over 300 views and over 500 stored procedures. I am planning to integrate EF Core into this project. Some of these procedures use user-defined tables as parameters and contain over 3000 lines of code. Is this approach feasible, or is there an alternative solution for this scenario?
When you already have an existing database you can also use EF Core, but with the "database first" approach (also called reverse engineering). With it, you would generate source code (entity types) from the db objects, instead of creating db objects from your source. This way round, migrations as a code-first feature, are off-table.
I learned so much concepts from you. Following you from some months, sometimes I go to your channel and watch complete play lists. Thank you for doing so much for the community.
The hardest part is managing multiple PRs with migrations. When one completes the next ideally needs to Pull and rerun migrations and it can be a pain to manage code reviews.
Tip #5 - we set the snapshot file to be binary in our git config so that they don’t automatically merge in the case where a pr created a migration without the latest migration snapshot. It’s not a magic bullet but it can at least throw a merge conflict on the pr and indicate that something is wrong.
I use EFCore migrations with DbUp. One of the key advantages of this approach is you have the full control over the SQL scripts that are getting executed on the database, while enjoying the EFCore's ability to automatically generate SQL for you. Sometimes, you will need to add custom SQL scripts in-between the generated scripts to populate seed data and to do data migrations.
Hello @MilanJovanovicTech & @chamikagoonetilaka4026 I have a question about this approach. Do you use a separate project specifically for managing database migrations, or is DbUp integrated directly within the main application? Additionally, how does DbUp handle database rollbacks if you need to revert the application version? For example, if your application is running in Kubernetes with three instances, each instance attempting to update the database could cause issues. I assume you manage database migrations with a separate service or project to avoid this. In a scenario where you roll back the application version (e.g., by deploying an older Docker image without making a new release), how would you manage the rollback of database changes using DbUp? Thank you!
Hey Milan, great video. Sometimes my gripe with some of your videos is not showcasing the full extent of how things work, with practical examples (understandably because of time constraints IRL and for video length). This video isn't one of those, you show many ways of doing it, how you do it, concerns to be aware of, actually implement it e.t.c - great video!
It's a bit difficult discussing every concept from scratch in every video, so that's a valid critique. With these newer videos, I'm trying better to include context for pros/cons and various approaches you could take
I understand that the code-first approach of EF Core is very convenient for developing the application's database. However, I often encounter errors when applying migrations, especially when changing table keys.
I remember just cracking open red gate back in the day. Asking it to do a db diff and generate the script for me. Ran that in production without worry, hassle, or headache
Typically, I use attributes in the Model, instead of the Fluent design, and use the Fluent design for more complicated implementation details. I believe this gives the developer one location to refer to when building out the application logic. Also, I use Apply Configuration and create Entity Builders, to minimize the code in the DbContext. Is there a reason that you did not reference SSMS as a DB Tool? Or how to execute SQL scripts directly from Visual Studio? Maybe you cover those in other videos?
great video, but i prefer use and external class to configure each entity, one file per entiy, imagine when you have more than 6 or 7 tables and all configuration is in the same place, that would be a nightmare, the class must have the decorator EntityTypeConfiguration. and you can check if there is any pending migration and run the service to run migrations, the app will not create a service in vain
The approach with generating SQL scripts from migrations is great! In one of my company's products we have a lot of microservices and each microservice upgrades itself executing fluent migrations at a application startup. We're using fluent migrator because our product runs on different SQL databases depending on our clients. And this approach simplifies a lot and is perfectly fine. I can't remember a single time when we had to downgrade the database in production after a bad update.
Nice video as usual. I have a question though, consider someone used ensure created command and didn't use migrations, how would you go around creating continuing with migrations from that point on without losing data, I know a bit out of scope, just something thats waiting for me soon, I see few options but all look bad:)
@@MilanJovanovicTech so if I wanted new tables added I have to use scripts? Ooorrr continue working with ensure created that could also work, or just tear band aid off and have downtime in prod while proper steps are taken to have migrations setup and then restore backup, these were the options I see sadly
Using sql query approach to validate if scripts are correct? I am 100% sure that you can make mistakes in sql but an open sourced, Microsoft leading EFCore won't create a false script. There are no value in using this approach unless the company you work ensures high security like a bank and the connection string of the app does not have alter permissions etc.
Can you elaborate on this? I don't quite get it. Do you mean that generating a script from your migrations to be able to validate it adds no value and that EFCore will make a better migration than me? Because if that is what you are suggesting then that's not exactly the case. Just because microsoft is behind it and it's open source does not mean it will always get things correct. Simplest example 1: What he showed with older ef versions where a column rename will be executed as a drop-create instead of a rename. That's a very destructive command as if you apply this on a production db, it will delete all data in the column Simple example 2 (This was more on me for not configuring ef correctly at the time, but that is also a very plausable case): I had created some entities with FK between them, but ef couldnt figure out which property points to which one exactly, so what it did instead was create "shadow" FKs called Id1, Id2, etc... this is also not a very good production solution and I managed to catch and solve it by looking through the migration/script generated by EF. Remember, efcore, no matter who stands behind it, works on configurations and assumptions. Whatever it generates is based on your configuration and it's own assumptions. You can never be 100% sure it won't create something bad just because a big tech company is behind it. Everyone makes mistakes
@@MilanJovanovicTech True - but when developing now product we tend to do database first and scaffold from the db. Once its out there, then we start using migrations, but still scaffold from the development db
@@MilanJovanovicTechI think they’re suggesting they prefer to create a database then scaffold the context. I think this is a nice approach for more sql-focused developers
Want to master Clean Architecture? Go here: bit.ly/3PupkOJ
Want to unlock Modular Monoliths? Go here: bit.ly/3SXlzSt
I swear it feels like u have access to my company's repos, it's like you read my mind with these videos😂
Maybe there's something to it 😁
Bro for real. Whenever im interested in some topic, Milan drops a video for it 😄😄
I generate a sql script during build and apply it in my CI pipeline. That way I can keep my app user limited to write and read.
Nice approach!
My approach is to handle all DB stuff within SQL and not use migrations at all. Generated SQL-changes might be ok if it's smaller DB and you don't have to consider optimizations.
Exactly my approach too. And this has made me not to even worry about destructive commands.
I do this as well. In fact, I have a SQL project in the solution where the design happens and get a DACPAC that I can validate with the DBA. It's also deployed to CI/CD for all or none approach. I like this better than magic migrations.
That's perfectly fine 👌 I've done it on some projects. But my SQL DDL skills aren't that sharp.
Great. Thanks Milan
You bet!
How to revert some specific applied migration correctly? (Not the latest) Let’s say 10th of 20
You can specify -From -To in the update command
When you have hundreds of migrations, are there best practices for consolidating them?
better reset it and scaffold from the latest conditions
Recreate them, pretty much, where you only end up with one migration
I am currently involved in a project that uses an existing SQL Server database, which includes over 300 views and over 500 stored procedures. I am planning to integrate EF Core into this project. Some of these procedures use user-defined tables as parameters and contain over 3000 lines of code.
Is this approach feasible, or is there an alternative solution for this scenario?
SQL? 😅
When you already have an existing database you can also use EF Core, but with the "database first" approach (also called reverse engineering). With it, you would generate source code (entity types) from the db objects, instead of creating db objects from your source. This way round, migrations as a code-first feature, are off-table.
I like to use liquibase instead of the migrations
Interesting
second
Almost made it
I learned so much concepts from you. Following you from some months, sometimes I go to your channel and watch complete play lists. Thank you for doing so much for the community.
Thank you very much, I appreciate that! :) And I'm glad you find the content helpful, that's all I care about.
The hardest part is managing multiple PRs with migrations. When one completes the next ideally needs to Pull and rerun migrations and it can be a pain to manage code reviews.
It's more about discipline there, but doable
Tip #5 - we set the snapshot file to be binary in our git config so that they don’t automatically merge in the case where a pr created a migration without the latest migration snapshot. It’s not a magic bullet but it can at least throw a merge conflict on the pr and indicate that something is wrong.
That's interesting!
I use EFCore migrations with DbUp. One of the key advantages of this approach is you have the full control over the SQL scripts that are getting executed on the database, while enjoying the EFCore's ability to automatically generate SQL for you. Sometimes, you will need to add custom SQL scripts in-between the generated scripts to populate seed data and to do data migrations.
This is an excellent approach 👌
Hello @MilanJovanovicTech & @chamikagoonetilaka4026
I have a question about this approach. Do you use a separate project specifically for managing database migrations, or is DbUp integrated directly within the main application?
Additionally, how does DbUp handle database rollbacks if you need to revert the application version? For example, if your application is running in Kubernetes with three instances, each instance attempting to update the database could cause issues. I assume you manage database migrations with a separate service or project to avoid this.
In a scenario where you roll back the application version (e.g., by deploying an older Docker image without making a new release), how would you manage the rollback of database changes using DbUp?
Thank you!
Hey Milan, great video.
Sometimes my gripe with some of your videos is not showcasing the full extent of how things work, with practical examples (understandably because of time constraints IRL and for video length).
This video isn't one of those, you show many ways of doing it, how you do it, concerns to be aware of, actually implement it e.t.c - great video!
It's a bit difficult discussing every concept from scratch in every video, so that's a valid critique. With these newer videos, I'm trying better to include context for pros/cons and various approaches you could take
I understand that the code-first approach of EF Core is very convenient for developing the application's database. However, I often encounter errors when applying migrations, especially when changing table keys.
How often do you change table keys?
I remember just cracking open red gate back in the day. Asking it to do a db diff and generate the script for me. Ran that in production without worry, hassle, or headache
Those were the days
Typically, I use attributes in the Model, instead of the Fluent design, and use the Fluent design for more complicated implementation details. I believe this gives the developer one location to refer to when building out the application logic. Also, I use Apply Configuration and create Entity Builders, to minimize the code in the DbContext. Is there a reason that you did not reference SSMS as a DB Tool? Or how to execute SQL scripts directly from Visual Studio? Maybe you cover those in other videos?
Exact!
I don't like having attributes on entities, but that's a personal preference. Agree on the IEntityTypeConfiguration.
great video, but i prefer use and external class to configure each entity, one file per entiy, imagine when you have more than 6 or 7 tables and all configuration is in the same place, that would be a nightmare, the class must have the decorator EntityTypeConfiguration. and you can check if there is any pending migration and run the service to run migrations, the app will not create a service in vain
That's fine, most people will end up doing it like that. The focus was more on the migrations here than EF config.,
Dziękujemy.
Zapraszamy! :) (hopefully translate didn't butchter it)
At 2:42 Description is of type 'string?' but isnt a string always nullable?
No if you omit it will become property required.
This is nullable reference types in C#. In general, it can still be null if there's no value.
Very Nice
Thanks
Aint indexes by default unique?
Nope
index ≠ constraint
The approach with generating SQL scripts from migrations is great!
In one of my company's products we have a lot of microservices and each microservice upgrades itself executing fluent migrations at a application startup. We're using fluent migrator because our product runs on different SQL databases depending on our clients. And this approach simplifies a lot and is perfectly fine. I can't remember a single time when we had to downgrade the database in production after a bad update.
It looks like you also take great care of generating "good" migration scripts, so it's easier to control issues
I love working with Migrations! Thank you for more videos like this, Milan!
Most welcome!
Nice video as usual. I have a question though, consider someone used ensure created command and didn't use migrations, how would you go around creating continuing with migrations from that point on without losing data, I know a bit out of scope, just something thats waiting for me soon, I see few options but all look bad:)
They don't go with one another. If you call EnsureCreated, Migrate will fail. You can generate the SQL scripts and run them.
@@MilanJovanovicTech so if I wanted new tables added I have to use scripts? Ooorrr continue working with ensure created that could also work, or just tear band aid off and have downtime in prod while proper steps are taken to have migrations setup and then restore backup, these were the options I see sadly
Excellent video, a note that few say, it is possible to do all this also from the Visual Studio UI.
Not sure what you mean? 🤔
@@MilanJovanovicTech You can do the same procedures directly from Visual Studio using a graphical interface
Using sql query approach to validate if scripts are correct? I am 100% sure that you can make mistakes in sql but an open sourced, Microsoft leading EFCore won't create a false script. There are no value in using this approach unless the company you work ensures high security like a bank and the connection string of the app does not have alter permissions etc.
Can you elaborate on this? I don't quite get it. Do you mean that generating a script from your migrations to be able to validate it adds no value and that EFCore will make a better migration than me?
Because if that is what you are suggesting then that's not exactly the case. Just because microsoft is behind it and it's open source does not mean it will always get things correct. Simplest example 1: What he showed with older ef versions where a column rename will be executed as a drop-create instead of a rename. That's a very destructive command as if you apply this on a production db, it will delete all data in the column
Simple example 2 (This was more on me for not configuring ef correctly at the time, but that is also a very plausable case): I had created some entities with FK between them, but ef couldnt figure out which property points to which one exactly, so what it did instead was create "shadow" FKs called Id1, Id2, etc... this is also not a very good production solution and I managed to catch and solve it by looking through the migration/script generated by EF.
Remember, efcore, no matter who stands behind it, works on configurations and assumptions. Whatever it generates is based on your configuration and it's own assumptions. You can never be 100% sure it won't create something bad just because a big tech company is behind it. Everyone makes mistakes
EF scripts can sometimes do destructive operation, which you should absolutely double check before running it on your database.
reorganize structure and suddenly it's just dropping operations with no data movement
No, thank you, too many type to coding and thinking, I'll use ef core power tools to generate a context in minutes...
But that only works with an existing database?
@@MilanJovanovicTech exactly. Only if your database is created.
@@MilanJovanovicTech Yes.
@@MilanJovanovicTech True - but when developing now product we tend to do database first and scaffold from the db. Once its out there, then we start using migrations, but still scaffold from the development db
@@MilanJovanovicTechI think they’re suggesting they prefer to create a database then scaffold the context. I think this is a nice approach for more sql-focused developers