Open your staging database and your production database as two diagrams in the same Schemity workspace, copy every entity from one and paste it into the other, and the drift draws itself: tables that exist in both snap into the same positions, and tables that exist only in the source arrive with a dashed border. No dump, no diff, no scrolling.
You already know the schemas don’t match. That is not the hard part. The hard part is that nobody can say how they don’t match without an afternoon of forensic work, and so the answer is usually a shrug and a deploy.
Why staging and production schemas drift apart
The gap opens through completely ordinary behavior. Something breaks in production at an inconvenient hour and someone runs an ALTER TABLE directly against it, meaning to backport the change into the migration files later. Red Gate, describing how this actually plays out, notes that many DBAs are forced to short-circuit the usual dev-test-prod model and apply the fix straight to production, because standing up a parallel hotfix environment “would cost too much in resources, time and money.” Bytebase calls emergency hotfixes the number-one source of schema drift, for exactly this reason.
Then the environments diverge on their own. Neon’s account of why your staging database never matches production puts numbers on the baseline: with a nightly refresh, “staging is between 0 and 24 hours behind production,” and on a weekly cycle, 168 hours behind. Indexes are where it bites hardest - one added to production to rescue a slow query and never propagated back, so the same query “completes in 50ms in staging and times out after 30 seconds in production.”
And migration tooling will not catch it for you. Flyway, Liquibase and Alembic record which migrations ran; none of them verify that the resulting schema is the one you expect. A migration marked applied says nothing about what somebody did to the table afterwards by hand.
How do I compare database schemas between staging and production?
The standard answers are all textual, and text is the wrong shape for this question.
| Approach | What you get | What it costs |
|---|---|---|
pg_dump -s both, then diff | Every difference, exactly | Sequence values, comment lines and column ordering create noise; a 200-table schema is thousands of lines |
information_schema comparison queries | Precise column-level answers | You have to know what to ask before you ask it |
| Vendor schema-compare tools | A change script | Usually a per-seat subscription, and often SQL Server only |
| Two diagrams, side by side | Where the difference sits in the model | You need a tool that can hold both environments at once |
The last row is the one that answers the question people are really asking. orders gained a fulfilment_status column in production is a fact. The fulfilment domain has a column in production that no other environment has, and three tables around it reference it is an answer. Only the second one tells you whether the next deploy is safe.
Holding two environments in one workspace
A Schemity workspace holds as many connections and diagrams as you want, so app-production, app-staging and app-local live next to each other in one list. Tag each connection by environment - Local, Staging, Production - and the list stays scannable, so you never have to squint at a host string to work out which database you are about to read. Test the connection before saving it, and every password goes into your operating system’s keychain rather than a config file.
Then reverse-engineer each one into its own diagram. Open them at once and switch with Ctrl/Cmd + 1, 2, 3 - the same schema, three environments, one keystroke apart. Because Schemity is a desktop ERD tool working over your own connections, all three reads happen from your machine; nothing about your production schema is uploaded anywhere to make the comparison possible.
That alone is worth the setup. But flipping between two tabs only helps if the two pictures are arranged the same way, and a fresh reverse-engineer never is.
Merge-aware paste turns the layout into the diff
This is where the comparison becomes visual. Select every entity in the production diagram, copy, switch to the staging tab, and paste. Schemity matches entities by name and treats the two cases differently:
- An entity that already exists in staging receives only the visual attributes - position, size and color, with the color carrying through to its relationship lines. The schema in staging is not touched.
- An entity that does not exist in staging yet is pasted in full, with a dashed border marking it as a draft that the database has not confirmed.
So after one paste, staging is laid out exactly like production, and every dashed box on the canvas is a table production has that staging does not. Reverse the direction and you get the other half of the answer. The drift is not a report you read - it is the shape of the picture.
Nothing has been written to staging. Dashed entities are drafts, not changes: on a live-connected diagram the schema only persists after a migration succeeds, and saving warns you about entities the database has never heard of. If you decide the gap should be closed, the generated migration SQL diff hands you the ALTER statements to review - your decision, applied deliberately, not a sync button.
For field-level differences inside tables that exist in both, the diagram carries the detail on its face: nullable flags, defaults, unique badges and check-constraint underlines are all readable without opening a single dialog, and fuzzy search jumps you to any column by a fragment of its name.
Keeping the comparison honest over time
A one-off comparison is a snapshot. What makes it a practice is that each diagram re-syncs itself against its own database every time you open it - so the production diagram is always today’s production, and the staging diagram is always today’s staging. You are never comparing two stale pictures of each other.
Underneath, every diagram is a plain JSON file in a folder you can commit. Commit all three environment diagrams into the repo and the drift acquires a history: git diff shows what changed in production between Tuesday and Friday, reviewable in a pull request alongside the migration that should have caused it. That is the same argument as keeping the ERD in your Git repo, pointed at a different problem - not documenting one schema, but auditing the distance between several.
If the schemas are large, scope the comparison. A context view is a read-only, focused subset of the main diagram, showing one domain’s entities without altering the model behind it. Compare the billing context across environments instead of all 200 tables, and export the SQL of just that view when you need the DDL. When the question is architectural rather than column-level, the Context Map answers it at the level of whole contexts.
Schema drift is not exotic. It is the ordinary residue of production being where the pressure lands - the environment where hotfixes happen, indexes appear, and nobody has time to backport. What you need is not a tool that prevents it, because nothing does. You need to be able to see it in ten seconds, on a picture you already understand, without your schema leaving the building to make it possible.