Hi Nick, why don't you make a video how Db migrations are performed with DB first approach using DbUp or similar library. I do understand why everybody is pushing towards EF, but I believe that a lot of devs that are starting their careers now are getting the wrong opinion the EF is the only way.
@@ЛюбомирГеоргиев-о5й DbUp doesn't support rollback, yet no migration system should be designed without it! Use FluentMigrator, if you wish to have an alternative. ;)
what is missing is the rollback, have you ever tried to rollback a previous version after a migration is done in a later version? the down method for the new migration does not exist in the previous version so you need to use the new software to first call the down method and then you can put the older software back in. My strategie is simple, i have a blazor ui for each database. when you start it it checks if it needs migrations, you can click the migration to execute it (or copy the genereated sql to do it manually. sometimes needed to prevent timeouts when you create a large index or something) on the same blazor app you can also click the previous migration and roll back to before that migration. so manually controlled, no issue for scaling. easy. we do not mind a few minutes downtime for a deployment so this fits us. it may not fit you
I have always encapsulated the migrations into separate console application that is being run by pipelines. And no migration system should be designed without rollback support. So I also have to implement a "release batch" support onto ef that allows me to give a release name to every applied migration so that I can also rollback all the applied migrations with the same release name. I can't understand why naming you migration installation is not oobe... Can you rollback the bundle? It might actually be an alternative...
What is entirely missing here is. What about open source self hostable / on prem software? I only publish, for example a container image. The users just run that. They do not want to worry about any migrations or dotnet cli. So I will still have to run migrations on app startup.
If your users are expected to treat the database as a blackbox and you enforce an upgrade order, that’s fine. The longer the gaps between users updating (if they control that), the greater the chances of them running into a failure.
If you have multiple instances your container, check init containers, which at least exists in Kubernetes. This was something I saw already at some other Kubernetes Helm charts. Your init container exists only once and after completion your main container starts in any number of instances.
We actually have a project that takes a parameter and execute different migrations depending on schema etc. That project is integrated with with a big system and always runs wth build pipeline. There we check if database migrations history tabell is updated or not. We also do run some tables as script that triggers with the other migrations like nservicebus tables that setup what it needs to. Work pretty well. Everytime we need a new compontent or change build that needs new tables etc we do it with new parameter or just run the same parameter with that db schema if we need to update table for example. A regular migration.
The workflow assumes your database is publicly accessible which isn't a good idea. It would be good to see this performed on a secure production environment. I guess a private runner within the vnet would resolve it.
Thanks Nick and Gui for the really good content. I have been releasing using efbundle from Azure DevOps for a while now and everything Gui spoke about makes a lot of sense. One video that would be very beneficial is how to squash or rollup your migrations. I have noticed on several projects that over time your builds start to slow down significantly as the number of migrations you have increases. I recently rolled up 500+ migrations into a single migration for schema changes and another migration for running SQL scripts directly which has halved my build times. The best solution I have come up with to do this so far is to drop everything in the migrations folder and recreate the initial migration and then in my case another migration to run direct SQL specific migrations. This works well for when new database but does require that you manually run a script to insert the new migrations into the __EfMigrationsHistory table on your production and uat, etc databases to prevent EF from trying to run any of the new migrations here.
I'm still not the least bit convinced code-first and migrations is the right way to manage your schemas and propagation of schema changes throughout your environments. Are devs that afraid/annoyed/indifferent of working with databases directly? I don't understand the problem we're solving, other than creating devs who have never actually worked in a MSSQL/PostgreSQL or other modern RDBMS. Data and where and how we store it isn't (or shouldn't be) just an afterthought.
Often times devs cannot / are not allowed to access production databases directly. Commonly they must provide an executable or installer directly to the client, and the client just needs to press a button to deploy and run.
@@CodeAbstract Restricting ALTER SCHEMA permissions on elevated (test/stage/prod) environments is a good practice. Migrations doesn't solve that, and I don't think it was ever intended to. Having a professional who understands databases and schema changes and data migration involved in propagating changes throughout the environments is a good thing, not something we should try to skip or work around as an industry. I understand some devs don't want to concern themselves with tables, columns, constraints, and indexes, but they should let the plentiful devs and dbas who do care about that do what they do (and love) to make sure it's done right. Migrations were/are a really ill-advised end-around good and proper database design and planned migrations with professional oversight.
And who says it all should be an afterthought? Using an ORM is just a means to abstract and make yourself more productive by simplifying implementation details that are otherwise very repetitive, but by no means is intended to completely dissociate yourself from such details (ie queries produced, schema produced, etc). Thinking otherwise is just blatant ignorance about what's the goal of an ORM and how to use them.
awesome video Nick and Gui! The only thing I missed is the fact that ef migration needs a connection string set up so it would know where to try to access, assuming thats not necessarily the default connection string set in the application, and that it also might be part of a seperate github action, I would probably add that as a secret for the specific github environment that is used, and then override the connection string environment, or pass it explicitly with the `--connection` argument. also, a Nick video without 69 or 420? come on guys
Your idea of using a secret specific to an environment is correct (if you have access to GH Environments). Regarding the 69 or 42... sorry. I am "low-cost" version of Nick 😅
What if migration is applied and web deploy fails? I dont consider this more "production ready" in comparison to Startup approach (if we dont scale app, and lot of apps out there are not)
Decent video, typically I want my migrations to be retryable and not in a transaction so I'm not holding any locks for schema stability which could force downtime in production. For example if I add an index in sql server and use WITH (ONLINE = ON), this is fine, it's a small lock at the end to swap some metadata. If I do this in a transaction, write access to the table is blocked by a schema stability lock until the index is built.
@@АлексейЩербак-б3ь But how are you automate the deployment, if you gave no runner at all access to prod? Someting needs to move bits and bytes there and needs connectivity.
How would you do this for a multi-tenant environment, especially one where you don't know the tenants at build time (but the application knows during runtime).
@gui.ferreira has such a calming voice. He could tell me to run migrations under any context and I'd consider it, because he sounds so reasonable. Too much power.
A miserable little pile of code? 😂 It’s just a console app, you won’t run into problems after 1000 migrations anymore than the EF migrations history table would run into issues.
using MySQL any DDL failure will result in a need for a database restore. Those "down" methods are kinda pointless IMO All seems a bit overly complicated, you can store a special DDL connection string as a separate secret and use that for DDL, and a different one for runtime (We have a process for clustered service deployments, it ensures the first service that hits the migration will cause others to wait) We have specific requirements though as we have some clients who purely pull a container image from us and run in their environments, and just want a black box.
This seems overly complicated. It is really simple to make this work in a cluster whether it is dev, test or production on startup. At least under java, there are numerous libraries to help with this. I'm sure c# does too.
So how do these libraries help you to deal with situation when there's a migration and you have several instances of the service? Each instance checks if the migration is executed during startup and every instance sees that migration is not executed yet. That's the situation when you need to extract migrations to separate step in the deployment process
@@dsvechnikov The only thing I could image would be in Kubernetes context the use of init containers. Then you know, they exists only once and are executed before your main container gets started. But that separation you need to do on your own anyway...
It basically locks all other instances while the single instance is being upgraded. The lock would be released after the migration and the other instances would skip the migration. Pretty simple really. On the other hand our system is not that complex and we deploy in chunks of instances in a rolling update migration.
![Seungmin "그렇게, 천천히, 우리(As we are)" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/kAzmhLHePqU/mqdefault.jpg)
Subscribe to Gui: www.youtube.com/@gui.ferreira
Get the source code: mailchi.mp/dometrain/stbf3-m5wdm
Hi Nick, why don't you make a video how Db migrations are performed with DB first approach using DbUp or similar library. I do understand why everybody is pushing towards EF, but I believe that a lot of devs that are starting their careers now are getting the wrong opinion the EF is the only way.
@@ЛюбомирГеоргиев-о5й DbUp doesn't support rollback, yet no migration system should be designed without it! Use FluentMigrator, if you wish to have an alternative. ;)
what is missing is the rollback, have you ever tried to rollback a previous version after a migration is done in a later version? the down method for the new migration does not exist in the previous version so you need to use the new software to first call the down method and then you can put the older software back in. My strategie is simple, i have a blazor ui for each database. when you start it it checks if it needs migrations, you can click the migration to execute it (or copy the genereated sql to do it manually. sometimes needed to prevent timeouts when you create a large index or something) on the same blazor app you can also click the previous migration and roll back to before that migration. so manually controlled, no issue for scaling. easy. we do not mind a few minutes downtime for a deployment so this fits us. it may not fit you
A very good tutorial on a very common company task - but every company has their own tweaks on it
I have always encapsulated the migrations into separate console application that is being run by pipelines. And no migration system should be designed without rollback support. So I also have to implement a "release batch" support onto ef that allows me to give a release name to every applied migration so that I can also rollback all the applied migrations with the same release name.
I can't understand why naming you migration installation is not oobe...
Can you rollback the bundle? It might actually be an alternative...
What is entirely missing here is. What about open source self hostable / on prem software? I only publish, for example a container image. The users just run that. They do not want to worry about any migrations or dotnet cli. So I will still have to run migrations on app startup.
If it's a single container that doesn't need to scale, it's likely the same scenario as the mobile app I mentioned initially.
If your users are expected to treat the database as a blackbox and you enforce an upgrade order, that’s fine.
The longer the gaps between users updating (if they control that), the greater the chances of them running into a failure.
If you have multiple instances your container, check init containers, which at least exists in Kubernetes. This was something I saw already at some other Kubernetes Helm charts. Your init container exists only once and after completion your main container starts in any number of instances.
Consider something like helm pre install and pre upgrade hooks for k8s.
The timing on this is crazy :p I just spoke about us needing to revamp our migrations in our standup this morning. Great video
This is part of the RUclips API for content creators. #bigbrother
Yes! I worked with migrations this morning too! We literally solved the same issue with our DevOps guy. F.. magic. Not the first time.
Thanks, Nick!
Keep Coding!💙
We actually have a project that takes a parameter and execute different migrations depending on schema etc. That project is integrated with with a big system and always runs wth build pipeline. There we check if database migrations history tabell is updated or not. We also do run some tables as script that triggers with the other migrations like nservicebus tables that setup what it needs to. Work pretty well. Everytime we need a new compontent or change build that needs new tables etc we do it with new parameter or just run the same parameter with that db schema if we need to update table for example. A regular migration.
The workflow assumes your database is publicly accessible which isn't a good idea. It would be good to see this performed on a secure production environment. I guess a private runner within the vnet would resolve it.
Thanks Nick and Gui for the really good content. I have been releasing using efbundle from Azure DevOps for a while now and everything Gui spoke about makes a lot of sense. One video that would be very beneficial is how to squash or rollup your migrations. I have noticed on several projects that over time your builds start to slow down significantly as the number of migrations you have increases. I recently rolled up 500+ migrations into a single migration for schema changes and another migration for running SQL scripts directly which has halved my build times. The best solution I have come up with to do this so far is to drop everything in the migrations folder and recreate the initial migration and then in my case another migration to run direct SQL specific migrations. This works well for when new database but does require that you manually run a script to insert the new migrations into the __EfMigrationsHistory table on your production and uat, etc databases to prevent EF from trying to run any of the new migrations here.
What about on-prem deployments? My pipeline generates an installer that customer runs.
run the migration when the app starts?
@@7th_CAV_Trooper this is what the video explicitly advises against.
In such cases, often running the migrations during the installation process is the best idea.
I would generate an idempotent SQL script, which will be executed as part of your installer. Makes without further information the most sense for me.
@@markovcd yeah, but it works fine.
I'm still not the least bit convinced code-first and migrations is the right way to manage your schemas and propagation of schema changes throughout your environments. Are devs that afraid/annoyed/indifferent of working with databases directly? I don't understand the problem we're solving, other than creating devs who have never actually worked in a MSSQL/PostgreSQL or other modern RDBMS. Data and where and how we store it isn't (or shouldn't be) just an afterthought.
Often times devs cannot / are not allowed to access production databases directly. Commonly they must provide an executable or installer directly to the client, and the client just needs to press a button to deploy and run.
@@CodeAbstract Restricting ALTER SCHEMA permissions on elevated (test/stage/prod) environments is a good practice. Migrations doesn't solve that, and I don't think it was ever intended to. Having a professional who understands databases and schema changes and data migration involved in propagating changes throughout the environments is a good thing, not something we should try to skip or work around as an industry. I understand some devs don't want to concern themselves with tables, columns, constraints, and indexes, but they should let the plentiful devs and dbas who do care about that do what they do (and love) to make sure it's done right. Migrations were/are a really ill-advised end-around good and proper database design and planned migrations with professional oversight.
And who says it all should be an afterthought?
Using an ORM is just a means to abstract and make yourself more productive by simplifying implementation details that are otherwise very repetitive, but by no means is intended to completely dissociate yourself from such details (ie queries produced, schema produced, etc).
Thinking otherwise is just blatant ignorance about what's the goal of an ORM and how to use them.
Dude, what amazing timing! I am currently working on doing pretty much that (except we roll our own database migration utility for the time being).
awesome video Nick and Gui!
The only thing I missed is the fact that ef migration needs a connection string set up so it would know where to try to access,
assuming thats not necessarily the default connection string set in the application, and that it also might be part of a seperate github action, I would probably add that as a secret for the specific github environment that is used, and then override the connection string environment, or pass it explicitly with the `--connection` argument.
also, a Nick video without 69 or 420? come on guys
Your idea of using a secret specific to an environment is correct (if you have access to GH Environments).
Regarding the 69 or 42... sorry. I am "low-cost" version of Nick 😅
The seed value was 420 in the seed data!
What if migration is applied and web deploy fails? I dont consider this more "production ready" in comparison to Startup approach (if we dont scale app, and lot of apps out there are not)
What about tools like DbUp and grate?
what about using Database Visual Studio project template instead of EF migrations ?
Decent video, typically I want my migrations to be retryable and not in a transaction so I'm not holding any locks for schema stability which could force downtime in production.
For example if I add an index in sql server and use WITH (ONLINE = ON), this is fine, it's a small lock at the end to swap some metadata. If I do this in a transaction, write access to the table is blocked by a schema stability lock until the index is built.
this is gold, thanks a gazillion
Hey @Nick what do you think about Abp framework and it DbMigrator ?
Thank you Gui, you are among the best and a big thanks to Nick for sharing the video.
Why is the bundle needed? We can apply migrations with the dotnet-ef tool directly
Because, runing your pipeline you have an artifact only and do not have source code.
Ci and Cd should be separated things. Moreover your runners should not have any access to prod. It was explained at the beginning of the video.
From your machine to the cloud!
@@АлексейЩербак-б3ь But how are you automate the deployment, if you gave no runner at all access to prod? Someting needs to move bits and bytes there and needs connectivity.
@@ray89520 u wanna give them everything? Connection string to DB, root access? No. We should share limited permissions. As limited as possible.
How would you do this for a multi-tenant environment, especially one where you don't know the tenants at build time (but the application knows during runtime).
deploy the same migration script for all tenants
@@AmateurSpecialist I use fluentmigrator in a console app
Great one. Well described. Easy to understand. Thanks.
@gui.ferreira has such a calming voice. He could tell me to run migrations under any context and I'd consider it, because he sounds so reasonable. Too much power.
Use flyway.
indeed, Gui has also really good content and the migrations video is well done
And what will this bundle look like after 1000 migrations?
A miserable little pile of code? 😂
It’s just a console app, you won’t run into problems after 1000 migrations anymore than the EF migrations history table would run into issues.
Am I the only one who always hears "hello everybody im naked" at the beginning? 😅
using MySQL any DDL failure will result in a need for a database restore. Those "down" methods are kinda pointless IMO
All seems a bit overly complicated, you can store a special DDL connection string as a separate secret and use that for DDL, and a different one for runtime
(We have a process for clustered service deployments, it ensures the first service that hits the migration will cause others to wait)
We have specific requirements though as we have some clients who purely pull a container image from us and run in their environments, and just want a black box.
Just finished cssd semester 😅
I’ve never came this fast, this usually doesn’t happen
👀
Bet you say that to all the girls!
comments.First();
CA1806
@@yegorandrosov6334 Dude, that was great. Gave me a chuckle =)
Guess the first will always be Nick's pinned comment. Yours some place in the middle here.
This seems overly complicated. It is really simple to make this work in a cluster whether it is dev, test or production on startup. At least under java, there are numerous libraries to help with this. I'm sure c# does too.
So how do these libraries help you to deal with situation when there's a migration and you have several instances of the service? Each instance checks if the migration is executed during startup and every instance sees that migration is not executed yet. That's the situation when you need to extract migrations to separate step in the deployment process
@@dsvechnikov The only thing I could image would be in Kubernetes context the use of init containers. Then you know, they exists only once and are executed before your main container gets started. But that separation you need to do on your own anyway...
It basically locks all other instances while the single instance is being upgraded. The lock would be released after the migration and the other instances would skip the migration. Pretty simple really. On the other hand our system is not that complex and we deploy in chunks of instances in a rolling update migration.
And now without entity framework. Go. Title should be: Do database migration with entity framework right.
...that IS the title
@@nicholaskinzel3908 It was not initially.