How do I synchronize two databases?

Question

I need to regularly synchronize two SQLite databases. One on a Raspberry Pi 4 running Node.js/NestJS (always on but without internet connection), another provided to the Raspberry Pi 4 via a USB drive from a dev system.

I want to connect the USB drive to the Raspberry Pi, wait for a while and unplug it knowing it got synchronized. I will run a NestJS cron job checking for the USB drive using libusb. My problem is the synchronization.

My ideas:

• Running a second NestJS instance which dynamically uses the database whenever it is on and then calls an internal synchronization REST-endpoint/CLI tool on the Raspberry Pi's main NestJS instance.
• Making the NestJS instance access the second database and run synchronization logic on it.
• Using some magic synchronize SQLite databases command line tool that I do not know.

Answer 1

With sqldiff you could plug in the USB drive and run:

$ sqldiff /usb/my_db.db primary.db > changes.sql

This produces an SQL file that if ran on the database file on your USB drive should result in a replicated primary database file.

sqlite3 /usb/my_db.db < changes.sql should do the trick, but if you change the file on your USB drive it may not work reliably if there are conflicts.