How to Audit Your Database Schema Before a Major Migration

Database migrations have a reputation. Not a good one. Ask any developer who's been through a messy one, and you'll hear the same story: it all looked fine on staging, then production went sideways in ways nobody predicted. The issue, almost every time, isn't the migration itself. It's what wasn't checked before it started.

A schema audit is the thing most teams skip. It feels like extra work when there's already a deadline, a stakeholder breathing down your neck, and a staging environment that "looks fine." But skipping it is exactly how you end up with broken foreign keys, orphaned tables, and a 2 AM rollback conversation nobody wanted to have.

Here's how to do it properly.

Start With What You Actually Have, Not What You Think You Have

This sounds obvious. It isn't. Most teams work off documentation or mental models of their schema that drifted from reality months ago. Before anything else, pull the actual current state of your database directly. Not a diagram someone made two years ago. Not the ORM models in your codebase. The live schema, as it exists right now.

Run a full DDL export. Look at every table, every column, every constraint, every index. If you're using PostgreSQL, pg_dump --schema-only does this cleanly. MySQL has mysqldump --no-data. The point is to have a real, current source of truth sitting in front of you before you change a single thing.

You'll often find things here that surprise you. Columns nobody uses but nobody dropped. Tables with no foreign key constraints that probably should have them. Indexes that were added to fix a slow query three years ago and were never re-evaluated. None of this is unusual. Schemas accumulate history, and most of that history is undocumented.

Map Your Dependencies Before You Touch Anything

Foreign keys are the obvious place to start, but they're not the only place. Views, stored procedures, triggers, and application-level joins all create dependencies that won't show up in a simple schema diagram. If you drop or rename a column without checking these, you'll break things you didn't know were connected.

Go through every foreign key relationship and ask whether the referencing data is still accurate. Are there rows in child tables that point to deleted parent records? Depending on how your constraints were set up, this might not have thrown an error yet, but it will matter during migration. A quick query checking for orphaned records in each referencing table takes about ten minutes and can save hours of debugging later.

Triggers are particularly easy to forget. They're often added as quick fixes, documented nowhere, and quietly run on every insert or update in the background. List them all. Understand what they do. Decide whether they still belong in the new schema or whether this migration is the right time to clean them up properly.

Check Data Quality, Because the Schema Lies Sometimes

A column defined as NOT NULL doesn't mean the data in it is actually meaningful. A VARCHAR(255) field might contain empty strings where NULLs should be. A date column might have values from 1900 in it because an old form used that as a default when something wasn't filled in.

Data quality issues that feel minor in your current system become real problems in a new one, especially if you're migrating to a stricter database engine or changing column types. If you're moving an integer column to a smaller data type, for instance, you need to know whether any existing values would overflow it. If you're changing a nullable column to required, you need to know whether any rows currently have NULLs in it.

Write simple validation queries against your current data before defining your migration plan. Look for NULLs where you're not expecting them, look for values outside expected ranges, look for duplicates in columns that are supposed to be unique. This step is tedious. Do it anyway.

Version the Schema and Diff It Against Your Target

Once you know what your current schema looks like and have cleaned up any obvious issues, you need to define exactly what the target state is. Not loosely, not "we'll figure it out as we go." Precisely, in SQL DDL, in a file you can version-control and review.

Then compare the two. Your current schema and your target schema, sitting side by side, with every difference clearly visible. ChartDB's SQL Compare tool does exactly this. You paste in two SQL scripts, and it shows you additions, deletions, and modifications at a glance. No guessing about what changed. No reading through hundreds of lines trying to spot differences manually.

This matters more than people realize. The diff is your migration plan in raw form. Every line that shows as added or removed represents something that needs to happen in the right order, with the right dependencies accounted for. If you're adding a NOT NULL column to a large table, you need to know that before you write the migration script, not after it times out in production.

Test the Migration on a Real Data Clone, Not Just Schema

Staging environments usually have schema parity with production. They rarely have data parity. A migration that runs in four seconds on a staging database with 500 rows might run for forty minutes on production with 50 million. That's not a hypothetical. It happens constantly.

If you can't clone production data in full for privacy reasons, at minimum generate realistic data volumes in staging before running your migration against it. Pay attention to table sizes, index rebuild times, and lock behavior. Some migrations will lock tables while they run, and knowing that ahead of time changes how you plan the deployment window.

Run the migration. Then run it again on a fresh clone. Verify that the result is idempotent where it should be. Check that rollback scripts actually work before you need them under pressure.

Document What Changed and Why

This is the part that nobody does and everyone regrets skipping. After the migration, write down what the schema looked like before, what changed, and the reasoning behind each decision. Not an essay. Just enough that someone who wasn't in the room can understand the state of the database six months from now.

Schema drift is how you end up back at square one. The team changes, the documentation doesn't get updated, and two years later someone's running an audit again asking the same questions about tables nobody recognizes. Migrations are expensive. The documentation that prevents you from having to undo them is worth the extra hour.

Wrapping UP

A schema audit isn't glamorous work. Nobody's going to congratulate you for finding an orphaned table or catching a data type mismatch before it caused a problem. But that's kind of the point. The best migrations are the ones nobody notices. They go out, they work, and life moves on. The audit is what makes that possible.