Finding what changed between database dumps

A question between backups

I have often had two dumps of the same database and wanted a quick answer to a very specific question: which records appeared, disappeared or changed? Looking through thousands of lines of INSERT statements with a text diff gets tiring, especially if rows move around or the dump format changes.

DumpTales reads snapshots locally and compares rows by primary key. It can report supported changes to table columns, primary keys and foreign keys as well. It reads conventional MySQL and MariaDB SQL dumps, experimental PostgreSQL COPY dumps, and native SQLite database files. It does not execute the SQL or connect to a database server.

A tiny example

Here are two deliberately small MySQL dumps. The second upgrades Acme's plan, removes Old Co, and adds New Co. Harbour stays as it was.

before.sql

CREATE TABLE `accounts` (
  `id` int NOT NULL,
  `name` varchar(80) NOT NULL,
  `plan` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `accounts` VALUES (1,'Acme','basic'),(2,'Harbour','basic'),(3,'Old Co','trial');

after.sql

CREATE TABLE `accounts` (
  `id` int NOT NULL,
  `name` varchar(80) NOT NULL,
  `plan` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `accounts` VALUES (1,'Acme','pro'),(2,'Harbour','basic'),(4,'New Co','trial');

You can download before.sql and after.sql to try the example yourself:

dumptales before.sql after.sql --color always

This is the actual output from those files, with its terminal colours represented on the page:

~ accounts ['1']
    plan: 'basic' → 'pro'
- accounts ['3']
+ accounts ['4']
Summary: added=1, removed=1, changed=1, schema=0 (engine=stream, parser=python)

The yellow ~ marks a changed row and shows the field values. Red - and green + mark removed and added keys. This is a snapshot comparison: if a primary key changes, the result is a removal and an addition. The output does not claim to know what sequence of SQL statements caused the difference.

Working with bigger dumps

DumpTales inspects both inputs to choose its MySQL, PostgreSQL or SQLite reader. If it cannot recognise a dump, or the two inputs appear to use different dialects, it stops and asks you to specify --dialect explicitly. The same command works for a conventional plain-text pg_dump as for the MySQL example above:

dumptales forgejo-before.sql forgejo-after.sql

Compression is detected from the file contents, regardless of the filename. Plain SQL, gzip, bzip2 and ZIP archives containing exactly one SQL dump can be compared directly. Native SQLite database files need to remain uncompressed. A PostgreSQL custom-format archive made with pg_dump -Fc is a different format from a ZIP file and is outside the supported inputs.

For MySQL dumps in compatible primary-key order, DumpTales streams through both inputs. When rows are out of order, its automatic engine falls back to compressed hash partitions on temporary disk. There are JSON and JSONL output formats if you want to inspect changes with another tool:

dumptales old.sql.gz new.sql.gz --format json | jq

This yields:

{
  "version": "0.1.2",
  "changes": [
    {
      "kind": "changed",
      "table": "accounts",
      "key": [
        "1"
      ],
      "fields": {
        "plan": {
          "before": "basic",
          "after": "pro"
        }
      }
    },
    {
      "kind": "removed",
      "table": "accounts",
      "key": [
        "3"
      ],
      "before": {
        "id": "3",
        "name": "Old Co",
        "plan": "trial"
      }
    },
    {
      "kind": "added",
      "table": "accounts",
      "key": [
        "4"
      ],
      "after": {
        "id": "4",
        "name": "New Co",
        "plan": "trial"
      }
    }
  ],
  "summary": {
    "counts": {
      "changed": 1,
      "removed": 1,
      "added": 1
    },
    "skipped_tables": [],
    "unchanged_tables_skipped": [],
    "old_rows": 3,
    "new_rows": 3,
    "engine": "stream",
    "parser": "native",
    "relationships_complete": true
  }
}

I made this for those moments when the backup exists, the database may be elsewhere, and I just need to understand the change. The source and installation instructions are available on my Forgejo.