Expo SQLite: A Real Local Database in React Native

Install and open a database

npx expo install expo-sqlite

There are two ways to get a connection. The direct one is fine for scripts and one-off work:

import * as SQLite from 'expo-sqlite';

const db = await SQLite.openDatabaseAsync('app.db');
await db.execAsync('PRAGMA journal_mode = WAL;');

In an actual app, use the provider instead. It opens the database once, keeps a single connection for the whole tree, and — the part that matters — lets you guarantee the schema exists before any screen runs a query. Opening a second connection to the same file from three different modules is how you end up with locking errors that only reproduce on device.

The provider, with migrations that survive updates

SQLite keeps an integer on every database file called user_version. It costs nothing, it is durable, and it is the entire migration system you need for a local database:

// db/migrate.ts
import type { SQLiteDatabase } from 'expo-sqlite';

const LATEST = 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 >= LATEST) return;

  if (version === 0) {
    await db.execAsync(`
      PRAGMA journal_mode = WAL;
      CREATE TABLE notes (
        id         TEXT PRIMARY KEY NOT NULL,
        title      TEXT NOT NULL,
        body       TEXT NOT NULL DEFAULT '',
        updated_at INTEGER NOT NULL
      );
      CREATE INDEX idx_notes_updated ON notes (updated_at DESC);
    `);
    version = 1;
  }

  if (version === 1) {
    // Additive changes only. Never drop a column your old build still writes to.
    await db.execAsync('ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0;');
    version = 2;
  }

  await db.execAsync(`PRAGMA user_version = ${LATEST}`);
}

// app/_layout.tsx
import { SQLiteProvider } from 'expo-sqlite';
import { Suspense } from 'react';
import { migrate } from '@/db/migrate';

export default function RootLayout() {
  return (
    <Suspense fallback={<Splash />}>
      <SQLiteProvider databaseName="app.db" onInit={migrate} useSuspense>
        <Stack />
      </SQLiteProvider>
    </Suspense>
  );
}

The rules that keep this from biting you later:

• Steps are append-only. Once a version has shipped, that block is frozen. Editing it changes nothing for existing installs — they already passed that number — and silently gives new installs a different schema from everyone else.
• Prefer additive changes. Adding a nullable column or one with a default is safe. Dropping or renaming is not, especially with over-the-air updates in play, where a user can roll between JS bundles while the database file stays put.
• Write the version last. If a step throws, the version stays where it was and the migration retries on next launch rather than leaving a half-applied schema behind.

Querying: four methods and one rule

import { useSQLiteContext } from 'expo-sqlite';

type Note = { id: string; title: string; body: string; updated_at: number; pinned: number };

function useNotes(search: string) {
  const db = useSQLiteContext();

  return useQuery({
    queryKey: ['notes', search],
    queryFn: () =>
      db.getAllAsync<Note>(
        `SELECT * FROM notes
          WHERE title LIKE ?
          ORDER BY pinned DESC, updated_at DESC
          LIMIT 50`,
        [`%${search}%`],                        // parameters, never template interpolation
      ),
  });
}

// One row or nothing
const note = await db.getFirstAsync<Note>('SELECT * FROM notes WHERE id = ?', [id]);

// Writes — result carries changes and lastInsertRowId
const res = await db.runAsync('UPDATE notes SET pinned = ? WHERE id = ?', [1, id]);
if (res.changes === 0) throw new Error('note vanished');

// Multiple statements, no parameters, no results
await db.execAsync('DELETE FROM notes WHERE updated_at < 0;');

The one rule is the placeholders. Interpolating a user-typed search string into SQL is the same injection bug it is on a server, except here it corrupts the user’s own data instead of a shared table — and it breaks on any name containing an apostrophe long before anyone attacks it. Parameters also let SQLite reuse the prepared statement, so the correct version is the faster one.

Transactions, and why your import is slow

Every standalone write in SQLite is its own implicit transaction, which means its own disk flush. Insert five hundred rows in a loop and you have paid for five hundred flushes. Wrapping the loop turns that into one:

await db.withTransactionAsync(async () => {
  const stmt = await db.prepareAsync(
    'INSERT OR REPLACE INTO notes (id, title, body, updated_at) VALUES (?, ?, ?, ?)',
  );
  try {
    for (const n of incoming) {
      await stmt.executeAsync([n.id, n.title, n.body, n.updatedAt]);
    }
  } finally {
    await stmt.finalizeAsync();          // always finalize, or the statement leaks
  }
});

The transaction also gives you atomicity, which matters more than speed on a phone: a sync that is interrupted by the user backgrounding the app leaves the database exactly as it was rather than half updated. And note finalizeAsync in a finally — a prepared statement that is never finalised holds resources for the life of the connection.

Keeping the UI in sync with the table

SQLite has no subscriptions of its own, so a screen will happily display stale rows after another screen writes. Two workable answers, depending on how much machinery you already have:

• You already use TanStack Query. Treat the database as the data source and invalidate the relevant query keys after a mutation. You get caching, loading states, and refetch-on-focus for free, and the local database slots in where the network call used to be.
• You do not. expo-sqlite exposes change listeners through addDatabaseChangeListener, which fires with the table and row that changed. Re-run the affected query when a table you care about is touched.

What does not work is loading rows into component state in useEffect and hoping. That is the pattern that produces the classic bug where an item deleted on the detail screen is still on the list when you swipe back.

Where SQLite sits among the storage options

One thing SQLite is not: a sync engine. It stores your data locally and answers questions about it. Getting those rows to a server and back is a separate design problem — connecting to a real backend covers the remote half, and the usual local half is an outbox table holding pending changes with a retry count, drained whenever connectivity returns.

Three habits worth adopting early

• Index what you sort by. A list ordered by updated_at DESC with no index scans and sorts the whole table on every render. One CREATE INDEX is the difference at a thousand rows, and a thousand rows arrives faster than you expect.
• Store timestamps as integers. Milliseconds since epoch sort correctly, compare cheaply, and avoid the timezone ambiguity of ISO strings. Format for display at the edge, never in the database.
• Generate ids on the client. A UUID written locally means a row can be created, edited, and referenced offline, then synced without rewriting foreign keys when the server hands back its own id.

Skip the setup

Provider, migration runner, typed query helpers — a day of plumbing before the first feature. Describe your app at shipnative.dev and it generates a React Native app with the data layer already wired, running on your phone in minutes, with the full Expo project available to export and take anywhere.