Turso SQLite Offline Sync Public Beta

A lot of offline sync projects just drop data on conflict and pretend it didn't happen. It's the salesman job to divert your questions about it.

I found another blogpost from turso where they say they offer 3 options on conflict: drop it, rebase it (and hope for no conflict?) and "handle it yourself".

Writing an offline sync isn't hard. Dealing with conflicts is a PITA.

https://turso.tech/blog/introducing-offline-writes-for-turso...

Conflict resolution can't work in a general sense.

How you reconcile many copies of the same record could depend on time of action, server location, authority level of the user, causality between certain business events, enabled account features, prior phase of the moon, etc.

Whether or not offline sync can even work is very much a domain specific concern. You need to talk to the business about the pros & cons first. For example, they might not like the semantics regarding merchant terminals and offline processing. I can already hear the "what if the terminal never comes back online?" afternoon meeting arising out of that one.

I think this point confuses me the most in this regard:

> Local-first architectures allow for fast and responsive applications that are resilient to network failures

So are we talking about apps that can work for days and weeks offline and then sync a lot of data at once, or are we talking about apps that can survive a few seconds glitch in network connectivity? I think that what is promised is the former, but what will make sense in practice is the latter.

I'm wondering too.

In general this seems to work only if there's a single offline client that accepts the writes.

With limitations to the data scheme (e.g. have distinct tables per client), it might work with multiple clients. However those would need to be documented and I couldn't see anything in this blog post.

Yes, this is a central issue in sync. For most applications, sync engines just aren't useful without some solution. Of course you need to validate inputs, support fine-grained permissions, etc., as developers have done with web apps for eons.

In Replicache, we addressed this by making your application server responsible for writes:

https://doc.replicache.dev/concepts/how-it-works

By doing this, your server can implement any validation it wants. It can also interact with external systems, do notifications, etc. Anything you can do with a traditional API.

In our new sync engine, Zero (https://zerosync.dev), we're adding this same ability soon (like this week) under the name custom mutators:

https://bugs.rocicorp.dev/issue/3045

This has been a hard project, but is really critical to use sync engines for anything serious.

The blog post doesn't even touch on write conflicts, which is the main reason I opened it (I was curious on how they solved them), so not surprised there's no many details about security etc.

I am not sure about Turso but I've seen a few different approaches to this with other sync engine architectures:

1. At a database level: Using something like RLS in Postgres

2. At a backend level: The sync engine processes write operations via the backend API, where custom validation and authorization logic can be applied.

3. At a sync engine level: If the sync engine processes the write operations, there can be some kind of authorization layer similar to RLS enforced by the sync engine on the backend.

I'm pretty sure you'll have to write parts of your app against your own APIs that represent the owner of the db for a group.

With Turso, you would want a model that had, for example a db per user and one per group. With the turso model you want to think something closer to sharding by hand for secure write user or group.

I could be wrong on this though. That's just my rough understanding.

This is my problem with these local first libraries. What happens if there's some data that needs to live in a db that's separate from the replicated sqlite db?

What I would really love is a sync engine library that is agnostic of your database.

Haven't really seen one yet.

That is a very crucial question. I am also interested in the answer.

Perhaps they have RLS type policies that are only modifiable on the server.

You raise an interesting point, that along with the replies, compels me to note that all of this stuff is bespoke, and things that sound simple like "I just want a good syncing library" are intractable in practice.

Ex. if I'm doing a document-based app, users can have at it, corrupt their own data all they want.

I honestly cannot wrap my mind around discussions re: SQLite x web dev, perhaps because I've been in mobile dev: but I don't even know what it'd mean to have an "offline-first forum" that syncs state: it's a global object with shared state rendered on the client.

When you set aside the implications introduced by using a hack scenario, a simpler question emerges: How would my clients sync the whole forum back to the cloud? Generally, my inclination is to handwave about users being able to make posts and have it "just work", after all, can't Turo help with simple scenarios like a posts table that has a date column? That makes it virtually conflict free...but my experience is "virtually" bites you, hard.

Honestly this is so simple and core to the idea that I literally just assume it's handled.

And it is probably not useful for most applications until there is some sort of security there.

Agreed. I've been seeing a lot of these posts lately, about embedding SQLite into web servers.

I think a lot of people just don't realize how few resources Postgres or MySQL use, and how fast they are. You can run Apache and MySQL and a scripting language on a tiny little 512 MB memory instance, and serve some decent traffic. It works great.

Wanting to use SQLite and deal with replication is a nightmare. I don't get it. (And I love using SQLite in apps and scripts. But not websites!)

Yup - I just recently learned this lesson the hard way with Turso's LibSQL server. While some of the features (like s3 replication) are cool and interesting, the amount of time working around multiple writers and foreign key shenanigans was not worth it when Postgres would have just gotten the job done.

(I work at Turso)

libSQL does not support realtime/reactivity features. There have been attempts (by both our team and the community), but we encountered difficulties and ultimately abandoned the idea. I can check with the team for the exact reasons.

However, Limbo (our SQLite rewrite in Rust) will include this feature, though there is no ETA yet. It's currently being discussed here: https://github.com/tursodatabase/limbo/discussions/1149. Please share your thoughts and use cases.

I think this might be true in an invoicing based world but I agree that PoS is a misnomer. But I can imagine e.g. a field sales rep in construction taking orders on site in a rural area then syncing up when they return to connectivity.

disclosure: I work at Turso.

libSQL is our fork of SQLite. It adds new features on top of SQLite, such as vector search, replication, server mode, and more. libSQL is open source (MIT license).

The Turso SaaS platform, which provides hosted databases, is not open source.

Limbo (which will be renamed to Turso in the future) is our Rust rewrite of SQLite. It is also open source (MIT license) - https://github.com/tursodatabase/limbo

Seems not really or only partially anyway; I cannot see, with disasters like Fauna, that anyone would trust their core stuff with something not open source. But maybe it's just me. I need to be able to switch and sure, I can switch to the open source fork libSQL (open core I think, so nope) (which they are rewriting to Rust for some bizarre reason; sqlite is one of the most readable, robust and well tested codebases in the business so it looks like burning vc money, but whatever), but once my business depends on their offline / replication / etc, then I cannot switch to anything, so never going to happen.

Again, opinion. It's core infra, in my opinion at least that should never depend on others or, if the others inevitably screw you over for a few $, you need to be able to move without possibly bankrupting your company.

turso is a SAAS built on libsql. libsql itself is opensource