Queries

Basic Usage#

import 'package:magic_local_storage/magic_local_storage.dart';

Future<void> main() async {
  final int id = await DB.table('users').insert({'name': 'Ada', 'email': '[email protected]'});

  final List<Map<String, dynamic>> adults = await DB.table('users')
      .where('age', '>=', 18)
      .orderBy('name')
      .limit(10)
      .get();

  print('$id, ${adults.length}');
}

Quick Reference#

Raw Queries#

Raw calls are synchronous: DatabaseManager().connection is a local SQLite handle, so nothing waits on a network. Bind values with ? placeholders and pass them as the second argument; never interpolate user input into the SQL string.

import 'package:magic_local_storage/magic_local_storage.dart';

void main() {
  final List<Map<String, dynamic>> adults = DB.select('SELECT * FROM users WHERE age > ?', [18]);

  final int id = DB.insert('INSERT INTO users (name, email) VALUES (?, ?)', ['Ada', '[email protected]']);
  final int changed = DB.update('UPDATE users SET name = ? WHERE id = ?', ['Grace', id]);
  final int removed = DB.delete('DELETE FROM users WHERE id = ?', [id]);

  DB.statement('CREATE INDEX idx_users_email ON users(email)');

  print('${adults.length} $changed $removed');
}

Query Builder#

DB.table(name) returns a QueryBuilder. Chain constraints, then end with a method that runs the query.

where accepts the operators =, ==, !=, <>, <, <=, >, >=, like, not like, glob, not glob, match, is and is not, in any case; any other operator, in included, throws an ArgumentError, and so does an orderBy direction other than asc or desc. A null value becomes IS NULL with =, == or is (and in the two-argument form) and IS NOT NULL with !=, <> or is not; with any other operator it throws an ArgumentError.

A plain name, letters, digits and underscores with optional dotted parts (order, users.id, cache.items), is quoted as an identifier wherever the builder takes a table or column: table, where, whereNull, whereNotNull, orderBy, value, pluck, insert and update. So a keyword column such as order or group works, and value('order') and pluck('order') read it back under its bare name. Anything else is written exactly as given, so an expression keeps working: orderBy('name COLLATE NOCASE'), orderBy('RANDOM()'), where('lower(email)', email), where('date(created_at)', '>=', day) and json_extract(data, '$.a'); a hand-quoted '"order"' stays as written. select([...]) entries are always raw SQL, so an expression such as COUNT(*) AS total works there and a keyword column has to be quoted by hand: select(['"order"']).

import 'package:magic_local_storage/magic_local_storage.dart';

Future<void> main() async {
  final Map<String, dynamic>? user = await DB.table('users').where('email', '[email protected]').first();
  final List<String> names = await DB.table('users').whereNotNull('email').orderBy('name', 'desc').pluck<String>('name');
  final bool taken = await DB.table('users').where('name', 'Ada').exists();

  await DB.table('users').where('id', 1).update({'name': 'Grace'});
  await DB.table('users').whereNull('email').delete();

  print('$user $names $taken');
}

Values are converted on the way in: bool becomes 1 or 0, DateTime and Carbon become ISO 8601 text, and a Map or List becomes JSON text. Reads return the raw SQLite values, so a boolean column comes back as 0 or 1; a Model cast turns them back, see Models.

Transactions#

DB.transaction runs the callback between BEGIN and COMMIT and rolls back when it throws, rethrowing your error.

import 'package:magic_local_storage/magic_local_storage.dart';

Future<void> main() async {
  await DB.transaction(() async {
    final int userId = await DB.table('users').insert({'name': 'Ada'});
    await DB.table('profiles').insert({'user_id': userId, 'bio': 'Mathematician'});
  });
}

Do not call DB.commit() or DB.rollback() inside the callback: the block owns the transaction, and anything written afterwards would run outside it. When the callback fails after closing it, the rollback is skipped so your error is not replaced by cannot rollback - no transaction is active; on success the call throws a StateError naming the mistake, because returning normally would claim the block was atomic.

To interleave other work between statements, use the manual API throughout instead of mixing the two:

import 'package:magic_local_storage/magic_local_storage.dart';

Future<void> main() async {
  DB.beginTransaction();

  try {
    await DB.table('accounts').where('id', 1).update({'balance': 90});
    await DB.table('transfers').insert({'account_id': 1, 'amount': 10});
    DB.commit();
  } catch (_) {
    DB.rollback();
    rethrow;
  }
}

BEGIN does not nest. A block that has to run inside someone else's transaction uses a SAVEPOINT through DB.statement, which is what Migrator and SyncLedger do.

Schema Awareness#

QueryBuilder.insert and update drop every key that is not a column of the table, using the cached PRAGMA table_info of DatabaseManager. A map that carries a computed or remote-only attribute therefore never fails the write; the price is that a typo in a column name is dropped without an error. When insert returns 0, none of your keys matched a column.

A table with no columns does not exist, so insert and update on it throw a StateError naming the table, usually because its migration has not run yet. The manager caches only tables that exist, so a table created afterwards, even by a raw DB.statement, is seen by the next write.

Gotchas#

Related Documentation#

• Models: useLocal models over the same connection.
• Migrations: creating the tables you query.
• Configuration: the connection behind DB.
• Testing: assertions with DB.table.