I would like to compare 2 SQLite databases in order to check whether it needs to be updated on the client computer.
I am not really sure how I should do this. Whether I should make an internal version ID or compare the file size (which probally is not a good idea because I think the file size doesn't change anytime I edit the database).
Does anybody know a good way to do what I need to do?
Thank you!
You might try dumping each SQLite database, using SQLite's .dump command:
$ sqlite3 /path/to/database1 .dump > database1.sql
$ sqlite3 /path/to/database2 .dump > database2.sql
And then comparing the generated files. If your two databases are quite similar, a simple diff might work to show any differences, e.g.:
$ diff -u database1.sql database2.sql
Hope this helps!
diff command to see what the specific differences might be. If nothing else, just looking through those dump files might be useful/helpful.
Commented
May 12, 2016 at 20:43
A bit late for original question but may be will be helpful for others.
On Windows you can use KS DB Merge Tools for SQLite. I am the author of this tool. Compares both DB object definitions and data. Free version available and pretty functional for diff/compare needs.
EDIT 2022-06-29 I've made the web wasm-based diff-only version of this tool: https://ksdbmerge.tools/for-sqlite-online
EDIT 2022-02-21:
Accepted .dump answer is good for many cases, but it can report false-positive changes. Here is dump result for this question:
Both columns are part of the primary key, so from the point of data model data is the same but dumps are different. My tool is using primary key to compare data so no changes would be reported for such case.
You could use sqldiff.
On Debian or Debian-based systems (and possibly other Linux systems) it is installed by default when installing sqlite3: apt install sqlite3
On MacOS, it can be installed with Homebrew : brew install sqldiff
