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.

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.

• sqlite
• raspberry-pi
• nestjs
• synchronization

• if one db is the master db then you might just copy the sqilte file? – jhole Commented Feb 4 at 15:20
• No, sadly not. In the one on the raspberry pi, data might change, on the one on the usb, datan and even structure might change. – Andresch Serj Commented Feb 4 at 19:08

1 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.