r/dotnet • u/MaximRob • 7h ago
Question Migrations squashing
How do you deal with ever expanding migrations and the need to squash them?
Each migration comes with a huge designer file, which ends up being a bottleneck when migration count stacks up: whenever doing a new migration, it takes more time than before / uses more resources because the migrator is scanning all designer files as well as all the migrations.
The solution is “squash the migrations and you’ll be good”. And that works.
But squashing the migrations is a coordinated process, because it means (at least for us) squashing and then updating the migration project on any open branch. Add to this the custom SQL when there is some.
Basically squashing is a bottleneck for us, and I’m wondering if there’s a default process to deal with this in EF. Or how people in other company deal with it
9
u/SerratedSharp 7h ago
Have always avoided EF migrations for this reason and they aren't required for EF to work properly. SQL Database Projects I feel are more straightforward to manage. You can point at any two databases and just generate the diff scripts. You can setup data movement scripts and if you write them to be idempotent then you're good.
6
1
u/MaximRob 7h ago
Yeah but then you lose the tight coupling Models <> DB don’t you?
3
u/SerratedSharp 7h ago
Just flips the script. Database becomes source of truth instead of the code first models. To me code first entities are pretty straightforward to update by hand anyway. Also ensures no surprises in what DB structure is generated from entities that I see happen sometimes with code first.
2
u/MaximRob 7h ago
I like the idea honestly…but you live a dangerous life my friend 🤣
1
u/SerratedSharp 6h ago
Do you generate DDL scripts from migrations CLI, or do you grant DDL permission to the app and let the EF migrations runtime make the changes?
1
u/MaximRob 6h ago
Neither, really. We don’t let the app migrate at runtime. Migrations run in the deploy pipeline with a separate migration login from secrets, before the new app version goes out. We review the migration C# in PRs; we don’t usually export standalone DDL scripts unless we need something hand-tuned. Usually it’s the usual EF-migration-csharp syntax style
3
u/SerratedSharp 5h ago
Then you're the one living dangerously LOL I'd rather run static DDL against production than trust giving migrations direct access to production DB. You're letting it work out what changes to make dynamically at deploy time, and you're gonna get surprised one day. You should at least run it in advance, generate DDL, and then that part of the dynamic process becomes a static process you can review before doing the deployment.
2
u/MaximRob 5h ago
yh, when you put it like that... I think we just got used to review things like
migrationBuilder.AddColumn<bool>( name: "IsDefault", table: "SubscriptionPlans", type: "bit", nullable: false, defaultValue: false); migrationBuilder.AddColumn<int>( name: "LifecycleStatus", table: "Organisations", type: "int", nullable: false, defaultValue: 0); migrationBuilder.CreateTable( name: "OrganisationLifecycleEvents", columns: table => new { Id = table.Column<int>(type: "int", nullable: false) .Annotation("SqlServer:Identity", "1, 1"), OrganisationId = table.Column<int>(type: "int", nullable: false), FromStatus = table.Column<int>(type: "int", nullable: false), ToStatus = table.Column<int>(type: "int", nullable: false), Trigger = table.Column<int>(type: "int", nullable: false), ActorUserId = table.Column<int>(type: "int", nullable: true), Created = table.Column<DateTime>(type: "datetime2", nullable: false, defaultValueSql: "GetUtcDate()"), Updated = table.Column<DateTime>(type: "datetime2", nullable: true, defaultValueSql: "GetUtcDate()") }, constraints: table => {and just hope EF is not stupid enough to do something else...
2
u/SessionIndependent17 5h ago
2nd this. There's zero chance our Production Support (who actually execute the deployment scripts) would run a script that had not been run directly against the UAT environment. Every script has to be checked in, and the deployment operation pulls the migration script directly from source control. The procedure to deploy to UAT and Prod must be the same.
3
u/techPOSiDON 6h ago
I found this tutorial online very helpful in determining the process my team would follow. After that it was just a matter of creating a recurring backlog item, evaluating how fast they pile up and setting a frequency, and performing the squash. I'd say we do it once every 3 (2 week) sprints or so but your mileage may vary as we're a fairly greenfield project.
Tutorial: https://codewithmukesh.com/blog/cleaning-migrations-efcore/
2
u/MaximRob 4h ago
Just read it, it's exactly what's happening. The takeaways for me there is this part:
- team rule: "One migration per PR - Never ship a PR with multiple migrations. If your feature needs three schema changes, combine them into one migration before opening the PR."
- "when to clean" for instance at the minute we have 57 and it's already getting slow (like 10min)
5
u/Sorry-Transition-908 7h ago
The more I learn about entity framework, the more I like the simplicity of dbup and dapper.
3
u/ModernTenshi04 4h ago
DbUp is legit and I wish more folks looked into it. It can require a bit more coordination and some tooling to keep the migration names sequential as folks add them, but it's not difficult to implement.
1
u/mmhawk576 2h ago
We use dbup and dapper in our apps (and now just npgsql directly for AOT work), and use entity frameworks scaffold for test projects if we need to be able to quickly setup data for a test. Works fine for us and I like the plain migrations of Dbup. Gonna need to flatten those now too since we’ve gotten to 1000+ scripts
2
u/zzbzq 7h ago
I don’t use the heavy migration frameworks. If you use a big framework or tool you always have r to force what you’re doing into the “shape” of the tool. If you make up your own really lightweight tooling that’s just DDL and scripts, you can do whatever you want because you can change the “shape” of the rules themselves as easily as you can, say, add a column.
1
u/MaximRob 7h ago
What we do is the “usual”, add a new variable to a class, we have that class mentioned in the dbContext and we run the dotnet “add migration” so that it gets picked up and put onto a new migration dir: migration file (up / down) + designer
… but it’s at the time of squashing that it feels very manual. And you kind of have to hope that at that moment in time you need to squash there’s not 30 PR opened
1
u/AutoModerator 7h ago
Thanks for your post MaximRob. Please note that we don't allow spam, and we ask that you follow the rules available in the sidebar. We have a lot of commonly asked questions so if this post gets removed, please do a search and see if it's already been asked.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/the_bananalord 7h ago
I minimize the folder and move on. What performance problems are you seeing exactly?
4
u/MaximRob 7h ago
What do you mean minimize? I just run dotnet add migration and I get 21GB of usage for just the terminal lol
4
u/desmaraisp 6h ago
Jeez your DB must be pretty huge lol. Mine's not too complicated, but has >100 migrations, never had any issue with it
4
u/MaximRob 6h ago
Lol yeah it’s pretty fat. Every 2 months or so running migrations start to kill computers
3
1
u/ur_gfs_best_friend 6h ago
You should look at migration scripts or migration bundles. We use migration scripts and the designer files are skipped for any dbup or down process. Only a single script is executed.
-2
u/donk8r 7h ago
The coordination pain is real, but there's a harder constraint sitting under it. You can only collapse migrations that every database you own has already applied. If a customer or staging instance is three migrations back, squashing strands it, because the steps it still needs stop existing. That's the check to run before picking a sprint, and it bites harder than the branch merging does.
EF does give you a mechanism for the rest. Generate the baseline, then for databases already past it, insert that migration's row into __EFMigrationsHistory without executing it. Fresh databases apply the baseline normally and everything converges.
3
u/MaximRob 7h ago
Yh that’s the bottleneck I’m on about…I think… like: you have master with a shit tone of migrations, need to squash but when you do so also make sure you transfer that squash onto every open branch with new migrations that are still not applied onto master. Basically making it the first record in the efmigrationhistory table in these branches…
2
u/brawneisdead 5h ago
This guy is clearly a Claude bot
2
u/MaximRob 5h ago
Bro 🤣
2
u/brawneisdead 4h ago
Mate I spend 10 hours a day reading claude outputs, no human ever wrote “That’s the check to run before picking a spring, and it bites harder than the branch merging does.” It’s a 4 month old account with an AI generated avatar. If you wanna chat with Claude, you don’t have to do it publicly
3
2
0
u/donk8r 7h ago
That part is a merge-order problem more than a migration one. Freeze new migrations for the window, squash master, then each open branch deletes its own migration and regenerates it against the new snapshot. A model-derived one comes back in seconds, since it was never hand-written to begin with.
The ones that actually cost you are the custom SQL migrations, which have to be carried across by hand. That is also the argument for timing squashes by how much custom SQL has piled up rather than by sprint count, since that's the only part that doesn't regenerate.
1
u/MaximRob 6h ago
Yeah this is what we do. But you never experience that moment when you’re like “it’s been 20min I’m waiting for this to add a mig ” I should have squashed before.
Do you think there’s something we can do with the designer files so that they don’t grow to be taking so much Space?1
u/donk8r 6h ago
Before doing anything about the files, find out where the 20 minutes actually goes.
migrations addbuilds the project and constructs the model before it touches anything historical, and on a big solution that build is usually the bulk of it. If that's your case, squashing buys you nothing and you'll have paid the coordination cost for no speedup.The files themselves are large by design, since each one carries a full model snapshot so EF can diff against it. The only real saving is dropping the ones you'd never revert past, because everything older than that point is dead weight.
24
u/doriman 7h ago
We do this every 5-10 sprints, as recurring product backlog item.
We have a detailed instruction in confluence and that also includes when to do it in the sprint (at the end) and how to coordinate with others. Basically: if it‘s a squash sprint, do all migrations early on so the poor soul doing thr squashing has it a bit easier.
Also, we wrote a command line tool that collects and (re-)generates manual commands that are found throughout migrations.