In expo-sqlite, how do you open a database and choose between execAsync, runAsync, getFirstAsync and getAllAsync for a given query?
answer
- openDatabaseAsync returns SQLiteDatabase
- exec: many statements, no params, no rows
- run: one write, lastInsertRowId and changes
- getFirst: one row or null
- getAll: array of row objects
basics
~20 sopenDatabaseAsync('ledger.db') returns a SQLiteDatabase. Use execAsync for unparameterised multi-statement scripts such as schema setup, runAsync for writes with bound values, getFirstAsync for one row (or null), and getAllAsync for every matching row as an array of objects.
solid answer
~40 s`const db = await SQLite.openDatabaseAsync('ledger.db')` opens or creates the file and gives a `SQLiteDatabase`. Then I pick the method by what I need back. `execAsync(sql)` runs one or many statements with **no parameters and no results**, so it is for fixed scripts: `CREATE TABLE`, `PRAGMA`. `runAsync(sql, ...params)` runs one statement with bound values and returns `{ lastInsertRowId, changes }`, which is what I want for `INSERT`, `UPDATE` and `DELETE`. `getFirstAsync<T>(sql, ...params)` returns the first row as an object keyed by column name, or `null` if nothing matched. `getAllAsync<T>(...)` returns every row as an array. For large result sets there is `getEachAsync`, an async iterator that fetches rows one at a time.
code
typescript · 21 linesimport * as SQLite from "expo-sqlite";
type Expense = { id: number; category: string; amount_cents: number; spent_on: string };
export async function monthExpenses(db: SQLite.SQLiteDatabase, month: string): Promise<Expense[]> {
// month is "YYYY-MM"; spent_on is stored as "YYYY-MM-DD"
return db.getAllAsync<Expense>(
"SELECT id, category, amount_cents, spent_on FROM expenses WHERE substr(spent_on, 1, 7) = ? ORDER BY spent_on",
month
);
}
export async function addExpense(db: SQLite.SQLiteDatabase, amountCents: number, category: string, spentOn: string): Promise<number> {
const result = await db.runAsync(
"INSERT INTO expenses (amount_cents, category, spent_on) VALUES (?, ?, ?)",
amountCents,
category,
spentOn
);
return result.lastInsertRowId;
}go deeper
Recall openDatabaseAsync and the four query methods, and which one returns rows, which returns change metadata and which returns nothing.
Explain why execAsync is only for fixed scripts, what lastInsertRowId and changes mean, and when getEachAsync beats getAllAsync.
Spot unbounded getAllAsync calls and unvalidated generic row types in review, and explain the prepare-execute-finalize cycle each helper performs.
Set conventions for a data-access layer over expo-sqlite: which methods are allowed where, how rows are validated, and how large reads are bounded.
## Opening a database **expo-sqlite** is Expo's SQLite module; it works in Expo Go and in development builds. The entry point is `openDatabaseAsync(databaseName, options?, directory?)`: ```typescript import * as SQLite from "expo-sqlite"; const db = await SQLite.openDatabaseAsync("ledger.db"); ``` - The file is created if it does not exist, in `defaultDatabaseDirectory` unless you pass a directory. - Opening the same name again reuses a **cached connection**; `useNewConnection: true` in the options forces a separate one. - The result is a `SQLiteDatabase`, the object every query method below lives on. The old callback API (`openDatabase`, `transaction`, `executeSql`) was removed in expo-sqlite 15; everything current is promise-based, with optional `*Sync` twins. ## The four everyday methods | Method | Statements | Parameters | Returns | Typical use | |---|---|---|---|---| | `execAsync(sql)` | one or many | none, not escaped | `void` | schema, `PRAGMA` | | `runAsync(sql, ...params)` | one | bound | `{ lastInsertRowId, changes }` | `INSERT`, `UPDATE`, `DELETE` | | `getFirstAsync<T>(sql, ...params)` | one | bound | `T \| null` | one record, an aggregate | | `getAllAsync<T>(sql, ...params)` | one | bound | `T[]` | a bounded list | A fifth, `getEachAsync<T>(sql, ...params)`, returns an **async iterator** instead of a Promise, stepping through the rows one at a time. The API cheatsheet recommends it for large result sets because it avoids building the whole array at once. ## Applying it to an expense ledger ```typescript await db.execAsync(` PRAGMA foreign_keys = ON; CREATE TABLE IF NOT EXISTS expenses ( id INTEGER PRIMARY KEY NOT NULL, amount_cents INTEGER NOT NULL, category TEXT NOT NULL, spent_on TEXT NOT NULL ); `); const { lastInsertRowId } = await db.runAsync( "INSERT INTO expenses (amount_cents, category, spent_on) VALUES (?, ?, ?)", 1250, "groceries", "2026-09-14" ); const total = await db.getFirstAsync<{ total: number | null }>( "SELECT SUM(amount_cents) AS total FROM expenses WHERE spent_on >= ? AND spent_on < ?", "2026-09-01", "2026-10-01" ); const rows = await db.getAllAsync<{ id: number; category: string; amount_cents: number }>( "SELECT id, category, amount_cents FROM expenses WHERE spent_on >= ? AND spent_on < ? ORDER BY spent_on", "2026-09-01", "2026-10-01" ); ``` Notes on the shapes: 1. **Rows are plain objects keyed by column name**, so aliases (`AS total`) decide the property names. 2. **The generic `T` is a promise, not a check.** TypeScript trusts you; nothing validates the row at runtime. 3. **`getFirstAsync` returns `null`** when no row matches, so handle it before reading a property. 4. **`lastInsertRowId` and `changes`** come from SQLite itself: the rowid of the last insert and the number of rows the statement changed. ## What comes back from SQLite Row values arrive as ordinary JavaScript values, converted from SQLite's storage classes: | SQLite value | JavaScript value | |---|---| | `INTEGER`, `REAL` | `number` | | `TEXT` | `string` | | `NULL` | `null` | | `BLOB` | `Uint8Array` | Two consequences for the ledger. A boolean you bound (for example `is_refund = true`) is stored as `1` and comes back as the number `1`, not `true`, so convert it when reading. And money is best kept as integer cents, as in the example, so that sums stay exact and the value is a plain `number` in JavaScript rather than a floating-point approximation of a decimal amount. ## Common mistakes - **Using `execAsync` for a query with user input.** It takes no parameters and the documentation warns that it does not escape anything, so interpolating values is an injection risk. - **Using `runAsync` for a `SELECT`.** It only returns change metadata, never the rows. - **Using `getAllAsync` on an unbounded table.** Every row crosses into JavaScript at once; add `LIMIT`, paginate, or iterate with `getEachAsync`. - **Expecting several statements to run through `runAsync`.** It prepares one statement; multi-statement scripts belong in `execAsync`. ## How the helpers work underneath `runAsync`, `getFirstAsync`, `getAllAsync` and `getEachAsync` are convenience wrappers. Each one prepares a statement, executes it with your parameters, reads what it needs and **finalizes** the statement, all in one call. That is why they are safe to call casually, and also why repeating the same statement thousands of times is better served by preparing it once yourself.
- What happens if you call openDatabaseAsync('ledger.db') in two different modules?By default both calls get the same cached native connection, because expo-sqlite reuses a connection per database name. Pass `useNewConnection: true` in the options only when you really need an independent connection.
- Your getFirstAsync call for a category total crashes with 'cannot read property of null'. Why?`getFirstAsync` resolves to `null` when the query returns no rows. An aggregate like `SUM` returns one row even for no matches (with a `null` value), but a plain `SELECT ... WHERE` may return none. Check for `null` before reading fields.
saying these in an interview costs you the question
- Passing user input into execAsync through string interpolation
- Expecting runAsync to return the rows of a SELECT
- Assuming getFirstAsync throws when no row matches
- Loading an unbounded table with getAllAsync
- Reaching for openDatabase and executeSql from the removed legacy API