← Back to Blog

How to Compare Database Schemas Before Deploying

Deploying a migration script without reviewing it is like merging a pull request without reading the code. It works—until it doesn't. One missing NOT NULL constraint, one dropped column still referenced by a background job, one index missing from a hot query path—and your on-call rotation starts at 2 AM.

The problem is that most teams don't have a clean way to see what changed. They rely ongit diff against SQL dump files, which is noisy, error-prone, and misses semantic meaning. This post shows a better way: semantic schema comparison that tells you exactly what changed and generates the migration script for you.

Why Text Diffs Fail for Schema Comparison

Let's say you rename a column from user_name to username and add a UNIQUE constraint. A text diff of two SQL dumps might look like this:

-- Old dump
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  user_name VARCHAR(255) NOT NULL
);

-- New dump
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE
);

A line-by-line diff flags the entire CREATE TABLE block as changed. It doesn't tell you that a column was renamed, a unique constraint was added, or that the old column was dropped. You have to reconstruct the intent manually.

Semantic diff, on the other hand, understands SQL. It sees:

• Column dropped: user_name
• Column added: username with UNIQUE
• Table altered: constraint change

That semantic understanding is what lets you generate a precise, safe ALTER TABLE script instead of guessing.

The Step-by-Step Workflow

Step 1: Export Your Schemas

Before you can compare, you need two schema snapshots: your current production schema and your proposed new schema.

PostgreSQL:

pg_dump --schema-only --no-owner --no-privileges \
  postgres://user:pass@host/dbname > schema_old.sql

MySQL / MariaDB:

mysqldump --no-data --skip-comments \
  -h host -u user -p dbname > schema_old.sql

SQLite:

sqlite3 database.db ".schema" > schema_old.sql

Do the same for your target schema—whether that's your local development database after running migrations, or a staging environment.

Step 2: Normalize and Clean

Dump files often contain noise that isn't schema structure:

• SET statements and session configuration
• Comments with timestamps or version info
• Ownership and privilege grants
• Auto-generated names for constraints

Strip these out or your diff will be flooded with false positives. The cleanest approach is to dump with flags that exclude them (--no-owner --no-privileges --no-comments in PostgreSQL) and then keep only CREATE TABLE and CREATE INDEX statements.

Step 3: Run a Semantic Diff

Paste both schemas into a semantic diff tool. Here's what you should expect to see:

1. Summary stats: tables added, removed, modified
2. Per-table breakdown: columns added/removed/modified, constraints changed, indexes added/dropped
3. Column-level detail: type changes, nullability flips, default value changes
4. Migration script: dialect-aware ALTER TABLE statements

Step 4: Review the Migration Script

Never run a generated migration blindly. Look for these common gotchas:

• Dropped columns with data: If a column is removed, does production data need to be migrated first?
• Type narrowing: Changing VARCHAR(255) to VARCHAR(50) will truncate existing data.
• NOT NULL without a default: Adding NOT NULL to a column with existing NULLs will fail.
• Missing indexes: New foreign keys or frequently queried columns need backing indexes.
• Constraint renames: Dropping and re-adding a constraint with a different name can break existing queries or ORM mappings.

Step 5: Run in Staging First

Even a perfect-looking migration can fail in practice due to locks, disk space, or existing data edge cases. Run the generated script against a staging database with production-like data volume before touching production.

PostgreSQL vs MySQL: What Changes in the Diff

Not all databases speak the same ALTER TABLE dialect. A semantic diff tool should generate the right syntax for your target database.

Change	PostgreSQL	MySQL
Change column type	ALTER COLUMN ... TYPE	MODIFY COLUMN
Add constraint	ADD CONSTRAINT	ADD CONSTRAINT
Drop default	ALTER COLUMN ... DROP DEFAULT	ALTER COLUMN ... DROP DEFAULT
Rename column	RENAME COLUMN	CHANGE COLUMN

SQLite is the most limited: it doesn't support dropping columns or altering column types directly. A semantic diff tool should warn you when a change requires recreating the table.

When to Automate This

If you're doing this manually more than once a week, automate it. The best places to hook in schema comparison are:

• Pull request CI: Compare the schema after migrations against production. Fail the build on unexpected drops or dangerous type changes.
• Pre-deploy checklist: Generate the migration diff as an artifact in your deployment pipeline.
• Weekly drift detection: Snapshot production schema weekly and diff against the expected schema from version control.

Try It Now

You don't need to install anything. Paste two schema dumps below and see the semantic diff in seconds.

Summary

• Text diffs of SQL dumps are noisy and miss semantic meaning.
• Semantic diffs show exactly which tables, columns, and constraints changed.
• Always review generated migrations for dropped columns, type narrowing, and missing defaults.
• Run migrations in staging with production-like data before deploying.
• Use a browser-based tool to keep your schema private.

SchemaLens is a browser-based SQL schema diff tool. No signup, no upload, no backend. Your schema never leaves your machine. Try it free →