How to Use Reactive Queries

[taufan-colliers]

Can you give me the complete example of how to use Reactive API?

I cannot find anything related with it in the example. Also the documentation only shows some parts of it. I saw people using it in useEffect, but it does not work for me. Also I cannot get the rowId, typescript complains and it throws error.

[rvibit]

This is how i am using it with Jotai (state manager)

import { atom, getDefaultStore } from 'jotai';
const defaultStore = getDefaultStore();
const query = `SELECT * FROM users`;

export const usersAtom = atom();

defaultStore.set(usersAtom, () => {
  const acc = db.execute(query);
  return acc.rows?._array;
});

let unsubscribe = db.reactiveExecute({
  query: query,
  arguments: [],
  fireOn: [
    {
      table: 'users'
    },
  ],
  callback: (users) => {
    defaultStore.set(usersAtom, users.rows._array);
  },
});

Then inside a component use the Atom.

const users = useAtomValue(usersAtom) as User[]; //need to cast Type, didn't find better solution

Whenver any insert/update/deletion happens in users table the reactive query triggers automatically and update the atom value which then triggers a re-render for the component.

[taufan-colliers]

@rvibit and where do you use the unsubscribe?

[rvibit]

@rvibit and where do you use the unsubscribe?

I am not using it as of now, I think you can use it if you are running reactive query inside an useEffect, You need to unsubscribe when a component unmounts because the subscription will be created again when the component mounts again.

[taufan-colliers]

  useEffect(() => {
    const unsubscribe = db.reactiveExecute({
      query: `SELECT * FROM Headings`,
      arguments: [],
      fireOn: [
        {
          table: 'Headings',
        },
      ],
      callback: users => {
        console.log({halo: users});
      },
    });

    return () => {
      unsubscribe();
    };
  }, []);

This won't trigger anything, not even the console.log, but if I put it outside the useEffect, it works.

Plus the example for Reactive has a lot of typos, not to mention the typescript stuff.

[rvibit]

This won't trigger anything, not even the console.log, but if I put it outside the useEffect, it works.

Plus the example for Reactive has a lot of typos, not to mention the typescript stuff.

I haven't used it with useEffect but can you try running an insert query on a button click and check if this console.log runs

[taufan-colliers]

@rvibit but then I have to set the state manually? I mean I just can use simple execute function then set the state. There's no significant impact on the Reactive usage.

[ospfranco]

There are more examples on how to use it in the reactive queries docs. Using it in a useEffect should work normally, unless maybe you are using react server components or something similar. Setting the state in the callback is the correct way to go.

There might be some confusion regarding it firing the first time, I'm not sure it does, something I might have overlooked. As @rvibit mentioned, you might want to attach a insert into the table to a button and then see the callback being fired first. rowid is a sqlite mechanism not dependent on ts/op-sqlite, so you should be able to get it in a normal query like in the docs.

Let me know if this helps. Also, if this continues not to work create a minimal reproducible example. Also please point me to the typos, so I can fix them.

[rvibit]

@rvibit but then I have to set the state manually? I mean I just can use simple execute function then set the state. There's no significant impact on the Reactive usage.

Yes you can execute a function and rerun a query inside it and set a state if you are using the result of that query in that screen/component or passing to children. But if you want same query result in multiple screens which are not connected then it becomes hard, you need to put same code in all the screens.

Think about it like this, you are using user list in 4 screens,

Now first option is you do SELECT * FROM users; in every screen and set the result in a useState, Now every time user visit the screen you need to run this query to be sure if you are showing up-to-date list because maybe from another screen you have added a new user and if you do not run the query again the new user will be missing from the list.

A second solution could be to use a state management and store result of SELECT * FROM users; in a global state, and inside the Reactive Query block update this global state variable. Use this global state variable in all 4 screens, Now whenever anything change in the users table the reactive query callback will run and update the global variable which change the data in all 4 screens.

[taufan-colliers]

Let me know if this helps. Also, if this continues not to work create a minimal reproducible example. Also please point me to the typos, so I can fix them.

rowid instead of rowId userResponse instead of usersResponse

[taufan-colliers]

@ospfranco Please check line 93 in App.tsx, it throws error. I only tested it run on Android Emulator

https://github.com/taufan-colliers/Reactive-Test

[taufan-colliers]

A second solution could be to use a state management and store result of SELECT * FROM users; in a global state, and inside the Reactive Query block update this global state variable. Use this global state variable in all 4 screens, Now whenever anything change in the users table the reactive query callback will run and update the global variable which change the data in all 4 screens.

I understand now, thank you for your insights!

[taufan-colliers]

But then when do you need to use db.close()? Sometimes the example use it, sometimes it doesn't. Sometimes it defined outside of the function component, sometimes it defined in a function inside the component. Where and when to close it? Is there any good rule of thumb?

[ospfranco]

You only close the db when you don't need it at all. On app close or app crash.

[taufan-colliers]

I found the problem of why my Reactive is not firing. In screen A, I open a db to execute some queries, including the Reactive. One of the function is to insert data to a table. Since the function is long, I moved it to another file. Then I open the same db there, so that I can reuse it anywhere else. Back to screen A, when I made changes to a table that I watched (using Reactive) by using the insert function above. The listener is not working.

But when I move the insert function to screen A and use the same open db as the Reactive, it works.

Do you have any do's and dont's regarding this behavior? What is the best place to store open db and use it anywhere in the app?

[taufan-colliers]

@ospfranco Please check line 93 in App.tsx, it throws error. I only tested it run on Android Emulator

https://github.com/taufan-colliers/Reactive-Test

I've updated the repo and it can run on iOS simulator also.

[taufan-colliers]

It's confirmed that I have to use the same open database. It needs to be initialized (open) first, so I put it in my entry file (App.tsx). Then export it so any function can consume it and the Reactive will work just fine. I don't know if this is the correct/official approach. I also can use react context but I have to use it inside a component, then inject it to my helper function.

[ospfranco]

You seem to be misunderstanding how the db object works and when to close it. Going to close this issue, nothing wrong with the library.

Just initialize a single db object and keep it alive through your application lifecycle.