sqldumpdiff 0.1.0

Compare SQL dump files and generate delta update scripts

SQL Dump Diff

A Python tool to compare two SQL dump files and generate a delta SQL script containing INSERT, UPDATE, and DELETE statements to transform the old dump into the new dump.

Features

• 🔍 Schema-aware comparison: Automatically detects PRIMARY KEY constraints to identify records
• 📊 Progress tracking: Visual progress bars for large dump files using tqdm
• 📈 Summary statistics: Displays counts of inserts, updates, and deletes at the end
• 🔄 Multi-line support: Handles multi-line CREATE TABLE and INSERT statements
• 📦 Multi-row INSERT support: Handles default mysqldump multi-value INSERTs and INSERTs without explicit column lists
• 🧵 Parallel per-table diffing: Splits dumps by table and optionally processes tables in parallel
• 🎯 Composite keys: Supports tables with composite primary keys
• 📝 Delta generation: Produces a complete SQL script with:
  • INSERT statements for new records
  • UPDATE statements for modified records
  • DELETE statements for removed records
• ⚡ Efficient processing: Indexes records by primary key for fast lookups
• 🔌 Flexible output: Output to file or stdout for easy piping to other commands

Installation

This project uses uv for dependency management.

Prerequisites

• Python 3.14 or higher
• uv installed

Setup

1. Clone the repository:

   git clone <repository-url>
   cd sqldumpdiff
   

2. Install dependencies with uv:

   uv sync
   

Usage

Basic Usage

The script accepts two required arguments and an optional third argument:

uv run sqldumpdiff.py <old_dump.sql> <new_dump.sql> [output.sql]

• If output.sql is provided: The delta script is written to that file
• If output.sql is omitted: The delta script is printed to stdout (useful for piping)

Note: Progress messages and summary statistics are always printed to stderr, so they won't interfere with stdout output when piping.

Examples

Write to a file

uv run sqldumpdiff.py database_old.sql database_new.sql delta.sql

This creates delta.sql with the delta script.

Print to stdout

uv run sqldumpdiff.py database_old.sql database_new.sql

The delta SQL is printed to stdout. Progress messages appear on stderr.

Pipe to MySQL

uv run sqldumpdiff.py database_old.sql database_new.sql | mysql mydatabase

Pipe to a file

uv run sqldumpdiff.py database_old.sql database_new.sql > delta.sql

Output Example

The generated delta script will contain:

-- Full Delta Update Script
SET FOREIGN_KEY_CHECKS = 0;

-- NEW RECORD IN users
INSERT INTO `users` (`id`, `name`, `email`) VALUES ('1', 'John Doe', '[e-mail]');

-- email old value: [e-mail]
UPDATE `users` SET `email`='[e-mail]' WHERE `id`='1';

-- DELETIONS
-- DELETED FROM users: ('2',)
DELETE FROM `users` WHERE `id`='2';

SET FOREIGN_KEY_CHECKS = 1;

Summary Output

After processing, the script displays a summary on stderr:

============================================================
SUMMARY
============================================================
Inserts:  1,234
Updates:  567
Deletes: 89
Total:    1,890
============================================================

How It Works

The tool performs the following steps:

1. Schema Parsing: Extracts PRIMARY KEY definitions and column order from CREATE TABLE statements
2. Per-table splitting: Streams INSERT statements and writes per-table row files (handles multi-row VALUES)
3. Comparison (parallel-capable): For each table, compares new rows vs old by primary key
   • Records not found in the old dump → INSERT statements
   • Records with changed values → UPDATE statements
   • Records matched are tracked to identify deletions
4. Deletion Detection: Identifies records present in the old dump but missing from the new dump → DELETE statements

Requirements

• Python 3.14+
• tqdm for progress bars

Limitations

• Tables without PRIMARY KEY constraints are skipped (records cannot be uniquely identified)
• Requires both dump files to be valid SQL with proper encoding (UTF-8)

Performance

Optimization Strategies

The tool uses multiple strategies to handle large SQL dumps efficiently:

1. Streaming approach: Processes data incrementally to handle files larger than available memory

2. Parallel processing:

   • INSERT statement parsing can be parallelized during the split phase using process-based parallelism to bypass Python's GIL
   • Table comparison runs in parallel with configurable workers
   • Both old and new files are split concurrently

Environment Variables

Parallelism is enabled by default when more than one table exists. Fine-tune performance with these environment variables:

Table Comparison

• SQLDUMPDIFF_PARALLEL=0 - Disable parallel processing entirely
• SQLDUMPDIFF_WORKERS=<n> - Set worker count for table comparison (default: CPU cores)
• SQLDUMPDIFF_WORKERS=-1 - Use as many workers as there are tables (subject to hard cap)
• SQLDUMPDIFF_MAX_WORKERS=<n> - Hard cap on workers (default: 4x CPU cores)
• SQLDUMPDIFF_EXECUTOR=thread - Use threads instead of processes for table comparison (default: process)

INSERT Parsing Optimization

• SQLDUMPDIFF_PARALLEL_INSERTS=1 - Enable parallel INSERT processing during splitting (recommended for large files)
• SQLDUMPDIFF_INSERT_WORKERS=<n> - Worker count for INSERT parsing (default: CPU cores)
• SQLDUMPDIFF_INSERT_EXECUTOR=thread - Use threads instead of processes (default: process)

Performance Tips

For best performance with large dumps:

# Recommended settings for large files
SQLDUMPDIFF_PARALLEL_INSERTS=1 \
SQLDUMPDIFF_EXECUTOR=thread \
SQLDUMPDIFF_WORKERS=-1 \
uv run sqldumpdiff.py old.sql new.sql > delta.sql

Note: Multi-row INSERTs are expanded per row during splitting to avoid redundant parsing during comparison.

Benchmarking

To find the optimal worker settings for your system, use the provided benchmark script:

./benchmark.sh 11.sql 22.sql

The script will test various configurations and save results with timing information. Common findings:

• Process-based parallelism (INSERT_EXECUTOR=process) performs better than threads for CPU-intensive INSERT parsing
• Parallel INSERT processing (PARALLEL_INSERTS=1) significantly speeds up large files (3-4x improvement)
• Worker count: Start with CPU cores, experiment with 2x CPU cores for I/O bound workloads
• Table comparison: Thread-based (EXECUTOR=thread) often performs better than processes when you have many small tables

Review benchmark_results_*.txt to identify the fastest configuration for your data.

Language Performance Considerations

Python's GIL Limitations

Python's Global Interpreter Lock (GIL) limits true parallel CPU execution for threads. While process-based parallelism (as used in this tool) bypasses the GIL, it has overhead for inter-process communication and memory duplication.

Alternative Languages for Better Performance

If you need significantly faster execution, consider these alternatives:

1. Rust (Recommended - Best Performance)

• Speed: 5-10x faster than Python
• Pros:
  • True parallelism without GIL
  • Zero-cost abstractions
  • Memory safety without garbage collection
  • Excellent for CPU-intensive parsing
  • Single binary deployment
• Cons: Steeper learning curve, longer development time
• Best for: Maximum performance, production deployments at scale

2. Go (Recommended - Best Balance)

• Speed: 3-5x faster than Python
• Pros:
  • Native concurrency with goroutines (cheap, lightweight)
  • Simple syntax, easy to learn
  • Fast compilation
  • Single binary deployment
  • Good standard library for parsing
• Cons: Less control than Rust, garbage collection overhead
• Best for: Fast development with good performance gains

3. C++ (Maximum Speed)

• Speed: 10-20x faster than Python (when optimized)
• Pros:
  • Absolute maximum performance
  • Full control over memory and threading
• Cons: Complex, manual memory management, longer development
• Best for: When every millisecond counts

4. Java/Kotlin (Enterprise Option)

• Speed: 2-4x faster than Python
• Pros:
  • Mature ecosystem
  • Good JVM threading
  • Great tooling
• Cons: JVM startup overhead, heavier runtime
• Best for: Enterprise environments with existing Java infrastructure

Realistic Performance Gains

Current Python implementation with optimizations: ~5:43 for your dataset

Estimated rewrite times:

• Go: ~2-3 minutes (50-60% faster)
• Rust: ~1-2 minutes (70-80% faster)
• C++: ~1 minute (80-90% faster, highly optimized)

Recommendation

For most use cases, the current Python implementation with PARALLEL_INSERTS=1 is sufficient. Consider a rewrite only if:

• You run this tool very frequently (hundreds of times per day)
• You need sub-minute execution for large dumps
• You want to distribute a zero-dependency binary

Quick win: The Go rewrite would be the best ROI (2-3 weeks development for 3-5x speedup).

Output Format

The generated delta script:

• Disables foreign key checks at the start
• Includes comments indicating the type of change (NEW RECORD, UPDATE, DELETION)
• For UPDATEs, includes comments showing the old values
• Re-enables foreign key checks at the end

Building a Native Executable

You can create a standalone executable that doesn't require Python to be installed.

Prerequisites

Install build dependencies:

uv sync --extra dev

Building

Use the provided build script:

./build.sh

Or build manually with PyInstaller:

uv run pyinstaller --onefile --name sqldumpdiff sqldumpdiff.py

The executable will be created in the dist/ directory.

Using the Executable

After building, you can use the executable directly:

# Write to file
./dist/sqldumpdiff <old_dump.sql> <new_dump.sql> output.sql

# Print to stdout
./dist/sqldumpdiff <old_dump.sql> <new_dump.sql>

The executable is platform-specific (macOS, Linux, or Windows) and includes all dependencies, so it can be distributed without requiring Python or any packages to be installed.

Development

Running the Script

# With output file
uv run python sqldumpdiff.py <old_dump.sql> <new_dump.sql> <output.sql>

# Print to stdout
uv run python sqldumpdiff.py <old_dump.sql> <new_dump.sql>

Project Structure

sqldumpdiff/
├── sqldumpdiff.py    # Main script
├── pyproject.toml    # Project configuration
├── build.sh          # Build script for executable
├── sqldumpdiff.spec  # PyInstaller spec file
├── uv.lock           # Dependency lock file
└── README.md         # This file

License

[Add your license here]

Contributing

[Add contribution guidelines here]