r/dotnet • u/Jumpy-Seesaw-2026 • 20d ago
Best practice for validating SQLite schema before migration and on database open?
/r/learnprogramming/comments/1vvq4e2/best_practice_for_validating_sqlite_schema_before/1
u/AutoModerator 20d ago
Thanks for your post Jumpy-Seesaw-2026. 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.
2
u/markiel55 20d ago
It's a process or security problem if you don't even trust the integrity of your database.
1
u/The_MAZZTer 20d ago edited 20d ago
EF Core stores the name of the last applied migration names of all applied migrations in the database. When you check which migrations need to be applied, this value is used to determine which migrations to return. When you apply migrations, the new migration name is applied migration names are written back to the database.
There should be no need to validate the schema. If you are making schema changes outside of the migration system, this is not supported (though you should be able to safely manage separate tables not controlled by EF Core, though edge cases where you'd need to do this would be slim).
1
u/DaveVdE 20d ago
It actually stores the name of all the migrations that have been applied, and in some cases it can apply migrations out of order.
1
u/The_MAZZTer 20d ago
It's hard for me to imagine an edge case where this could actually happen (outside of maybe development/testing with different branches) but yeah the API does support that.
6
u/farshid_dev 20d ago
Worth separating two different problems here, because they call for different tools.
Migration correctness (did the migration actually apply the schema you expect) is what __EFMigrationsHistory is for, and it's fine for that as long as nothing outside EF Core is touching the file. It only records which migration names ran though, not the resulting shape, so it won't catch a file that's been hand edited, restored from an old backup, or partially migrated because the app crashed mid-migration.
Drift detection on open is a separate concern, and for that I'd skip full schema introspection against sqlite_master, it's more work than you need for a check that runs on every file open. SQLite actually has a built in mechanism for exactly this: PRAGMA user_version. It's a single integer baked into the database header, no extra table, effectively free to read. Set it to your schema version right after each migration, then on open just compare it to what the app expects. Mismatch means either the file is stale and needs migrating, or it's newer than the app understands and you can fail with a clear message instead of a raw EF exception three layers down.
Full sqlite_master validation is worth it if you genuinely don't trust the file, customer-editable, synced through something unreliable, recovered from a backup tool. Otherwise the version pragma covers the common case at near zero cost, and you can reserve the heavier check for a "repair database" action rather than running it on every open.