Make OP SQLite Reactive

[ospfranco]

Here is a proposal for an API that would allow to run reactive queries.

Besides your normal SQL string and argument, you can add two keys: tableName and rowID. Why? Because this two values allow you to subscribe to changes in the table using the native sqlite hook, plus the row (if ID is specified). With this it is possible to automatically re-run the query when an event is emitted with the matching criteria.

Then your query would be re-run. Not the most efficient way to get a diff between states but OP-sqlite already been as fast as it is, should cover most of the apps out there.

Samples

Here is an example of a reactive query for an entire table

// will trigger every time there is a change to the users table
db.reactiveExecute({
  query: 'SELECT * FROM Users',
  arguments: [],
  tables: ['Users'],
  callback: (users: any) => {
    console.log(users)
  }
})

Here is an example for a single row

// will trigger every time there is a change to the users table
db.reactiveExecute({
  query: 'SELECT * FROM Users',
  arguments: [],
  tables: ['Users'],
  callback: (user: any) => {
    console.log(user)
  }
})

With Mobx

Once you have a reactive query running, you can pair this with your favorite state management to get a fully reactive system

// on your state store
@observable
user: User

// later register the callback
db.reactiveExecute({
  query: 'SELECT * WHERE id = ? FROM Users',
  arguments: ['123'],
  tables: ['Users'],
  ids: ['123'],
  callback: (user: any) => {
    runInAction(() => {
      this.user = user
    }
  }
})

// finally your component is a normal mobx observer component
const myComp = observer(() => {
  const user = store.user;
// ..
  return (
    <Text>{user.name}</Text>
  )
}

This also means you can have your own complex logic in your query, joins, sorting, filtering, etc. Giving you a generic mechanism to add observability, allowing you to offload work to SQLite itself.

Let's do it

Unfortunately, this will require a big re-write of the library, so I will need people to sponsor this work

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

[abdelhameedhamdy]

Thanks for this proposal, wondering, would it be something like the reactive concept in riffle

[focux]

Love this proposal, I built a similar system but on the JS side for a local-first chat app and would love to know more about the benefits of this approach. I've been using the updateHook for this and I've run into several foot guns on the way with the performance of my approach, some of them are:

1. Due to the nature of chat applications, which normally have a high volume of writes/reads, the updateHook executes SO MANY times and refreshes the queries SO MANY TIMES that I had to add a small queue and throttle the updates to avoid blocking the JS thread.

2. The initial sync of the chat/db is very slow. When the user installs the app, we insert thousands of records into the DB, and due to all the changes and queries we are refreshing, it blocks the DB thread for a few seconds. Even with throttling in place, our solution so far has been to not react to any change until the sync is done and then run all the queries.

I'm wondering if by implementing this logic on the native side, are these not issues anymore?

Also, I see that you are declaring the reactive queries outside of the render function, which means that you can't pass dynamic args to the queries, I might be wrong though but maybe a hook is better for the API and also some kind of dedupe logic to avoid calling the same query many times if you re-use hooks on different screens/components.

Anyways, this proposal looks promising to build local-first apps backed by SQLite.

[ospfranco]

1. Yes, calling a JS function a few times will be a lot slower than calling a native function, by adding the table and id on the C++ side it will be exponentially faster to trigger the update only when the correct table/row changes

2. You probably are choking the JS thread with callbacks, doesn't delaying the registering of the update hook is the best option? a C++ callback would definitely help but this sounds like another type of race condition

3. I don't like hooks but you can build a hook abstraction on top of the callback. It makes little difference since whenever the filter params would change you need to create a new reactive query, that should not be the bottle neck here.

[Volland]

@ospfranco could you explain me a magic around updatehook ? It is super useful and could help with simplified reactivity / I see that hook executed not immediately after i do a changes but at some point of time later.

[ospfranco]

It's a direct binding to the sqlite3 hook, you can take a look at the documentation of sqlite

[ospfranco]

This is now available to test.

npm i @op-engineering/op-sqlite@6.0.2-beta1

The API is as described. Give it a try and let me know if it is working for you!

There is one limitation though, you need to use INTs as row ids, no strings, otherwise the magic does not work.

[ospfranco]

After some playing around, realized the API was wrong, the new API is:

const unsubscribe = db.reactiveExecute({
  query: 'SELECT * FROM User;',
  arguments: [],
  fireOn: [
    {
      table: 'User',
      ids: [2],
    },
  ],
  callback: () => {
    firstReactiveRan = true;
  },
});

It will now correctly fire on the matched ids on the table. Also, the query now is internally a prepared statement, which means it should be even faster.

[simonwh]

@ospfranco that looks very promising, we are going to test it out soon!

You mention that it only works with INTs as row ids. Any way to get it to work with UUIDs? We're using UUIDs as that makes merging data easier (cr-sqlite), but we also need reactivity.

[ospfranco]

No, it's because the update_hook from sqlite emits the row id, which I'm not even sure it matches user defined primary keys. In any case, I could query the entire row and then look up the id key but it's brittle and it will add latency to the event emitter.

[abdelhameedhamdy]

Awesome, I will test it in my project soon.

Regarding the ids, what I understand if I have e.g. a primary key as text as uuid, sqlite already generates underlying int rowid, I think the new API use it, maybe I am not getting this, if I pass the id as string, I thought it reads the corresponding int rowid ! If that is a limitation for performance, why not directly passing the underlying rowid instead of the actual used id in schema ?

[ospfranco]

it is the row id :)

It's just I haven't quite gotten a hold of how it works, if a row is deleted do all the row ids move back? I didn't get the time to test all the behaviors or how to explain them to the user

[abdelhameedhamdy]

According to sqlite docs

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

[ospfranco]

ok, cool, well, I guess you can use any id you want then, but you need to fetch the ROWID first

[abdelhameedhamdy]

Sorry, what does it mean to fetch the rowid first, select rowid .... in the query key !

db.reactiveExecute({
  query: 'SELECT * WHERE id = ? FROM Users',
  arguments: ['123'],
  tables: ['Users'],
  ids: ['123'], <-- is it to pass the corresponding rowid, or still use what ever id I want !
  callback: (user: any) => {
    runInAction(() => {
      this.user = user
    }
  }
})

[ospfranco]

you have to first get the rowid which will not be the same as the id column you create, it is also a number not a string.

let rowid = db.execute('SELECT rowid WHERE id = ? FROM Users', [123]).item(0).rowid;

let unsubscribe = db.reactiveExecute({
  query: 'SELECT * WHERE id = ? FROM Users',
  arguments: ['123'],
  fireOn: [
    {
      table: 'User',
      ids: [rowId],
    },
  ],
  callback: (user: any) => {
    runInAction(() => {
      this.user = user
    }
  }
})

[ospfranco]

Reactive queries are out and should be working. Open new tickets if you find any issues.

[murattil]

reactiveExecute wasn't working for me; spent some time debugging until I realized that commenting out the db.updateHook I already had made it start working. Seems like updateHook is overwriting reactiveExecute. Guessing this is was not intentional?

[ospfranco]

hmm weird, they both should work. I will take a look later, can you open a ticket for it?

[ospfranco]

Found the issue, should be fixed in 6.0.4