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.

if one db is the master db then you might just copy the sqilte file? — jhole
– 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
– Andresch Serj, Commented Feb 4 at 19:08

user4157124 · Accepted Answer · 2025-02-08 03:20:09Z

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.

edited Feb 8 at 3:20

user4157124

3,00021 gold badges33 silver badges48 bronze badges

answered Feb 4 at 15:28

Binaek Sarkar

6368 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I synchronize two databases?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related