What happens to Expo SQLite pagination when rows are added or deleted between LIMIT/OFFSET pages?

What Expo‑SQLite Does (and Doesn’t) With LIMIT/OFFSET

Expo‑SQLite forwards the SQL string you give it straight to the underlying SQLite engine. The engine runs each query against the current, live state of the table – there is no built‑in snapshot isolation or read‑committed mode that would freeze the data for the duration of a multi‑page walk.

Immediate Consequences of Live State

• Insert before the current OFFSET – The new row appears in the result set of the next page that would have started at that OFFSET. The page that originally contained that row now starts one row later, so the inserted row is effectively skipped unless you re‑run the earlier page.
• Delete before the current OFFSET – Rows that followed the deleted one are pulled up. The page that originally started at that OFFSET now contains the next row in the sequence, but the page that follows may miss a row or duplicate the last one from the previous page.
• Because each page fetch is a separate SQL statement, the table can change between calls, and the offsets you calculate in your app code no longer match the physical layout of the data.

Transactions Do Not Solve the Problem

Wrapping a single executeSql call in db.transaction only guarantees that the write you perform inside that transaction is atomic. It does not create a snapshot for subsequent reads. If you perform a paginated read in one transaction and then delete/insert in another, the offsets will still shift.

Keyset (Cursor‑Based) Pagination Is the Robust Alternative

Instead of relying on OFFSET, use a stable, monotonically increasing column (usually an auto‑increment id or the SQLite rowid) to keep track of where you are:

SELECT * FROM myTable
WHERE id > :lastId
ORDER BY id ASC
LIMIT :pageSize;

Because the WHERE id > :lastId clause is evaluated against the current table state, rows added or removed before lastId never affect the next page. Rows inserted after lastId will appear on the next page, and deletions after lastId simply shorten the result set – no gaps or duplicates occur.

When You Don’t Have a Stable Ordering Column

• Add a surrogate key or a timestamp that never changes after insertion.
• Alternatively, serialize all writes while paginating (e.g., an application‑level lock). This is safe but reduces concurrency and can hurt performance.

Practical Verification Steps

1. Create a test table with an auto‑increment id and insert 100 rows.
2. In a background async task, start deleting or inserting rows while the main thread fetches pages using LIMIT/OFFSET.
3. Log the rows returned per page; you should see duplicates or gaps when the table changes.
4. Repeat the test with the keyset query above; the same set of rows should be returned regardless of concurrent modifications.
5. Optionally, wrap each fetch in db.transaction and repeat – you will notice no difference in offset behavior.

Bottom Line

Expo‑SQLite does not provide snapshot isolation for LIMIT/OFFSET pagination. If your app can tolerate gaps or duplicates, you can use it as a quick solution, but for a reliable user experience, switch to keyset pagination or enforce a write lock while paginating.

Missing diagnostic detail: Does your table already have a stable, increasing column you can use for keyset pagination? If not, adding one is the simplest fix.