After a bulk import, an expo-sqlite live-query screen stutters and refetches thousands of times; how do change events behave, and how do you tame them?
answer
- per row, not per statement
- fired before commit
- one listener hears all flagged databases
- a hint to re-query, not data
- coalesce into one reload
basics
~20 sexpo-sqlite's change listener fires once per changed row, as the row changes and before commit, for every database opened with enableChangeListener. A 5,000-row import means 5,000 events; coalesce them into one reload, ideally after the import finishes.
solid answer
~40 sWith `enableChangeListener: true`, expo-sqlite installs SQLite's update hook, which reports each inserted, updated or deleted row individually, at the moment it changes. So a transaction that inserts 5,000 sightings sends 5,000 `onDatabaseChange` events to JavaScript, and a screen that re-runs `getAllAsync` per event queues thousands of queries and re-renders on the JS thread. The events also arrive before `COMMIT`, so rows later rolled back still produce them; and `addDatabaseChangeListener` is module-wide, hearing every flagged database. Treat an event as a staleness hint: filter on `tableName`, coalesce with a short timer so at most one reload is pending, and for known bulk jobs reload once when the job resolves. Keep the reloaded query bounded, a page rather than the whole table.
code
tsx · 38 linesimport { addDatabaseChangeListener, useSQLiteContext } from 'expo-sqlite';
import { useEffect, useState } from 'react';
type Sighting = { id: number; species: string; seen_at: number };
export function useRecentSightings(): Sighting[] {
const db = useSQLiteContext();
const [rows, setRows] = useState<Sighting[]>([]);
useEffect(() => {
let cancelled = false;
let timer: ReturnType<typeof setTimeout> | null = null;
const load = async () => {
const next = await db.getAllAsync<Sighting>(
'SELECT id, species, seen_at FROM sightings ORDER BY seen_at DESC LIMIT 100'
);
if (!cancelled) setRows(next);
};
load();
const subscription = addDatabaseChangeListener(({ tableName }) => {
if (tableName !== 'sightings' || timer !== null) return;
timer = setTimeout(() => {
timer = null;
load();
}, 150);
});
return () => {
cancelled = true;
if (timer !== null) clearTimeout(timer);
subscription.remove();
};
}, [db]);
return rows;
}go deeper
Recall that expo-sqlite change events are per row, so a big import fires one event for every row it touches.
Explain that events arrive before commit and from every flagged database, so listeners filter by table and re-query rather than trust the event.
Diagnose JS-thread stalls after bulk writes and fix them with coalesced reloads, a pause-and-reload signal for known jobs, and bounded queries.
Decide where change propagation lives, per-screen listeners versus one data layer that batches invalidations, before many features add their own.
## The symptom A bird-sighting app adds "import from CSV". Importing 5,000 sightings in one `withExclusiveTransactionAsync` takes a couple of seconds, but for much longer the log screen stutters, gestures lag and the list flickers. The log screen uses the usual live-query pattern: `addDatabaseChangeListener`, and on each event with `tableName === 'sightings'`, run `getAllAsync` again and set state. The import is not the slow part; the reaction to it is. ## How the events are produced When a database is opened with `enableChangeListener: true`, expo-sqlite's native module registers SQLite's **update hook** on that connection. The hook has three properties that explain the symptom: - **Per row.** It fires once for every row inserted, updated or deleted, not once per statement or transaction. Five thousand inserts produce five thousand events. - **Immediate.** The native code forwards each event to JavaScript as soon as the row changes, **before** the transaction commits. If the transaction later rolls back, the events for its rows have already been sent. - **Per connection, with the flag.** Only connections opened with the flag report changes. The connection that `withExclusiveTransactionAsync` creates copies the database's open options, so it reports too; a separately opened connection without the flag does not. On the JavaScript side, `addDatabaseChangeListener` subscribes to the module's `onDatabaseChange` event, so **one listener hears every flagged database** in the app. Each event carries only `databaseName`, `databaseFilePath`, `tableName` and `rowId`. ## Why per-event reloads melt the JS thread With one reload per event: 1. Thousands of `getAllAsync` calls are queued, each reading and serialising up to the full page of rows. 2. Each result calls `setRows`, triggering a re-render of the list. 3. All of that runs on the JavaScript thread, which also handles touches and navigation, so the app feels frozen long after the import itself finished. ## Taming it | Technique | What it does | When to use | |---|---|---| | Filter | ignore events whose `tableName` (and file) the screen does not show | always | | Coalesce | at most one reload pending; later events during the wait are dropped | always, for any live query | | Pause and reload | the importer signals start and end; the screen reloads once at the end | known bulk jobs | | Bound the query | reload a page (`LIMIT`) or a count, not the whole table | large tables | The pause-and-reload technique is simple to build when the import is your own code: the importer sets a shared "bulk write in progress" flag before starting, the screen's listener ignores `sightings` events while the flag is set, and the importer triggers one reload after its transaction resolves, whether it committed or rolled back. That gives exactly one reload per import and none of the intermediate states. A coalescing timer of 100 to 200 milliseconds turns thousands of events into a handful of reloads during the import and one after it. Because events arrive before commit, a reload can also run while the import's transaction is still open. With the exclusive variant, the screen's connection reads the last committed data, and the final reload after the job resolves shows the committed result. With the plain `withTransactionAsync` on the shared connection, the reload's query joins the open transaction and sees uncommitted rows, one more reason bulk imports belong on the exclusive variant. ## Treat events as hints, not facts - An event does not prove the row exists now: it may have been rolled back or deleted since. - An event is not the data: re-query to learn the current state. - The absence of events is not proof of no change: writes through an unflagged connection, and tables SQLite's hook does not report (such as `WITHOUT ROWID` tables), stay silent. ORM live-query hooks built on this listener inherit the same per-row behaviour, so the same coalescing concern applies to them during bulk writes. ## Measuring before and after - Count events: a temporary listener that increments a counter during one import shows the real volume. - Count reloads: log each `load()` call; after coalescing it should be a handful per import. - Watch the JS frame rate in the performance monitor while the import runs; the target is that it stays near the display rate once reloads are coalesced.
- Why can a change event refer to a row that no longer exists?The update hook fires as each row changes, before the transaction commits. If that transaction rolls back, or a later statement deletes the row, the event has already been delivered. That is why the listener should trigger a fresh query rather than trust the event.
- Does a write made on txn inside withExclusiveTransactionAsync produce change events?Yes, if the database was opened with `enableChangeListener`. The exclusive transaction's connection is created with a copy of the database's open options, so the native side installs the update hook on it as well.
saying these in an interview costs you the question
- A multi-row INSERT produces a single change event per statement
- Change events are delivered only after the transaction commits
- A rolled-back insert never produces a change event
- Each listener hears only the database it was registered for
- Reloading the full table per event is fine because SQLite is fast