Drift ORM: Type-Safe Database with Code Generation

Drift ORM: Type-Safe Database with Code Generation

Drift (formerly known as Moor) is a reactive, type-safe persistence library for Flutter and Dart. Instead of writing raw SQL and manually mapping rows to Dart objects, Drift lets you define your database schema as ordinary Dart classes. A code generator then produces a fully-typed database layer, DAOs, and query helpers — eliminating an entire class of runtime errors caused by typos or type mismatches in SQL strings.

Setting Up Drift

Add the following to your pubspec.yaml:

dependencies:
  drift: ^2.14.0
  drift_flutter: ^0.2.0   # provides the Flutter-specific database opener
  sqlite3_flutter_libs: ^0.5.0

dev_dependencies:
  drift_dev: ^2.14.0
  build_runner: ^2.4.0

Run flutter pub get after editing the file.

Defining Tables as Dart Classes

Every table is a Dart class that extends Table. Each getter you declare becomes a column in the generated SQL schema. Drift infers the SQL type from the Dart type you use:

import 'package:drift/drift.dart';

// Each class = one SQL table
class Tasks extends Table {
  // INTEGER PRIMARY KEY AUTOINCREMENT
  IntColumn get id => integer().autoIncrement()();

  // TEXT NOT NULL
  TextColumn get title => text().withLength(min: 1, max: 200)();

  // TEXT (nullable)
  TextColumn get description => text().nullable()();

  // INTEGER (0 or 1, stored as boolean)
  BoolColumn get isCompleted => boolean().withDefault(const Constant(false))();

  // INTEGER representing a Unix timestamp
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

class Categories extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 100)();
  // Foreign key to tasks (Drift validates at the Dart level)
  IntColumn get taskId => integer().references(Tasks, #id)();
}

Creating the Database Class and Running build_runner

You wire the tables together in a single class annotated with @DriftDatabase. The part directive tells Dart to expect a generated file alongside yours:

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

part 'app_database.g.dart'; // generated by build_runner

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

  // Schema version — increment when you add/change tables
  @override
  int get schemaVersion => 1;

  static QueryExecutor _openConnection() {
    return driftDatabase(name: 'app_db');
  }
}

Generate the boilerplate by running:

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

This creates app_database.g.dart containing the _$AppDatabase mixin, typed row classes (Task, Category), and companion insert/update classes (TasksCompanion, CategoriesCompanion).

Type-Safe CRUD with DAOs

A DAO (Data Access Object) groups related queries into one class and keeps your database logic out of widgets. Annotate it with @DriftAccessor:

import 'package:drift/drift.dart';
import 'app_database.dart';
import 'tables.dart';

part 'task_dao.g.dart';

@DriftAccessor(tables: [Tasks])
class TaskDao extends DatabaseAccessor<AppDatabase> with _$TaskDaoMixin {
  TaskDao(super.db);

  // SELECT * FROM tasks ORDER BY created_at DESC
  Stream<List<Task>> watchAllTasks() =>
      (select(tasks)..orderBy([(t) => OrderingTerm.desc(t.createdAt)])).watch();

  // SELECT * FROM tasks WHERE is_completed = 0
  Future<List<Task>> getIncompleteTasks() =>
      (select(tasks)..where((t) => t.isCompleted.equals(false))).get();

  // INSERT — uses a companion to safely supply only the columns you know
  Future<int> insertTask(TasksCompanion entry) => into(tasks).insert(entry);

  // UPDATE — only the columns present in the companion are changed
  Future<bool> updateTask(TasksCompanion entry) => update(tasks).replace(entry);

  // DELETE by primary key
  Future<int> deleteTask(int id) =>
      (delete(tasks)..where((t) => t.id.equals(id))).go();
}

In your widget you consume the DAO reactively using StreamBuilder:

class TaskListPage extends StatelessWidget {
  final TaskDao dao;
  const TaskListPage({super.key, required this.dao});

  @override
  Widget build(BuildContext context) {
    return StreamBuilder<List<Task>>(
      stream: dao.watchAllTasks(),
      builder: (context, snapshot) {
        if (!snapshot.hasData) return const CircularProgressIndicator();
        final tasks = snapshot.data!;
        return ListView.builder(
          itemCount: tasks.length,
          itemBuilder: (context, index) {
            final task = tasks[index];
            return ListTile(
              title: Text(task.title),
              subtitle: Text(task.description ?? ''),
              trailing: Checkbox(
                value: task.isCompleted,
                onChanged: (_) => dao.updateTask(
                  TasksCompanion(
                    id: Value(task.id),
                    isCompleted: Value(!task.isCompleted),
                  ),
                ),
              ),
              onLongPress: () => dao.deleteTask(task.id),
            );
          },
        );
      },
    );
  }
}

Migrations — Handling Schema Changes

When you add a column or table, increment schemaVersion and override migration in your database class:

@override
int get schemaVersion => 2;

@override
MigrationStrategy get migration => MigrationStrategy(
  onUpgrade: (migrator, from, to) async {
    if (from < 2) {
      // Add the new column; existing rows default to 0
      await migrator.addColumn(tasks, tasks.priority);
    }
  },
);

Summary

Drift transforms SQLite database access in Flutter from error-prone raw SQL into a fully compile-time-checked experience. You define tables as Dart classes, run build_runner once to generate the database and DAO boilerplate, and then perform CRUD using strongly-typed methods and companion objects. Reactive streams (via watch()) let your UI rebuild automatically whenever the underlying data changes — making Drift the most robust choice for complex local data requirements.