Compare SQL Schema Definitions and Detect Migration Changes

SQL Schema Diff

Comparing SQL schema definitions helps database administrators and developers understand what changed between schema versions, generate migration scripts, and validate that migrations will produce the expected result.

Schema Comparison Example

-- Version A
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Version B
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    role ENUM('admin', 'user') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Detected Changes

Table: users
  Modified: id       INT → BIGINT
  Modified: email    added NOT NULL constraint
+ Added:   role     ENUM('admin','user') DEFAULT 'user'
+ Added:   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Types of Schema Changes

Change Type	Example	Migration Risk
Column added	New role column	Low — nullable or has default
Column removed	Dropped legacy_field	High — data loss
Type changed	INT → BIGINT	Medium — may require data conversion
Constraint added	NOT NULL added	High — existing NULLs block migration
Index added	New index on email	Low — but can be slow on large tables
Default changed	DEFAULT value modified	Low
Table added	New audit_log table	Low
Table removed	Dropped temp_data	High — data loss

Generating Migration Scripts

From the diff above, an ALTER TABLE script can be generated:

ALTER TABLE users MODIFY COLUMN id BIGINT AUTO_INCREMENT;
ALTER TABLE users MODIFY COLUMN email VARCHAR(255) UNIQUE NOT NULL;
ALTER TABLE users ADD COLUMN role ENUM('admin', 'user') DEFAULT 'user' AFTER email;
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Cross-Database Compatibility

Different databases have different DDL syntax. A schema diff tool should understand the dialect:

• MySQL: AUTO_INCREMENT, ENUM, MODIFY COLUMN
• PostgreSQL: SERIAL, ALTER COLUMN ... TYPE, custom types
• SQLite: Limited ALTER TABLE support