Comment by benbjohnson | Hacker News Reader

Comment by benbjohnson | Hacker News Reader

Parent Full thread

LiteFS author here. I agree with you that most applications don't need to have all the distributed bells and whistles. Most projects that I run start out with just SQLite on a single node with Litestream and/or a cron backup. However, the number of applications that need to reduce latency in multiple regions is growing and the options tend to be pretty complicated or expensive. LiteFS aims to be a low-cost, self-hosted alternative to those options.

> I could worry less about enums and nulls, in a world with orders of magnitude more dangerous creatures: losing writes because I wrote to a replica, reading or writing inconsistent state, duplicate writes etc etc

LiteFS uses a primary/replica setup (as do many distributed databases) where the primary can perform writes and replicas are read-only. You won't be able to lose writes written to a replica because LiteFS doesn't allow that. As for inconsistent state, there is a transaction ID that lets you track your replication position[1]. You can implement strict serializability across your cluster using that. Then for duplicate writes, all writes go to the primary so it has the same serializable guarantees as regular SQLite.

I hope that explanation helps. Let me know if you have any other questions.

[1]: https://fly.io/docs/litefs/position/

Very cool, and thanks for taking the time, it is indeed calming to a db novice like myself. In fact, I’m using Fly right now to try out the platform in general.

While I have you, do you have any high level thoughts on reactivity within a db like sqlite? (My app needed some real-time features so I have mostly been looking at NATS.io plus websockets). Are there any real-time like features (reactivity, subscriptions, CDC, materialized views) that work well and ergonomically within that context, either today or in the future? If so it could be really, really useful for my app.

Thanks again!

I have never considered reacting to the database like that. If I need something to happen on db writes, then I cobble together some kind of event system, or delegate those writes to a function that takes care of the side effects. Things like websockets are app layer. There are many db features that I have never tried, have you used a reactive database in production?

Kent's recommended NodeJS module, `better-sqlite3`, has some very nifty features including the creation of JavaScript user-defined functions[0] that (if I understand this right) can be called from SQLite. Combined with TRIGGERs, I wonder if it might fire a function within the app when an UPDATE/INSERT happens from a different process? (This is me wondering out loud, I don't actually know.)

I also recommend checking out Replicache[1] and alternatives, which may be a better way to handle the networking and database replication so that it doesn't rely on the underlying DB.

[0] https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/ap... [1] https://replicache.dev/

This will not work as SQLite triggers only work for writes within the same process; a trigger cannot execute code in another process.

But you could use triggers to write to a table (as any process that writes will run that trigger), and then poll that table from another process.

Real-time is a vague term.

Webassembly is a compile target for SQLite, AFAIR, so you might use that in the client to get the fastest possible response without using too much other libraries/services.

But without more information, it is hard to help you.

> Real-time is a vague term.

Yeah I know. Sorry. Think chat application latency. I think I should have just used event-driven or reactive instead.

If you're running a single-node SQLite application then you can handle reactivity within your application code itself and use either websockets or SSE to propagate it to the client.

It definitely gets more complicated when you have a distributed application. Using something like NATS works but you'd need to synchronize between the NATS message and your database state on your replicas (e.g. NATS could arrive before data is propagated to replicas).

We do have plans to add event data to the LiteFS transaction files[1]. That would let you write out a message like "user_updated id=123" during your transaction and it would get bundled with the transaction payload on commit. Then your application on your replica can listen for events and they are already synced up with your database state.

[1]: https://github.com/superfly/litefs/issues/20

Reply on news.ycombinator.com