Drift (Moor) — What It Is, Reactive ORM and Database Work

What Is Drift?

Drift is an ORM for Dart and Flutter, formerly known as Moor. It was developed by Simon Binder in 2019 and has since gone through several major versions. Drift compiles Dart queries into SQL at build time using drift_dev and build_runner, providing full type safety and eliminating SQL syntax errors at runtime.

Unlike Floor, Drift uses its own DSL (Domain-Specific Language) for building queries — the developer writes in Dart, and the generator translates it into SQL. This allows the IDE to check syntax, autocomplete table fields, and refactor the data model without fear of breaking queries.

According to Drift (2024), the library is used in more than 8000 Flutter projects. It supports all popular platforms: Android via sqflite, iOS via sqflite, web via sqlite3 WASM, desktop via sqlite3 native driver.

Renaming History: Moor → Drift

Moor was renamed to Drift in version 2.0 (2022). The reason was a name conflict with other projects and a desire to distance from the old code. The API remained compatible: to migrate, simply replace the import from moor to drift and update the dependencies.

Key Features

Drift provides: built-in Stream queries with automatic updates when data changes, transaction support with rollback, custom SQL queries via rawQuery, DAO pattern for logic encapsulation, cross-platform migrations, and integration with Riverpod and BLoC through the drift_riverpod and drift_bloc packages.

How Does Drift Work?

Drift uses code generation at compile time. The developer describes tables through @DataClass annotations or Dart classes extending Table. The generator creates helper classes: Companion (for nullable fields during insert/update), DriftDatabase (entry point), and DAO implementations.

Drift does not execute SQLite queries directly. Instead, the developer writes in Dart: select(tasks).where(tasks.priority.greaterThan(3)).build(). The generator translates this into SQL, and at runtime Drift simply sends the ready SQL query to SQLite. This combines the convenience of Dart syntax with native SQL performance.

Query Architecture

Drift supports two query modes: DSL (recommended) and raw SQL. DSL queries are safer to write — the compiler checks field names, types, and compatibility. Raw SQL is needed for complex queries not covered by DSL: window functions, recursive CTEs, specific SQLite extensions.

Drift DSL vs SQL: Approach Comparison

Drift offers two ways to write queries: Dart DSL (native) and raw SQL (for complex cases). DSL is preferable for 90% of scenarios: it is safer, more readable, and supports refactoring. Raw SQL is used only when DSL does not cover the required construct.

When to Use DSL

Drift DSL is the primary way of working. It covers SELECT, INSERT, UPDATE, DELETE, WHERE, ORDER BY, LIMIT, JOIN, and grouping. For all typical CRUD queries, use DSL: it is shorter, safer, and automatically updates Stream on changes.

When to Use Raw SQL

Raw SQL in Drift is needed for: custom SQLite functions (FTS5, JSON1), complex subqueries with EXISTS, INSERT OR REPLACE, mass UPDATE with CASE, as well as queries where performance is critical and DSL does not generate the optimal execution plan. Raw SQL can be written in .sql files with typing support via drift_dev.

Drift Code Examples

Drift uses classes extending Table or the @DataClass annotation. Below is a complete example of the Task model with queries via DSL, raw SQL, and reactive updates. After running build_runner, all generated classes are ready.

Table and Database Definition

The Tasks class extends Table and defines columns. Each column is an expression of type Column<T>. Parameters: withDefault() sets a default value, autoIncrement() sets auto-increment. The database is an abstract class extending $DriftDatabase.

class Tasks extends Table {
    IntColumn get id => integer().autoIncrement();
    TextColumn get title => text().withDefault(const Constant(''))();
    BoolColumn get isCompleted => boolean().withDefault(const Constant(false))();
    IntColumn get priority => integer().withDefault(const Constant(0))();
}

@DriftDatabase(tables: [Tasks])
class AppDatabase extends $AppDatabase {
    AppDatabase(QueryExecutor e) : super(e);
}

CRUD Operations via DSL

Drift generates methods into(tasks).insert(), select(tasks), update(tasks) and delete(tasks) for tables. All operations return Future — working with SQLite is asynchronous. To track changes, use .watch() instead of .get().

// Insert
await into(tasks).insert(TasksCompanion.insert(
    title: Value('Buy groceries'),
    priority: Value(3),
));

// Read with filter
final highPriority = await (select(tasks)
    ..where((t) => t.priority.greaterThan(2))
    ..orderBy([(t) => OrderingTerm(expression: t.priority, mode: OrderingMode.desc)]))
    .get();

// Reactive watch
select(tasks).watch().listen((tasksList) {
    // tasksList — List, updates on every table change
    updateUi(tasksList);
});

Raw SQL with Typed Result

For complex queries, Drift allows writing raw SQL while preserving typing. The customSelect method takes a query string and returns a typed result via code generator. This approach combines SQL flexibility with Drift type safety.

final result = await customSelect(
    'SELECT title, COUNT(*) as cnt FROM tasks GROUP BY title',
    readsFrom: { tasks },
).get();

for (final row in result) {
    print('${row.readString("title")}: ${row.readInt("cnt")}');
}

Migrations and Versioning in Drift

Drift supports both automatic migrations (for simple changes) and manual ones (for complex transformations). The database version is set in the AppDatabase constructor. If the version does not match, Drift applies all outstanding migrations sequentially.

Automatic Migrations

To add a column with a default value, Drift can generate a migration automatically via MigrationStrategy. If the change does not break existing data (adding a nullable field), you can use beforeOpen with version checking and executing ALTER TABLE.

Manual Migrations

For complex changes (renaming a table, merging data, changing a column type), Drift requires manual SQL migration. Migrations are specified via the migrations parameter in the database class. Each migration is an object with from/to numbers and SQL queries.

Drift and Testing

Drift supports running in test mode via NativeDatabase.memory(). The in-memory database is created from scratch before each test and destroyed after. For mocking, use the mocktail package with a mocked QueryExecutor. Drift also provides DatabaseTestHelper for integration tests with migration and query verification.

DAO Pattern in Drift

Drift supports DAO (Data Access Object) through abstract classes with the @DriftAccessor annotation. DAO encapsulates queries to one or more tables and can be tested separately from the database. Unlike direct queries through Database, DAO allows reusing query logic between different parts of the application and simplifies unit testing.

@DriftDatabase(tables: [Tasks])
class AppDatabase extends $AppDatabase {
    AppDatabase(QueryExecutor e) : super(e) {
        migrations.add(Migration(1, 2, (m) async {
            await m.addColumn(tasks, tasks.dueDate);
            await m.createIndex(tasks.idxPriority);
        }));
    }
}