Comment by infogulch
1 day ago
I've used this for Microsoft SQL Server and SQL Database Projects. It's basically as you say: write it as if creating a new database, then deploy it in CI where it does a diff on the live database to come up with the actual migration strategy on the fly. If you're clever you add a manual review stage in the pipeline and have the db engineers approve the generated migration script before deployment is completed automatically. https://learn.microsoft.com/en-us/sql/tools/sql-database-pro...
I helped set this up in a fortune 500 company a few years ago. They were using a team of db engineers to execute manually written change scripts, with manual reviews, control processes, and deployment schedules. You'd be lucky if you got your schema change to prod in a month. When they started integrating this tool on some trial candidates they found SO many inconsistencies between environments: server settings differences, extraneous or missing indexes, vestigial "temp" tables created during previous migrations, enum tables that should be static with extra or missing rows, etc, etc. All the environment differences meant that deployments had to be manual in the past. Once they got through the initial pain of syncing up the environments the whole department got way more efficient.
> they found SO many inconsistencies between environments
This implies somebody with admin rights makes alterations in ad-hoc way without first doing it in test env.
If they continue with adhoc stuff, then it means auto-generated migrations will be different in test vs prod. (I prefer to test exactly same thing that will be used in prod)
> This implies somebody with admin rights makes alterations in ad-hoc way without first doing it in test env.
Not necessarily. With a large team/org using the same database schema, it can just mean multiple people were trying to make changes to an environment around the same time, e.g. the migrations were applied in a different order in staging vs prod.
Some migration tools provide extra checks for strict ordering, but many do not. There's often no guarantee that the migration file naming scheme ordering, Git commit ordering, and actual DB apply ordering line up -- that's 3 different possible sources of truth, or more since the DB state varies by environment (dev/stage/prod etc).
Late-night hot-fixes (to solve an emergency outage) can be another source of inconsistencies / drift.
> If they continue with adhoc stuff, then it means auto auto-generated migrations will be different in test vs prod
That depends on the declarative tool and whether it fully syncs the schema each time, or just generates migrations which are frozen into a plan which is executed as-is in all environments. Not that full-sync is bad, but yes in that case it will generate different things in each env. Although the end result is that it will solve the drift, and give you the same end state in all environments. And that's likely what you want to happen: after running the tool, the database state will match the desired state which was expressed by the CREATE statements in your schema repo.
That said, the declarative tooling should have sufficient safety checks to ensure it doesn't do anything destructive in prod without very loudly telling you and requiring manual confirmation. That way, you won't be harmed when trying to synchronize an environment that had unexpected out-of-band changes.
> When they started integrating this tool on some trial candidates they found SO many inconsistencies between environments: server settings differences, extraneous or missing indexes, vestigial "temp" tables created during previous migrations, enum tables that should be static with extra or missing rows, etc, etc. All the environment differences meant that deployments had to be manual in the past. Once they got through the initial pain of syncing up the environments the whole department got way more efficient.
That was exactly our experience too.
Perhaps we didn't highlight enough in the blog post that schema diff was not meant to replace manual review. It simply provided a good starting point for us to iterate on the migration, which often boosts efficiency.
>If you're clever you add a manual review stage in the pipeline and have the db engineers approve the generated migration script before deployment is completed automatically.
This is how I've set it up at my current employer. It works well. We modeled it after the Terraform Plan/Apply steps, and double check that the script generated by the "apply" step matches the script generated by the "plan" step, since these can occur at significant temporal distances, and fail it if not, just so that we can be sure what we've read and approved matches what gets executed.
> db engineers approve the generated migration script
yeah - this is definitely the intended flow here. We won't be recommending anyone blindly applying generated migrations.
As you mention, it is expected that you generate & review on your local development machine, check into source control, push & merge. We've also been using this internally for ~2 years now and it works great
Do you keep the history of applied migrations? (Just in case subtle issue need to be investigated later)
1 reply →