Hacker News Reader

Comment by klabb3 | Hacker News Reader

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!

View on HN

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

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.

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?

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.

Reply on news.ycombinator.com