skip to content

What does expo-sqlite's db.sql tagged template do with interpolated values, how does it decide what to return, and what can it not interpolate?

level: middleimportance: nice to knowfreq 20%

answer

  1. added in expo-sqlite 55
  2. every ${} becomes a bound ?
  3. rows for SELECT or RETURNING
  4. run result for plain writes
  5. no table or column names

basics

~20 s

db.sql turns every ${} in the template into a bound ? parameter, so values are injection-safe. Awaiting it returns row objects for SELECT, PRAGMA, WITH, EXPLAIN or RETURNING queries and a lastInsertRowId/changes result for other writes. Identifiers cannot be interpolated.

solid answer

~40 s

`db.sql` is a tagged template added in expo-sqlite 55. The template's literal parts are joined with `?` and each `${value}` is passed as a bound parameter, so ``sql`SELECT * FROM expenses WHERE category = ${category}` `` is as safe as `getAllAsync` with `?`. The query is lazy: it runs when awaited. A small parser strips quoted strings and looks for keywords: `RETURNING`, or a `SELECT`/`PRAGMA`/`WITH`/`EXPLAIN` without a mutation keyword, means rows via `getAllAsync`; `INSERT`/`UPDATE`/`DELETE`/`CREATE`/`ALTER`/`DROP` mean a `SQLiteRunResult` via `runAsync`. Helpers `.first()`, `.each()` and `.values()`, plus `allSync()`/`firstSync()`, change the shape. Because every interpolation is a value, a column name, table name or `ASC`/`DESC` cannot be interpolated; those need a whitelist and ordinary string building.

go deeper

for a junior

Recall that db.sql looks like interpolation but binds every value as a parameter, and that awaiting it runs the query.

for a middle

Explain how the tag chooses between rows and a run result, the RETURNING rule, and the .first(), .each() and .values() helpers.

for a senior

Catch misuse in review: interpolated identifiers, un-awaited queries, detection edge cases like INSERT ... SELECT, and sync helpers on hot paths.

for a principal

Decide whether the tag, plain methods or a query builder is the team convention, weighing readability against explicit return shapes.

## What the tag is A **tagged template** is a JavaScript function called with a template literal: the function receives the literal string parts and the interpolated values separately. expo-sqlite exposes one as the `sql` property of every `SQLiteDatabase`, modelled on Bun's SQLite API and added in **expo-sqlite 55.0.0**: ```typescript const sql = db.sql; const category = "groceries"; const rows = await sql<{ id: number; amount_cents: number }>` SELECT id, amount_cents FROM expenses WHERE category = ${category} `; ``` ## How values are handled Internally the tag joins the literal parts with `?` and keeps the values as parameters. The example above becomes: - SQL: `SELECT id, amount_cents FROM expenses WHERE category = ?` - params: `["groceries"]` So the syntax looks like string interpolation but behaves like **parameter binding**. A value containing quotes cannot change the query. The same bindable types apply: strings, numbers, `null`, booleans (as 1/0) and binary arrays. ## How it decides what to return Awaiting the object calls its `then`, so **the query runs only when awaited** (or when a helper is called). To pick between rows and write metadata, a keyword check runs on the SQL with quoted strings stripped: | Query contains | Treated as | Await returns | |---|---|---| | `RETURNING` | returns rows | `T[]` via `getAllAsync` | | `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `ALTER`, `DROP` (no `RETURNING`) | write | `SQLiteRunResult` via `runAsync` | | `SELECT`, `PRAGMA`, `WITH`, `EXPLAIN` (no mutation keyword) | returns rows | `T[]` | | anything else | write | `SQLiteRunResult` | Two consequences worth knowing: 1. `INSERT INTO archive SELECT * FROM expenses` is a **write**, because the mutation keyword wins over `SELECT`. 2. When the generic `T` is omitted, TypeScript types the result as `unknown[] | SQLiteRunResult`, which is why the documentation casts the awaited result of a write to `SQLiteRunResult`. ## Helpers on the query object - `.first()` returns the first row or `null`. - `.each()` returns an async iterator over rows. - `.values()` returns rows as arrays of column values rather than objects. - `allSync()`, `firstSync()`, `eachSync()`, `valuesSync()` do the same **synchronously on the JavaScript thread**, with the usual warning about heavy work. ```typescript const total = await sql<{ total: number | null }>` SELECT SUM(amount_cents) AS total FROM expenses WHERE substr(spent_on, 1, 7) = ${month} `.first(); ``` ## What cannot be interpolated Because every `${}` becomes a bound value, anything that is part of the statement's **structure** cannot go through it: - table and column names; - `ASC` / `DESC`, `LIMIT` clauses written as text, SQL keywords; - fragments of SQL, such as an optional `AND category = ?` condition. Writing ``sql`SELECT * FROM expenses ORDER BY ${column}` `` binds `column` as a string constant, so the sort does nothing useful. For dynamic structure, build the SQL string from a **whitelist** of known fragments and pass values through `getAllAsync` parameters, or use a query builder. ## Three ways to write the same query | Style | Example | Return shape | Best for | |---|---|---|---| | plain method | `db.getAllAsync(sql, month)` | explicit from the method name | any query; dynamic SQL built from whitelists | | tagged template | ``await db.sql`... ${month}` `` | inferred from keywords | fixed-shape queries with inline values | | prepared statement | `stmt.executeAsync({ $month })` | cursor plus metadata | the same SQL run many times | All three bind values the same way, so the choice is about readability and control, not safety. Teams often mix them: the tag for simple screens, plain methods where the SQL is assembled, statements for imports. ## When to use it - **Good fit:** fixed-shape queries with a few values, where the inline style reads better than a list of `?` and trailing arguments. - **Less good:** very dynamic queries, or code that must support the tag's detection edge cases explicitly; the plain methods make the return shape obvious. - **Either way**, it is a thin layer over `getAllAsync`, `getFirstAsync`, `getEachAsync` and `runAsync`, which prepare, execute and finalize a statement per call.

  • You assign a db.sql DELETE query to a variable but never await it, and nothing is deleted. Why?
    The tagged query is lazy: it is a thenable that runs when awaited or when a helper such as `allSync()` is called. Without `await q` (or a helper call) the DELETE never executes.
  • Why does db.sql treat an INSERT INTO archive SELECT * FROM expenses query as a write?
    The detector checks for mutation keywords before query keywords. `INSERT` is found, and there is no `RETURNING`, so the tag runs it through `runAsync` and returns `lastInsertRowId` and `changes` rather than rows.

saying these in an interview costs you the question

  • Believing db.sql concatenates values into the SQL string
  • Interpolating a column name or sort direction with ${}
  • Expecting the query to run without await or a helper call
  • Assuming INSERT ... SELECT returns rows through the tag
  • Calling allSync on large queries from UI code