Skip to main content

dust_db_sqlite3 0.3.0 copy "dust_db_sqlite3: ^0.3.0" to clipboard
dust_db_sqlite3: ^0.3.0 copied to clipboard

SQLite runtime for Dust Database. Executes build-time validated SQL and generated row mapping over package:sqlite3.

dust_db_sqlite3 #

pub package pub points downloads

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 #

This package requires stable Dart 3.10 or newer. Flutter 3.38.1 provides that stable SDK; Flutter 3.38.0 bundles an older Dart prerelease rejected by Pub. See the SDK consumer validation guide. Other Dust runtime packages keep their existing Dart 3.6 floor.

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 and SHA-256 checksums of exact UTF-8 SQL are stored in __dust_schema_migrations. Startup checks the entire applied history before running new files: editing, renaming or omitting an applied migration throws a SqlxError with category migration and its filename as operation. Whitespace and line endings count as changes. Add a new migration instead of editing one. Generated @SqlxDatabase openers embed the configured migration directory and create the same Sqlite3Driver automatically.

Migration control failures retain their native cause and report the boundary in SqlxError.operation, such as COMMIT. See the COMMIT failure example and native storage-fault validation for recovery boundaries and required additional local integration checks.

Omit migrations (or pass null) for an unmanaged open, including read-only inspection. Passing migrations: {} explicitly manages an empty bundle and rejects existing applied history. Managed startup requires a writable connection and uses BEGIN IMMEDIATE before reading history; the configured busyTimeout controls waits for other writers. History upgrades, baselines and pending SQL commit or roll back together. Sqlite3Driver startup is synchronous and can block while waiting or migrating. Use the opt-in worker below when that work must leave the caller isolate.

Migration files must leave transaction control to Dust. Native commit filtering rejects early commits and implicit writes after a migration's rollback; a boundary savepoint also detects a rollback followed by a replacement transaction. Automatic rollback preserves the original failure instead of reporting a false cleanup error. This guard does not sandbox trusted migration SQL or make journal_mode = OFF crash-safe. Keep migrations to schema/data SQL on the managed database.

Existing name-only history #

Old databases cannot prove which SQL was originally applied. Managed startup fails closed until you back up the database, recover the original deployed migration bundle and review it, then explicitly opt in to a one-time baseline:

final db = Sqlite3Driver.open(
  'app.db',
  migrations: reviewedMigrations,
  options: const SqliteConnectOptions(baselineLegacyMigrations: true),
);

The same option works with generated AppDatabase.open(..., options: ...). Remove the opt-in after upgrading. Timestamps and data are preserved; old rows are marked checksum_source = 'legacy-baseline', new rows 'applied'. A baseline detects future drift; it does not verify historical SQL. It cannot bypass a missing file, an existing checksum mismatch or invalid applied metadata.

These runtime checks complement Dust's Rust SQLx build-time validation, but Dust's history table is not SQLx's _sqlx_migrations and is not interchangeable. Checksums detect bundle drift, not malicious edits by someone able to rewrite the database. See migration integrity and legacy baseline for runnable examples.

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.

Optional Background Connection #

Choose execution per connection: Sqlite3Driver remains synchronous; Sqlite3WorkerDriver owns one native connection in one persistent isolate. Await opening before using it: options, migration checks and pending SQL have finished when the future resolves.

final db = await Sqlite3WorkerDriver.open(
  'app.db',
  migrations: reviewedMigrations,
  options: const SqliteConnectOptions(
    foreignKeys: true,
    busyTimeout: Duration(seconds: 5),
  ),
);
try {
  final result = await db.fetchAll(
    'SELECT id, name FROM users ORDER BY id LIMIT 50',
    [],
    (row) => (row.read<int>('id'), row.read<String>('name')),
  );
  final users = result.unwrap();
  print(users);
} finally {
  (await db.close()).unwrap();
}

await Sqlite3WorkerDriver.connect(options, migrations: ...) also supports memory and read-only connections. Omitted versus empty migration bundles and the explicit legacy baseline policy are identical to the synchronous driver. Startup failures reject the future with SqlxError; query and close failures return Err. Worker-originated errors keep category, driver, operation and constraint kind; their native cause is diagnostic text, not a native object.

Opening, migrations, native statements and native close run on the worker. Row mappers, transaction callbacks, parameter-list JSON encoding and application code still run on the caller. Results are materialized and copied across isolates: use bounded queries/pagination rather than fetching an entire large table. This adds isolate startup, message-copy and memory overhead; it is not a connection pool and does not make SQLite support concurrent writers.

All five Executor terminals work directly, through runtime query helpers, or with existing generated DAOs:

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

DAO SQL is still checked by Dust/Rust SQLx at generation time; direct runtime SQL remains unchecked. No DAO is required to use fetchOne, fetchOptional, fetchAll, fetchScalar or execute. Generated AppDatabase.open remains synchronous. For generated openBackground, add the explicit static forwarding method shown in the generated background guide. It uses the facade's embedded migrations and a worker-safe asynchronous unsafe adapter, with no native-handle casts. This API is scheduled for v0.3.0; until it is published, use the matching current-source CLI and runtime together. Without a generated facade, supply the same reviewed migration bundle yourself when opening a worker.

Prefer ordinary functions taking Executor db when no DAO is needed; no handwritten executor extension is required. The query parity guide shows all terminals, checked source discovery, exact cardinality rules and online/offline limits, with runnable server and Flutter regressions.

Transaction ownership and nested-savepoint rules below also apply to workers. Unrelated work during a transaction fails immediately; there is no application work queue or automatic retry. close() is rejected while a transaction owns the connection. Once accepted, close rejects new work, lets already-sent normal statements finish and returns the same future on repeated calls. The worker exposes no native database handle or synchronous unsafe-SQL API.

If a worker exits or its transport fails, pending operations settle with a connection error and the connection cannot be reused. Close it and reopen; reconcile uncertain writes before retrying. Dust never replays them. A worker exit does not prove that a write was rolled back. There is no public cancel/restart API or deadline for a hung native call; busyTimeout only limits SQLite lock waits. This remains native-only Dart/Flutter FFI, not web support.

See WAL contention and checkpoints for snapshot conflicts, checkpoint result rows and native-engine safety requirements. Run the checkpoint example in foreground or worker mode. A successful checkpoint query can still report incomplete progress; inspect its status row before assuming the WAL was reset or truncated.

The recovery guide describes 24 process-kill and 40 controlled SQLITE_FULL cases for sync/worker connections. If SQLite automatically rolls back after a native statement error, the original error blocks further work through tx until its outer callback ends, then permits connection reuse. Real cleanup failures still require reopening. Run disk-full recovery for a safe temporary-database demonstration. A separate bounded-volume ENOSPC gate tests actual filesystem exhaustion on a disposable macOS image. Devices, other filesystems and power loss remain separate validation boundaries; these changes are scheduled for v0.3.0.

Run background opening and background commit recovery, or see the Flutter generated-DAO proof.

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');
});

An Ok callback does not guarantee that SQLite can commit: a deferred constraint or another connection's read lock can make COMMIT fail. The driver rolls back any transaction still active before returning Err, keeping the original commit error after successful cleanup. If rollback also fails, the returned error identifies that cleanup failure; do not assume the connection is reusable. Writes are not automatically retried. See the runnable commit recovery example.

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).

SQLite ownership is per connection. While a transaction is active, unrelated driver reads, writes, transactions and close() return a transaction error immediately; they are not queued or retried. Inside the callback, use tx (or UserDao(tx)), never the captured outer driver or database facade. An active transaction executor cannot be used from outside its callback's async context. Independent connections remain usable.

Await nested transactions and use their own executors. Parent operations and parallel sibling savepoints are rejected while a child is active. A parent retains ownership until already-started children settle, even when a callback forgets to await one. Still await them to observe their results and avoid circular waits. Closing a transaction executor only retires that handle; the callback result still controls commit/rollback.

After a rollback or savepoint cleanup failure, Dust blocks further queries and transactions on that connection. Close and reopen it before doing more work. Direct database native-handle access bypasses these checks; never overlap it with Dust operations. See the ownership example.

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
  • kind: the constraint a statement broke, read from SQLite's extended result code: uniqueViolation (including a duplicate primary key), foreignKeyViolation, notNullViolation, or checkViolation

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 #

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

Licensed under the MIT License.

0
likes
160
points
1.02k
downloads

Documentation

Documentation
API reference

Publisher

verified publisherwidget-lab.dev

Weekly Downloads

SQLite runtime for Dust Database. Executes build-time validated SQL and generated row mapping over package:sqlite3.

Repository (GitHub)
View/report issues
Contributing

Topics

#codegen #database #sqlite #sql #sqlite3

License

MIT (license)

Dependencies

crypto, dust_dart, sqlite3

More

Packages that depend on dust_db_sqlite3