sql - How to compare two SQLite databases on Linux

Question

Using Linux, I want to compare two SQLite databases that have the same schema. There will be just a few differences.

Is there a tool that would output these differences? Preferably output them to the command line, so that I can grep/sed them.

SQLite uses SQL, so a general SQL tool might also do.

Answer 1

Please have a look at the SQLite Release 3.8.10 which was released on May 7, 2015. This release for the first time contains the sqldiff.exe utility program for computing the differences between two SQLite database files. Most likely this program will also be part of future releases.

The sqldiff.exe command-line line tool should work for all supported operating systems and offers several switches for altering its output behavior. Example usage:

sqldiff [options] database1.sqlite database2.sqlite

If no options are specified, then the output of sqldiff.exe is SQL statements that will transform database1.sqlite (the "source" database) into database2.sqlite (the "destination" database).

However, there are also certain limitations. For example, the sqldiff.exe utility (at least currently) does not display differences in TRIGGERs, VIEWs, or virtual tables.

---

Sample command and output

I took a simple key-value store database (db1.sqlite) and made a copy of it (db2.sqlite). I then inserted one key-value pair into db2.sqlite. After that I ran the following command:

sqldiff db1.sqlite db2.sqlite

and got the following output:

INSERT INTO my_table(rowid,"key",value) VALUES(1,'D:TestTest.txt',x'aabbccdd');
UPDATE my_table_size SET counter=1 WHERE rowid=1;

The table my_table_size was automatically updated by a TRIGGER after the key-value pair was inserted to my_table. I then ran sqldiff.exe again, but this time with with db2.sqlite as first argument and db1.sqlite as second argument:

sqldiff db2.sqlite db1.sqlite

and got the following output:

DELETE FROM my_table WHERE rowid=1;
UPDATE my_table_size SET counter=0 WHERE rowid=1;

---

sqldiff download links

Since SQLite version 3.10.2 which was released on January 20, 2016, the 32-bit binaries for sqldiff can be directly downloaded from the SQLite Download Page. They can be found in the sqlite tools archives for the corresponding operating systems (see the Precompiled Binaries sections). For example, here are the links to the sqlite tools archives of version 3.36.0:

• SQLite tools for Linux
• SQLite tools for OS X
• SQLite tools for Windows

For SQLite versions prior to version 3.10.2, the SQLite website hosts 32-bit binaries for sqldiff, but does not link to them. Here are the links to sqldiff of version 3.8.10:

• sqldiff for Linux
• sqldiff for OS X
• sqldiff for Windows

If you need 64-bit binaries, then you have to download the raw sources and compile them by yourself. (The file sqldiff.c is located in the tool sub-directory of the archive containing the sources.)