basalt_sqlite #

The SQLite backend for basalt_dart — a concrete SqlDialect and a package:sqlite3-backed Connection. Depends only on the dialect-agnostic core (basalt); the same typed DSL, schema, and migrations also run on basalt_postgres unchanged.

Contents #

• Install
• Opening a connection
• What SqliteConnection implements
• Transactions
• Dialect
• Type mapping
• Introspection
• Testing tips

Install #

dependencies:
  basalt:
  basalt_sqlite:

package:sqlite3 uses the system SQLite via FFI. On platforms without a bundled libsqlite3 (e.g. some Linux CI images) add sqlite3_flutter_libs (Flutter) or install the native library.

Opening a connection #

import 'package:basalt/basalt.dart';
import 'package:basalt_sqlite/basalt_sqlite.dart';

final db = SqliteConnection.open('app.db');   // file (created if missing)
// final db = SqliteConnection.memory();      // in-memory — ideal for tests

final users = await db.fetch(from(Users.table).map(userMapper.read));
await db.execute(insertInto(Users.table).value(Users.name.set('Bob')));
await db.close();

What SqliteConnection implements #

The full Connection interface:

The sqlite3 driver is synchronous, so these complete their work eagerly and return already-resolved futures — the async signatures exist so an async backend (Postgres) can implement the same interface unchanged.

Transactions #

await db.transaction((tx) async {
  await tx.execute(insertInto(Users.table).value(Users.name.set('Bob')));
  await tx.transaction((inner) async {          // nested → SAVEPOINT
    await inner.execute(insertInto(Users.table).value(Users.name.set('Dave')));
  });
}); // commits on success; rolls back (or releases the savepoint) on error

Dialect #

SqliteDialect quotes identifiers with double quotes ("users"."id"), uses positional ? placeholders, and adapts canonical values to the driver form (bool → 0/1, DateTime → epoch-ms).

Type mapping #

SQLite has no native boolean or timestamp, so:

Because bool/DateTime are indistinguishable from int at the storage level, introspect() (and thus generate-schema) reports them as int. Full details and the cross-backend story: types (core) · type mapping (SQLite caveats).

Introspection #

introspect() reads sqlite_master + PRAGMA table_info / PRAGMA foreign_key_list into a dialect-neutral List<IntrospectedTable> (excluding sqlite_* and __basalt_schema_migrations). This is what basalt generate-schema consumes.

Testing tips #

SqliteConnection.memory() gives a fast, isolated database per test — no files, no cleanup:

final db = SqliteConnection.memory();
await db.executeSql('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
// … assert on db.fetch / db.execute …
await db.close();