When should you use expo-sqlite's prepareAsync statements instead of runAsync, and what must you do with a statement when you are finished?
answer
- helpers prepare and finalize every call
- compile once, execute many
- executeAsync returns a cursor result
- resetAsync before reading again
- finalizeAsync in a finally block
basics
~20 sUse prepareAsync when the same SQL runs many times, such as importing a month of expenses: it compiles once and executeAsync rebinds new values each time. Always call finalizeAsync in a finally block, because a statement holds native resources until finalized.
solid answer
~40 s`runAsync`, `getFirstAsync` and `getAllAsync` are wrappers: each call prepares the SQL, executes it and finalizes it. For a statement I run once, that is ideal. When I run the same statement hundreds of times, such as inserting every row of an imported bank export, I call `db.prepareAsync(sql)` once and then `statement.executeAsync(params)` per row, which skips recompiling. `executeAsync` returns a result with `lastInsertRowId` and `changes`, plus `getFirstAsync()`, `getAllAsync()` and async iteration for reads; reading the rows a second time needs `resetAsync()` first, or it throws. When done I call `statement.finalizeAsync()` inside `finally`. expo-sqlite finalizes orphaned statements when the database closes, but the docs call manual finalizing best practice to avoid leaks, and using a finalized statement is an error.
go deeper
Recall that prepareAsync returns a statement you run with executeAsync and must release with finalizeAsync.
Explain that the shorthands prepare and finalize per call, when reusing one statement pays off, and the resetAsync rule for reading a result again.
Structure statement ownership: finalize in finally, keep long-lived statements with a long-lived owner, and avoid per-row shorthand calls in bulk paths.
Decide whether a data layer manages prepared statements itself or leans on a query builder or ORM, weighing performance control against complexity.
## What a prepared statement is Before SQLite can run SQL it **compiles** it into an internal program. A **prepared statement** is that compiled program, kept around so it can be executed repeatedly with different bound values. expo-sqlite exposes it as `SQLiteStatement`: ```typescript const insert = await db.prepareAsync( "INSERT INTO expenses (amount_cents, category, spent_on) VALUES ($amount, $category, $day)" ); ``` ## What the convenience methods already do Every shorthand on `SQLiteDatabase` is a wrapper around the statement API: | Shorthand | Equivalent | |---|---| | `runAsync(sql, ...p)` | `prepareAsync` + `executeAsync` + `finalizeAsync` | | `getFirstAsync(sql, ...p)` | the same, reading `getFirstAsync()` from the result | | `getAllAsync(sql, ...p)` | the same, reading `getAllAsync()` from the result | | `getEachAsync(sql, ...p)` | the same, iterating the result | So a shorthand called in a loop compiles and discards the same statement on every iteration. For one-off queries that cost is irrelevant; for a bulk import it adds up. ## Using a statement for many executions ```typescript const insert = await db.prepareAsync( "INSERT INTO expenses (amount_cents, category, spent_on) VALUES ($amount, $category, $day)" ); try { for (const row of imported) { await insert.executeAsync({ $amount: row.cents, $category: row.category, $day: row.day }); } } finally { await insert.finalizeAsync(); } ``` Key points: 1. **Binding still applies.** `executeAsync` accepts the same variadic, array or `$name`-object parameters as the shorthands, so a statement is just as injection-safe. 2. **Each execution rebinds.** Values from the previous call do not leak into the next. 3. **Finalize exactly once, at the end**, in a `finally` so an exception mid-loop does not leak the statement. ## Reading results from a statement `executeAsync` returns a `SQLiteExecuteAsyncResult`, which is both metadata and a cursor: - `lastInsertRowId` and `changes`, as with `runAsync`; - `getFirstAsync()` and `getAllAsync()` to read rows; - it is an **async iterable**, so `for await (const row of result)` streams rows; - `resetAsync()` rewinds the cursor. The cursor rule matters: `getFirstAsync()` and `getAllAsync()` require the cursor in its initial state. After reading rows once, call `resetAsync()` before reading again, otherwise **an error is thrown**. A prepared `SELECT` is useful for a screen that re-runs the same month query as the user swipes between months: ```typescript const byMonth = await db.prepareAsync( "SELECT id, category, amount_cents FROM expenses WHERE substr(spent_on, 1, 7) = $month" ); // later, for each month the user views: const result = await byMonth.executeAsync<{ id: number; category: string; amount_cents: number }>({ $month: "2026-09" }); const rows = await result.getAllAsync(); ``` ## Why finalizing matters `finalizeAsync()` calls SQLite's `sqlite3_finalize()` and releases the compiled program and its memory. Consequences: - **Unfinalized statements are leaks** until the database closes. expo-sqlite does finalize orphaned statements on close, but an app that keeps its database open for its whole life effectively never closes it. - **A finalized statement cannot be used**; any later call errors. - **Long-lived statements are fine** if their owner is long-lived too, for example a repository object that prepares on open and finalizes on close. ## Raw results and column names Two lower-level tools are available on a statement when object rows are more than you need: - **`executeForRawResultAsync(params)`** executes like `executeAsync` but its result yields each row as an **array of values in column order** instead of an object. For a large export that skips building one object per row. - **`getColumnNamesAsync()`** (and `getColumnNamesSync()`) returns the statement's column names, which pairs naturally with raw rows, for example when writing a CSV header. ```typescript const stmt = await db.prepareAsync("SELECT spent_on, category, amount_cents FROM expenses ORDER BY spent_on"); try { writeCsvLine(await stmt.getColumnNamesAsync()); const result = await stmt.executeForRawResultAsync(); for await (const values of result) { writeCsvLine(values); } } finally { await stmt.finalizeAsync(); } ``` ## Choosing in practice - One statement, run occasionally: a shorthand (`runAsync`, `getAllAsync`). - Same statement, many times in a row: `prepareAsync` plus a loop, finalized in `finally`. - Same statement, reused across a screen's lifetime: prepare when the screen's data layer starts, finalize when it is torn down. - Synchronous twins exist (`prepareSync`, `executeSync`, `finalizeSync`) with the same rules, but they run on the JavaScript thread.
- You call getAllAsync() twice on the same executeAsync result and the second call throws. Why?The result is a cursor. After the first read it is no longer in its initial state, and expo-sqlite requires `resetAsync()` before `getFirstAsync()` or `getAllAsync()` can read again. Either reset it or keep the array from the first read.
- Is it a problem to forget finalizeAsync if the database is closed at the end?expo-sqlite finalizes orphaned statements when the database is closed, so nothing is lost for good. But many apps keep one connection open for their whole lifetime, so in practice the statement lives until the process dies. Finalizing in `finally` keeps memory bounded and makes ownership explicit.
saying these in an interview costs you the question
- Calling runAsync in a tight loop for thousands of identical inserts
- Forgetting to finalize a prepared statement after an exception
- Using a statement after calling finalizeAsync on it
- Reading a result cursor twice without resetAsync
- Believing prepared statements skip parameter binding