How can I diff 2 SQLite files?

Question

Using SQLite-manager (in its XUL form) on a Mac.

How can I diff a SQLite file from one submitted by someone else on the team, and incorporate his changes?

Thanks.

Aneto

Answer 1

I believe you could use the following, in combination:

$ diff sqlite-file-1.sql sqlite-file-2.sql > sqlite-patch.diff
$ patch -p0 sqlite-file-1.sql sqlite-patch.diff

I hope that works for you. Otherwise, I highly suggest consulting the man-pages:

$ man diff
$ man patch

EDIT: Alright, here's the whole walk-through.

First, dump the databases:

$ sqlite test1.sql .dump > test1.sql.txt
$ sqlite test2.sql .dump > test2.sql.txt

Next, generate a diff file:

$ diff -u test1.sql.txt test2.sql.txt > patch-0.1.diff

And, finally, to apply the patch:

$ patch -p0 test1.sql.txt patch-0.1.diff

Boaz

Doesn't work, as SQLite is a binary file, the only result you get is "binary files differ" – Aneto

Won't this break if you modify the schema that already has data in it? For instance if you add a field and want to patch the additional field to an existing database. – Hasheem

User may need sqlite3 instead of sqlite. – Darlleen

Answer 2

We can use the sqldiff Utility Program:

https://www.sqlite.org/sqldiff.html

It will compare the source to the destination databases and generate SQL commands to make source equivalent to destination.

• Any differences in the content of paired rows are output as UPDATEs.
• Rows in the source database that could not be paired are output as DELETEs.
• Rows in the destination database that could not be paired are output as INSERTs.

We have to download the sources and compile it, from the tool folder.

Wrap

No need to compile, it's available as a precompiled binary on the download page – Allotment

You don't have to mess around with the tool folder. Just ./configure && make sqldiff. – Playboy

Answer 3

Maybe using this tool: http://download.cnet.com/SQLite-Diff/3000-10254_4-10894771.html ? But you can use the solution provided by @indienick provided you first dump the binary sqlite database with something like: sqlite x.db .dump > output.sql

Hope this helps, Moszi

Orsa

How exactly do I dump my SQLite? I do not have a sqlite command line installed. – Aneto

If you use a standard Mac box, then you should have sqlite in /usr/bin/sqlite3 ;) – Orsa

The last time I checked, you did not need to worry about dumping SQLite files, as SQLite works directly with a .sql file - it's not all hidden away in binary flat-files, like PostgreSQL or MySQL. – Boaz

Got it, and it works. Now I have to decide who gets the V - @indienick got it 90% right, but you brought it home... :) – Aneto

hahh :) - thats all right :) award it to indienick, his answer was faster then mine ;) – Orsa

Answer 4

There is a free web tool to compare SQLite databases, both schema and data - https://ksdbmerge.tools/for-sqlite-online Works on wasm-compatible browsers including Safari on Mac.

I am the author of that tool, it is an uno-platform port of my desktop tool for SQLite. Unlike few other online tools I could find - it does not upload your data to the server to generate data diff. However it uploads schema to handle the rest of logic.

As of June 2022 it is diff-only tool, web version does not produce any syncronization scripts.

Bilharziasis