Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 38 ยท Data, Storage and Networking

SQLite Databases with expo-sqlite

Store structured data offline with expo-sqlite: open a database, create tables, insert, query, use transactions, run migrations and wire it into React with a provider.

When your app has lists of things (notes, tasks, workouts, products) a key-value store gets awkward fast. You want to filter, sort, count and update single rows. That is exactly what SQLite is for, and Expo ships it as expo-sqlite: a real SQLite engine inside your app, working offline.

The API is small. You open a database, run SQL, and read rows back as plain objects. If you know basic SQL from the SQL tutorial, you already know most of what you need.

What runs on this site. The native module cannot run in a browser, so the real expo-sqlite code is read-only. The runnable stand-in wraps Node's built-in node:sqlite in a small class with the same method names (execAsync, runAsync, getAllAsync, getFirstAsync), so the SQL and the call patterns are real.

Install and open a database

Terminal
npx expo install expo-sqlite
JSX
import * as SQLite from 'expo-sqlite';

const db = await SQLite.openDatabaseAsync('app.db');

await db.execAsync(`
  PRAGMA journal_mode = WAL;
  CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    done INTEGER NOT NULL DEFAULT 0
  );
`);

execAsync runs one or more statements and returns nothing. Use it for schema changes. The file app.db lives in the app's private storage and survives restarts.

The four methods you use daily

Method Use it for
execAsync(sql) Several statements, no parameters (schema)
runAsync(sql, params) INSERT, UPDATE, DELETE. Returns lastInsertRowId and changes
getAllAsync(sql, params) SELECT many rows (array of objects)
getFirstAsync(sql, params) SELECT one row, or null

Always pass values as parameters (? placeholders), never by building the string yourself. That prevents SQL injection and handles quotes for you.

Node.js CRUD with parameters (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Transactions

A transaction makes several writes succeed or fail together. If moving money between two accounts fails after the first update, you do not want half a transfer. In expo-sqlite use withTransactionAsync:

JSX
await db.withTransactionAsync(async () => {
  await db.runAsync('UPDATE accounts SET balance = balance - ? WHERE id = ?', 50, 1);
  await db.runAsync('UPDATE accounts SET balance = balance + ? WHERE id = ?', 50, 2);
});

If the callback throws, everything inside is rolled back. Transactions are also much faster for bulk inserts, because SQLite syncs to disk once instead of once per row.

Node.js Rollback on failure

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Migrations with user_version

Your app will change. Users still have the old database file, so on startup you must upgrade it. SQLite keeps an integer called user_version for this:

JSX
const DATABASE_VERSION = 2;

async function migrateDbIfNeeded(db) {
  const row = await db.getFirstAsync('PRAGMA user_version');
  let version = row?.user_version ?? 0;
  if (version >= DATABASE_VERSION) return;

  if (version === 0) {
    await db.execAsync(`
      PRAGMA journal_mode = WAL;
      CREATE TABLE todos (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL);
    `);
    version = 1;
  }
  if (version === 1) {
    await db.execAsync('ALTER TABLE todos ADD COLUMN done INTEGER NOT NULL DEFAULT 0;');
    version = 2;
  }
  await db.execAsync(`PRAGMA user_version = ${DATABASE_VERSION}`);
}

Each step runs once, in order, on any starting version. Never edit an old step; add a new one.

Using it in React

expo-sqlite offers a provider so every screen shares one connection:

JSX
import { SQLiteProvider, useSQLiteContext } from 'expo-sqlite';
import { Suspense, useEffect, useState } from 'react';
import { ActivityIndicator, FlatList, Text } from 'react-native';

export default function Root() {
  return (
    <Suspense fallback={<ActivityIndicator />}>
      <SQLiteProvider databaseName="app.db" onInit={migrateDbIfNeeded} useSuspense>
        <TodoList />
      </SQLiteProvider>
    </Suspense>
  );
}

function TodoList() {
  const db = useSQLiteContext();
  const [todos, setTodos] = useState([]);
  useEffect(() => {
    db.getAllAsync('SELECT * FROM todos ORDER BY id DESC').then(setTodos);
  }, [db]);
  return <FlatList data={todos} keyExtractor={(t) => String(t.id)} renderItem={({ item }) => <Text>{item.title}</Text>} />;
}

There is also useSQLiteContext().getEachAsync for streaming large result sets row by row, and addDatabaseChangeListener to refresh a screen when data changes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why pass values as ? parameters?

    Parameters keep data separate from SQL text, so user input cannot change the query.
  2. 2. Which method returns a single row or null?

    getFirstAsync returns the first row as an object, or null if there is none.
  3. 3. What happens if the callback of withTransactionAsync throws?

    A transaction is all or nothing.
  4. 4. What is PRAGMA user_version used for?

    It is an integer stored in the database file that your migration code reads and updates.

Summary

  • expo-sqlite gives a real local SQL database: open, execAsync, runAsync, getAllAsync, getFirstAsync.
  • Use ? parameters for all values.
  • Wrap related writes in withTransactionAsync; bulk inserts get faster too.
  • Migrate with PRAGMA user_version, adding steps and never editing old ones.
  • SQLiteProvider and useSQLiteContext share the connection across screens.

Next: Caching server data with TanStack Query