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
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 transactiondriver:Driver.sqlite3when the SQLite runtime produced the erroroperation: SQL string, migration name, transaction command, or read actioncause: lower-level driver error when availablekind: the constraint a statement broke, read from SQLite's extended result code:uniqueViolation(including a duplicate primary key),foreignKeyViolation,notNullViolation, orcheckViolation
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.
Libraries
- dust_db_sqlite3
- sqlite3 runtime for Database.