skip to content

In sqflite, how do the insert and query helpers relate to rawInsert and rawQuery, and why must values go through arguments rather than string interpolation?

level: juniorimportance: should knowfreq 45%

answer

  1. helpers build SQL from maps
  2. ? placeholders plus whereArgs
  3. IN needs one ? per value
  4. null needs IS NULL
  5. num, String, Uint8List only

basics

~20 s

insert, query, update and delete build the SQL from a table name and maps; rawInsert and rawQuery take your SQL. Either way values go in as ? placeholders with whereArgs or arguments, which binds them safely and avoids quoting bugs and SQL injection.

solid answer

~40 s

The helpers — `insert(table, values)`, `query(table, where:, whereArgs:, orderBy:, limit:)`, `update`, `delete` — generate SQL for you; the `raw*` methods take a complete SQL string plus an `arguments` list. In both, values should be `?` placeholders bound from the list, never interpolated: interpolation breaks on a note like `Joe's` and opens SQL injection. The gotchas are sqflite-specific: `IN (?)` with a list does not work, so you generate one `?` per value; `= ?` with `null` matches nothing, so you write `IS NULL`; only `num`, `String` and `Uint8List` are supported, so `bool` becomes 0/1 and `DateTime` becomes millis or ISO text; and Android binds arguments as strings, which can surprise you in `SELECT ?`. `insert` returns the new row id; `update` and `delete` return counts.

code

dart · 27 lines
dart
import 'package:sqflite/sqflite.dart';

Future<int> addExpense(Database db, int cents, String note, DateTime at) {
  return db.insert('expenses', {
    'amount_cents': cents,
    'note': note, // "Joe's cafe" is safe: bound, not interpolated
    'spent_at': at.millisecondsSinceEpoch, // DateTime is not supported
  });
}

Future<List<Map<String, Object?>>> expensesIn(
  Database db,
  List<String> categories,
  DateTime since,
) async {
  if (categories.isEmpty) return const [];
  final marks = List.filled(categories.length, '?').join(', ');
  return db.query(
    'expenses',
    where: 'category IN ($marks) AND spent_at >= ?',
    whereArgs: [...categories, since.millisecondsSinceEpoch],
    orderBy: 'spent_at DESC',
  );
}

Future<List<Map<String, Object?>>> uncategorised(Database db) =>
    db.query('expenses', where: 'category IS NULL');

go deeper

for a junior

Recall that values go into whereArgs or arguments with ? placeholders, and that insert returns the new row id.

for a middle

Explain the helper versus raw split and the gotchas: IN lists, IS NULL, supported types, read-only results and Android string binding.

for a senior

Choose conflict algorithms deliberately, keep identifiers out of user input, and design type conversions once in the model layer.

for a principal

Decide when hand-written SQL through sqflite stays maintainable and when a typed query layer is worth adopting.

## Two ways to run SQL `sqflite` exposes two layers on a `Database` (and on a `Transaction`): | Helper | Raw equivalent | Returns | |---|---|---| | `insert(table, values, conflictAlgorithm:)` | `rawInsert(sql, arguments)` | the new row id | | `query(table, columns:, where:, whereArgs:, orderBy:, limit:, offset:, ...)` | `rawQuery(sql, arguments)` | `List<Map<String, Object?>>` | | `update(table, values, where:, whereArgs:)` | `rawUpdate(sql, arguments)` | rows changed | | `delete(table, where:, whereArgs:)` | `rawDelete(sql, arguments)` | rows deleted | The helpers build the statement from a table name and a `Map<String, Object?>` whose keys are column names. The raw methods are for joins, aggregates or anything the helpers cannot express. `execute` runs a statement with no result, such as `CREATE TABLE`. sqflite does not parse SQL, and each call runs a single statement. Rows come back as **read-only** maps in a read-only list; copy with `Map.from` or `List.from` before modifying. ## Why placeholders, not interpolation With `'... WHERE note = \'$note\''`, a note such as `Joe's` breaks the statement, and a crafted value can change what the SQL does. With `where: 'note = ?', whereArgs: [note]`, the value is **bound** by SQLite and never parsed as SQL. The package's documentation is explicit: do not try to sanitise values yourself; use the binding syntax. Placeholders only stand for **values**; table and column names cannot be bound, so those must come from your own constants. ## sqflite-specific gotchas 1. **Count must match.** The number of `?` must equal the number of arguments. 2. **No list binding.** `IN (?)` with a `List` does not work, because lists are not a supported argument type (except blobs). Generate the placeholders: `List.filled(values.length, '?').join(', ')`. 3. **`NULL` needs `IS NULL`.** `where: 'category = ?', whereArgs: [null]` matches nothing; write `category IS NULL`. 4. **Supported types.** Values must be `num`, `String` or `Uint8List`. `bool` is not a SQLite type — store `0`/`1` in an `INTEGER`. `DateTime` is not supported — store `millisecondsSinceEpoch` or an ISO-8601 string. Nested maps must be flattened or JSON-encoded into `TEXT`. 5. **Android binds as strings.** Arguments are bound as `String` on Android. In `WHERE` clauses that usually works because SQLite converts, but `SELECT ?1 AS item` returns `'3'` rather than `3` there. 6. **`?NNN` positions.** You can reuse an argument with numbered placeholders like `?1`. ## Conflicts on insert and update `insert` and `update` take an optional `conflictAlgorithm`. Without it, SQLite's default **abort** applies: a UNIQUE violation throws a `DatabaseException`, which you can test with `isUniqueConstraintError()`. `ConflictAlgorithm.ignore` skips the conflicting row silently; `ConflictAlgorithm.replace` deletes the existing conflicting row and inserts the new one — a quick upsert, but the old row's other columns are lost and delete triggers do not fire. `rollback` and `fail` mirror SQLite's other conflict clauses. ## In the expense ledger Filtering expenses by several categories since a date becomes `query('expenses', where: 'category IN (?, ?, ?) AND spent_at >= ?', whereArgs: [...cats, since.millisecondsSinceEpoch])`, with the placeholder count generated from the list. Uncategorised rows from before version 2 are found with `category IS NULL`, not with a `null` argument. ## Reading results back The other half of the contract is what comes out: - `query` and `rawQuery` return `List<Map<String, Object?>>`; both the list and each map are **read-only**, so copy before mutating. - Column values come back as `int`, `double`, `String`, `Uint8List` or `null`; convert `0`/`1` back to `bool` and millisecond integers back to `DateTime` in one place, typically a `fromMap` factory on the model. - For a single number such as `SELECT COUNT(*)`, `Sqflite.firstIntValue(rows)` reads the first column of the first row. - `update` and `delete` return the number of affected rows, which is the cheap way to tell whether a `where` clause matched anything.

  • Why does whereArgs: [categories] with 'category IN (?)' not work?
    A `?` binds a single value, and lists are not a supported argument type in sqflite except as blob content. Generate one placeholder per element with `List.filled(n, '?').join(', ')` and spread the list into `whereArgs`.
  • How do you store a bool and a DateTime in sqflite?
    Neither is a supported type. Store the bool as an `INTEGER` 0 or 1 and convert when reading. Store the `DateTime` as `millisecondsSinceEpoch` in an `INTEGER`, or as an ISO-8601 string in `TEXT`, and parse it back in your model.

saying these in an interview costs you the question

  • Escaping quotes by hand is as safe as using whereArgs.
  • whereArgs: [list] expands automatically for IN (?).
  • where: 'col = ?' with a null argument finds rows where col is NULL.
  • sqflite stores Dart bool and DateTime values natively.
  • Table names can be passed as ? placeholders too.