LiteSync – Easy synchronization of SQLite databases | Hacker News Reader

LiteSync – Easy synchronization of SQLite databases

litesync.io

102 pointsthunderbong29 comments

I'm not entirely opposed to a commercial product, but the list of documented limitations does not make me reach for my wallet just yet:

1. Avoid using functions that return different values each time they are called, like random() and date('now')

2. AUTOINCREMENT keyword is not supported

3. We must use just a single connection to each database file. Do not let many apps access the same database file. Each instance must use its own db file, and then they will be replicated and synchronized using LiteSync

There are definitely obvious technical reasons for these, but it may make using the product for existing apps pretty much impossible.

Also worrisome is the lack, at least anywhere in the examples, of authentication parameters on the (proprietary, I guess?) TCP replication protocol, and no mention of over-the-wire encryption.

As LiteSync used eventual consistency AUTOINCREMENT isn't possible. You would have collisions when a node come back on after what they are calling "Airplane Mode".

I suspect the issue with random() and date('now') similarly are they it will replay transactions on other nodes, and these would return different values. Obviously they could design the system to save the values for returning when replaying a transaction if they decided it was necessary.

I haven't looked in detail, but I'm suspicious they are using the SQLite Session extension: https://www.sqlite.org/sessionintro.html

>I haven't looked in detail, but I'm suspicious they are using the SQLite Session extension: https://www.sqlite.org/sessionintro.html

Having used the Session extension extensively, from reading the info this would also be my conclusion (makes no use if it, which to me makes little sense).

I don't think they are using the session extension, as this project provides its own binary encoding, which wouldn't be needed if the session extension is used. It wouldn't affect random/date either, as the session extension doesn't replay transactions, but captures applied changes as changesets (so it would capture the results of random/date), which can then be applied to other nodes.

RANDOM and NOW you can easily do in application space.

AUTOINCREMENT you can replace in application space with nanoid, or similar semi-random ordered ID.

These practices are standard if you’re creating any data offline on clients or syncing two ways.

Also no mention of how it handles conflicting transactions from separate nodes.

> 3. We must use just a single connection to each database file. Do not let many apps access the same database file. Each instance must use its own db file

Isn't this a sqlite limitation?

There are file locking mechanisms you can opt into (compile time options) to have multi-process access to the same database file - it does require either some coordination and/or more error handling, especially when writing to the database.

Also makes no mention about schema changes.

Looks like it uses a homemade version of RC4 (mentioned here https://litesync.io/en/licensing.html):

Many have discouraged the use of RC4 for its weaknesses. But why not enhance it? Or create another algorithm based on it?

https://github.com/litereplica/xrc4

Oof. RC4 is so bad that trying to tweak it back to security (a move with a bad track record) is a little like trying to design your own stream cipher from scratch.

There’s a free CRDT based SQLite sync system. It’s for a different use-case — local first software including peer to peer sync use-case.

https://github.com/vlcn-io/cr-sqlite

51 days ago: https://news.ycombinator.com/item?id=33606311

Rqlite[0] does not use SQLite API but it handles replication too.

0. https://github.com/rqlite/rqlite

rqlite author here -- happy to answer any questions. While rqlite[1] does replicate a SQLite database, it's not simply a replication system (just to be clear). It's a distributed database that uses SQLite as its database engine, and replicates for fault tolerance. That said, it mostly exposes SQLite via its HTTP API, so can work for many SQLite applications[2].

[1] https://rqlite.io

[2] https://rqlite.io/docs/faq/#is-it-a-drop-in-replacement-for-...

> That said, it mostly exposes SQLite via its HTTP API,

How does it compare to PostgREST in this regard?

PostgREST is new to me. Interesting things in common, now that I see it, but much that is not.

$750 for a one time company license plus extra for dedicated support.

Who's behind this? The pages say they are copyright Gensis Sistemas LTDA but I can't find anything else by that name.

What tech is this? There's no licensing information in the download. sqlite doesn't require any attribution. I think litestream does, so maybe they're not using it?

They have a similar product at https://litereplica.io, and that one has an About Us page: https://litereplica.io/company.html

then there is https://github.com/benbjohnson/litestream

Litestream is one-way replication, so it can't sync across multiple writers.

LiteFS is Ben Johnson's newer project that does SQLite synchronization across nodes: https://github.com/superfly/litefs

So we've got LiteFS, LiteStream and LiteSync ... all of which handle the same problem of sqlite replication in different ways. I know naming is hard but this is bound to lead to confusion.

The website has a low contrast font that is very bad to read. I need to customize the css or use some browser extension to make it readable - very annoying.

It is a bad idea to publish text in a way that makes it hard to read - please stop that.

"Low contrast" also is an indicator for people following bad trends ("fashion") without thinking. It seems like this is not something that fits your project, so you should not put yourself into a bad light with a bad design decision for your website.

Please don't complain about tangential annoyances—e.g. article or website formats, name collisions, or back-button breakage. They're too common to be interesting.

https://news.ycombinator.com/newsguidelines.html

Thank you very much for your important contribution!

There is no definition for "tangential" in that guideline.

The main reason for a website to exist is being read, so it is a cardinal issue, when this requirement is not fulfilled, not a tangential one.

I hope this keeps the discussion interesting!

I’d be more interested in a managed SQLite database accessible through the web. Basically Hasura but for SQLite.

Like running redbean on someone else's computer?

redbean embeds Lua, SQLite, and MbedTLS into a fork() driven application server that benchmarks at 1.1 million qps on a personal computer.

https://redbean.dev/

https://www.youtube.com/watch?v=1ZTRb-2DZGs

This exists https://github.com/subzerocloud/showcase/tree/main/flyio-sql...

Lol isn't this just Litestream?

Lightstream is single node only, effectively a remote WAL for point in time recovery, this is multi-node and eventually consistent. You can write to nodes while they are "off line" and sync later.

This is geared towards, say, having an application running on a device on an airplane, maybe a passenger CRM. You can have it run and update the DB while in the air with no connection, the DB will then sync with main node once on the ground with a connection.

Reply on news.ycombinator.com