How to make Expo SQLite reactive with React Query

Ramsay Romero — Thu, 09 Nov 2023 01:22:02 +0000

If you need to store data locally on a user's device with React Native, Expo SQLite is a great option due to its simplicity and compatibility with Expo Go. However, its simplicity also means that you have to manage everything other than the raw sql queries yourself. Expo SQLite only exposes an api for executing SQL statements, so if you have complex requirements like handling migrations, and syncing local data to your backend, you should probably use something like WatermelonDB, but if your database schema will stay relatively stable, and your data is local only, Expo SQLite is a sensible choice.

Reactivity

When using Expo SQLite, there's one feature that is not built in that you will surely need: reactivity. If you update database items using Expo SQLite, your local database will update, but your app won't be notified, so it won't re-render and reflect the latest state of the database. We need a way to trigger re-renders in React when the database updates so we can show users the correct state of the database. Let's take a look at how the Expo SQLite to-do list example handles reactivity. I have altered the example slightly for clarity, and TypeScript usage.

import { useState, useEffect } from 'react';
import {
  ScrollView,
  StyleSheet,
  Text,
  TextInput,
  TouchableOpacity,
  View,
} from 'react-native';
import Constants from 'expo-constants';
import * as SQLite from 'expo-sqlite';

const db = SQLite.openDatabase('db.db');

type TodoItem = {
  id: number;
  done: number;
  value: string;
};

function Items({
  done: doneHeading,
  onPressItem,
}: {
  done: boolean;
  onPressItem: (id: number) => void;
}) {
  const [items, setItems] = useState<TodoItem[]>([]);

  // Selects todo items on mount
  // Whenever the todos are updated, we change the key so the component remounts
  useEffect(() => {
    db.transaction((tx) => {
      tx.executeSql(
        `select * from items where done = ?;`,
        [doneHeading ? 1 : 0],
        (_, { rows: { _array } }) => setItems(_array)
      );
    });
  }, []);

  const heading = doneHeading ? 'Completed' : 'Todo';

  if (items.length === 0) {
    return null;
  }

  return (
    <View style={styles.sectionContainer}>
      <Text style={styles.sectionHeading}>{heading}</Text>
      {items.map(({ id, done, value }) => (
        <TouchableOpacity
          key={id}
          onPress={() => onPressItem(id)}
          style={{
            backgroundColor: done ? '#1c9963' : '#fff',
            borderColor: '#000',
            borderWidth: 1,
            padding: 8,
          }}
        >
          <Text style={{ color: done ? '#fff' : '#000' }}>{value}</Text>
        </TouchableOpacity>
      ))}
    </View>
  );
}

export default function App() {
  const [text, setText] = useState('');
  const [forceUpdate, forceUpdateId] = useForceUpdate();

  // Creates the database table on mount
  useEffect(() => {
    db.transaction((tx) => {
      tx.executeSql(
        'create table if not exists items (id integer primary key not null, done int, value text);'
      );
    });
  }, []);

  function addTodoItem(text: string) {
    if (!text) {
      return;
    }

    db.transaction(
      (tx) => {
        tx.executeSql('insert into items (done, value) values (0, ?)', [text]);
      },
      undefined,
      forceUpdate
    );
  }

  function completeTodoItem(id: number) {
    db.transaction(
      (tx) => {
        tx.executeSql(`update items set done = 1 where id = ?;`, [id]);
      },
      undefined,
      forceUpdate
    );
  }

  function deleteTodoItem(id: number) {
    db.transaction(
      (tx) => {
        tx.executeSql(`delete from items where id = ?;`, [id]);
      },
      undefined,
      forceUpdate
    );
  }

  return (
    <View style={styles.container}>
      <Text style={styles.heading}>SQLite Example</Text>
      <View style={styles.flexRow}>
        <TextInput
          onChangeText={(text) => setText(text)}
          onSubmitEditing={() => {
            addTodoItem(text);
            setText('');
          }}
          placeholder='What do you need to do?'
          style={styles.input}
          value={text}
        />
      </View>
      <ScrollView style={styles.listArea}>
        <Items
          key={`forceupdate-todo-${forceUpdateId}`}
          done={false}
          onPressItem={completeTodoItem}
        />
        <Items
          done
          key={`forceupdate-done-${forceUpdateId}`}
          onPressItem={deleteTodoItem}
        />
      </ScrollView>
    </View>
  );
}

function useForceUpdate() {
  const [value, setValue] = useState(0);
  return [() => setValue(value + 1), value] as const;
}

const styles = StyleSheet.create({
  container: {
    backgroundColor: '#fff',
    flex: 1,
    paddingTop: Constants.statusBarHeight,
  },
  heading: {
    fontSize: 20,
    fontWeight: 'bold',
    textAlign: 'center',
  },
  flexRow: {
    flexDirection: 'row',
  },
  input: {
    borderColor: '#4630eb',
    borderRadius: 4,
    borderWidth: 1,
    flex: 1,
    height: 48,
    margin: 16,
    padding: 8,
  },
  listArea: {
    backgroundColor: '#f0f0f0',
    flex: 1,
    paddingTop: 16,
  },
  sectionContainer: {
    marginBottom: 16,
    marginHorizontal: 16,
  },
  sectionHeading: {
    fontSize: 18,
    marginBottom: 8,
  },
});

In this example, we are creating the items table when the App component mounts. The App component renders a text field that allows you to add to-do items. It also renders a list of completed and incomplete to-dos. Pressing an incomplete to-do marks the item as done in the database, and pressing a completed item deletes the item from the database. In order for the app to react to the database updates and display the correct data, the example uses the useForceUpdate hook to trigger remounts of the Items components. The hook just returns a number value and an increment function. The increment function is passed to the successCallback argument of the database transaction function, so every time the transaction has completed, the forceUpdate function will increment the forceUpdateId value. This value is passed to the key prop of the Items components, so that every time the forceUpdateId value increments, the components will remount, the useEffect will re-run, select the updated values from the database and render the items.

This is not a great way to handle reactivity because not only do you have to remember to pass a key to every future component that will need to render items, but it also causes a janky user experience. If you run the example yourself, you'll notice that any time you add or update items, you'll see a quick flash where there is no content, before the items re-appear. This is because our method is forcing the Items components to unmount, then remount with an empty list of items. The components then query the database, and render the updated items. This happens very quickly, but the flash is still perceptible and is quite jarring, especially when you have many items, as it causes the majority of the screen to flash on every interaction.

Using React state

We can fix this issue by using React state to handle reactivity. Rather than forcing the the component to unmount and re-query it's own data on every interaction, we can just keep track of all of the items in state and sync the state with the database after every successful database update. Here is the example updated to use state.

import { useState, useEffect } from 'react';
import {
  ScrollView,
  StyleSheet,
  Text,
  TextInput,
  TouchableOpacity,
  View,
} from 'react-native';
import Constants from 'expo-constants';
import * as SQLite from 'expo-sqlite';

const db = SQLite.openDatabase('db.db');

type TodoItem = {
  id: number;
  done: number;
  value: string;
};

function Items({
  done: doneHeading,
  onPressItem,
  items: unfilteredItems,
}: {
  done: boolean;
  onPressItem: (id: number) => void;
  items: TodoItem[];
}) {
  const items = unfilteredItems.filter(
    (item) => Boolean(item.done) === doneHeading
  );

  const heading = doneHeading ? 'Completed' : 'Todo';

  if (items.length === 0) {
    return null;
  }

  return (
    <View style={styles.sectionContainer}>
      <Text style={styles.sectionHeading}>{heading}</Text>
      {items.map(({ id, done, value }) => (
        <TouchableOpacity
          key={id}
          onPress={() => onPressItem(id)}
          style={{
            backgroundColor: done ? '#1c9963' : '#fff',
            borderColor: '#000',
            borderWidth: 1,
            padding: 8,
          }}
        >
          <Text style={{ color: done ? '#fff' : '#000' }}>{value}</Text>
        </TouchableOpacity>
      ))}
    </View>
  );
}

export default function App() {
  const [text, setText] = useState('');
  const [items, setItems] = useState<TodoItem[]>([]);

  function selectItems() {
    db.transaction((tx) => {
      tx.executeSql('select * from items', [], (_, { rows: { _array } }) =>
        setItems(_array)
      );
    });
  }

  // Creates the database table on mount
  useEffect(() => {
    db.transaction(
      (tx) => {
        tx.executeSql(
          'create table if not exists items (id integer primary key not null, done int, value text);'
        );
      },
      undefined,
      selectItems
    );
  }, []);

  function addTodoItem(text: string) {
    if (!text) {
      return;
    }

    db.transaction(
      (tx) => {
        tx.executeSql('insert into items (done, value) values (0, ?)', [text]);
      },
      undefined,
      selectItems
    );
  }

  function completeTodoItem(id: number) {
    db.transaction(
      (tx) => {
        tx.executeSql(`update items set done = 1 where id = ?;`, [id]);
      },
      undefined,
      selectItems
    );
  }

  function deleteTodoItem(id: number) {
    db.transaction(
      (tx) => {
        tx.executeSql(`delete from items where id = ?;`, [id]);
      },
      undefined,
      selectItems
    );
  }

  return (
    <View style={styles.container}>
      <Text style={styles.heading}>SQLite Example</Text>
      <View style={styles.flexRow}>
        <TextInput
          onChangeText={(text) => setText(text)}
          onSubmitEditing={() => {
            addTodoItem(text);
            setText('');
          }}
          placeholder='What do you need to do?'
          style={styles.input}
          value={text}
        />
      </View>
      <ScrollView style={styles.listArea}>
        <Items items={items} done={false} onPressItem={completeTodoItem} />
        <Items items={items} done onPressItem={deleteTodoItem} />
      </ScrollView>
    </View>
  );
}

In our updated code, we've added a selectItems function which queries the database for the items and updates the items state with the results. Rather than calling forceUpdate after our database statements, we now call selectItems to make sure our items state always has the latest values. In our Items components, rather than querying the database for the items in the component, we now accept the items state as a prop and just render the items filtered by their done status. Now, since the components are just re-rendering with new state values rather than remounting, the flashing issue is gone.

Issues with React state

This is a fine solution for a simple example like this, but once we start adding more database tables, and we add more screens that need access to different data, this approach may start to lead to performance issues and complex code. Since you will likely need access to database state in many parts of your app, you will likely store the state in a top level context, meaning that any time you update your database, your entire app will re-render even if the current screen you're on doesn't care about the data you changed. You could solve this by using something like Zustand to keep your database state in a store and using selectors to subscribe to the specific data each component needs. But what about handling error states, having more fine-grained control of data invalidation, and dealing with the async nature of the SQLite statements? These sound a lot like the same problems you have when dealing with syncing your application state with your server state, a problem that React Query was created to solve. That's why when I'm using Expo SQLite, I treat my local database state as just a slightly different form of server state and use React Query to manage it. This way, I can have access to my data in any component that needs it, and isolate re-renders to the specific components that currently need access to that data. I also have a nicer api to handle data invalidation, mutations, and have error and loading states out of the box. Let's rewrite this example to use React Query.

Using React Query

First, let's make a file where we can export all our SQLite-related items. Then, let's write custom hooks for each query and mutation that our app needs.

import { useMutation, useQuery, useQueryClient } from '@tanstack/react-query';
import * as SQLite from 'expo-sqlite';

export const db = SQLite.openDatabase('db.db');

type TodoItem = {
  id: number;
  done: number;
  value: string;
};

export function useTodos(done: boolean) {
  return useQuery({
    queryKey: ['items', done],
    queryFn: () => {
      return new Promise<TodoItem[]>((resolve, reject) => {
        db.transaction(
          (tx) => {
            tx.executeSql(
              `select * from items where done = ?;`,
              [done ? 1 : 0],
              (_, { rows: { _array } }) => resolve(_array)
            );
          },
          (error) => reject(error)
        );
      });
    },
  });
}

export function useAddTodo() {
  const queryClient = useQueryClient();

  return useMutation({
    mutationFn: async (text: string) => {
      if (!text) {
        return;
      }

      return new Promise<void>((resolve, reject) => {
        db.transaction(
          (tx) => {
            tx.executeSql('insert into items (done, value) values (0, ?)', [
              text,
            ]);
          },
          (error) => reject(error),
          () => resolve()
        );
      });
    },
    onSuccess: () => {
      queryClient.refetchQueries({ queryKey: ['items', false] });
    },
  });
}

export function useCompleteTodo() {
  const queryClient = useQueryClient();

  return useMutation({
    mutationFn: (id: number) => {
      return new Promise<void>((resolve, reject) => {
        db.transaction(
          (tx) => {
            tx.executeSql('update items set done = 1 where id = ?;', [id]);
          },
          (error) => reject(error),
          () => resolve()
        );
      });
    },
    onSuccess: () => {
      queryClient.refetchQueries({ queryKey: ['items'] });
    },
  });
}

export function useDeleteTodo() {
  const queryClient = useQueryClient();

  return useMutation({
    mutationFn: (id: number) => {
      return new Promise<void>((resolve, reject) => {
        db.transaction(
          (tx) => {
            tx.executeSql('delete from items where id = ?;', [id]);
          },
          (error) => reject(error),
          () => resolve()
        );
      });
    },
    onSuccess: () => {
      queryClient.refetchQueries({ queryKey: ['items', true] });
    },
  });
}

Notice that since Expo SQLite has a callback-based api, we'll need to manually return a resolved or rejected promise ourselves. In the onSuccess callbacks of the useMutation hooks, we're refetching the queries that are effected by the mutation we just ran. This is similar to how we passed our selectItems function to the successCallback of the sql transactions in order to re-query the database, however, with this method, we can have more control over which specific queries we need to refetch. Notice we are calling refetchQueries rather than invalidateQueries which is more commonly used when dealing with invalidation from mutations. This is because by default, invalidateQueries only refetches queries that are currently active. If we were to just invalidate a query that wasn't currently active, once we navigate to a screen that uses that query, the user would see a brief flash of stale data. By always refetching our SQLite queries rather than just invalidating them, all of the app interactions involving this data will feel smooth and instantaneous.

Now in our App component, we need to wrap our app in a QueryClientProvider and make sure that our database table is ready before we can render our app and run our queries.

import { QueryClient, QueryClientProvider } from '@tanstack/react-query';

const queryClient = new QueryClient();

export default function App() {
  const [dbIsReady, setDbIsReady] = useState(false);

  useEffect(() => {
    db.transaction(
      (tx) => {
        tx.executeSql(
          'create table if not exists items (id integer primary key not null, done int, value text);'
        );
      },
      undefined,
      () => setDbIsReady(true)
    );
  }, []);

  if (!dbIsReady) {
    return null;
  }

  return (
    <QueryClientProvider client={queryClient}>
      <Main />
    </QueryClientProvider>
  );
}

In a real-world app, you would add this to the rest of the initialization logic for your app and would show a splash screen until it was ready.

Now we can move our content into our new Main component

function Items({
  done: doneHeading,
  onPressItem,
}: {
  done: boolean;
  onPressItem: (id: number) => void;
}) {
  const { data } = useTodos(doneHeading);

  const heading = doneHeading ? 'Completed' : 'Todo';

  if (!data || data.length === 0) {
    return null;
  }

  return (
    <View style={styles.sectionContainer}>
      <Text style={styles.sectionHeading}>{heading}</Text>
      {data.map(({ id, done, value }) => (
        <TouchableOpacity
          key={id}
          onPress={() => onPressItem(id)}
          style={{
            backgroundColor: done ? '#1c9963' : '#fff',
            borderColor: '#000',
            borderWidth: 1,
            padding: 8,
          }}
        >
          <Text style={{ color: done ? '#fff' : '#000' }}>{value}</Text>
        </TouchableOpacity>
      ))}
    </View>
  );
}

function Main() {
  const [text, setText] = useState('');

  const { mutate: addTodoItem } = useAddTodo();
  const { mutate: completeTodoItem } = useCompleteTodo();
  const { mutate: deleteTodoItem } = useDeleteTodo();

  return (
    <View style={styles.container}>
      <Text style={styles.heading}>SQLite Example</Text>
      <View style={styles.flexRow}>
        <TextInput
          onChangeText={(text) => setText(text)}
          onSubmitEditing={() => {
            addTodoItem(text);
            setText('');
          }}
          placeholder='What do you need to do?'
          style={styles.input}
          value={text}
        />
      </View>
      <ScrollView style={styles.listArea}>
        <Items done={false} onPressItem={completeTodoItem} />
        <Items done onPressItem={deleteTodoItem} />
      </ScrollView>
    </View>
  );
}

and that's it! After creating a few custom hooks with React Query, our app is now reactive to database updates, re-renders are isolated to specific components, and our app code is much cleaner.

Handling loading states

Notice that I omitted handling the isPending and isError states. In a real-world app, you'll probably want to handle the error state and display a message to the user if a query failed. However, when the query is in a pending state, I would recommend just returning null rather than showing any loading indicators. The query will resolve so quickly, that it will feel almost instantaneous to the user. If you rendered a loading indicator, it would appear as a quick flash to the user which would be a jarring experience.

Changing default query options

We could stop here, but there are a few extra options we should change in React Query to make our user experience even better. Here are the default query options I use for my SQLite queries along with comments to describe each option

const queryClient = new QueryClient({
  defaultOptions: {
    queries: {
      // Since all data is local, it will never be stale unless we explicitly invalidate it
      staleTime: Infinity,
      // Since data is never stale, the cache should never be garbage collected
      gcTime: Infinity,
      // Since the data is local, if the query fails, a retry won't lead to a different result
      retry: false,
      // Since the data is local, we should always fetch our queries regardless of the network state
      networkMode: 'always',
    },
  },
});

If you are also using React Query for your server state in addition to your SQLite state, you will likely want to have different defaults for your network queries, and your SQLite queries. To achieve this, we can add a 'sqlite' prefix to each of our SQLite query keys. So in our example, our todo query key would become this: queryKey: ['sqlite', 'items', done]. Then we can use queryClient.setQueryDefaults to use these defaults for only our SQLite queries. Our code would then look like this:

const queryClient = new QueryClient({
  defaultOptions: {
    // ...default options for our server queries
  },
});

// default options for our sqlite queries
queryClient.setQueryDefaults(['sqlite'], {
  staleTime: Infinity,
  gcTime: Infinity,
  retry: false,
  networkMode: 'always',
});

Conclusion

When I first started using Expo SQLite, I couldn't find much guidance on how to actually use the library in a real-world app other than Expo's example app which we looked at. When I started trying to write my own code to handle my database state, I realized that a lot of the issues I was trying to solve were the same issues involved in data fetching, and that the api that I wanted to use was similar to the React Query api. I figured I would just give React Query a try for this unconventional use-case and it has made my life easier. I hope this article helps make your life easier when using Expo SQLite as well. Let me know if you have any other tips for handling your database state when using Expo SQLite. Thanks for reading!

DEV Community: Ramsay Romero