How to compare two SQLite databases on Linux

Know your tools: Going beyond 'sqldiff'

Now, sqldiff is not the Oracle for SQLite. Its kryptonite is triggers, views, and virtual tables: it can't compare them. If you need to handle such cases, let's broaden our arsenal of tools.

'.dump' and 'diff': The Dynamic Duo

In comes sqlite3 with its .dump command to turn your SQLite database into an SQL text file. Follow it up with the diff command:

sqlite3 db1.db ".dump" > dump1.sql
sqlite3 db2.db ".dump" > dump2.sql
diff dump1.sql dump2.sql


This nifty bit of shell magic gives you line-by-line differences for those SQL files, perfect for both data and structure inconsistencies.

Automation: Let the Script Do the Work

If you're more of a set-and-forget type, you'll want an automated solution. Good news: sqlite3 .dump and diff can be used in tandem, with the addition of grep or sed, for scripted filtering and emphasis on differences you care about:

#!/bin/bash
# Because who has time to do stuff manually, amiright?

DB1=db1.db
DB2=db2.db
DUMP1=dump1.sql
DUMP2=dump2.sql

# Dump the databases. No, not like that.
sqlite3 $DB1 ".dump" > $DUMP1
sqlite3 $DB2 ".dump" > $DUMP2

# Let 'diff' handle the 'diff'icult part
# Adjust the diff's output to your liking. Your pizza, your toppings!
diff -y --suppress-common-lines $DUMP1 $DUMP2 | grep -E "^\|"


Exploring third-party tools: GUI and Commercial Solutions

When Command Line Isn’t Enough: SQLite GUI tools

Not everyone is a command-line wizard. Luckily, there's DB Browser for SQLite, a cross-platform tool that lets you handle SQLite databases with ease:

1. Open both databases.
2. Examine data and structure side by side.
3. Utilise the in-built SQL functions for comparisons.

When Free Isn’t Enough: Commercial Tools

When you require high-end features and premium support, commercial tools like SQLite Diff step in. This paid tool eases database comparison by providing clear difference reports, ready to conquer your command line.

Specific cases: Handling Large Databases

For whale-sized databases, direct diff of .dump files can choke your CPU. You can optimise by breaking down the dumps to smaller, more digestible chunks, or by comparing table-by-table.