In expo-sqlite, how do you open a database and choose between execAsync, runAsync, getFirstAsync and getAllAsync for a given query?

juniormust knowfreq 60%

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 s

openDatabaseAsync('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.

`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 lines

typescript

import * 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

for a junior

Recall openDatabaseAsync and the four query methods, and which one returns rows, which returns change metadata and which returns nothing.

for a middle

Explain why execAsync is only for fixed scripts, what lastInsertRowId and changes mean, and when getEachAsync beats getAllAsync.

for a senior

Spot unbounded getAllAsync calls and unvalidated generic row types in review, and explain the prepare-execute-finalize cycle each helper performs.

for a principal

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:

Rows are plain objects keyed by column name, so aliases (AS total) decide the property names.
The generic T is a promise, not a check. TypeScript trusts you; nothing validates the row at runtime.
getFirstAsync returns null when no row matches, so handle it before reading a property.
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.

## 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

In expo-sqlite, how do you open a database and choose between execAsync, runAsync, getFirstAsync and getAllAsync for a given query?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Opening a database

The four everyday methods

Applying it to an expense ledger

What comes back from SQLite

Common mistakes

How the helpers work underneath

▾common follow-ups2 questions

▾red flags5 flags

Opening a database

The four everyday methods

Applying it to an expense ledger

What comes back from SQLite

Common mistakes

How the helpers work underneath

answer

go deeper