dbdiff - Database Schema Comparison Tool

A fast, clean CLI tool written in Go that compares two databases (PostgreSQL/MySQL) for schema-level differences.

Features

Schema Comparison

Compares the following schema elements:

Tables - presence/absence
Columns - data type, nullability, default values
Primary Keys - columns
Foreign Keys - columns, referenced table/columns, ON DELETE/UPDATE rules
Unique Constraints - columns
Indexes - name, columns, uniqueness
Check Constraints - expressions (where supported)

v2 Features ✨

🔧 Migration Generation - Automatically generate SQL migration scripts from detected differences
🎯 Filtering Options - Ignore specific tables, columns, or schema objects
- Ignore tables by name (comma-separated list)
- Ignore tables by regex pattern
- Ignore all indexes, foreign keys, or check constraints
⚡ Parallel Extraction - Concurrent schema extraction for faster performance on large databases

Supported Databases

PostgreSQL
MySQL

Installation

Option 1: Download Pre-built Binaries (Recommended)

Download the latest release for your platform from the Releases page.

Linux (AMD64)

wget https://github.com/YOUR_USERNAME/dbdiff/releases/latest/download/dbdiff-linux-amd64
chmod +x dbdiff-linux-amd64
sudo mv dbdiff-linux-amd64 /usr/local/bin/dbdiff

Linux (ARM64)

wget https://github.com/YOUR_USERNAME/dbdiff/releases/latest/download/dbdiff-linux-arm64
chmod +x dbdiff-linux-arm64
sudo mv dbdiff-linux-arm64 /usr/local/bin/dbdiff

macOS (Intel)

wget https://github.com/YOUR_USERNAME/dbdiff/releases/latest/download/dbdiff-darwin-amd64
chmod +x dbdiff-darwin-amd64
sudo mv dbdiff-darwin-amd64 /usr/local/bin/dbdiff

macOS (Apple Silicon)

wget https://github.com/YOUR_USERNAME/dbdiff/releases/latest/download/dbdiff-darwin-arm64
chmod +x dbdiff-darwin-arm64
sudo mv dbdiff-darwin-arm64 /usr/local/bin/dbdiff

Windows (AMD64)

# Download dbdiff-windows-amd64.exe from releases
# Add to your PATH or run directly

Windows (ARM64)

# Download dbdiff-windows-arm64.exe from releases
# Add to your PATH or run directly

Option 2: Build from Source

# Clone the repository
git clone https://github.com/YOUR_USERNAME/dbdiff.git
cd dbdiff

# Install dependencies
go mod download

# Build for current platform
make build

# Or build for all platforms
make build-all

# Optional: Install globally
make install

Usage

Basic Usage

dbdiff \
  --source <source-connection-string> \
  --source-driver <postgres|mysql> \
  --target <target-connection-string> \
  --target-driver <postgres|mysql> \
  [options]

Command-Line Options

Required Flags:

--source <conn> - Source database connection string
--source-driver <driver> - Source database driver (postgres or mysql)
--target <conn> - Target database connection string
--target-driver <driver> - Target database driver (postgres or mysql)

Output Options:

--json - Output as JSON (for automation/CI/CD)
--migration - Generate SQL migration script

Performance Options:

--parallel - Use parallel schema extraction (faster for large databases)

Filter Options:

--ignore-tables <list> - Comma-separated list of table names to ignore
--ignore-table-pattern <regex> - Regex pattern for table names to ignore
--ignore-indexes - Ignore all index differences
--ignore-foreign-keys - Ignore all foreign key differences
--ignore-checks - Ignore all check constraint differences

Examples

Basic Comparison (PostgreSQL)

dbdiff \
  --source "postgres://user:pass@localhost:5432/db1?sslmode=disable" \
  --source-driver postgres \
  --target "postgres://user:pass@localhost:5432/db2?sslmode=disable" \
  --target-driver postgres

MySQL Comparison

dbdiff \
  --source "user:pass@tcp(localhost:3306)/db1?parseTime=true" \
  --source-driver mysql \
  --target "user:pass@tcp(localhost:3306)/db2?parseTime=true" \
  --target-driver mysql

Cross-Database Comparison

You can compare schemas across different database types:

dbdiff \
  --source "postgres://user:pass@localhost:5432/pgdb?sslmode=disable" \
  --source-driver postgres \
  --target "user:pass@tcp(localhost:3306)/mysqldb?parseTime=true" \
  --target-driver mysql

Generate Migration SQL

dbdiff \
  --source "postgres://user:pass@localhost:5432/db1?sslmode=disable" \
  --source-driver postgres \
  --target "postgres://user:pass@localhost:5432/db2?sslmode=disable" \
  --target-driver postgres \
  --migration > migration.sql

Ignore Specific Tables

dbdiff \
  --source "..." \
  --source-driver postgres \
  --target "..." \
  --target-driver postgres \
  --ignore-tables "temp_logs,old_data,cache_table"

Ignore Tables by Pattern

# Ignore all tables starting with "temp_" or "old_"
dbdiff \
  --source "..." \
  --source-driver postgres \
  --target "..." \
  --target-driver postgres \
  --ignore-table-pattern "^(temp_|old_)"

Ignore Indexes and Foreign Keys

dbdiff \
  --source "..." \
  --source-driver postgres \
  --target "..." \
  --target-driver postgres \
  --ignore-indexes \
  --ignore-foreign-keys

Parallel Extraction for Large Databases

dbdiff \
  --source "..." \
  --source-driver postgres \
  --target "..." \
  --target-driver postgres \
  --parallel

JSON Output for CI/CD

dbdiff \
  --source "..." \
  --source-driver postgres \
  --target "..." \
  --target-driver postgres \
  --json

Exit Codes

0 - No differences found
1 - Error occurred
2 - Differences found

This makes it easy to use in CI/CD pipelines:

if dbdiff --source "$SOURCE" --source-driver postgres --target "$TARGET" --target-driver postgres; then
  echo "Schemas match!"
else
  echo "Schema drift detected!"
  exit 1
fi

Output Format

Pretty Text Output (Default)

Schema Differences Found:
================================================================================

📋 Tables only in SOURCE:
  - old_table

📋 Tables only in TARGET:
  + new_table

📊 Table: users
--------------------------------------------------------------------------------
  Columns only in TARGET:
    + email_verified

  Column differences:
    ~ age: type: integer → bigint
    ~ status: nullable: false → true

  Primary Key: columns: [id] → [id email]

  Foreign Keys only in TARGET:
    + fk_users_roles

  Indexes differences:
    ~ idx_email: unique: false → true

JSON Output

{
  "tables_only_in_source": ["old_table"],
  "tables_only_in_target": ["new_table"],
  "table_diffs": [
    {
      "table_name": "users",
      "columns_only_in_target": ["email_verified"],
      "column_diffs": [
        {
          "column_name": "age",
          "diff": "type: integer → bigint"
        }
      ]
    }
  ]
}

Architecture

The tool is structured with clean separation of concerns:

Models - Schema representation (Table, Column, PrimaryKey, ForeignKey, etc.)
Dialects - Database-specific schema extraction (PostgresDialect, MySQLDialect)
Diff Engine - Schema comparison logic
Output - Pretty text and JSON formatters
CLI - Command-line interface and main orchestration

Extending

To add support for a new database:

Implement the Dialect interface:

type Dialect interface {
    ExtractSchema(db *sql.DB) (*Schema, error)
}

Add the dialect to getDialect() function
Import the appropriate database driver

Testing

To test the tool, you'll need running database instances. Here's a quick setup using Docker:

PostgreSQL Test Setup

# Start two PostgreSQL instances
docker run -d --name pg1 -e POSTGRES_PASSWORD=pass -p 5432:5432 postgres:15
docker run -d --name pg2 -e POSTGRES_PASSWORD=pass -p 5433:5432 postgres:15

# Create test schemas
psql "postgres://postgres:pass@localhost:5432/postgres" -c "CREATE TABLE users (id INT PRIMARY KEY, name TEXT);"
psql "postgres://postgres:pass@localhost:5433/postgres" -c "CREATE TABLE users (id BIGINT PRIMARY KEY, name TEXT, email TEXT);"

# Run comparison
./dbdiff \
  --source "postgres://postgres:pass@localhost:5432/postgres?sslmode=disable" \
  --source-driver postgres \
  --target "postgres://postgres:pass@localhost:5433/postgres?sslmode=disable" \
  --target-driver postgres

MySQL Test Setup

# Start two MySQL instances
docker run -d --name mysql1 -e MYSQL_ROOT_PASSWORD=pass -p 3306:3306 mysql:8
docker run -d --name mysql2 -e MYSQL_ROOT_PASSWORD=pass -p 3307:3306 mysql:8

# Create test schemas
mysql -h 127.0.0.1 -P 3306 -u root -ppass -e "CREATE DATABASE testdb; USE testdb; CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(255));"
mysql -h 127.0.0.1 -P 3307 -u root -ppass -e "CREATE DATABASE testdb; USE testdb; CREATE TABLE users (id BIGINT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255));"

# Run comparison
./dbdiff \
  --source "root:pass@tcp(127.0.0.1:3306)/testdb?parseTime=true" \
  --source-driver mysql \
  --target "root:pass@tcp(127.0.0.1:3307)/testdb?parseTime=true" \
  --target-driver mysql

Creating a Release

For Maintainers

To create a new release with pre-built binaries:

Tag the release:

git tag -a v1.0.0 -m "Release version 1.0.0"
git push origin v1.0.0

GitHub Actions will automatically:
- Build binaries for all platforms (Linux, macOS, Windows - AMD64 & ARM64)
- Generate SHA256 checksums
- Create a GitHub release with all binaries attached
- Add installation instructions to the release notes

Manual local build (optional):

# Build all platform binaries locally
make release VERSION=1.0.0

# Binaries will be in build/ directory
ls -lh build/

Available Platforms

Linux: AMD64, ARM64
macOS: AMD64 (Intel), ARM64 (Apple Silicon)
Windows: AMD64, ARM64

Development

Makefile Commands

make help          # Show all available commands
make build         # Build for current platform
make build-all     # Build for all platforms
make checksums     # Generate SHA256 checksums
make install       # Install to /usr/local/bin
make clean         # Remove build artifacts
make test          # Run tests
make deps          # Download dependencies
make release       # Create release build (requires VERSION)

Project Structure

dbdiff/
├── main.go                      # Complete implementation (~1,300 lines)
├── go.mod                       # Go module definition
├── Makefile                     # Build automation
├── .github/workflows/release.yml # Automated release workflow
└── README.md                    # Documentation

License

MIT

Contributing

Contributions welcome! This is a production-ready MVP that can be extended with:

More database support (SQLite, SQL Server, Oracle, etc.)
Schema migration generation
Filtering options (ignore certain tables/columns)
Configuration file support
Parallel schema extraction for large databases
Data comparison (not just schema)
Homebrew formula for easier macOS installation
Chocolatey package for easier Windows installation

Name		Name	Last commit message	Last commit date
Latest commit History 3 Commits
.github/workflows		.github/workflows
.gitignore		.gitignore
Makefile		Makefile
QUICKSTART_RELEASE.md		QUICKSTART_RELEASE.md
README.md		README.md
RELEASE.md		RELEASE.md
go.mod		go.mod
go.sum		go.sum
main.go		main.go

nidhey27/dbdiff

Folders and files

Latest commit

History

Repository files navigation