dust_db_sqlite3

Native SQLite runtime for Database code generated by Dust.

This package implements Dust's driver-independent Connection, Executor, and Row contracts using package:sqlite3.

Database is beta. It uses raw SQL with build-time validation; it is not an ORM or query builder.

Installation

Add the Database annotations and SQLite runtime:

dart pub add dust_dart dust_db_sqlite3

Install the Dust CLI by following the main installation guide. If the CLI and package versions differ, check the compatibility guide.

The driver uses Dart FFI and supports native Dart and Flutter targets. It is not intended for web applications.

Quick Start

Open a database and apply named migrations:

import 'package:dust_dart/db.dart';
import 'package:dust_db_sqlite3/dust_db_sqlite3.dart';

Future<void> main() async {
  final db = Sqlite3Driver.open(
    'app.db',
    options: const SqliteConnectOptions(
      foreignKeys: true,
      busyTimeout: Duration(seconds: 5),
    ),
    migrations: const {
      '0001_create_users.sql': '''
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
''',
    },
  );

  try {
    await queryExecute(
      'INSERT INTO users (name) VALUES (?)',
      ['Ada'],
    ).execute(db);

    final count = await queryScalar<int>(
      'SELECT COUNT(*) FROM users',
      const [],
    ).fetchOne(db);

    print('users: $count');
  } finally {
    await db.close();
  }
}

Migrations run in sorted filename order. Simple .sql files and SQLx reversible .up.sql / .down.sql pairs are supported. For reversible pairs, only .up.sql files run during normal startup; .down.sql files are never applied automatically.

Applied names are stored in __dust_schema_migrations, so reopening the database applies only new files. Generated @SqlxDatabase openers embed the configured migration directory and create the same Sqlite3Driver automatically.

Connection Options

Use SqliteConnectOptions when an app needs explicit SQLite behavior:

final db = Sqlite3Driver.open(
  'app.db',
  options: const SqliteConnectOptions(
    foreignKeys: true,
    busyTimeout: Duration(seconds: 5),
    journalMode: SqliteJournalMode.wal,
    synchronous: SqliteSynchronousMode.normal,
  ),
);

Use SqliteConnectOptions.memory for tests:

final db = Sqlite3Driver.connect(
  const SqliteConnectOptions.memory(foreignKeys: true),
);

Use SqliteConnectOptions.readOnly for existing snapshot databases that do not need migrations:

final db = Sqlite3Driver.connect(
  SqliteConnectOptions.readOnly('snapshot.db'),
);

Supported options include create-if-missing, read-only open mode, busy timeout, foreign keys, journal mode, synchronous mode, and custom pragmas.

Generated Database Code

Application queries normally live in @SqlxDao methods. Dust validates those queries and generates typed calls against this driver:

dust build
dust db build
dust check --db

Open the generated database and pass its connection to generated DAOs:

final database = AppDatabase.open('app.db');
final users = UserDao(database.connection);

final result = await users.findById(42);
await database.connection.close();

See the Database guide for migrations, row mapping, DAO return types, placeholders, and offline validation.

Transactions

Transactions commit on Ok and roll back on Err or a thrown exception:

final result = await database.connection.transaction((tx) async {
  return UserDao(tx).createUser('ada@example.com', 'Ada');
});

Nested transactions use SQLite savepoints:

await database.connection.transaction((tx) async {
  await UserDao(tx).createUser('ada@example.com', 'Ada');
  final nested = await tx.transaction<Unit>((nestedTx) async {
    await UserDao(nestedTx).createUser('bad@example.com', 'Bad');
    return Err<Unit, SqlxError>(SqlxError.driver('skip nested work'));
  });
  if (nested.isErr) {
    await UserDao(tx).createUser('grace@example.com', 'Grace');
  }
  return const Ok<Unit, SqlxError>(unit);
});

Transaction executors are valid only while the callback is running. Operations after the callback return Err(SqlxError).

Error Context

SQLite operations return Result<T, SqlxError>. Error strings stay concise, and errors also expose structured context for logging:

• category: connection, migration, query, decode, cardinality, or transaction
• driver: Driver.sqlite3 when the SQLite runtime produced the error
• operation: SQL string, migration name, transaction command, or read action
• cause: lower-level driver error when available

Generated DAOs pass TypeFromRow.fromRow mappers directly.

Raw SQLite Access

Use raw only for dynamic SQL that cannot be checked during generation:

final rows = await database.pool.raw.fetch(
  'SELECT * FROM users WHERE id = ?',
  [id],
);

Access the underlying package:sqlite3 database only for driver-specific operations:

final sqlite = (database.pool as Sqlite3Executor).database;

Prefer database.connection plus generated DAOs for product queries because raw and native access do not receive Dust's build-time SQL validation.

Documentation

• Database guide
• Shopping app database
• Examples — one file per question, from opening a database to what a failure looks like

Report problems through the Dust issue tracker. Contributions follow the repository's contributor guide.

Licensed under the MIT License.