Skip to content
Tutorials Center
My learning

Topics

Lesson 41 of 71 ยท State and Data

Local Databases with SQLite

Store structured data on the device with expo-sqlite: queries, transactions, schema migrations, Drizzle and WatermelonDB overview, and offline-first sync ideas.

Key-value storage is fine for a theme or a token, but it falls apart when you have hundreds of notes, messages or workouts. You want to filter them, sort them, count them, and update one without rewriting all of them. That is a job for a database, and every phone already ships with one: SQLite, a small relational database that lives in a single file inside your app's storage.

In Expo, the expo-sqlite package gives you access to it. If you know SQL (and if not, the SQL tutorial teaches it), you already know most of what you need. This lesson covers the API, how to change your tables safely in later releases, the libraries built on top of SQLite, and the ideas behind offline-first apps.

What runs on this site. The phone's SQLite file and the expo-sqlite module need a real device or emulator, so the app code is read-only. Two stand-ins run here: a normal SQL editor (SQLite-compatible SQL, so the queries you write are the queries you would run in the app) and a small migration runner written in plain JavaScript that shows the logic behind schema versions.

When to use a database

Key-value or database?

Key-value A few settings, flags and tokens.
SQLite Lists of records you filter, sort and join.
Offline-first Data must work with no connection.

If you would write a loop over a big stored array, you want a database.

1 / 2

Using expo-sqlite

You open a database by name. If the file does not exist, SQLite creates it. All methods have async versions, which is what you should use so the UI thread stays responsive.

JSX
import * as SQLite from 'expo-sqlite';

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

// Run several statements at once (schema, pragmas)
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
  );
`);

// Insert with parameters
const result = await db.runAsync('INSERT INTO todos (title) VALUES (?)', 'Buy milk');
console.log(result.lastInsertRowId, result.changes);

// Read many rows, or just one
const open = await db.getAllAsync('SELECT * FROM todos WHERE done = ?', 0);
const one = await db.getFirstAsync('SELECT * FROM todos WHERE id = ?', 1);

Always pass values as parameters (the ? placeholders), never by gluing strings together. Parameters keep a title like Robert'); DROP TABLE todos;-- as plain text, which is how you avoid SQL injection even in an app that "only talks to itself" (data may come from a deep link, a QR code or a server).

Several changes that belong together go in a transaction, so a crash in the middle leaves no half-finished state:

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);
});

In an app, you usually open the database once and share it through a provider so every screen can reach it:

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

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

function TodoScreen() {
  const db = useSQLiteContext();
  // db.getAllAsync(...), db.runAsync(...)
}

Practicing the SQL

Everything inside those strings is ordinary SQLite SQL. Here it is in the browser. This is a stand-in for the database file; the statements are the same ones you would pass to execAsync and getAllAsync. Change the data and re-run to explore, including the upsert (ON CONFLICT ... DO UPDATE) that is handy for settings.

SQL A todo schema in SQLite SQL

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

The second upsert replaces the value instead of failing on the duplicate key, so the settings table ends with one theme row set to dark.

Migrations: changing tables later

Your first release creates the tables. Your second release needs a new column. But users have the old file already, so you cannot just edit the CREATE TABLE and hope. You need migrations: ordered, numbered steps that bring any older database up to date.

SQLite keeps one free integer for this purpose, PRAGMA user_version. It starts at 0. The usual pattern is to read it at startup, run every step above it, and write the new number:

JSX
async function migrateDbIfNeeded(db) {
  const LATEST = 2;
  const row = await db.getFirstAsync('PRAGMA user_version');
  let version = row.user_version;
  if (version >= LATEST) return;

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

Two rules keep migrations safe. Never edit an old step: users who already ran it will not run it again, so changes go in a new step. And make each step all-or-nothing: if a step fails halfway, the database must not claim it is at the new version. The runner below shows both ideas. "Add a broken step" simulates a failing migration: it rolls back, the version stays put, and the app can try again after a fix.

A migration runner with rollback

Launch v1 first, then v2: the second launch runs only the third step. That is a migration working as intended.

Libraries built on SQLite

Raw SQL strings are flexible but easy to mistype, and they know nothing about your JavaScript types. Several libraries sit on top:

  • Drizzle ORM lets you describe tables in TypeScript and write typed queries (db.select().from(todos).where(eq(todos.done, false))). Its CLI generates migration files from your schema changes, and a hook applies them at startup. It keeps SQL visible, so what you learn transfers.
  • WatermelonDB is built for large, reactive apps. It loads records lazily, so big databases open fast, and components can observe a query and re-render automatically when its rows change. It also has a built-in synchronization protocol with pullChanges and pushChanges hooks.
  • Other options include faster native SQLite bindings (such as op-sqlite) and the key-value store expo-sqlite/kv-store, which offers an AsyncStorage-like API on top of SQLite.

A sensible default: raw expo-sqlite for small and medium needs, Drizzle when you want type safety, WatermelonDB when you have tens of thousands of records and sync.

Offline-first ideas

An offline-first app treats the local database as the source of truth. Screens read from and write to SQLite only, and a separate process exchanges changes with the server whenever there is a connection. That makes the app fast and usable in a tunnel or on a plane.

flowchart LR
  UI["Screen"] -->|"read and write"| DB[("Local SQLite")]
  DB -->|"pending changes"| OUT["Outbox"]
  OUT -->|"push when online"| API["Server"]
  API -->|"pull changes since cursor"| DB

Screens only ever talk to the local database; syncing is a background job.

The pieces you will need:

  • Client-generated IDs (such as UUIDs), so a record created offline has an identity before the server has seen it.
  • An outbox: a table of changes not yet sent. Pushes must be idempotent, so sending the same change twice does no harm.
  • updated_at and soft deletes: instead of deleting a row, mark it deleted so the deletion can be synced too.
  • A pull cursor: ask the server for "everything changed since timestamp or version X" and remember the newest one you received.
  • A conflict rule: if two devices edited the same record, decide the winner. "Last write wins" is simple; merging per field is kinder to users.

Sync is hard to get right, which is a good reason to consider WatermelonDB's protocol or a sync service before writing your own.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why pass values as ? parameters instead of building the SQL string yourself?

    With parameters the database never interprets the value as SQL, so text like a quote or a DROP TABLE statement stays just text.
  2. 2. What is PRAGMA user_version used for in migrations?

    It is a free integer stored in the database file. At startup you read it, run the steps above it and write the new value.
  3. 3. A released migration step has a mistake. What should you do?

    Old steps are history. Corrections go into a new numbered step so every database ends in the same state.
  4. 4. In an offline-first app, where do screens read and write data?

    The local database is the source of truth. A separate sync process pushes the outbox and pulls server changes when a connection exists.

Summary

  • SQLite is the database built into every phone; expo-sqlite exposes it with execAsync, runAsync, getAllAsync, getFirstAsync and withTransactionAsync.
  • Always use ? parameters, and group related changes in a transaction.
  • Use PRAGMA user_version for numbered migrations: never edit old steps, keep each step all-or-nothing.
  • Drizzle adds typed queries and generated migrations; WatermelonDB adds lazy loading, observable queries and a sync protocol.
  • Offline-first apps read and write locally and sync in the background using client IDs, an outbox, soft deletes, a pull cursor and a conflict rule.
  • On this site the real database is read-only; the SQL editor and migration runner are stand-ins.

Next, collect and check what users type with Forms and Validation.