A Local Database

expo-sqlite gives you real SQL on the device, with versioned migrations instead of a growing pile of keys.

Syntaxconst db = await SQLite.openDatabaseAsync(databaseName: string, options?: SQLiteOpenOptions)

A key-value store answers one question: what is under this key. As soon as you want the ten most recent items, everything matching a search, or a count, you are loading the whole blob and filtering it in JavaScript. expo-sqlite runs a real SQLite database inside your app, so the query runs in C over an index instead. Install it with npx expo install expo-sqlite.

Opening and querying

openDatabaseAsync(name, options?) returns a database. Writes go through runAsync, which returns { lastInsertRowId, changes }. Reads use getAllAsync for a bounded result set, getFirstAsync for one row or null, and getEachAsync for an async iterator over a large one. execAsync runs several statements at once but binds no parameters, so it is for schema work only.

Migrations

The schema on a user's phone is whichever version they last installed. SQLite tracks this for you in PRAGMA user_version: read it, apply each step above it in order, then write the new number. Put that function in SQLiteProvider's onInit prop and every screen below it can call useSQLiteContext() to get the same open database.

What trips people up

Column names come back exactly as the SQL spells them, including a missing alias on COUNT(*). Booleans are integers; there is no boolean type. And a migration step, once shipped, can never be edited — only followed by another one.

Example

import { useEffect, useState } from 'react';
import { FlatList, StyleSheet, Text } from 'react-native';
import { SQLiteProvider, useSQLiteContext, type SQLiteDatabase } from 'expo-sqlite';

type Note = { id: number; body: string };

const DATABASE_VERSION = 1;

async function migrate(db: SQLiteDatabase) {
  const row = await db.getFirstAsync<{ user_version: number }>('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 notes (id INTEGER PRIMARY KEY NOT NULL, body TEXT NOT NULL);
    `);
    version = 1;
  }
  await db.execAsync(`PRAGMA user_version = ${DATABASE_VERSION}`);
}

export default function NotesApp() {
  return (
    <SQLiteProvider databaseName="notes.db" onInit={migrate}>
      <NoteList />
    </SQLiteProvider>
  );
}

function NoteList() {
  const db = useSQLiteContext();
  const [notes, setNotes] = useState<Note[]>([]);

  useEffect(() => {
    // The generic types the rows; it does not verify them at runtime.
    db.getAllAsync<Note>('SELECT id, body FROM notes ORDER BY id DESC').then(setNotes);
  }, [db]);

  return (
    <FlatList
      data={notes}
      keyExtractor={(item) => String(item.id)}
      renderItem={({ item }) => <Text style={styles.row}>{item.body}</Text>}
      ListEmptyComponent={<Text style={styles.row}>No notes yet</Text>}
    />
  );
}

const styles = StyleSheet.create({
  row: { padding: 16, fontSize: 16 },
});

When to use it

Store a synced list of records you need to search, sort and paginate without a network call.
Keep an outbox table of changes made offline, with a row per pending write.
Cache an API response as rows so a list screen can open instantly and query by date.
Ship a prefilled reference database with the app and read from it on first launch.

More examples

Three ways to bind the same parameters

All three binding forms escape the value for you; the difference is readability. The variadic form is the one you will meet most often in other people's code, and it is the same call as the array form. runAsync's return value is how you learn the id of the row you just created: the Output line above assumes a freshly created database, where the first INSERT gets rowid 1 and changes one row.

import { type SQLiteDatabase } from 'expo-sqlite';

async function rename(db: SQLiteDatabase, id: number, body: string) {
  // Named placeholders. Clearest when a statement has several parameters.
  await db.runAsync('UPDATE notes SET body = $body WHERE id = $id', { $body: body, $id: id });

  // Positional, variadic. Shorthand for the array form below.
  await db.runAsync('UPDATE notes SET body = ? WHERE id = ?', body, id);

  // Positional, array. Exactly what the variadic call turns into.
  await db.runAsync('UPDATE notes SET body = ? WHERE id = ?', [body, id]);
}

async function insert(db: SQLiteDatabase) {
  const result = await db.runAsync('INSERT INTO notes (body) VALUES (?)', 'Buy milk');
  console.log(result.lastInsertRowId, result.changes);
  // Output: 1 1
}

Adding a column in version 2

This is what a second release looks like. The version 0 block is untouched, because a user installing for the first time still runs it, and a user upgrading from the previous release starts at 1 and runs only the new step. Each step is an if on the current version rather than an else, so a device two versions behind walks through both in order. The DEFAULT clause is what keeps existing rows valid.

import { type SQLiteDatabase } from 'expo-sqlite';

const DATABASE_VERSION = 2;

export async function migrate(db: SQLiteDatabase) {
  const row = await db.getFirstAsync<{ user_version: number }>('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 notes (id INTEGER PRIMARY KEY NOT NULL, body TEXT NOT NULL);
    `);
    version = 1;
  }
  if (version === 1) {
    // SQLite has no boolean: 0 and 1 in an INTEGER column.
    await db.execAsync('ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0');
    version = 2;
  }
  await db.execAsync(`PRAGMA user_version = ${DATABASE_VERSION}`);
}

A batch of writes in one transaction

Reach for this when importing or syncing: a thousand separate runAsync calls each commit on their own and are far slower than one transaction, and if the import fails halfway you are left with half an import. withExclusiveTransactionAsync only includes work done on the txn object it hands you, which is why it is the safer choice over withTransactionAsync when other screens may be querying at the same time.

import { type SQLiteDatabase } from 'expo-sqlite';

export async function importNotes(db: SQLiteDatabase, bodies: string[]) {
  await db.withExclusiveTransactionAsync(async (txn) => {
    // Compile the statement once, then run it per row.
    const statement = await txn.prepareAsync('INSERT INTO notes (body) VALUES (?)');
    try {
      for (const body of bodies) {
        await statement.executeAsync(body);
      }
    } finally {
      // Without finalizeAsync the statement leaks, even if a row throws.
      await statement.finalizeAsync();
    }
  });
}

Discussion

Be the first to comment on this lesson.

Opening and querying

Migrations

What trips people up

Example

When to use it

More examples

Three ways to bind the same parameters

Adding a column in version 2

A batch of writes in one transaction

Related

Discussion