SQLite in React Native: Local Data Storage in Practice

1. Why SQLite in React Native apps and where AsyncStorage hits its limits

AsyncStorage only stores key value pairs as strings. That is fine for a handful of settings or an auth token, but as soon as an app needs to keep hundreds or thousands of related records available offline, such as orders, product catalogs or chat histories, every single query becomes a problem. There is no filtering, no sorting and no joins at the database level: every read means loading the entire JSON string, parsing it, and then filtering it in JavaScript. As the data volume grows, deserialization time grows linearly with it, while the main thread stays blocked.

SQLite solves this by providing a full relational database directly on the device. Through expo-sqlite or react-native-sqlite-storage, the app talks to a native SQLite engine connected via JSI, so without the classic asynchronous bridge serialization. For local data storage with real queries, indexes and transaction guarantees, that is the only practical path: SQL queries filter and sort directly inside the database engine, not on the JavaScript thread.

This article deliberately covers the direct SQL approach without an ORM abstraction layer. Unlike WatermelonDB, which maps reactivity and a built in sync engine through its own model system, this piece uses hand written SQL with parameterized queries and a custom migration system. That means more control over every single query, but also more responsibility for schema changes and query optimization.

2. Setup with expo-sqlite

Installing expo-sqlite is done through the Expo CLI, which automatically picks the matching native version for the current Expo SDK. In a bare React Native project without the Expo managed workflow, react-native-sqlite-storage is used instead, which requires manual native linking through CocoaPods and Gradle. Since Expo SDK 51, expo-sqlite offers a fully promise based API with openDatabaseAsync, execAsync, runAsync, getAllAsync and getFirstAsync, which fully replaces the older callback based approach.

The actual SQLite database file lives in the app's sandboxed documents directory and is isolated from other apps, an important aspect for local data storage without any cloud dependency. On the first call to openDatabaseAsync("app.db"), the file is created automatically if it does not exist yet. In app.json, the Expo config plugin can set additional native options, for example whether SQLCipher should be enabled for encrypted local data storage.

# Install expo-sqlite for local SQLite-based data storage
npx expo install expo-sqlite

# For bare React Native projects (no Expo managed workflow)
npm install react-native-sqlite-storage
cd ios && pod install

{
  "expo": {
    "name": "InventoryApp",
    "plugins": [
      [
        "expo-sqlite",
        {
          "enableFTS": true,
          "useSQLCipher": true,
          "android": {
            "enableFTS": true,
            "useSQLCipher": true
          }
        }
      ]
    ]
  }
}

3. Schema design and migrations

A relational schema for local data storage starts with clear tables, primary keys and foreign key relationships, just like on a server database. The difference lies in versioning: SQLite provides PRAGMA user_version, a built in integer counter stored directly inside the database file, which is a great fit as a schema version. On app start, this value is read, compared against the target version in code, and every missing migration is run one after another.

The migration runner is therefore not an external framework, but a simple loop over numbered SQL scripts. Each migration gets its own function that maps exactly one version step, such as adding a column or creating a new index. After a migration runs successfully, PRAGMA user_version is set to the new number. This guarantees that a user updating directly from version 3 to version 7 goes through all four intermediate steps in the correct order, without the app ever having to check that explicitly.

What matters for stable local data storage is that migrations never start out destructive. ALTER TABLE ... ADD COLUMN is straightforward in SQLite, but removing a column has, until recently, required the detour of creating a new table, copying the data, and renaming it. Wrapping this flow in a transaction prevents an aborted migration from leaving the database in an inconsistent intermediate state.

// db.js: database initialization and versioned migrations
import { openDatabaseAsync } from "expo-sqlite";

const TARGET_VERSION = 3;

// Each migration maps exactly one version step
const migrations = {
  1: async (db) => {
    await db.execAsync(`
      CREATE TABLE IF NOT EXISTS notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        body TEXT NOT NULL DEFAULT '',
        created_at INTEGER NOT NULL
      );
    `);
  },
  2: async (db) => {
    await db.execAsync(`ALTER TABLE notes ADD COLUMN is_archived INTEGER NOT NULL DEFAULT 0;`);
  },
  3: async (db) => {
    await db.execAsync(`CREATE INDEX IF NOT EXISTS idx_notes_created_at ON notes(created_at);`);
  },
};

export async function openDatabase() {
  const db = await openDatabaseAsync("app.db");
  const result = await db.getFirstAsync("PRAGMA user_version;");
  let currentVersion = result.user_version;

  while (currentVersion < TARGET_VERSION) {
    currentVersion += 1;
    await migrations[currentVersion](db);
    await db.execAsync(`PRAGMA user_version = ${currentVersion};`);
  }

  return db;
}

4. CRUD operations in practice

CRUD on a SQLite based local data storage layer follows the same basic rules as on any other relational database: values never belong inside a query through string concatenation, they always go in as bound parameters. runAsync handles INSERT, UPDATE and DELETE and returns, among other things, lastInsertRowId and changes, so the caller knows how many rows were affected without an extra query. For SELECT, getAllAsync is available for lists and getFirstAsync for a single record or null.

Parameterized queries with placeholders like ? not only protect against SQL injection, they also let SQLite reuse the same prepared statement, which is noticeably faster on repeated queries with different values than parsing a fresh query on every call. In a React Native component, this access is typically wrapped in a dedicated hook that pulls the database handle from a context and manages loading and error states.

For local data storage with frequent changes coming from multiple screens, a simple event system or a global store that reloads the affected lists after every write is also worth adding. Unlike WatermelonDB, there are no built in observables that propagate changes automatically here, that has to be wired up deliberately with direct SQLite.

// useNotes.js: custom hook for CRUD access to local SQLite data
import { useCallback, useEffect, useState } from "react";
import { useDatabase } from "./DatabaseProvider";

export function useNotes() {
  const db = useDatabase();
  const [notes, setNotes] = useState([]);
  const [loading, setLoading] = useState(true);

  const loadNotes = useCallback(async () => {
    setLoading(true);
    const rows = await db.getAllAsync(
      "SELECT * FROM notes WHERE is_archived = ? ORDER BY created_at DESC;",
      [0]
    );
    setNotes(rows);
    setLoading(false);
  }, [db]);

  const createNote = useCallback(
    async (title, body) => {
      const result = await db.runAsync(
        "INSERT INTO notes (title, body, created_at) VALUES (?, ?, ?);",
        [title, body, Date.now()]
      );
      await loadNotes();
      return result.lastInsertRowId;
    },
    [db, loadNotes]
  );

  const archiveNote = useCallback(
    async (id) => {
      await db.runAsync("UPDATE notes SET is_archived = 1 WHERE id = ?;", [id]);
      await loadNotes();
    },
    [db, loadNotes]
  );

  const deleteNote = useCallback(
    async (id) => {
      await db.runAsync("DELETE FROM notes WHERE id = ?;", [id]);
      await loadNotes();
    },
    [db, loadNotes]
  );

  useEffect(() => {
    loadNotes();
  }, [loadNotes]);

  return { notes, loading, createNote, archiveNote, deleteNote, reload: loadNotes };
}

5. Transactions and batch operations

When several related writes must either all succeed or none at all, an explicit transaction is mandatory. expo-sqlite provides withTransactionAsync for this, which takes a callback and automatically issues a COMMIT once the callback completes successfully, or a ROLLBACK as soon as an exception is thrown. A typical example for local data storage is creating an order together with its line items: either all rows land in the database, or none of them do.

For bulk operations like importing an entire product catalog, a transaction matters not only for consistency but also for performance. Every single INSERT outside a transaction forces its own disk sync by default, which causes noticeable delays with thousands of rows. Wrapping the same import into a single transaction reduces the number of fsync calls to just one at the end, which often speeds up the import by an order of magnitude.

SQLite supports nested transactions through savepoints, which is rarely needed in practice but useful when one sub-step inside a larger transaction needs to be rolled back in isolation without aborting the outer transaction. For most use cases in a React Native app, however, a single flat transaction per logical operation is entirely sufficient.

6. Indexes and query performance

Without a matching index, SQLite scans the entire table on every filtered or sorted query, a so called full table scan. On small tables with a few hundred rows this hardly shows, but on tables with tens of thousands of entries, such as a local cache of product data, every query gets noticeably slower the more rows the app keeps offline. CREATE INDEX idx_name ON table(column) builds a B-tree index on exactly the columns that are frequently filtered or sorted by.

To understand whether an index is actually being used, EXPLAIN QUERY PLAN run before the real query is the tool of choice. Its output shows whether SQLite performs an index scan or a full table scan, and for joins, in what order the tables get combined. A common mistake in local data storage is placing an index on a column that gets transformed by a function in the WHERE clause, for example WHERE lower(name) = ?, since a regular index does not help here. An explicit expression index on exactly that expression fixes it.

Composite indexes across multiple columns pay off when a query regularly filters or sorts by the same two or three columns together, for example CREATE INDEX idx_notes_status_date ON notes(is_archived, created_at). Too many indexes, on the other hand, slow down every write, because each index has to be maintained on every INSERT or UPDATE. For local data storage with frequent writes, a deliberate trade off between read and write performance is needed.

7. Encryption and security of local data

An unencrypted SQLite file sits as a regular file in the device's file system. On a rooted Android device, or with physical access to an iPhone backup, this file can be read directly with common tools. For sensitive local data storage, such as health data, payment information or private messages, the operating system's sandbox isolation alone is not enough.

SQLCipher transparently encrypts the entire database file with AES-256 and can be enabled through the useSQLCipher option of the Expo config plugin. When opening the database, a passphrase parameter is passed in as well, which ideally should not be hard coded but instead come from the device's secure key store, the Keychain on iOS, the Android Keystore on Android, both reachable through expo-secure-store.

One important point for encrypted local data storage: the encryption overhead applies to every single page that SQLite reads or writes, not just to the initial connection. On very large databases with heavy write loads, benchmarking before and after enabling SQLCipher is worth doing, to verify that performance stays acceptable across the target range of supported devices.

8. Testing SQLite code

Code that writes directly against SQLite is most reliably tested with a real, but temporary, database instead of fully mocking the database layer. expo-sqlite supports the special database name :memory:, which creates a complete SQLite instance purely in memory. Every test opens a fresh in memory database, runs the same migrations as the production app, and then verifies the actual behavior of queries, constraints and transactions.

This approach catches bugs that a plain mock would never find, such as a forgotten NOT NULL constraint, a broken foreign key, or a migration that fails at a particular intermediate state. For Jest tests, that means: in a beforeEach block, the in memory database is reopened and migrated, in afterEach it gets closed, so tests run fully isolated from one another and do not influence each other.

For components that get the database handle through context, tests can substitute the same provider with an in memory database instead of the real file. That allows testing the full chain from UI interaction down to the actual SQL query with React Testing Library, without mocking a single line of production logic.

// notes.test.js: testing SQLite-backed logic with an in-memory database
import { openDatabaseAsync } from "expo-sqlite";
import { runMigrations } from "../db/migrations";
import { createNote, archiveNote } from "../db/notesRepository";

describe("notesRepository", () => {
  let db;

  beforeEach(async () => {
    // A fresh in-memory database for every test, fully isolated
    db = await openDatabaseAsync(":memory:");
    await runMigrations(db);
  });

  afterEach(async () => {
    await db.closeAsync();
  });

  it("creates a note and returns its generated id", async () => {
    const id = await createNote(db, "Shopping list", "Milk, eggs, bread");
    const row = await db.getFirstAsync("SELECT * FROM notes WHERE id = ?;", [id]);

    expect(row.title).toBe("Shopping list");
    expect(row.is_archived).toBe(0);
  });

  it("archives a note without deleting it", async () => {
    const id = await createNote(db, "Old note", "");
    await archiveNote(db, id);
    const row = await db.getFirstAsync("SELECT * FROM notes WHERE id = ?;", [id]);

    expect(row.is_archived).toBe(1);
  });
});

9. SQLite compared to WatermelonDB and MMKV for different use cases

Direct SQLite, WatermelonDB and MMKV all solve the problem of local data storage, but with very different trade offs between control, reactivity and setup effort. The right choice depends less on personal preference and more on the actual data model and the reactivity the app requires.

For an app that primarily needs to keep structured, relational data available offline and does not need automatic server synchronization, direct SQLite is usually the most pragmatic choice: fewer abstraction layers, full control over schema and query plan. Once reactivity across many screens and a built in sync engine are required, WatermelonDB offsets that extra effort with less boilerplate code. MMKV remains the right choice for simple key value data with no relational requirements at all.

10. Summary

SQLite remains the most robust choice for local data storage in React Native apps once AsyncStorage hits its limits: real SQL queries instead of string parsing, ACID transactions instead of ad hoc consistency, and a built in version counter via PRAGMA user_version for clean migrations. expo-sqlite provides a modern, promise based API for this that directly supports parameterized queries, batch transactions and in memory databases for testing, with no additional ORM abstraction at all.

Choosing the direct SQL route means taking on responsibility for migrations, indexing and reactivity yourself, instead of delegating them to a framework like WatermelonDB. For relational data with complex queries and a genuine need for control over schema and query plan, that is the right trade off. For simple key value data, MMKV remains the lighter weight alternative, and for apps with heavy server sync needs, WatermelonDB is the more reactive option.

11. FAQ: SQLite and local data storage in React Native