Flutter Drift

Flutter Drift

Use this skill to add or repair Drift-based local persistence in Flutter apps. The skill is an operating workflow: inspect the target app first, choose only the relevant reference files, implement with current Drift APIs, and validate generated code.

Core Workflow

1. Inspect the app before editing:

• Check pubspec.yaml, existing database files, build.yaml, state management package, target platforms, and tests.
• Prefer the app's current architecture and naming over the examples in this skill.

2. Add or verify dependencies with package commands instead of hardcoding versions:

• dart pub add drift drift_flutter path_provider dev:drift_dev dev:build_runner
• Add provider or flutter_riverpod only when the app already uses it or the user asks for that integration.

3. Create or update the database entry point:

• Define tables in Dart or .drift files.
• Add the tables to @DriftDatabase.
• Open Flutter databases with driftDatabase from package:drift_flutter/drift_flutter.dart unless the project needs a custom executor.

4. Keep Drift calls scoped to the database object:

• Use database.select(database.todoItems), database.into(database.todoItems), database.update(database.todoItems), and database.delete(database.todoItems) from widgets, services, and repositories.
• Use bare select(todoItems) only inside GeneratedDatabase subclasses or DatabaseAccessor classes where the methods and table getters are in scope.

5. Generate code after changing Drift declarations:

• Run dart run build_runner build.
• Treat generator errors as blockers, not optional cleanup.

6. For schema changes in an existing app, use Drift's guided migration workflow:

• Configure drift_dev databases in build.yaml.
• Run dart run drift_dev make-migrations before and after schema changes as needed.
• Bump schemaVersion, write generated step-by-step migrations, and run generated migration tests.

7. Validate before finishing:

• Run dart format on edited Dart files.
• Run flutter analyze or dart analyze for the package.
• Run targeted tests, including generated migration tests when migrations changed.

Resource Routing

• Read references/setup.md when adding Drift, opening a database, configuring web support, or sharing a database across isolates.
• Read references/tables.md when defining tables, columns, defaults, keys, indexes, constraints, generated columns, or strict tables.
• Read references/queries.md when implementing selects, filters, sorting, pagination, joins, aggregations, subqueries, custom columns, or unions.
• Read references/writes.md when implementing inserts, updates, deletes, upserts, companions, transactions, or batch operations.
• Read references/streams.md when implementing reactive Drift streams, StreamBuilder, StreamProvider, custom select streams, table update listeners, or manual stream invalidation.
• Read references/migrations.md when schemaVersion, existing user data, build.yaml, make-migrations, generated schema files, or migration tests are involved.
• Read references/flutter-ui.md when wiring Drift data into Flutter widgets with Provider, Riverpod, StreamBuilder, search, filtering, pagination, or user-facing loading and error states.

Required API Rules

• Do not call select(database.table) as a top-level function from a widget. Use database.select(database.table) outside database classes.
• Do not use watch(...), todoUpdates(...), or notifyTableUpdates(...). Use customSelect(...).watch(), tableUpdates(...), and notifyUpdates(...).
• Do not call delete(table).go(id). Add a where clause and then call go(), or use generated table extension helpers if the project already uses them.
• Do not use batch.updateAll(...) or pass raw ids to batch.delete(...). Use batch.update(..., where: ...), batch.delete(...) with an insertable row, or batch.deleteWhere(...).
• Do not mix Provider and Riverpod APIs. Provider.of<AppDatabase>(context) belongs to provider; Provider<AppDatabase>((ref) { ... }) and AsyncValue.when(...) belong to Riverpod.
• Do not bump schemaVersion without a migration plan for existing databases.
• Do not rely on raw SQLite writes when UI streams must update. Use Drift write APIs or call notifyUpdates with explicit TableUpdate metadata.

Fallbacks

• If package downloads are blocked, update source files and leave exact dart pub add or flutter pub add commands for the user, then state that dependency resolution was not validated.
• If a project cannot run build_runner, do not hand-write generated *.g.dart files. Fix source declarations and report the generator blocker.
• If migration state is unclear, stop before changing schemaVersion; ask for the current released schema history or database files.
• If web support is required but sqlite3.wasm and drift_worker.js are missing, add the code path and explicitly report the required web assets.

Validation Checklist

• pubspec.yaml contains Drift runtime and generator dependencies.
• Database files have valid part directives, table declarations, @DriftDatabase, constructor, and schemaVersion.
• All query and write examples are scoped to the correct database/accessor context.
• Generated code was rebuilt with build_runner.
• Flutter UI examples handle loading, empty, error, and data states without treating AsyncValue as a List.
• Migrations changed only with generated schema snapshots, step-by-step migration code, and tests.
• flutter analyze or dart analyze passes for the edited package, or any remaining analyzer failure is reported with the blocker.

Provider Package

Use this pattern when the app uses the provider package.

Provider<AppDatabase>(
  create: (_) => AppDatabase(),
  dispose: (_, database) => database.close(),
  child: const MyApp(),
)

Read the database from widgets with Provider.of or context.read.

final database = Provider.of<AppDatabase>(context, listen: false);

Riverpod

Use this pattern when the app uses flutter_riverpod.

final databaseProvider = Provider<AppDatabase>((ref) {
  final database = AppDatabase();
  ref.onDispose(database.close);
  return database;
});

final todosProvider = StreamProvider<List<TodoItem>>((ref) {
  final database = ref.watch(databaseProvider);
  return (database.select(database.todoItems)
        ..orderBy([(t) => OrderingTerm.desc(t.createdAt)]))
      .watch();
});

Consume AsyncValue with when, not as a list.

class TodoList extends ConsumerWidget {
  const TodoList({super.key});

  @override
  Widget build(BuildContext context, WidgetRef ref) {
    final todosAsync = ref.watch(todosProvider);

    return todosAsync.when(
      loading: () => const Center(child: CircularProgressIndicator()),
      error: (error, stackTrace) => Text('Error: $error'),
      data: (todos) {
        if (todos.isEmpty) {
          return const Center(child: Text('No todos yet'));
        }

        return ListView.builder(
          itemCount: todos.length,
          itemBuilder: (context, index) {
            return TodoTile(todo: todos[index]);
          },
        );
      },
    );
  }
}

StreamBuilder

Use StreamBuilder directly when the widget owns the query and no state-management wrapper is needed.

class TodoList extends StatelessWidget {
  const TodoList({required this.database, super.key});

  final AppDatabase database;

  @override
  Widget build(BuildContext context) {
    return StreamBuilder<List<TodoItem>>(
      stream: (database.select(database.todoItems)
            ..orderBy([(t) => OrderingTerm.desc(t.createdAt)]))
          .watch(),
      builder: (context, snapshot) {
        if (snapshot.connectionState == ConnectionState.waiting) {
          return const Center(child: CircularProgressIndicator());
        }

        if (snapshot.hasError) {
          return Text('Error: ${snapshot.error}');
        }

        final todos = snapshot.data ?? [];
        if (todos.isEmpty) {
          return const Center(child: Text('No todos yet'));
        }

        return ListView.builder(
          itemCount: todos.length,
          itemBuilder: (context, index) {
            return TodoTile(todo: todos[index]);
          },
        );
      },
    );
  }
}

Todo Tile

Use partial updates for checkbox changes. Do not replace a full row unless every non-nullable field is present.

class TodoTile extends StatelessWidget {
  const TodoTile({required this.todo, super.key});

  final TodoItem todo;

  @override
  Widget build(BuildContext context) {
    final database = Provider.of<AppDatabase>(context, listen: false);

    return CheckboxListTile(
      value: todo.isCompleted,
      title: Text(todo.title),
      subtitle: todo.content == null ? null : Text(todo.content!),
      onChanged: (value) async {
        await (database.update(database.todoItems)
              ..where((t) => t.id.equals(todo.id)))
            .write(
          TodoItemsCompanion(
            isCompleted: Value(value ?? false),
          ),
        );
      },
      secondary: IconButton(
        icon: const Icon(Icons.delete),
        onPressed: () async {
          await (database.delete(database.todoItems)
                ..where((t) => t.id.equals(todo.id)))
              .go();
        },
      ),
    );
  }
}

Add Todo Dialog

Dispose controllers in a StatefulWidget and insert with a companion.

class AddTodoDialog extends StatefulWidget {
  const AddTodoDialog({super.key});

  @override
  State<AddTodoDialog> createState() => _AddTodoDialogState();
}

class _AddTodoDialogState extends State<AddTodoDialog> {
  final _titleController = TextEditingController();
  final _contentController = TextEditingController();

  @override
  void dispose() {
    _titleController.dispose();
    _contentController.dispose();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    final database = Provider.of<AppDatabase>(context, listen: false);

    return AlertDialog(
      title: const Text('Add Todo'),
      content: Column(
        mainAxisSize: MainAxisSize.min,
        children: [
          TextField(
            controller: _titleController,
            decoration: const InputDecoration(labelText: 'Title'),
          ),
          TextField(
            controller: _contentController,
            decoration: const InputDecoration(labelText: 'Notes'),
          ),
        ],
      ),
      actions: [
        TextButton(
          onPressed: () => Navigator.pop(context),
          child: const Text('Cancel'),
        ),
        FilledButton(
          onPressed: () async {
            final title = _titleController.text.trim();
            if (title.isEmpty) return;

            await database.into(database.todoItems).insert(
                  TodoItemsCompanion.insert(
                    title: title,
                    content: Value(
                      _contentController.text.trim().isEmpty
                          ? null
                          : _contentController.text.trim(),
                    ),
                  ),
                );

            if (context.mounted) {
              Navigator.pop(context);
            }
          },
          child: const Text('Add'),
        ),
      ],
    );
  }
}

Filtered List

For nullable filters, branch the query so null means the intended behavior.

Stream<List<TodoItem>> watchTodosByCategory(
  AppDatabase database,
  int? categoryId,
) {
  final query = database.select(database.todoItems)
    ..orderBy([(t) => OrderingTerm.desc(t.createdAt)]);

  if (categoryId == null) {
    query.where((t) => t.category.isNull());
  } else {
    query.where((t) => t.category.equals(categoryId));
  }

  return query.watch();
}

Search With Debounce

Debounce the input state, then rebuild the query from the debounced value.

class SearchTodoList extends StatefulWidget {
  const SearchTodoList({required this.database, super.key});

  final AppDatabase database;

  @override
  State<SearchTodoList> createState() => _SearchTodoListState();
}

class _SearchTodoListState extends State<SearchTodoList> {
  final _searchController = TextEditingController();
  Timer? _debounce;
  String _query = '';

  @override
  void dispose() {
    _debounce?.cancel();
    _searchController.dispose();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    final stream = (widget.database.select(widget.database.todoItems)
          ..where((t) => t.title.contains(_query))
          ..orderBy([(t) => OrderingTerm.desc(t.createdAt)]))
        .watch();

    return Column(
      children: [
        TextField(
          controller: _searchController,
          decoration: const InputDecoration(labelText: 'Search todos'),
          onChanged: (value) {
            _debounce?.cancel();
            _debounce = Timer(const Duration(milliseconds: 300), () {
              setState(() => _query = value.trim());
            });
          },
        ),
        Expanded(
          child: StreamBuilder<List<TodoItem>>(
            stream: stream,
            builder: (context, snapshot) {
              final todos = snapshot.data ?? [];
              return ListView.builder(
                itemCount: todos.length,
                itemBuilder: (context, index) {
                  return TodoTile(todo: todos[index]);
                },
              );
            },
          ),
        ),
      ],
    );
  }
}

Pagination

For simple pagination, load pages with limit and offset. For infinite scroll, keep an accumulated list in state instead of replacing the list with only the last page.

Future<List<TodoItem>> loadTodoPage(
  AppDatabase database, {
  required int page,
  int pageSize = 20,
}) {
  return (database.select(database.todoItems)
        ..orderBy([(t) => OrderingTerm.desc(t.createdAt)])
        ..limit(pageSize, offset: page * pageSize))
      .get();
}

UI Checklist

• Close the database from the provider owner.
• Keep database objects out of build methods unless they are read from a provider.
• Handle loading, empty, error, and data states.
• Use partial companion updates for row edits from controls.
• Do not call broad update or delete statements from row-level UI actions.
• Avoid rebuilding expensive streams on every keystroke; debounce or use provider parameters.

Default Rule

Use Drift's guided make-migrations workflow for existing apps. Manual migrations are error-prone and should be a fallback only when generated migration tooling cannot be adopted.

Do not bump schemaVersion unless the migration path for existing user databases is known.

Configure Guided Migrations

Add every database entry point to build.yaml:

targets:
  $default:
    builders:
      drift_dev:
        options:
          databases:
            app_database: lib/database.dart
          schema_dir: drift_schemas/
          test_dir: test/drift/

databases is required for make-migrations. schema_dir and test_dir may use the defaults, but declaring them makes the workflow explicit.

Initial Schema

Before changing a database schema, generate the initial schema snapshot:

dart run drift_dev make-migrations

Commit the generated schema files and migration test scaffolding. They are part of the migration contract.

Schema Change Workflow

1. Modify table declarations. 2. Bump schemaVersion. 3. Run:

dart run drift_dev make-migrations

4. Implement the generated step-by-step migration file. 5. Run generated migration tests. 6. Run dart run build_runner build. 7. Run flutter analyze and app tests.

Step-By-Step Migration

After make-migrations, import the generated steps file and wire onUpgrade to the generated stepByStep helper.

import 'database.steps.dart';

@DriftDatabase(tables: [Categories, TodoItems])
class AppDatabase extends _$AppDatabase {
  AppDatabase(QueryExecutor executor) : super(executor);

  @override
  int get schemaVersion => 2;

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      onCreate: (m) async => m.createAll(),
      onUpgrade: _schemaUpgrade,
      beforeOpen: (details) async {
        await customStatement('PRAGMA foreign_keys = ON');
      },
    );
  }
}

extension Migrations on GeneratedDatabase {
  OnUpgrade get _schemaUpgrade => stepByStep(
        from1To2: (m, schema) async {
          await m.addColumn(
            schema.todoItems,
            schema.todoItems.priority,
          );
        },
      );
}

Keep the stepByStep call outside the database class body when possible so migration code does not accidentally reference the current schema instead of generated schema snapshots.

Common Operations

Add a nullable column or a column with a SQL default:

from1To2: (m, schema) async {
  await m.addColumn(schema.todoItems, schema.todoItems.priority);
}

Create a new table:

from1To2: (m, schema) async {
  await m.createTable(schema.categories);
}

Alter a table when constraints or generated columns require a rebuild:

from2To3: (m, schema) async {
  await m.alterTable(TableMigration(schema.todoItems));
}

Create an index generated from a @TableIndex annotation:

from2To3: (m, schema) async {
  await m.create(schema.todoItemsCompleted);
}

Use custom SQL only when Drift's migrator API cannot express the operation:

from2To3: (m, schema) async {
  await m.customStatement('''
    UPDATE todo_items
    SET priority = 0
    WHERE priority IS NULL
  ''');
}

Data Migrations

Prefer SQL statements over reading through current generated row classes during migration. Drift query builders expect the latest schema, which can be unsafe while upgrading older schemas.

from1To2: (m, schema) async {
  await m.addColumn(schema.todoItems, schema.todoItems.priority);
  await m.customStatement('''
    UPDATE todo_items
    SET priority = 0
    WHERE priority IS NULL
  ''');
}

If Dart transformation is unavoidable, run it only after the columns or tables it needs exist.

Post-Migration Callbacks

Use beforeOpen for pragmas and seed data. Guard seed data with details.wasCreated or details.hadUpgrade.

@override
MigrationStrategy get migration {
  return MigrationStrategy(
    onCreate: (m) async => m.createAll(),
    onUpgrade: _schemaUpgrade,
    beforeOpen: (details) async {
      await customStatement('PRAGMA foreign_keys = ON');

      if (details.wasCreated) {
        final inboxId = await into(categories).insert(
          CategoriesCompanion.insert(name: 'Inbox'),
        );

        await into(todoItems).insert(
          TodoItemsCompanion.insert(
            title: 'First todo',
            category: Value(inboxId),
          ),
        );
      }
    },
  );
}

Testing Migrations

make-migrations generates migration tests. Run them after every schema change:

dart test test/drift/

Migration tests should verify:

• a new database can be created at the latest schema;
• every old schema can migrate to the latest schema;
• important user data survives migration;
• indexes, constraints, and foreign keys match the expected schema.

Manual Fallback

Use manual migrations only when generated migration files cannot be used. Keep conditions incremental with from < version, not only exact equality, so users can migrate across multiple versions.

@override
MigrationStrategy get migration {
  return MigrationStrategy(
    onCreate: (m) async => m.createAll(),
    onUpgrade: (m, from, to) async {
      if (from < 2) {
        await m.addColumn(todoItems, todoItems.priority);
      }
      if (from < 3) {
        await m.createTable(categories);
      }
    },
  );
}

Report manual migration fallback as a residual risk unless it has equivalent tests.

Scope Rule

From widgets, repositories, or services, call query builders on the database instance:

final todos = await database.select(database.todoItems).get();

Use bare select(todoItems) only inside AppDatabase or a DatabaseAccessor where Drift exposes the table and query methods directly.

Basic Select

Get all rows:

final allTodos = await database.select(database.todoItems).get();

Watch all rows:

final allTodosStream = database.select(database.todoItems).watch();

Where Clauses

Use cascades on the statement, then call get() or watch() on the completed statement.

final completedTodos = await (database.select(database.todoItems)
      ..where((t) => t.isCompleted.equals(true)))
    .get();

Combine expressions with & and |:

final importantTodos = await (database.select(database.todoItems)
      ..where(
        (t) => t.isCompleted.equals(false) & t.priority.isBiggerThanValue(2),
      ))
    .get();

Common filters:

• equals(value)
• isBiggerThanValue(value)
• isSmallerThanValue(value)
• isBiggerOrEqualValue(value)
• isSmallerOrEqualValue(value)
• like(pattern)
• contains(value)
• isNull()
• isNotNull()

Limit, Offset, And Order

final page = await (database.select(database.todoItems)
      ..orderBy([
        (t) => OrderingTerm(
              expression: t.createdAt,
              mode: OrderingMode.desc,
            ),
      ])
      ..limit(20, offset: 40))
    .get();

Single Row

Use getSingle() only when the query must return exactly one row.

final todo = await (database.select(database.todoItems)
      ..where((t) => t.id.equals(id)))
    .getSingle();

Use getSingleOrNull() for optional rows:

final todo = await (database.select(database.todoItems)
      ..where((t) => t.id.equals(id)))
    .getSingleOrNull();

Watch a single row:

final todoStream = (database.select(database.todoItems)
      ..where((t) => t.id.equals(id)))
    .watchSingleOrNull();

Mapping

Map after the statement has been configured:

final titles = await (database.select(database.todoItems)
      ..where((t) => t.title.contains(searchTerm)))
    .map((row) => row.title)
    .get();

Joins

Use joins when UI needs related rows in one query.

class TodoWithCategory {
  TodoWithCategory({required this.todo, required this.category});

  final TodoItem todo;
  final TodoCategory? category;
}

final query = database.select(database.todoItems).join([
  leftOuterJoin(
    database.categories,
    database.categories.id.equalsExp(database.todoItems.category),
  ),
]);

final rows = await query
    .map(
      (row) => TodoWithCategory(
        todo: row.readTable(database.todoItems),
        category: row.readTableOrNull(database.categories),
      ),
    )
    .get();

Use useColumns: false when a joined table is needed only for filtering:

final joined = database.select(database.todoItems).join([
  innerJoin(
    database.categories,
    database.categories.id.equalsExp(database.todoItems.category),
    useColumns: false,
  ),
]);

joined.where(database.categories.name.equals('Work'));

final workTodos = await joined
    .map((row) => row.readTable(database.todoItems))
    .get();

Self Joins

Alias tables when joining a table to itself:

final otherTodos = alias(database.todoItems, 'other');

final query = database.select(otherTodos).join([
  innerJoin(
    database.todoItems,
    database.todoItems.category.equalsExp(otherTodos.category),
    useColumns: false,
  ),
]);

query.where(database.todoItems.title.contains('important'));

final relatedTodos = await query.map((row) => row.readTable(otherTodos)).get();

Aggregations

Use selectOnly when selecting computed columns.

final countExpression = database.todoItems.id.count();

final count = await (database.selectOnly(database.todoItems)
      ..addColumns([countExpression]))
    .map((row) => row.read(countExpression) ?? 0)
    .getSingle();

Group by a related table:

final countExpression = database.todoItems.id.count();
final query = database.select(database.categories).join([
  leftOuterJoin(
    database.todoItems,
    database.todoItems.category.equalsExp(database.categories.id),
    useColumns: false,
  ),
])
  ..addColumns([countExpression])
  ..groupBy([database.categories.id]);

final counts = await query
    .map(
      (row) => (
        category: row.readTable(database.categories),
        count: row.read(countExpression) ?? 0,
      ),
    )
    .get();

Subqueries

Use subqueries for reusable query fragments:

final recent = Subquery(
  database.select(database.todoItems)
    ..orderBy([(t) => OrderingTerm.desc(t.createdAt)])
    ..limit(10),
  'recent_todos',
);

final rows = await database.select(recent).get();

Custom Columns

Add computed expressions with addColumns:

final isImportant = database.todoItems.priority.isBiggerThanValue(2);

final rows = await database.select(database.todoItems)
    .addColumns([isImportant])
    .map(
      (row) => (
        todo: row.readTable(database.todoItems),
        important: row.read(isImportant) ?? false,
      ),
    )
    .get();

Exists

final hasTodosExpression = existsQuery(database.select(database.todoItems));

final hasTodos = await (database.selectOnly(database.todoItems)
      ..addColumns([hasTodosExpression]))
    .map((row) => row.read(hasTodosExpression) ?? false)
    .getSingle();

Union

final completed = database.select(database.todoItems)
  ..where((t) => t.isCompleted.equals(true));

final highPriority = database.select(database.todoItems)
  ..where((t) => t.priority.isBiggerThanValue(2));

final rows = await completed.unionAll(highPriority).get();

Dependencies

Prefer package commands so the project resolves current compatible versions:

dart pub add drift drift_flutter path_provider dev:drift_dev dev:build_runner

Use flutter pub add instead of dart pub add if that is the local project convention.

If a document must pin versions, verify them against pub.dev first. At the time this skill was revised, current package pages showed:

• drift: ^2.32.1
• drift_dev: ^2.32.1
• drift_flutter: ^0.3.0
• build_runner: ^2.15.0

Canonical Database

Create lib/database.dart or follow the app's existing database module layout.

import 'package:drift/drift.dart';
import 'package:drift_flutter/drift_flutter.dart';
import 'package:path_provider/path_provider.dart';

part 'database.g.dart';

@DataClassName('TodoCategory')
class Categories extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 80)();
}

class TodoItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text().withLength(min: 1, max: 120)();
  TextColumn get content => text().nullable()();
  BoolColumn get isCompleted =>
      boolean().withDefault(const Constant(false))();
  IntColumn get priority => integer().withDefault(const Constant(0))();
  IntColumn get category =>
      integer().nullable().references(Categories, #id)();
  DateTimeColumn get createdAt =>
      dateTime().withDefault(currentDateAndTime)();
}

@DriftDatabase(tables: [Categories, TodoItems])
class AppDatabase extends _$AppDatabase {
  AppDatabase([QueryExecutor? executor])
      : super(executor ?? _openConnection());

  static QueryExecutor _openConnection() {
    return driftDatabase(
      name: 'app_db',
      native: const DriftNativeOptions(
        databaseDirectory: getApplicationSupportDirectory,
      ),
      web: DriftWebOptions(
        sqlite3Wasm: Uri.parse('sqlite3.wasm'),
        driftWorker: Uri.parse('drift_worker.js'),
      ),
    );
  }

  @override
  int get schemaVersion => 1;
}

Run the generator:

dart run build_runner build

Use watch mode during active development:

dart run build_runner watch

Web Support

Flutter web needs the sqlite3 WASM module and Drift worker in web/:

• sqlite3.wasm
• drift_worker.js

Do not claim web support is complete until those assets are present and the app has been tested in a browser.

Manual Database Setup

Use manual setup only when driftDatabase is not flexible enough.

import 'dart:io';

import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'package:sqlite3/sqlite3.dart';

QueryExecutor openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'app_db.sqlite'));

    final cacheBase = (await getTemporaryDirectory()).path;
    sqlite3.tempDirectory = cacheBase;

    return NativeDatabase.createInBackground(file);
  });
}

Isolate Sharing

Enable shareAcrossIsolates only when multiple isolates must access the same database, such as foreground app code plus background work.

@DriftDatabase(tables: [Categories, TodoItems])
class AppDatabase extends _$AppDatabase {
  AppDatabase.defaults()
      : super(
          driftDatabase(
            name: 'app_db',
            native: const DriftNativeOptions(
              shareAcrossIsolates: true,
            ),
          ),
        );

  @override
  int get schemaVersion => 1;
}

When isolate sharing is enabled, always close database instances explicitly so the shared server can shut down.

Testing Setup

Keep the constructor injectable so tests can use an in-memory executor:

AppDatabase createTestDatabase() {
  return AppDatabase(NativeDatabase.memory());
}

Basic Streams

Every runnable Drift select can become a stream.

final todosStream = database.select(database.todoItems).watch();

Flutter can consume the stream with StreamBuilder:

StreamBuilder<List<TodoItem>>(
  stream: database.select(database.todoItems).watch(),
  builder: (context, snapshot) {
    if (snapshot.connectionState == ConnectionState.waiting) {
      return const Center(child: CircularProgressIndicator());
    }

    if (snapshot.hasError) {
      return Text('Error: ${snapshot.error}');
    }

    final todos = snapshot.data ?? [];
    return ListView.builder(
      itemCount: todos.length,
      itemBuilder: (context, index) {
        return ListTile(title: Text(todos[index].title));
      },
    );
  },
)

Drift streams emit an up-to-date value after listening. You usually do not need to call get() before watch().

Single Row Streams

Use watchSingle() only when exactly one row must exist:

final todoStream = (database.select(database.todoItems)
      ..where((t) => t.id.equals(id)))
    .watchSingle();

Use watchSingleOrNull() when the row may not exist:

final todoStream = (database.select(database.todoItems)
      ..where((t) => t.id.equals(id)))
    .watchSingleOrNull();

Filtered Streams

final activeTodos = (database.select(database.todoItems)
      ..where((t) => t.isCompleted.equals(false)))
    .watch();

final sortedTodos = (database.select(database.todoItems)
      ..orderBy([
        (t) => OrderingTerm.desc(t.createdAt),
      ]))
    .watch();

StreamBuilder With State

class TodoList extends StatelessWidget {
  const TodoList({required this.database, super.key});

  final AppDatabase database;

  @override
  Widget build(BuildContext context) {
    return StreamBuilder<List<TodoItem>>(
      stream: (database.select(database.todoItems)
            ..orderBy([(t) => OrderingTerm.desc(t.createdAt)]))
          .watch(),
      builder: (context, snapshot) {
        if (snapshot.connectionState == ConnectionState.waiting) {
          return const Center(child: CircularProgressIndicator());
        }

        if (snapshot.hasError) {
          return Text('Error: ${snapshot.error}');
        }

        final todos = snapshot.data ?? [];
        if (todos.isEmpty) {
          return const Center(child: Text('No todos yet'));
        }

        return ListView.builder(
          itemCount: todos.length,
          itemBuilder: (context, index) {
            final todo = todos[index];
            return CheckboxListTile(
              value: todo.isCompleted,
              title: Text(todo.title),
              onChanged: (value) {
                (database.update(database.todoItems)
                      ..where((t) => t.id.equals(todo.id)))
                    .write(
                  TodoItemsCompanion(
                    isCompleted: Value(value ?? false),
                  ),
                );
              },
            );
          },
        );
      },
    );
  }
}

Riverpod StreamProvider

Do not treat AsyncValue<List<TodoItem>> as a list. Use when.

final databaseProvider = Provider<AppDatabase>((ref) {
  final database = AppDatabase();
  ref.onDispose(database.close);
  return database;
});

final todosProvider = StreamProvider<List<TodoItem>>((ref) {
  final database = ref.watch(databaseProvider);
  return (database.select(database.todoItems)
        ..orderBy([(t) => OrderingTerm.desc(t.createdAt)]))
      .watch();
});

class TodoList extends ConsumerWidget {
  const TodoList({super.key});

  @override
  Widget build(BuildContext context, WidgetRef ref) {
    final todosAsync = ref.watch(todosProvider);

    return todosAsync.when(
      loading: () => const Center(child: CircularProgressIndicator()),
      error: (error, stackTrace) => Text('Error: $error'),
      data: (todos) => ListView.builder(
        itemCount: todos.length,
        itemBuilder: (context, index) {
          return ListTile(title: Text(todos[index].title));
        },
      ),
    );
  }
}

Join Streams

class TodoWithCategory {
  TodoWithCategory({required this.todo, required this.category});

  final TodoItem todo;
  final TodoCategory? category;
}

Stream<List<TodoWithCategory>> watchTodosWithCategory(AppDatabase database) {
  final query = database.select(database.todoItems).join([
    leftOuterJoin(
      database.categories,
      database.categories.id.equalsExp(database.todoItems.category),
    ),
  ]);

  return query
      .map(
        (row) => TodoWithCategory(
          todo: row.readTable(database.todoItems),
          category: row.readTableOrNull(database.categories),
        ),
      )
      .watch();
}

Custom Query Streams

For custom SQL, use customSelect(...).watch() and always declare readsFrom so Drift knows which tables invalidate the stream.

Stream<List<TodoItem>> watchCompletedTodos(AppDatabase database) {
  return database
      .customSelect(
        '''
        SELECT * FROM todo_items
        WHERE is_completed = ?
        ORDER BY created_at DESC
        ''',
        variables: [Variable.withBool(true)],
        readsFrom: {database.todoItems},
      )
      .watch()
      .map(
        (rows) => rows
            .map((row) => database.todoItems.map(row.data))
            .toList(),
      );
}

Table Update Events

Use tableUpdates for low-level update events:

final subscription = database
    .tableUpdates(
      TableUpdateQuery.onTable(
        database.todoItems,
        limitUpdateKind: UpdateKind.update,
      ),
    )
    .listen((updates) {
  for (final update in updates) {
    debugPrint('Todo table update: $update');
  }
});

Cancel subscriptions you create manually:

await subscription.cancel();

Manual Update Trigger

If a write happens outside Drift and streams must refresh, manually notify the affected table:

database.notifyUpdates({
  TableUpdate.onTable(
    database.todoItems,
    kind: UpdateKind.update,
  ),
});

Stream Transformations

Map streams to lightweight UI state when widgets do not need full rows:

final titlesStream = database
    .select(database.todoItems)
    .watch()
    .map((todos) => todos.map((todo) => todo.title).toList());

Debounce user input before rebuilding the query in UI state. Do not depend on a debounceTime extension unless the app already imports a package that provides it.

Limitations

• Updates outside Drift do not trigger query streams unless you call notifyUpdates.
• Drift stream invalidation is table-based, so streams can rerun more often than the exact row changes require.
• Keep stream queries indexed and reasonably small for UI use.

Table Style

Use generated Dart table classes for ordinary Flutter apps. Keep the schema close to the domain model, but remember that Drift data classes are generated from the table definitions.

@DataClassName('TodoCategory')
class Categories extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 80)();
}

class TodoItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text().withLength(min: 1, max: 120)();
  TextColumn get content => text().nullable()();
  BoolColumn get isCompleted =>
      boolean().withDefault(const Constant(false))();
  IntColumn get priority => integer().withDefault(const Constant(0))();
  IntColumn get category =>
      integer().nullable().references(Categories, #id)();
  DateTimeColumn get createdAt =>
      dateTime().withDefault(currentDateAndTime)();
}

Add every table to the database annotation:

@DriftDatabase(tables: [Categories, TodoItems])
class AppDatabase extends _$AppDatabase {
  AppDatabase(QueryExecutor executor) : super(executor);

  @override
  int get schemaVersion => 1;
}

Column Types

Use nullable() for optional values:

TextColumn get content => text().nullable()();

Use SQL defaults for values that should exist even when inserts omit them:

BoolColumn get isCompleted =>
    boolean().withDefault(const Constant(false))();
DateTimeColumn get createdAt =>
    dateTime().withDefault(currentDateAndTime)();

Use client defaults only when the value must be computed in Dart:

TextColumn get clientId => text().clientDefault(() => const Uuid().v4())();

Keys And References

Use autoIncrement() for ordinary integer primary keys:

IntColumn get id => integer().autoIncrement()();

Use a custom primary key only when the domain really owns the identifier:

class Profiles extends Table {
  TextColumn get email => text()();

  @override
  Set<Column<Object>> get primaryKey => {email};
}

Reference another table with references:

class TodoItems extends Table {
  IntColumn get category =>
      integer().nullable().references(Categories, #id)();
}

Enable SQLite foreign keys when the app relies on enforcement:

@override
MigrationStrategy get migration {
  return MigrationStrategy(
    beforeOpen: (details) async {
      await customStatement('PRAGMA foreign_keys = ON');
    },
  );
}

Unique Constraints

Use unique() for a single column:

TextColumn get username => text().unique()();

Use uniqueKeys for multi-column uniqueness:

class Reservations extends Table {
  TextColumn get room => text()();
  DateTimeColumn get onDay => dateTime()();

  @override
  List<Set<Column<Object>>> get uniqueKeys => [
        {room, onDay},
      ];
}

Indexes

Index columns that are frequently filtered, sorted, or joined.

@TableIndex(name: 'todo_items_completed', columns: {#isCompleted})
@TableIndex(
  name: 'todo_items_created_at',
  columns: {IndexedColumn(#createdAt, orderBy: OrderingMode.desc)},
)
class TodoItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  BoolColumn get isCompleted =>
      boolean().withDefault(const Constant(false))();
  DateTimeColumn get createdAt =>
      dateTime().withDefault(currentDateAndTime)();
}

Use custom SQL indexes for partial indexes:

@TableIndex.sql('''
  CREATE INDEX pending_todos ON todo_items (created_at)
    WHERE is_completed = 0;
''')
class TodoItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  BoolColumn get isCompleted =>
      boolean().withDefault(const Constant(false))();
  DateTimeColumn get createdAt =>
      dateTime().withDefault(currentDateAndTime)();
}

Constraints

Use length constraints for user-facing text:

TextColumn get title => text().withLength(min: 1, max: 120)();

Use check constraints for numeric domain rules:

IntColumn get priority => integer().check(
      priority.isBiggerOrEqualValue(0) & priority.isSmallerOrEqualValue(5),
    )();

Use generated columns when SQLite should compute derived data:

class Boxes extends Table {
  IntColumn get length => integer()();
  IntColumn get width => integer()();
  IntColumn get area => integer().generatedAs(length * width)();
}

Table Mixins

Use mixins for repeated columns, but keep them simple.

mixin TimestampColumns on Table {
  DateTimeColumn get createdAt =>
      dateTime().withDefault(currentDateAndTime)();
}

class Notes extends Table with TimestampColumns {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get body => text()();
}

Strict Tables

Use strict tables only when the target SQLite runtime supports them and the app benefits from stricter typing.

class Preferences extends Table {
  TextColumn get key => text()();
  AnyColumn get value => sqliteAny().nullable()();

  @override
  Set<Column<Object>> get primaryKey => {key};

  @override
  bool get isStrict => true;
}

Scope Rule

From Flutter code outside the database class, call write builders on the database instance:

await database.into(database.todoItems).insert(
  TodoItemsCompanion.insert(title: title),
);

Use bare into(todoItems), update(todoItems), and delete(todoItems) only inside GeneratedDatabase or DatabaseAccessor code.

Insert

Insert a row with required values:

final id = await database.into(database.todoItems).insert(
  TodoItemsCompanion.insert(
    title: 'First todo',
    content: const Value('Some description'),
  ),
);

Omit nullable columns and columns with SQL defaults:

final id = await database.into(database.todoItems).insert(
  TodoItemsCompanion.insert(title: 'First todo'),
);

Insert multiple rows in a batch:

await database.batch((batch) {
  batch.insertAll(database.todoItems, [
    TodoItemsCompanion.insert(title: 'First entry'),
    TodoItemsCompanion.insert(
      title: 'Another entry',
      priority: const Value(2),
    ),
  ]);
});

Upsert

Use insertOnConflictUpdate when the row has a primary key or unique key that should be replaced on conflict.

await database.into(database.todoItems).insertOnConflictUpdate(
  TodoItemsCompanion.insert(
    id: const Value(1),
    title: 'Updated title',
  ),
);

For custom conflict handling, pass a DoUpdate clause with an update companion:

await database.into(database.todoItems).insert(
  TodoItemsCompanion.insert(
    id: const Value(1),
    title: 'Important',
  ),
  onConflict: DoUpdate(
    (old) => const TodoItemsCompanion(
      priority: Value(5),
    ),
  ),
);

Use insertReturning only when the target SQLite runtime supports RETURNING.

final row = await database.into(database.todoItems).insertReturning(
  TodoItemsCompanion.insert(title: 'A todo entry'),
);

Update

Always add a where clause unless the intent is truly to update every row.

await (database.update(database.todoItems)
      ..where((t) => t.id.equals(id)))
    .write(
  const TodoItemsCompanion(
    title: Value('Updated title'),
  ),
);

Update multiple columns with a companion:

await (database.update(database.todoItems)
      ..where((t) => t.id.equals(id)))
    .write(
  const TodoItemsCompanion(
    title: Value('Updated title'),
    isCompleted: Value(true),
  ),
);

Use replace only when you have a complete generated data row:

await database.update(database.todoItems).replace(
  todo.copyWith(isCompleted: true),
);

Use custom expressions for SQL-side updates:

await (database.update(database.todoItems)
      ..where((t) => t.id.equals(id)))
    .write(
  TodoItemsCompanion.custom(
    priority: database.todoItems.priority + const Constant(1),
  ),
);

Delete

Delete matching rows with where and go():

await (database.delete(database.todoItems)
      ..where((t) => t.id.equals(id)))
    .go();

Delete multiple rows:

await (database.delete(database.todoItems)
      ..where((t) => t.isCompleted.equals(true)))
    .go();

Delete all rows only when that is explicitly intended:

await database.delete(database.todoItems).go();

Do not use delete(table).go(id). That is not Drift's delete API.

Companions And Value

Generated data classes represent full rows. Companions represent partial writes.

final companion = TodoItemsCompanion(
  title: const Value('Title'),
  content: const Value('Content'),
  priority: const Value(2),
  category: const Value.absent(),
);

For nullable fields, distinguish setting NULL from leaving a column unchanged:

const TodoItemsCompanion(
  category: Value<int?>(null),
);

const TodoItemsCompanion(
  category: Value.absent(),
);

Transactions

Use transactions for multi-step writes that must succeed or fail together.

final categoryId = await database.transaction(() async {
  final id = await database.into(database.categories).insert(
    CategoriesCompanion.insert(name: 'Work'),
  );

  await database.into(database.todoItems).insert(
    TodoItemsCompanion.insert(
      title: 'First work todo',
      category: Value(id),
    ),
  );

  return id;
});

Errors thrown inside the callback roll the transaction back.

Batch Operations

Batches are useful for many similar writes.

await database.batch((batch) {
  batch.insertAll(database.todoItems, [
    TodoItemsCompanion.insert(title: 'First'),
    TodoItemsCompanion.insert(title: 'Second'),
  ]);

  batch.update(
    database.todoItems,
    const TodoItemsCompanion(priority: Value(1)),
    where: (t) => t.isCompleted.equals(false),
  );

  batch.deleteWhere(
    database.todoItems,
    (t) => t.isCompleted.equals(true),
  );
});

Use batch.delete(table, row) only when you have an insertable row with the primary key. Use batch.deleteWhere for predicates.

Write Safety

Avoid broad updates and deletes in UI actions:

// Bad: updates every todo.
await database.update(database.todoItems).write(
  const TodoItemsCompanion(isCompleted: Value(true)),
);

// Good: updates one row.
await (database.update(database.todoItems)
      ..where((t) => t.id.equals(id)))
    .write(
  const TodoItemsCompanion(isCompleted: Value(true)),
);