We use the RedGate SQL compare tools [1] to compare our new schema to our old one and auto-apply the diffs to the production DB (this is done automatically by o

We use the RedGate SQL compare tools [1] to compare our new schema to our old one and auto-apply the diffs to the production DB (this is done automatically by our deployment process). To reduce the chance of error we don’t destroy columns or tables. Our application then has an update step which runs on startup for any data migrations (or new data additions), and then updates a version number stored in the DB. The data migrations are super rare. This all means we can migrate from any past version to our latest one: because we have all previous schemas stored in git and n update functions in our app that can walk the versions It’s worked reliably for over a decade and is pretty much entirely pain free [1] https://www.red-gate.com/products/sql-development/sql-compare/ https://www.red-gate.com/products/sql-development/sql-compar...