Integrating Local Databases in Flutter Using Drift

Introduction

Local persistence is a must for many Flutter apps: caching API responses, offline-first flows, or storing user data. Drift (formerly Moor) is a typed, reactive persistence library for Flutter and Dart that combines the familiarity of SQL with generated, type-safe Dart APIs. This tutorial shows how to integrate Drift into a Flutter mobile development workflow, covering setup, schema design, DAOs, queries, and migration strategy with concise, practical examples.

Why Choose Drift

Drift blends raw SQL expressiveness with Dart-level safety. Key benefits for mobile development in Flutter:

• Type-safe tables and queries generated at build time reduce runtime errors.

• Reactive API (Streams) makes it easy to reflect DB changes in the UI with Provider, Riverpod, or Bloc.

• Support for both native and sqflite backends enables consistent code across Android, iOS, and desktop.

• Built-in migration hooks and schemaVersion simplify upgrades.

Drift is especially useful when you want SQL's power (complex joins, indexes) without writing error-prone boilerplate for mapping rows to Dart objects.

Setting Up Drift

Add dependencies to pubspec.yaml for a Flutter app using the native sqlite implementation or sqflite if you prefer:

dependencies:

• drift: ^2.0.0

• path_provider: ^2.0.0

• sqlite3_flutter_libs: ^0.5.0

dev_dependencies:

• drift_dev: ^2.0.0

• build_runner: ^2.0.0

Use path_provider to locate an app-specific directory for the database file. Configure a QueryExecutor in your database constructor. Running flutter pub run build_runner build generates the supporting code.

Defining Tables And DAOs

Define tables by extending Table. Drift generates companion classes and typed entities. Keep models focused and use DAO classes for encapsulated query logic.

Example table definition:

import 'package:drift/drift.dart';

class Todos extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text().withLength(min: 1, max: 255)();
  BoolColumn get done => boolean().withDefault(const Constant(false))();
  DateTimeColumn get dueDate => dateTime().nullable()();
}

Create a database class that wires the tables and exposes high-level APIs. Use DAOs to group related queries for reuse and testability.

@DriftDatabase(tables: [Todos])
class AppDatabase extends _$AppDatabase {
  AppDatabase(QueryExecutor e) : super(e);
  @override
  int get schemaVersion => 1;

  Stream<List<Todo>> watchAllTodos() => select(todos).watch();
  Future<int> insertTodo(Insertable<Todo> todo) => into(todos).insert(todo);
}

Keep DAOs small and single-responsibility: CRUD, filtered queries, and analytic queries (counts, aggregates). This makes unit testing straightforward because DAOs can be tested against an in-memory database.

Querying And Migrations

Drift supports both fluent DSL queries and raw SQL. Use the DSL for common operations to preserve type-safety and move to customSelect when you need complex SQL. Streams returned by .watch() automatically emit on change, which integrates directly with Flutter widgets via StreamBuilder, Provider, or state management libraries.

Migrations are handled through schemaVersion and migration strategies. When you bump schemaVersion, implement migration logic in migration.dart or override migration getter on the database.

Typical pattern:

• schemaVersion increments when you change tables.

• Provide onUpgrade to run ALTER TABLEs, create new tables, and migrate data.

• For destructive operations, prefer adding new columns and backfills to avoid data loss.

Example migration sketch:

• Add a new column with ALTER TABLE (or create a new table and copy rows), then update existing rows with default values.

• Run queries within a single transaction to maintain consistency.

Testing migrations locally before releasing is critical: run migration scripts against a copy of production data or a realistic dataset.

Performance tips:

• Use indexes for frequently filtered columns.

• Avoid N+1 queries by composing joins or batch-fetching related rows.

• Use generated SQL via Drift to keep query plans stable and inspect SQL when tuning.

Vibe Studio

Vibe Studio, powered by Steve’s advanced AI agents, is a revolutionary no-code, conversational platform that empowers users to quickly and efficiently create full-stack Flutter applications integrated seamlessly with Firebase backend services. Ideal for solo founders, startups, and agile engineering teams, Vibe Studio allows users to visually manage and deploy Flutter apps, greatly accelerating the development process. The intuitive conversational interface simplifies complex development tasks, making app creation accessible even for non-coders.

Conclusion

Drift is a pragmatic choice for Flutter mobile development when you need robust local persistence with SQL power and Dart type-safety. The workflow—define tables, generate code, write DAOs, and run migrations—scales from small caches to complex offline-capable apps. Start by integrating Drift with a single feature, expose reactive streams to your UI, and expand to more sophisticated schema changes with controlled migrations. With Drift, you get the performance and flexibility of SQL plus a predictable, testable Dart API that fits naturally into Flutter apps.