Drift ORM: Queries, Streams, and Relations

Drift ORM: Queries, Streams, and Relations

Drift (formerly Moor) is a type-safe, reactive SQLite ORM for Flutter. Beyond basic CRUD, Drift excels at three advanced capabilities: custom select queries with filters and ordering, reactive Streams that push live UI updates whenever the database changes, and relational joins that model one-to-many table relationships. Mastering these three features unlocks the full power of local-first app architectures.

Writing Custom Select Queries

Drift generates a select() helper for every table, but real apps need filters, sorting, and pagination. You add these by chaining methods on a SimpleSelectStatement before calling get() or watch().

• where((tbl) => expr) — filters rows by a boolean expression
• orderBy([...criteria]) — sorts results ascending or descending
• limit(n, offset: k) — paginates results
• Expressions use generated column accessors: tbl.priority.isBiggerThanValue(2), tbl.title.like('%Flutter%'), tbl.isCompleted.equals(false)

// In your Drift database class (AppDatabase extends _$AppDatabase)
Future<List<Task>> getHighPriorityTasks() {
  return (select(tasks)
        ..where((t) => t.priority.isBiggerThanValue(2))
        ..orderBy([
          (t) => OrderingTerm(expression: t.dueDate),
          (t) => OrderingTerm.desc(t.priority),
        ])
        ..limit(20))
      .get();
}

Future<List<Task>> searchTasks(String keyword) {
  return (select(tasks)
        ..where((t) => t.title.like('%$keyword%')))
      .get();
}

Reactive Streams with watch()

The real differentiator of Drift is reactivity. Replace .get() with .watch() and the query returns a Stream<List<T>>. Every time any row in the queried table changes — insert, update, or delete — Drift automatically re-runs the query and pushes a new list to all active listeners. This eliminates the need to manually refresh data after writes.

In Flutter, pair watch() with a StreamBuilder widget to get a fully reactive UI with minimal boilerplate:

// DAO or database method
Stream<List<Task>> watchIncompleteTasks() {
  return (select(tasks)
        ..where((t) => t.isCompleted.equals(false))
        ..orderBy([(t) => OrderingTerm(expression: t.dueDate)]))
      .watch();
}

// Flutter widget
class TaskListScreen extends StatelessWidget {
  const TaskListScreen({super.key});

  @override
  Widget build(BuildContext context) {
    final db = context.read<AppDatabase>();
    return StreamBuilder<List<Task>>(
      stream: db.watchIncompleteTasks(),
      builder: (context, snapshot) {
        if (!snapshot.hasData) return const CircularProgressIndicator();
        final tasks = snapshot.data!;
        return ListView.builder(
          itemCount: tasks.length,
          itemBuilder: (_, i) => ListTile(title: Text(tasks[i].title)),
        );
      },
    );
  }
}

Modeling One-to-Many Relations with Joins

Relational data is the norm in real apps. A common pattern is one Category having many Tasks. Drift handles this with its join() API, which mirrors SQL JOINs but remains fully type-safe.

Steps to model a one-to-many relationship:

• Declare both tables with Drift's @DataClassName annotations
• Add a foreign-key column in the child table: IntColumn get categoryId => integer().references(Categories, #id)()
• Use select(tasks).join([innerJoin(categories, categories.id.equalsExp(tasks.categoryId))]) to fetch joined rows
• Read each table's columns via row.readTable(tasks) and row.readTable(categories)

// Table definitions
class Categories extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 64)();
}

class Tasks extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  IntColumn get categoryId => integer().references(Categories, #id)();
  BoolColumn get isCompleted => boolean().withDefault(const Constant(false))();
}

// Result type pairing both rows
class TaskWithCategory {
  final Task task;
  final Category category;
  const TaskWithCategory(this.task, this.category);
}

// Database query using join
Future<List<TaskWithCategory>> getTasksWithCategory() async {
  final query = select(tasks).join([
    innerJoin(categories, categories.id.equalsExp(tasks.categoryId)),
  ]);

  final rows = await query.get();
  return rows.map((row) {
    return TaskWithCategory(
      row.readTable(tasks),
      row.readTable(categories),
    );
  }).toList();
}

// Reactive version — updates live when either table changes
Stream<List<TaskWithCategory>> watchTasksWithCategory() {
  final query = select(tasks).join([
    innerJoin(categories, categories.id.equalsExp(tasks.categoryId)),
  ]);
  return query.watch().map((rows) => rows.map((row) {
        return TaskWithCategory(
          row.readTable(tasks),
          row.readTable(categories),
        );
      }).toList());
}

Using Data Access Objects (DAOs)

As your database grows, keep it organized by grouping related queries into DAO classes. A DAO is annotated with @DriftAccessor(tables: [Tasks, Categories]) and mixes in the generated _$TaskDaoMixin. The main database class registers all DAOs via the @DriftDatabase(daos: [TaskDao]) annotation, and you access them as db.taskDao. This separation of concerns keeps your database class lean and your query logic testable in isolation.

Summary

Drift's query API lets you write expressive, type-safe SQL through Dart method chains. Swapping .get() for .watch() transforms any query into a reactive stream that automatically drives your Flutter UI. The join() API handles relational data cleanly, returning typed result sets from multiple tables. Combined with DAOs, these patterns form the foundation for production-grade local data layers in Flutter apps.