If you need to store data locally on a user's device with React Native, [Expo SQLite](https://docs.expo.dev/versions/latest/sdk/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](https://watermelondb.dev/docs), 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. ```tsx 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([]); // 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 ( {heading} {items.map(({ id, done, value }) => ( onPressItem(id)} style={{ backgroundColor: done ? '#1c9963' : '#fff', borderColor: '#000', borderWidth: 1, padding: 8, }} > {value} ))} ); } 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 ( SQLite Example setText(text)} onSubmitEditing={() => { addTodoItem(text); setText(''); }} placeholder='What do you need to do?' style={styles.input} value={text} /> ); } 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. ```tsx 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 ( {heading} {items.map(({ id, done, value }) => ( onPressItem(id)} style={{ backgroundColor: done ? '#1c9963' : '#fff', borderColor: '#000', borderWidth: 1, padding: 8, }} > {value} ))} ); } export default function App() { const [text, setText] = useState(''); const [items, setItems] = useState([]); 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 ( SQLite Example setText(text)} onSubmitEditing={() => { addTodoItem(text); setText(''); }} placeholder='What do you need to do?' style={styles.input} value={text} /> ); } ``` 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. ```tsx 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((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((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((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((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. ```tsx 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 (

); } ``` 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 ```tsx 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 ( {heading} {data.map(({ id, done, value }) => ( onPressItem(id)} style={{ backgroundColor: done ? '#1c9963' : '#fff', borderColor: '#000', borderWidth: 1, padding: 8, }} > {value} ))} ); } function Main() { const [text, setText] = useState(''); const { mutate: addTodoItem } = useAddTodo(); const { mutate: completeTodoItem } = useCompleteTodo(); const { mutate: deleteTodoItem } = useDeleteTodo(); return ( SQLite Example setText(text)} onSubmitEditing={() => { addTodoItem(text); setText(''); }} placeholder='What do you need to do?' style={styles.input} value={text} /> ); } ``` 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 ```ts 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: ```ts 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!

ramsayromero

How to make Expo SQLite reactive with React Query

Join Our Newsletter. No Spam, Only the good stuff.