Method to replicate sqlite database across multiple servers

Question

I'm developing an application that works distributed, and I have a SQLite database that must be shared between distributed servers. If I'm in serverA, and change sqlite row, this change must be in the other servers instantly, but if a server were offline and then it came online, it must update all info equal other servers.

I'm trying to develop a HA service with small SQLite databases.

I'm thinking on something like MongoDB or ReThinkDB, due to replication works fine and I have got data independently server online I had.

There are a library or other SQL methodology to share data between servers?

Hark

No considerations about race conditions or other types of conflict? – Butterbur

Answer 1

I used the Raft consensus protocol to replicate my SQLite database. You can find the system here:

https://github.com/rqlite/rqlite

Neolith

is the JSON1 extension available with rqlite? – Ballroom

Looks amazing. Looking forward to trying it. – Hemichordate

I wanted to try this but I need a drop-in replacement for SQLite. RQLite requires you use its HTTP API when executing the sql statements. – Hemichordate

Answer 2

Here are some options:

LiteReplica:

It supports master-slave replication for SQLite3 databases using a single master (writable node) and one or many replicas (read-only nodes).

If a device went offline and then it came online, the secondary/slave dbs are updated with the primary/master one incrementally.

LiteSync:

It implements multi-master replication so we can write to the db in any node, even when the device is off-line.

On both we open the database using a modified URI, like this:

  “file:/path/to/app.db?replica=master&bind=tcp://0.0.0.0:4444”

AergoLite:

Blockchain based, it has the highest level of security. Stores immutable relational data, secured by a distributed consensus with low resource usage.

Disclosure: I am the author of these solutions

Sunset

Would this work for many to one? Meaning I have many sources (same layout/tables) to one single slave? – Moonset

No, it works with a single master. It will not merge data from many sources on a single db, but you can keep a separate replica for each source. – Sunset

Hi, very interested in these especially LiteSync. We have a situation where multiple high latency locations need to write to a local database and sync to a master database. It is not ideal or necessary to keep all the data in the main database in each location, so we're looking for more of a "one way" sync, Do you know a good solution for this? – Aubreyaubrie

One way sync from secondary to primary nodes? Maybe OctoDB. BTW this is not the place for queries – Sunset

Answer 3

You can synchronize SQLite databases by embedding SymmetricDS in your application. It supports occasionally connected clients, so it will capture changes and sync them when a server comes online. It supports several different database platforms and can be used as a library or as a standalone service.

Moschatel

Is there a way to synchronize divergent file-hosted SQLite databases? – Fabaceous

Answer 4

You can also use CopyCat, which support SQLite as well as a few other database types.

Dichotomy

Apparently it only supports SQLite under very limited conditions (e.g. using Delphi?!) – Cuxhaven

Answer 5

Marmot looks good:

https://github.com/maxpert/marmot

From their docs:

What & Why?

Marmot is a distributed SQLite replicator with leaderless, and eventual consistency. It allows you to build a robust replication between your nodes by building on top of fault-tolerant NATS Jetstream. This means if you are running a read heavy website based on SQLite, you should be easily able to scale it out by adding more SQLite replicated nodes. SQLite is probably the most ubiquitous DB that exists almost everywhere, Marmot aims to make it even more ubiquitous for server side applications by building a replication layer on top.

Chante