Catching schema drift in CI

Comparing schemas by hand before a release works, right up to the point where somebody forgets. A pipeline does not forget, so once comparison is routine it is worth automating.

What to compare

The useful comparison is the one that predicts your next deployment. For most teams that is staging against production: if those two have diverged in a way nobody expected, the next release is going to be interesting.

Comparing a feature branch against staging is also valuable, but it answers a different question — *what will this change do?* rather than *are my environments still in agreement?* Start with the second one.

A check that fails usefully

schemasync compare ^  --source "server=%STAGE_HOST%;database=orders;user=%CI_USER%" ^  --target "server=%PROD_HOST%;database=orders;user=%CI_USER%" ^  --filters .\schemasync-filters.json ^  --report .\artifacts\schema-diff.html ^  --fail-on-difference

Three details make the difference between a check people act on and a check people mute:

1. 1A read-only account. The comparison never needs to write. Give it SELECT and SHOW VIEW, nothing more, and the step cannot cause the problem it is looking for.
2. 2A committed filter file. Without one, every AUTO_INCREMENT difference fails the build and the step gets disabled within a week. The filter file is also a written record of how your environments are allowed to differ.
3. 3A report as an artefact. A red build that says only "schemas differ" sends someone hunting. Publish the HTML report and the answer is one click away.

When to run it

On every merge to the release branch

Catches drift at the point where it is about to matter, while the change is fresh in someone's mind.

Nightly

Catches manual changes made directly against an environment during the day, which is exactly where the emergency index shows up.

Immediately after a deployment

Confirms the deployment produced the schema you expected. This is the cheapest verification step available and almost nobody runs it.

Expect noise at first

The first run will report differences you did not know about, and most of them will turn out to be legitimate environment-specific exceptions. Resist the urge to filter them all away in one sitting. Work through them, decide in each case whether it is drift or intent, and write only the intentional ones into the filter file.

That first pass is tedious and it is also the most valuable part of the exercise: at the end of it you have an explicit, reviewed statement of how your environments differ. Afterwards, the check stays quiet until something real changes — which is the only kind of check worth having.