Drift SQL Database Guide

Goal

Implement type-safe reactive SQL database using Drift (formerly Moor). Drift provides compile-time verified SQL queries, automatic migrations, and reactive streams built on SQLite.

Process

Phase 1: Install Dependencies

dependencies:
  drift: ^2.32.0
  sqlite3_flutter_libs: ^0.6.0+eol
  path_provider: ^2.1.0
  path: ^1.9.1

dev_dependencies:
  drift_dev: ^2.32.0
  build_runner: ^2.14.1

Phase 2: Define Database Schema

import 'package:drift/drift.dart';
import 'dart:io';

part 'database.g.dart';

// Define tables
class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 50)();
  TextColumn get email => text().unique()();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

class Todos extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  BoolColumn get isCompleted => boolean().withDefault(const Constant(false))();
  IntColumn get userId => integer().references(Users, #id, onDelete: KeyAction.cascade)();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

@DriftDatabase(tables: [Users, Todos])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());

  @override
  int get schemaVersion => 1;
  
  // Queries
  Future<List<User>> getAllUsers() => select(users).get();
  Stream<List<User>> watchAllUsers() => select(users).watch();
  
  Future<User> getUserById(int id) =>
      (select(users)..where((u) => u.id.equals(id))).getSingle();
  
  Future<int> insertUser(UsersCompanion user) => into(users).insert(user);
  
  Future<bool> updateUser(User user) => update(users).replace(user);
  
  Future<int> deleteUser(int id) =>
      (delete(users)..where((u) => u.id.equals(id))).go();
}

// Open connection
LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'app.db'));
    return NativeDatabase(file);
  });
}

Phase 3: Generate Code

flutter pub run build_runner build --delete-conflicting-outputs

Phase 4: Initialize Database

late AppDatabase database;

void main() async {
  WidgetsFlutterBinding.ensureInitialized();
  database = AppDatabase();
  runApp(MyApp());
}

Phase 5: CRUD Operations

Insert:

final userId = await database.insertUser(
  UsersCompanion.insert(
    name: 'John Doe',
    email: 'john@example.com',
  ),
);

Query:

// Simple query
final allUsers = await database.getAllUsers();

// With where clause
final activeUsers = await (database.select(database.users)
  ..where((u) => u.isActive.equals(true)))
  .get();

// Join query
final query = database.select(database.todos).join([
  leftOuterJoin(database.users, database.users.id.equalsExp(database.todos.userId)),
]);

final results = await query.get();
for (final row in results) {
  final todo = row.readTable(database.todos);
  final user = row.readTable(database.users);
  print('${todo.title} by ${user.name}');
}

Update:

await database.updateUser(
  user.copyWith(name: 'Jane Doe'),
);

// Or partial update
await (database.update(database.users)
  ..where((u) => u.id.equals(userId)))
  .write(UsersCompanion(name: Value('Jane')));

Delete:

await database.deleteUser(userId);

Phase 6: Reactive Streams

// Watch changes
Stream<List<User>> watchUsers() {
  return database.select(database.users).watch();
}

// In widget
StreamBuilder<List<User>>(
  stream: database.watchAllUsers(),
  builder: (context, snapshot) {
    if (!snapshot.hasData) return CircularProgressIndicator();
    return ListView(
      children: snapshot.data!.map((u) => UserTile(u)).toList(),
    );
  },
)

Phase 7: Migrations

@DriftDatabase(tables: [Users, Todos])
class AppDatabase extends _$AppDatabase {
  @override
  int get schemaVersion => 2;  // Increment version
  
  @override
  MigrationStrategy get migration => MigrationStrategy(
    onCreate: (Migrator m) async {
      await m.createAll();
    },
    onUpgrade: (Migrator m, int from, int to) async {
      if (from == 1) {
        // Add new column
        await m.addColumn(users, users.phoneNumber);
      }
    },
  );
}

Phase 8: Transactions

await database.transaction(() async {
  final userId = await database.insertUser(userCompanion);
  await database.into(database.todos).insert(
    TodosCompanion.insert(
      title: 'First todo',
      userId: userId,
    ),
  );
});

---

Drift 2.32+ Migration & Performance

⚠️ Breaking Changes in Drift 2.32

Migrated to sqlite3 package v3.x:

• Web apps must update sqlite3.wasm file in web/ directory
• Encrypted databases need PRAGMA adjustments
• Native platforms may require sqlite3_flutter_libs update

Migration checklist:

dependencies:
  drift: ^2.32.0
  sqlite3_flutter_libs: ^0.5.20  # Update to latest

WAL Mode Performance Optimization

Enable Write-Ahead Logging for 10x better write performance:

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'app.db'));
    
    final database = NativeDatabase(file);
    
    // Enable WAL mode
    await database.customStatement('PRAGMA journal_mode=WAL;');
    
    return database;
  });
}

WAL Mode Benefits:

• ✅ Concurrent reads while writing
• ✅ ~10x better write performance
• ✅ Reduced I/O blocking
• ✅ Better crash recovery

⚠️ WAL Mode Caveats:

• WAL file can grow large over time
• Run periodic checkpoints in production:

  // Run checkpoint every app launch or periodically
  await database.customStatement('PRAGMA wal_checkpoint(TRUNCATE);');
  

• Not suitable for network-mounted filesystems
• Monitor WAL file size in production apps

Type Safety Best Practices

⚠️ NEVER manually edit generated files (*.g.dart)

ALWAYS follow this workflow:

1. Update table definitions in .dart or .drift files
2. Run code generator:

   dart run build_runner build --delete-conflicting-outputs
   

3. Review generated code for warnings
4. Test migrations thoroughly in development
5. Commit both source and generated files

Common type safety errors:

// ❌ Wrong - nullable/non-nullable mismatch
TextColumn get name => text().nullable()();
final user = UsersCompanion.insert(name: null);  // Type error

// ✅ Correct - use Value wrapper for nullable updates
await database.update(users).replace(
  user.copyWith(name: Value(newName)),  // or Value.absent()
);

Common Errors & Solutions

Error: "Table X doesn't exist in the database"

• ✅ Increment schemaVersion and add migration
• ✅ Rerun build_runner after schema changes
• ✅ Clear app data or uninstall/reinstall during development

Error: "column Y has no value"

• ✅ Use Companion.insert() with required fields
• ✅ Add .withDefault() to column definition
• ✅ Provide default in migration when adding new column

Error: "Nested transactions causing deadlocks"

• ✅ Avoid calling transaction() inside another transaction
• ✅ Use single transaction for multi-step operations
• ✅ Check call stack for nested transaction patterns

Error: "Type mismatch after migration"

• ✅ Delete generated .g.dart files
• ✅ Run build_runner clean then rebuild
• ✅ Verify column types match between old/new schema

Performance issues with watch() streams:

• ✅ Enable WAL mode (see above)
• ✅ Use indexes on frequently queried columns
• ✅ Limit watch() to visible data only
• ✅ Dispose streams when widgets unmount

---

Constraints

• Type Safety: All queries are type-checked at compile time.
• Schema Migrations: Always increment schemaVersion when changing schema.
• Async Operations: All database operations are async.
• Close Database: Call database.close() when app terminates.
• Unique TypeIds: Drift table names must be unique.
• Foreign Keys: Enable foreign key constraints in SQLite configuration.
• Generated Files: Never manually edit *.g.dart files - always regenerate with build_runner.
• WAL Mode: Enable for production apps but monitor WAL file size and run periodic checkpoints.