Local Databases in Flutter with Drift: Tables, Streams and Migrations

Every app starts by storing one tiny thing locally. A theme setting. An “onboarding seen” flag. Then it’s a cached list of products, then a draft that has to survive the app being killed, then an offline queue of changes waiting to sync. Somewhere along that path, a key-value store quietly turns into a badly designed database.

I’d rather pick the right tool up front. For anything relational in Flutter, that’s usually Drift: SQLite underneath, type-safe Dart on top, and reactive queries that make the UI update itself. This post covers how I choose local storage, then how I actually set Drift up in a project.

Pick the storage for the data, not the other way around

Not everything belongs in a database. Here’s roughly how I split it:

• shared_preferences for small, flat settings: theme, locale, a few flags. It’s not meant for large or structured data, and it isn’t encrypted, so never put tokens in it. Use flutter_secure_storage for secrets.
• Key-value / document stores (the Hive style) for caching blobs of data you read and write whole, like a cached API response. Fast and simple, but the moment you need “all unpaid orders for this customer, newest first”, you’re filtering in Dart.
• SQLite through Drift when data has relationships, you need to query, sort or filter it, or it’s the backbone of an offline-first app.
• Isar, ObjectBox and other object databases are worth knowing about, with fast object-oriented APIs. Before committing to any of them, check the current maintenance status and its recent release history. A local database is very hard to swap out once user data lives in it.

That last point is really why I default to SQLite. It’s been around for decades, it’s on every phone, and if a Dart wrapper ever disappeared, the data file would still be readable.

Setting up Drift

You’ll need drift and drift_flutter as dependencies, plus drift_dev and build_runner as dev dependencies. Tables are Dart classes:

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

part 'app_database.g.dart';

class Customers extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 120)();
  TextColumn get email => text().nullable()();
}

class Orders extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get customerId => integer().references(Customers, #id)();
  IntColumn get totalCents => integer()();
  BoolColumn get paid => boolean().withDefault(const Constant(false))();
  DateTimeColumn get createdAt =>
      dateTime().withDefault(currentDateAndTime)();
}

@DriftDatabase(tables: [Customers, Orders])
class AppDatabase extends _$AppDatabase {
  AppDatabase([QueryExecutor? executor])
      : super(executor ?? driftDatabase(name: 'app'));

  @override
  int get schemaVersion => 1;
}

Run dart run build_runner build and Drift generates the row classes (Customer, Order), “companion” classes for inserts and updates, and all the query plumbing.

Two details I always include. Money is stored as integer cents, never doubles. And the constructor takes an optional QueryExecutor, which costs nothing now and makes testing trivial later.

Queries that the compiler checks

Drift’s query builder reads like SQL but is checked by the Dart compiler. Rename a column and every broken query becomes a compile error, not a runtime crash in production.

extension OrderQueries on AppDatabase {
  Future<int> addOrder(int customerId, int totalCents) {
    return into(orders).insert(
      OrdersCompanion.insert(customerId: customerId, totalCents: totalCents),
    );
  }

  Future<void> markPaid(int orderId) {
    return (update(orders)..where((o) => o.id.equals(orderId)))
        .write(const OrdersCompanion(paid: Value(true)));
  }

  Future<List<Order>> unpaidFor(int customerId) {
    return (select(orders)
          ..where((o) => o.customerId.equals(customerId) & o.paid.not())
          ..orderBy([(o) => OrderingTerm.desc(o.createdAt)]))
        .get();
  }
}

Notice OrdersCompanion.insert only requires the columns without defaults. id, paid and createdAt fill themselves in.

For joins, aggregates or anything awkward to express with the builder, Drift also lets you write raw SQL in .drift files and generates typed Dart methods from them. I reach for that when a report query gets complicated.

watch(): the feature that sells Drift

Swap .get() for .watch() and you get a Stream that emits a new result whenever any table it depends on changes:

Stream<List<Order>> watchUnpaid(int customerId) {
  return (select(orders)
        ..where((o) => o.customerId.equals(customerId) & o.paid.not()))
      .watch();
}

This changes how you structure screens. The list screen watches the query. The detail screen calls markPaid. The list updates itself. Nobody has to remember to refresh anything, and nobody has to pass callbacks back up the navigation stack.

It fits neatly with state management too. With Riverpod, a StreamProvider around watchUnpaid gives you loading and error states for free. With BLoC, a Cubit subscribes and emits. Either way, the database becomes the single source of truth, and network sync just writes into it. (If you’re choosing between those two, I compared them in Riverpod vs BLoC.)

One habit worth building: when you write several rows that belong together, wrap them in transaction(() async { ... }). Watchers then see one consistent change instead of three intermediate ones, and a failure halfway rolls everything back.

Migrations: the part people skip until it hurts

Your first release ships with schema version 1. Three months later you need a notes column. Users already have data on their phones, and they won’t be reinstalling.

Drift handles this with schemaVersion and a MigrationStrategy:

@override
int get schemaVersion => 2;

@override
MigrationStrategy get migration => MigrationStrategy(
      onCreate: (m) => m.createAll(),
      onUpgrade: (m, from, to) async {
        if (from < 2) {
          await m.addColumn(orders, orders.notes);
        }
      },
      beforeOpen: (details) async {
        await customStatement('PRAGMA foreign_keys = ON');
      },
    );

(Here notes is a new TextColumn get notes => text().nullable()(); on the Orders table. New columns on existing tables need to be nullable or have a default, otherwise existing rows have nothing to put there.)

The rules I follow:

• Never edit an old migration step. Users may be upgrading from any version, so each if (from < n) block has to stay as it was.
• Use from < n, not from == n, so someone jumping from version 1 to 4 runs every step in order.
• Turn on foreign keys in beforeOpen. SQLite has them off by default, which surprises everyone once.
• Export schema snapshots. drift_dev can dump each schema version to JSON and generate step-by-step migration helpers and tests from them. Do this from version 1. Reconstructing old schemas after the fact is miserable.

A bad migration is one of the few bugs that can permanently damage user data, so this is the place in the app I’m strictest about testing.

Testing with an in-memory database

Because the constructor accepts an executor, tests can use a fresh in-memory database every time:

import 'package:drift/native.dart';
import 'package:flutter_test/flutter_test.dart';

void main() {
  late AppDatabase db;

  setUp(() => db = AppDatabase(NativeDatabase.memory()));
  tearDown(() => db.close());

  test('marking an order paid removes it from unpaid', () async {
    final customerId = await db
        .into(db.customers)
        .insert(CustomersCompanion.insert(name: 'Test customer'));
    final orderId = await db.addOrder(customerId, 4999);

    await db.markPaid(orderId);

    expect(await db.unpaidFor(customerId), isEmpty);
  });
}

These run in milliseconds and test real SQL, not mocks. Desktop test runs need the SQLite library available on the machine. macOS ships with it. On Linux CI agents you may need to install it. My wider approach to tests is in my Flutter testing strategy.

Keep heavy work off the UI isolate

SQLite calls are synchronous at the native level, and a big query or a bulk insert of a few thousand rows can stall the UI thread. The driftDatabase() helper from drift_flutter runs the database on a background isolate for you, which is a big reason I use it instead of opening the file manually. If you set things up by hand, NativeDatabase.createInBackground(file) does the same.

For bulk imports, use batch() so Drift sends many inserts in one go instead of one round trip per row.

Takeaways

• Use preferences for flags, secure storage for secrets, and a real database once data has relationships or needs querying.
• Drift gives you SQLite with compile-time checked queries and generated, typed row classes.
• watch() streams let the database drive the UI, so screens update themselves after any write.
• Treat migrations as permanent history: bump schemaVersion, add from < n steps, never rewrite old ones, and export schema snapshots from day one.
• Test against NativeDatabase.memory() and keep heavy work on a background isolate.

Get the local data layer right early and offline support, sync and caching all become much easier to build later.