SQLite in Flutter with Drift

SQLite is the right choice for structured, queryable local data that needs to survive app restarts. Drift (formerly Moor) wraps SQLite with a type-safe Dart API, reactive streams, and compile-time verified queries. Here is a production-grade setup.

When to use Drift vs alternatives

Package	Best for
drift	Relational data, complex queries, reactive UI
hive	Simple key-value, fast reads, non-relational
shared_preferences	App settings, single values
isar	High-performance, no-SQL, complex indexes

Drift is the right pick when you have relational data (orders with items, users with addresses) and need SQL-level queries.

Setup

dependencies:
  drift: ^2.18.0
  sqlite3_flutter_libs: ^0.5.0  # SQLite native libraries
  path_provider: ^2.0.0
  path: ^1.9.0

dev_dependencies:
  drift_dev: ^2.18.0
  build_runner: ^2.4.0

Define tables

// lib/local/tables.dart
import 'package:drift/drift.dart';

class Orders extends Table {
  TextColumn get id => text()();
  TextColumn get status => text()();
  RealColumn get totalAmount => real()();
  DateTimeColumn get createdAt => dateTime()();
  TextColumn get customerId => text().references(Customers, #id)();

  @override
  Set<Column> get primaryKey => {id};
}

class OrderItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get orderId => text().references(Orders, #id)();
  TextColumn get productId => text()();
  TextColumn get productName => text()();
  IntColumn get quantity => integer()();
  RealColumn get unitPrice => real()();
}

class Customers extends Table {
  TextColumn get id => text()();
  TextColumn get name => text()();
  TextColumn get email => text().unique()();

  @override
  Set<Column> get primaryKey => {id};
}

Create the database

// lib/local/database.dart
import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;
import 'tables.dart';

part 'database.g.dart';

@DriftDatabase(tables: [Orders, OrderItems, Customers])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());

  @override
  int get schemaVersion => 1;

  @override
  MigrationStrategy get migration => MigrationStrategy(
    onCreate: (m) => m.createAll(),
    onUpgrade: (m, from, to) async {
      if (from < 2) {
        await m.addColumn(orders, orders.customerId);
      }
    },
  );
}

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dir = await getApplicationDocumentsDirectory();
    final file = File(p.join(dir.path, 'app.sqlite'));
    return NativeDatabase.createInBackground(file);
  });
}

Generate the code:

dart run build_runner build --delete-conflicting-outputs

DAOs for organized data access

// lib/local/orders_dao.dart
part of 'database.dart';

@DriftAccessor(tables: [Orders, OrderItems])
class OrdersDao extends DatabaseAccessor<AppDatabase>
    with _$OrdersDaoMixin {
  OrdersDao(super.db);

  // Reactive stream — auto-updates when data changes
  Stream<List<Order>> watchAllOrders() {
    return (select(orders)
          ..orderBy([(o) => OrderingTerm.desc(o.createdAt)]))
        .watch();
  }

  Future<Order?> getOrder(String id) {
    return (select(orders)..where((o) => o.id.equals(id)))
        .getSingleOrNull();
  }

  // Join query
  Future<List<OrderWithItems>> getOrderWithItems(String orderId) {
    final query = select(orders).join([
      leftOuterJoin(
        orderItems,
        orderItems.orderId.equalsExp(orders.id),
      ),
    ])..where(orders.id.equals(orderId));

    return query.map((row) {
      return OrderWithItems(
        order: row.readTable(orders),
        items: row.readTableOrNull(orderItems),
      );
    }).get();
  }

  Future<void> upsertOrders(List<OrdersCompanion> rows) {
    return batch((b) {
      b.insertAllOnConflictUpdate(orders, rows);
    });
  }

  Future<void> updateStatus(String id, String status) {
    return (update(orders)..where((o) => o.id.equals(id)))
        .write(OrdersCompanion(status: Value(status)));
  }

  Future<void> deleteOrder(String id) {
    return (delete(orders)..where((o) => o.id.equals(id))).go();
  }
}

Using the DAO in a repository

class OrderRepositoryImpl implements OrderRepository {
  OrderRepositoryImpl(this._dao, this._api);

  final OrdersDao _dao;
  final OrderApiClient _api;

  @override
  Stream<List<Order>> watchOrders() => _dao.watchAllOrders();

  @override
  Future<void> syncFromServer() async {
    final serverOrders = await _api.getOrders();
    final companions = serverOrders.map((o) => OrdersCompanion.insert(
      id: Value(o.id),
      status: o.status,
      totalAmount: o.totalAmount,
      createdAt: o.createdAt,
      customerId: Value(o.customerId),
    )).toList();
    await _dao.upsertOrders(companions);
  }
}

Migrations

Drift handles migrations with version numbers:

@override
MigrationStrategy get migration => MigrationStrategy(
  onCreate: (m) => m.createAll(),
  onUpgrade: (m, from, to) async {
    await customStatement('PRAGMA foreign_keys = OFF');

    if (from < 2) {
      // Version 1 → 2: add a nullable column
      await m.addColumn(orders, orders.notes);
    }
    if (from < 3) {
      // Version 2 → 3: recreate table with new constraints
      await m.recreateTable(orders);
    }

    await customStatement('PRAGMA foreign_keys = ON');
  },
  beforeOpen: (details) async {
    await customStatement('PRAGMA foreign_keys = ON');
  },
);

Testing with an in-memory database

AppDatabase createTestDatabase() {
  return AppDatabase.connect(
    DatabaseConnection(NativeDatabase.memory()),
  );
}

void main() {
  late AppDatabase db;
  late OrdersDao dao;

  setUp(() {
    db = createTestDatabase();
    dao = db.ordersDao;
  });

  tearDown(() => db.close());

  test('upsert and watch orders', () async {
    await dao.upsertOrders([
      OrdersCompanion.insert(
        id: const Value('order-1'),
        status: 'pending',
        totalAmount: 99.0,
        createdAt: DateTime.now(),
      ),
    ]);

    final orders = await dao.watchAllOrders().first;
    expect(orders, hasLength(1));
    expect(orders.first.id, 'order-1');
  });
}

Common pitfalls

Not running build_runner after schema changes. Drift generates code from your table definitions. Every change to a Table class requires re-running the generator.

Forgetting migrations. Incrementing schemaVersion without an onUpgrade handler causes the database to be wiped on upgrade. Always test migrations on a real device with existing data.

Blocking the main thread with synchronous queries. Use NativeDatabase.createInBackground to move SQLite off the UI thread. Drift's streams are already async, but get() calls can block if the connection is synchronous.

Not enabling foreign keys. SQLite has foreign keys disabled by default. Enable them in beforeOpen or you won't get referential integrity constraints.