3.3: SQLite, Context, and Reducer

Learning Objectives

• Explain why local SQLite storage is useful after API fetch and parsing workflows
• Create a local SQLite table in Expo with CREATE TABLE IF NOT EXISTS
• Run INSERT, SELECT, and UPDATE queries with async SQLite APIs
• Organize SQLite logic using repository + reducer + context boundaries

In 3.1, we learned when to run async effects. In 3.2, we learned how to parse and model API data safely.

Now we answer a new question:

What happens when the app closes or the network is unavailable?

SQLite gives you structured local persistence, so data can survive reloads and work offline.

SQLite in Expo

expo-sqlite lets your app run SQL directly on a local database file.¹

The recommended pattern uses SQLiteProvider + useSQLiteContext:

1. Wrap your app in <SQLiteProvider> with an onInit callback.
2. Create tables in the initialization function.
3. Access the database in child components via useSQLiteContext().
4. Run queries with getAllAsync, runAsync, and execAsync.

export default function App() {
  return (
    <SQLiteProvider databaseName="app.db" onInit={initDb}>
      <Main />
    </SQLiteProvider>
  );
}

async function initDb(db: SQLiteDatabase) {
  await db.execAsync(`
    CREATE TABLE IF NOT EXISTS todos (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      todo TEXT NOT NULL,
      completed INTEGER NOT NULL DEFAULT 0
    );
  `);
}

function Main() {
  const db = useSQLiteContext();
  // Use db for queries
}

Prevent SQL Injection

Use parameters (?) in runAsync instead of string concatenation. It keeps queries cleaner and safer.

Simple SQLite Example

Database Context

SQLiteProvider manages the database connection lifecycle automatically:

• Opens the database when the provider mounts.
• Calls your onInit function to set up tables.
• Provides the database instance via React Context.
• Closes the database when the provider unmounts.

This pattern is cleaner than manually opening/closing databases in useEffect hooks and prevents common lifecycle bugs during Fast Refresh.

Child components access the database with useSQLiteContext():

function TodoList() {
  const db = useSQLiteContext();
  const [todos, setTodos] = useState([]);

  useEffect(() => {
    async function loadTodos() {
      const rows = await db.getAllAsync('SELECT * FROM todos');
      setTodos(rows);
    }
    loadTodos();
  }, [db]);

  return <FlatList data={todos} />;
}

Official docs: expo-sqlite Reference

Deeper Dive: Why use INTEGER for booleans in SQLite?

SQLite does not have a dedicated boolean column type. Most apps store booleans as integers:

• 0 means false
• 1 means true

That is why the examples use completed INTEGER NOT NULL DEFAULT 0 and toggle values with SQL.

Deeper Dive: What about database migrations?

In production apps, you often need a migration system to track schema versions as your app evolves. The Expo SQLite docs show a pattern using PRAGMA user_version for this.

For learning SQLite basics, we use a simpler pattern:

async function initDb(db: SQLiteDatabase) {
  // Create table if it doesn't exist (idempotent)
  await db.execAsync(`
    CREATE TABLE IF NOT EXISTS todos (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      todo TEXT NOT NULL,
      completed INTEGER NOT NULL DEFAULT 0
    );
  `);

  // Seed data only if table is empty
  const result = await db.getFirstAsync<{ count: number }>(
    'SELECT COUNT(*) as count FROM todos'
  );
  if (result?.count === 0) {
    // Insert initial rows
  }
}

This keeps focus on SQL operations without migration complexity. You can introduce version tracking later when building larger apps that need schema evolution.

Separation of (Data) Concerns

The previous Snack example keeps everything in one component on purpose so you can see the full flow. However, your developer-senses were hopefully tingling at all the tight coupling! Before reading on, go back to Moodle and complete the two interactive video activities titled Exercise 3.3.1 - Learn React Hooks: useContext and Exercise 3.3.2 - Learn React Hooks: useReducer.

Deeper Dive: Context & Reducer

After watching the videos, if you want more context (hah) then you can give the following pages from the official React docs a read:

1. Passing Data Deeply with Context
2. Extracting State Logic into a Reducer

Too Many States

Once you introduce reducer + context + SQLite, your app effectively has state in three places:

1. UI state is what is currently rendered on screen (loading spinners, list rows, error text).
2. In-memory app state is what reducer/context handles in the current runtime session.
3. Persisted database state are the rows stored in SQLite that survive reloads and app restarts.

These layers are related, but not identical at every moment. Your job is to reconcile them intentionally:

1. User triggers an action in UI (addTodo, toggleTodo, deleteTodo).
2. Context/provider runs repository SQL (write to SQLite).
3. SQLite returns the result (new ID, affected rows, or error).
4. Context/provider dispatches a specific reducer action (addSuccess, toggleSuccess, removeSuccess).
5. Reducer updates in-memory state with immutable array operations.
6. UI re-renders from updated in-memory state.

If any step is skipped, you get drift (UI shows stale values, reducer and DB disagree, or errors are hidden).

The Split

We can then split responsibilities:

1. Repository layer that SQL functions (getTodos, insertTodo, toggleTodo).
2. State layer that reducer + context to coordinate loading/error/data transitions.
3. UI layer that components that render state and dispatch actions.

Suggested target structure:

• Directorysrc/

  • Directorystate/

    • todos-repo.ts (SQL functions)
    • todos-reducer.ts (dispatch todo state actions)
    • todos-context.tsx (package reducer to be passed around)
    • useTodos.tsx (custom hook used by app/ screens)
  • Directoryapp/ (layout and views)

    • …

Note

If you’re confused, that’s because… This can all quite easily be very confusing! The intent is that working through the exercises will demystify.

Exercises

In these exercises, you’ll build and verify a SQLite local todo flow. Then, you’ll split that same flow into repository + reducer + context patterns.

1. Click the external link icon (top-left of each Snack) to open in a new tab.
2. Click the blue “Save” button to save to your account.
3. Copy the Snack URL once completed and submit on Moodle.

3.3.3 Build Local SQLite Todos

3.3.4 Split Into Repo + Reducer + Context

Start from the same behavior as 3.3.3, but reorganize responsibilities:

1. Keep SQL calls in the repository functions.
2. Keep state transitions in the reducer.
3. Keep async orchestration in the context/provider.

Hint

3.3.3 Build Local SQLite Todos

Start by setting up the SQLiteProvider wrapper with databaseName and an onInit callback that creates your table structure.

In your initDb function, use CREATE TABLE IF NOT EXISTS to define the todos table with columns for id, todo text, and completed status. Check if the table is empty using a COUNT query, and if so, seed it with a sample todo.

In your main component, access the database using useSQLiteContext() hook. Then implement SQL operations in this order:

1. Query rows with getAllAsync to load todos from the database.
2. Display rows in a FlatList component.
3. Add UPDATE queries for toggling completion status using WHERE id = ? with parameters.

3.3.4 Split Into Repo + Reducer + Context

For 3.3.2, focus on separating responsibilities across three layers:

1. Repository: Pure SQL functions that take db as a parameter and return data or IDs
2. Context: Gets db from useSQLiteContext(), orchestrates async operations, calls repository functions, then dispatches actions
3. Reducer: Pure state transitions using immutable array operations - no side effects or async code

Implementation strategy:

• In refresh: dispatch loading start, call repository to get all todos, dispatch success with the data
• In add: call repository to insert and get the new ID, construct the new todo object, dispatch success with that object
• In toggle: call repository to update the row, dispatch success with just the ID (reducer updates state without refetch)
• In remove: call repository to delete the row, dispatch success with the ID (reducer filters it out)
• In reducer: handle addSuccess by prepending to array, handle toggleSuccess by mapping over array to toggle the matching todo, handle removeSuccess by filtering out the deleted todo

Quick rule for what belongs where:

• SQL statement? → Repository
• Calling repository and dispatch(...)? → Context/provider
• Immutable array update from action? → Reducer

Solution

3.3.3 Build Local SQLite Todos

3.3.4 Split Into Repo + Reducer + Context

Summary

• SQLite is the next step after fetch + parse when you need persistent local data.
• Expo SQLite supports async SQL APIs for table creation, inserts, updates, and queries.
• Separation of concerns keeps SQL logic, state transitions, and UI rendering easier to maintain.
• Local structured storage prepares you for larger offline-friendly app features.

References

Footnotes

1. https://docs.expo.dev/versions/latest/sdk/sqlite/ ↩