Real-time event subscriptions around SQLite API

[maietta]

I would love to see a thin layer that enables real-time event subscriptions API around SQLite for listening to changes on the database for different types of actions such as inserts or updates, deletes, watching changes on a specific record, etc.

I currently leverage Pocketbase for a local instance of a real-time database that uses SQLite, but to have this kind of functionality as a native feature of Bun would be amazing, if it makes sense.

Curious of this feature idea would be a good one for the Bun project. I don't believe it would be very difficult and if I had some extra time, I might consider seeing if I could get a basic implementation working. I've not written anything in Rust yet.

Let's discuss.

[maietta]

Update:

A Twitter follower (PavitraGolchha) kindly pointed out to me that SQLite has a Date Change Notification that could be exposed via Bun runtime.

https://www.sqlite.org/c3ref/update_hook.html

[truongan07]

real-time event subscriptions API around SQLite for listening to changes on the database for different types of actions such as inserts or updates, deletes, watching changes on a specific record, etc.

You want to log info for monitoring management or what ?

[maietta]

As it turns out, SQLite now offers the ability register a callback to listen for actions taken on the database.

Imagine a multi-player game where each-other's health, movements, scores and other data are updated as they happen. You wouldn't ever want to "poll" the database at a set interval to see what the updates are. No, you'd need real-time streaming of that information.

I have many use-cases including updates to articles, a content management system in development, a game I'm working on, a radio station website network and notifying users of changes to any listings they are monitoring as they browse a realty websites.

The choice to use SQLite is because it's robust enough and can live with the website. Most of my projects need only a very small database.

[truongan07]

I don't think SQLite is a small database because i use this database to record information of farms in huge data. It reads data faster than PostgreSQL or MySQL.

[maietta]

No offense, but I think English is not your first language.

Let's not get distracted from the point here. The goal would be to extend the already-existing data notification service hook(s) to the Bun API.

The issue of SQLite's size or performance is not being called into question or up for discussion.

[bokunodev]

its possible to do such thing using sqlite update hook. but i found that its very lacking.
there is a few gotchas with it, for example you dont receive the row data, and you cant fetch the row from within the callback. you will only receive the table name, old id and new id.
also it wont call the hook on on conflict(col_name) do update set x = y.

i found that its easier to just register a function and call it from triggers. but to register a function you need to register it before opening any database connection and you cant register variadic function (or perhaps i just don't know how to do so).

reference: https://sqlite.org/c3ref/update_hook.html

sqlite triggers sample

create trigger session_insert_notify after insert on session
begin
    select session_change_notify('insert',
        null,
        new.id, new.mac_address, new.active, new.verified, new.expiration, new.time_stamp
    );
end;

create trigger session_update_notify after update on session
begin
    select session_change_notify('update',
        old.id,
        new.id, new.mac_address, new.active, new.verified, new.expiration, new.time_stamp
    );
end;

create trigger session_delete_notify after delete on session
begin
    select session_change_notify('delete',
        old.id,
        null, null, null, null, null, null
    );
end;

[bokunodev]

@maietta