Development

Building Offline-First Flutter Apps with Drift — The Complete 2026 Guide

Muhammad Shakil Muhammad Shakil
Feb 23, 2026
32 min read
Building Offline-First Flutter Apps with Drift
Back to Blog

Building Offline-First Flutter Apps with Drift — The Complete 2026 Guide

Your users don't live in a world of perfect Wi-Fi. They're in elevators, on subways, in rural areas, and on planes. If your app breaks when the network drops, you've already lost them. Offline-first isn't a nice-to-have — it's the difference between an app people rely on and an app they uninstall after one frustrating experience.

Drift (formerly Moor) is the most powerful local database solution for Flutter. It wraps SQLite in a type-safe, reactive Dart API with code generation, schema migrations, complex joins, and cross-platform support. This guide covers everything: from setting up your first table to building a production sync engine with conflict resolution, encryption, and background synchronization.

🚀 What You'll Build

By the end of this guide, you'll have a production-ready offline-first architecture with: type-safe Drift tables, CRUD operations with reactive streams, a sync engine that queues operations offline and processes them when connectivity returns, timestamp-based conflict resolution, encrypted databases with SQLCipher, background sync via WorkManager, and comprehensive tests.

1. Why Offline-First? The Business Case

Offline-first isn't about handling edge cases — it's about building for reality:

The strategy is simple: write local, sync later. Your app reads from and writes to a local database. A background sync engine pushes changes to the server when connectivity is available. The user never notices.

2. Offline-First Architecture Principles

Before writing code, understand the core principles:

┌────────────────────────────────────────────────────────────┐
│ FLUTTER APP │
│ │
│ ┌──────────┐ ┌──────────────┐ ┌──────────────────┐ │
│ │ UI │───▶│ Repository │───▶│ Drift Database │ │
│ │ Layer │◀───│ Layer │◀───│ (local SQLite) │ │
│ └──────────┘ └──────┬───────┘ └──────────────────┘ │
│ │ ▲ │
│ │ ┌───────┴────────┐ │
│ │ │ Operation │ │
│ │ │ Queue │ │
│ ▼ └───────┬────────┘ │
│ ┌──────────────┐ │ │
│ │ Sync Engine │◀─────────────┘ │
│ └──────┬───────┘ │
│ │ (when online) │
└─────────────────────────┼──────────────────────────────────┘
 ▼
 ┌──────────────┐
 │ Remote API │
 │ (REST/gRPC) │
 └──────────────┘

The key principles:

  1. Local-first reads — the UI always reads from the local Drift database, never directly from the network.
  2. Optimistic writes — writes go to the local database immediately. The user sees instant results.
  3. Operation queue — every local write is also recorded in a pending operations queue for later sync.
  4. Background sync — when connectivity is detected, the sync engine processes the queue and pushes changes to the server.
  5. Conflict resolution — when the server has newer data than local (or vice versa), a deterministic strategy resolves the conflict.
  6. Eventual consistency — the local and remote databases will converge, but may be temporarily out of sync.

3. Why Drift for Flutter Offline Apps

Drift is built specifically for Flutter and Dart. Here's why it's our go-to for every offline-first project:

Feature Drift Raw sqflite
Type safety Full — compile-time query validation None — runtime string SQL
Code generation Automatic DAOs, companions, data classes Manual everything
Reactive streams Built-in watch() queries Manual StreamControllers
Migrations Structured MigrationStrategy Raw SQL strings
Joins Type-safe join builder Raw SQL
Transactions transaction(() async { ... }) Manual
Testing NativeDatabase.memory() Complex setup
Multi-platform Mobile, Web (WASM), Desktop (FFI) Mobile only

Drift's documentation at drift.simonbinder.eu is excellent, and the package is actively maintained by Simon Binder with 4,900+ GitHub stars.

4. Project Setup & Code Generation

Dependencies

# pubspec.yaml
dependencies:
 drift: ^2.21.0 # https://pub.dev/packages/drift
 sqlite3_flutter_libs: ^0.5.24 # https://pub.dev/packages/sqlite3_flutter_libs
 path_provider: ^2.1.4 # https://pub.dev/packages/path_provider
 path: ^1.9.0
 connectivity_plus: ^6.0.5 # https://pub.dev/packages/connectivity_plus
 flutter_riverpod: ^2.5.1 # https://pub.dev/packages/flutter_riverpod

dev_dependencies:
 drift_dev: ^2.21.0 # https://pub.dev/packages/drift_dev
 build_runner: ^2.4.12 # https://pub.dev/packages/build_runner
 flutter_test:
 sdk: flutter
 mocktail: ^1.0.4 # https://pub.dev/packages/mocktail

Database File Setup

// lib/core/database/app_database.dart
import 'dart:io';

import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;

part 'app_database.g.dart';

// We'll define tables in the next section
@DriftDatabase(tables: [Notes, PendingOperations, SyncMetadata])
class AppDatabase extends _$AppDatabase {
 AppDatabase() : super(_openConnection());

 // For testing — accept any QueryExecutor
 AppDatabase.forTesting(super.executor);

 @override
 int get schemaVersion => 1;

 @override
 MigrationStrategy get migration => MigrationStrategy(
 onCreate: (Migrator m) => m.createAll(),
 onUpgrade: (Migrator m, int from, int to) async {
 // We'll add migrations in §8
 },
 );
}

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

Code Generation

# Generate database code
dart run build_runner build --delete-conflicting-outputs

# Watch mode during development
dart run build_runner watch --delete-conflicting-outputs

💡 Pro Tip: Background Isolate

NativeDatabase.createInBackground(file) runs all database operations on a separate isolate, preventing jank on the UI thread. Always use this in production — it's a one-line performance win from the Drift docs.

5. Defining Tables & Schemas

Drift tables are Dart classes that extend Table. Each getter defines a column:

// lib/core/database/tables/notes_table.dart
import 'package:drift/drift.dart';

/// The main data table — stores notes locally.
class Notes extends Table {
 // Primary key — UUID string for offline-safe IDs
 TextColumn get id => text()();
 TextColumn get title => text().withLength(min: 1, max: 200)();
 TextColumn get content => text().withDefault(const Constant(''))();
 TextColumn get category => text().nullable()();
 BoolColumn get isPinned => boolean().withDefault(const Constant(false))();
 DateTimeColumn get createdAt => dateTime()();
 DateTimeColumn get updatedAt => dateTime()();
 BoolColumn get isDeleted => boolean().withDefault(const Constant(false))();

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

/// Tracks pending operations that haven't been synced to the server yet.
class PendingOperations extends Table {
 IntColumn get id => integer().autoIncrement()();
 TextColumn get tableName => text()(); // 'notes'
 TextColumn get recordId => text()(); // UUID of the affected record
 TextColumn get operationType => text()(); // 'create', 'update', 'delete'
 TextColumn get payload => text()(); // JSON of the full record
 DateTimeColumn get createdAt => dateTime()();
 IntColumn get retryCount => integer().withDefault(const Constant(0))();
}

/// Tracks the last sync timestamp per table.
class SyncMetadata extends Table {
 TextColumn get tableName => text()();
 DateTimeColumn get lastSyncedAt => dateTime()();

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

Key design decisions:

6. CRUD Operations — Create, Read, Update, Delete

Drift generates typed companion classes for inserts and data classes for reads. Here's a complete DAO pattern: 

// lib/features/notes/data/notes_dao.dart
import 'package:drift/drift.dart';
import '../../../core/database/app_database.dart';

part 'notes_dao.g.dart';

@DriftAccessor(tables: [Notes, PendingOperations])
class NotesDao extends DatabaseAccessor<AppDatabase> with _$NotesDaoMixin {
 NotesDao(super.db);

 // ── CREATE ──
 Future<void> createNote(NotesCompanion note) async {
 await transaction(() async {
 await into(notes).insert(note);

 // Queue for sync
 await into(pendingOperations).insert(PendingOperationsCompanion.insert(
 tableName: 'notes',
 recordId: note.id.value,
 operationType: 'create',
 payload: _noteToJson(note),
 createdAt: DateTime.now(),
 ));
 });
 }

 // ── READ ──
 Future<List<Note>> getAllNotes() {
 return (select(notes)
 ..where((n) => n.isDeleted.equals(false))
 ..orderBy([(n) => OrderingTerm.desc(n.isPinned),
 (n) => OrderingTerm.desc(n.updatedAt)]))
 .get();
 }

 Future<Note?> getNoteById(String id) {
 return (select(notes)..where((n) => n.id.equals(id))).getSingleOrNull();
 }

 Future<List<Note>> getNotesByCategory(String category) {
 return (select(notes)
 ..where((n) => n.category.equals(category) & n.isDeleted.equals(false))
 ..orderBy([(n) => OrderingTerm.desc(n.updatedAt)]))
 .get();
 }

 // ── UPDATE ──
 Future<void> updateNote(String id, NotesCompanion updates) async {
 final withTimestamp = updates.copyWith(updatedAt: Value(DateTime.now()));

 await transaction(() async {
 await (update(notes)..where((n) => n.id.equals(id))).write(withTimestamp);

 await into(pendingOperations).insert(PendingOperationsCompanion.insert(
 tableName: 'notes',
 recordId: id,
 operationType: 'update',
 payload: _companionToJson(withTimestamp),
 createdAt: DateTime.now(),
 ));
 });
 }

 // ── SOFT DELETE ──
 Future<void> softDeleteNote(String id) async {
 await transaction(() async {
 await (update(notes)..where((n) => n.id.equals(id))).write(
 NotesCompanion(
 isDeleted: const Value(true),
 updatedAt: Value(DateTime.now()),
 ),
 );

 await into(pendingOperations).insert(PendingOperationsCompanion.insert(
 tableName: 'notes',
 recordId: id,
 operationType: 'delete',
 payload: '{"id": "$id"}',
 createdAt: DateTime.now(),
 ));
 });
 }

 // Helper: convert companion to JSON string
 String _noteToJson(NotesCompanion note) { /* serialization logic */ }
 String _companionToJson(NotesCompanion companion) { /* serialization logic */ }
}

Notice how every write operation is wrapped in a transaction() that also inserts into PendingOperations. This ensures the local data and the sync queue are always in sync — if either fails, both roll back.

7. Reactive Queries & Streams

Drift's killer feature is reactive queries. Call .watch() instead of .get() and you get a Stream that automatically re-emits whenever the underlying data changes:

// Reactive — emits whenever any note changes
Stream<List<Note>> watchAllNotes() {
 return (select(notes)
 ..where((n) => n.isDeleted.equals(false))
 ..orderBy([(n) => OrderingTerm.desc(n.isPinned),
 (n) => OrderingTerm.desc(n.updatedAt)]))
 .watch();
}

// Watch a single note by ID
Stream<Note?> watchNoteById(String id) {
 return (select(notes)..where((n) => n.id.equals(id)))
 .watchSingleOrNull();
}

// Watch count of notes per category
Stream<List<CategoryCount>> watchCategoryCounts() {
 final query = selectOnly(notes)
 ..addColumns([notes.category, countAll()])
 ..where(notes.isDeleted.equals(false))
 ..groupBy([notes.category]);

 return query.watch().map((rows) => rows.map((row) {
 return CategoryCount(
 category: row.read(notes.category) ?? 'Uncategorized',
 count: row.read(countAll())!,
 );
 }).toList());
}

With Riverpod, this integrates beautifully:

// The UI rebuilds automatically when data changes — zero manual refresh logic
final notesStreamProvider = StreamProvider<List<Note>>((ref) {
 final dao = ref.watch(notesDaoProvider);
 return dao.watchAllNotes();
});

⚠️ Performance Note

Reactive queries re-execute the full SQL query whenever any row in the watched table changes. For tables with 10,000+ rows, use .watchSingle() or add selective where clauses to limit the result set. See the Drift docs on watching.

8. Database Migrations & Schema Versioning

Real apps evolve. You'll add columns, create new tables, and restructure data. Drift's migration system handles this cleanly:

Simple Migration — Adding a Column

@override
int get schemaVersion => 2; // bumped from 1

@override
MigrationStrategy get migration => MigrationStrategy(
 onCreate: (Migrator m) => m.createAll(),
 onUpgrade: (Migrator m, int from, int to) async {
 if (from < 2) {
 // v2: Add 'color' column to Notes table
 await m.addColumn(notes, notes.color);
 }
 },
);

Step-by-Step Migrations (Recommended for Production)

@override
int get schemaVersion => 4;

@override
MigrationStrategy get migration => MigrationStrategy(
 onCreate: (Migrator m) => m.createAll(),
 onUpgrade: (Migrator m, int from, int to) async {
 // Run each migration step sequentially
 for (var target = from + 1; target <= to; target++) {
 switch (target) {
 case 2:
 await m.addColumn(notes, notes.color);
 break;
 case 3:
 await m.createTable(tags);
 await m.createTable(noteTags);
 break;
 case 4:
 await m.addColumn(notes, notes.wordCount);
 // Backfill word counts for existing notes
 await customStatement(
 'UPDATE notes SET word_count = LENGTH(content) - LENGTH(REPLACE(content, " ", "")) + 1 WHERE content != ""',
 );
 break;
 }
 }
 },
);

Testing Migrations

Drift provides a migration testing API to verify schemas at each version:

import 'package:drift_dev/api/migrations.dart';

// Generated by: dart run drift_dev schema dump
@DriftSchemaTest(schemaDir: 'test/schemas')
void main() {
 schemaTest('upgrade from v1 to v4', (tester) async {
 final connection = await tester.runMigrations(
 from: 1,
 to: 4,
 );
 // Verify the schema matches what we expect
 await tester.validateDatabaseSchema(connection);
 });
}

9. Connectivity Detection

Use connectivity_plus to detect network state changes:

// lib/core/network/connectivity_service.dart
import 'dart:async';
import 'package:connectivity_plus/connectivity_plus.dart';

class ConnectivityService {
 final Connectivity _connectivity = Connectivity();

 /// Stream of connectivity changes.
 Stream<bool> get onConnectivityChanged {
 return _connectivity.onConnectivityChanged.map((results) {
 return results.any((r) => r != ConnectivityResult.none);
 });
 }

 /// Check current connectivity (one-shot).
 Future<bool> get isConnected async {
 final results = await _connectivity.checkConnectivity();
 return results.any((r) => r != ConnectivityResult.none);
 }
}

⚠️ Connectivity ≠ Internet Access

connectivity_plus tells you if you're connected to Wi-Fi or cellular — not if the internet actually works. A captive portal (hotel, airport) shows as "connected" but HTTP requests fail. For production apps, also ping your API endpoint to verify actual reachability. The internet_connection_checker_plus package handles this.

10. Building a Sync Engine

The sync engine is the heart of offline-first architecture. It processes the pending operations queue when the device comes back online:

// lib/core/sync/sync_engine.dart
import 'dart:async';
import 'dart:convert';

class SyncEngine {
 final AppDatabase _db;
 final ApiClient _api;
 final ConnectivityService _connectivity;
 StreamSubscription? _connectivitySub;
 bool _isSyncing = false;

 SyncEngine(this._db, this._api, this._connectivity);

 /// Start listening for connectivity changes.
 void initialize() {
 _connectivitySub = _connectivity.onConnectivityChanged.listen((isOnline) {
 if (isOnline) processQueue();
 });
 }

 /// Process all pending operations in order.
 Future<void> processQueue() async {
 if (_isSyncing) return; // prevent concurrent syncs
 _isSyncing = true;

 try {
 final pending = await _db.getPendingOperations();

 for (final op in pending) {
 try {
 await _processOperation(op);
 await _db.deletePendingOperation(op.id);
 } on ApiException catch (e) {
 if (e.isRetryable) {
 // Network timeout, 5xx — increment retry count
 await _db.incrementRetryCount(op.id);
 } else {
 // 4xx — permanent failure, log and remove
 await _db.deletePendingOperation(op.id);
 _logPermanentFailure(op, e);
 }
 }
 }

 // After pushing local changes, pull remote updates
 await _pullRemoteChanges();

 } finally {
 _isSyncing = false;
 }
 }

 Future<void> _processOperation(PendingOperation op) async {
 final payload = jsonDecode(op.payload) as Map<String, dynamic>;

 switch (op.operationType) {
 case 'create':
 await _api.post('/notes', body: payload);
 break;
 case 'update':
 await _api.put('/notes/${op.recordId}', body: payload);
 break;
 case 'delete':
 await _api.delete('/notes/${op.recordId}');
 break;
 }
 }

 Future<void> _pullRemoteChanges() async {
 final lastSync = await _db.getLastSyncTimestamp('notes');
 final remoteNotes = await _api.get(
 '/notes',
 queryParams: {'updated_after': lastSync?.toIso8601String() ?? ''},
 );

 await _db.transaction(() async {
 for (final json in remoteNotes) {
 await _db.upsertNote(json); // insert or update
 }
 await _db.updateSyncTimestamp('notes', DateTime.now());
 });
 }

 void dispose() {
 _connectivitySub?.cancel();
 }
}

The flow is straightforward:

  1. Connectivity resumes → processQueue() fires
  2. Each pending operation is sent to the API in order (FIFO)
  3. Successful operations are removed from the queue
  4. Retryable failures (network issues) increment a retry counter
  5. Permanent failures (validation errors) are logged and removed
  6. After pushing, we pull any new changes from the server

11. Conflict Resolution Strategies

When two devices edit the same record offline, you have a conflict. There are three main strategies:

Strategy 1: Last-Write-Wins (Simplest)

/// Compare timestamps — the more recent write wins.
Future<void> resolveConflict(Note local, Map<String, dynamic> remote) async {
 final remoteUpdatedAt = DateTime.parse(remote['updatedAt']);

 if (remoteUpdatedAt.isAfter(local.updatedAt)) {
 // Server version is newer — overwrite local
 await _db.upsertNote(remote);
 }
 // else: local version is newer — it will be pushed on next sync
}

Pros: dead simple. Cons: the "loser" edit is silently discarded.

Strategy 2: Field-Level Merging (Recommended)

/// Merge changes at the field level — keep the newest version of each field.
Future<Note> mergeFieldLevel(Note local, Map<String, dynamic> remote) async {
 final remoteUpdatedAt = DateTime.parse(remote['updatedAt']);

 return Note(
 id: local.id,
 title: remoteUpdatedAt.isAfter(local.updatedAt)
 ? remote['title'] as String
 : local.title,
 content: remoteUpdatedAt.isAfter(local.updatedAt)
 ? remote['content'] as String
 : local.content,
 category: remote['category'] as String? ?? local.category,
 isPinned: local.isPinned, // local preference wins
 createdAt: local.createdAt,
 updatedAt: remoteUpdatedAt.isAfter(local.updatedAt)
 ? remoteUpdatedAt
 : local.updatedAt,
 isDeleted: local.isDeleted || (remote['isDeleted'] as bool? ?? false),
 );
}

Strategy 3: Conflict Queue (User Decides)

/// Store both versions and let the user choose.
Future<void> queueConflict(Note local, Map<String, dynamic> remote) async {
 await _db.insertConflict(Conflict(
 recordId: local.id,
 localVersion: jsonEncode(local.toJson()),
 remoteVersion: jsonEncode(remote),
 detectedAt: DateTime.now(),
 ));
 // Show a conflict resolution UI to the user
}

💡 Our Recommendation

Use last-write-wins for most apps — it covers 90% of cases with zero UI complexity. Add field-level merging for apps where data integrity is critical (e.g., medical records, financial data). Reserve conflict queuing for collaborative editing tools.

12. Using Drift with Riverpod

Drift + Riverpod is a powerful combination. Riverpod manages the database lifecycle and exposes reactive streams to the UI:

// lib/core/providers.dart
import 'package:flutter_riverpod/flutter_riverpod.dart';

// Database — singleton for the entire app
final databaseProvider = Provider<AppDatabase>((ref) {
 final db = AppDatabase();
 ref.onDispose(() => db.close());
 return db;
});

// DAO
final notesDaoProvider = Provider<NotesDao>((ref) {
 return NotesDao(ref.watch(databaseProvider));
});

// Sync engine
final syncEngineProvider = Provider<SyncEngine>((ref) {
 final db = ref.watch(databaseProvider);
 final api = ref.watch(apiClientProvider);
 final connectivity = ref.watch(connectivityProvider);
 final engine = SyncEngine(db, api, connectivity);
 engine.initialize();
 ref.onDispose(() => engine.dispose());
 return engine;
});

// Reactive notes stream
final notesProvider = StreamProvider<List<Note>>((ref) {
 return ref.watch(notesDaoProvider).watchAllNotes();
});

// Single note by ID
final noteByIdProvider = StreamProvider.family<Note?, String>((ref, id) {
 return ref.watch(notesDaoProvider).watchNoteById(id);
});

Using in a Widget

class NotesListScreen extends ConsumerWidget {
 const NotesListScreen({super.key});

 @override
 Widget build(BuildContext context, WidgetRef ref) {
 final notesAsync = ref.watch(notesProvider);

 return Scaffold(
 appBar: AppBar(title: const Text('Notes')),
 body: notesAsync.when(
 data: (notes) => notes.isEmpty
 ? const Center(child: Text('No notes yet'))
 : ListView.builder(
 itemCount: notes.length,
 itemBuilder: (_, i) => NoteCard(note: notes[i]),
 ),
 loading: () => const Center(child: CircularProgressIndicator()),
 error: (err, _) => Center(child: Text('Error: $err')),
 ),
 floatingActionButton: FloatingActionButton(
 onPressed: () => _createNote(ref),
 child: const Icon(Icons.add),
 ),
 );
 }

 Future<void> _createNote(WidgetRef ref) async {
 final dao = ref.read(notesDaoProvider);
 await dao.createNote(NotesCompanion.insert(
 id: const Uuid().v4(),
 title: 'New Note',
 createdAt: DateTime.now(),
 updatedAt: DateTime.now(),
 ));
 // No manual refresh needed — the stream auto-updates the UI
 }
}

For a deeper dive into Riverpod architecture, see our BLoC vs Riverpod guide.

13. Drift vs Hive vs Isar vs ObjectBox

Choosing the right local database is critical. Here's how the major Flutter options compare:

Feature Drift Hive Isar ObjectBox
Type SQL (SQLite) Key-Value NoSQL NoSQL
Type safety Full (generated) Partial Full (generated) Full (generated)
Relations Yes (foreign keys, joins) No Yes (links) Yes (relations)
Reactive streams Built-in Boxes only Built-in Built-in
Complex queries Full SQL No Indexed queries Query builder
Migrations Structured API Manual Manual Automatic
Web support Yes (WASM) Yes Limited No
Encryption SQLCipher Built-in Built-in No
Insert speed Good Fast Very fast Very fast
Best for Relational data, complex queries Simple caching, settings NoSQL with full-text search High-throughput NoSQL

Our verdict: Use Drift when your data is relational (users → posts → comments) or you need complex queries. Use Hive for simple key-value caching (auth tokens, user preferences). Use Isar or ObjectBox when you need NoSQL with high insert throughput.

14. Multi-Platform Support (Web, Desktop, Mobile)

Drift supports every Flutter platform, but the database backend differs:

// lib/core/database/connection/connection.dart
// Use conditional imports for platform-specific database backends

import 'connection_stub.dart'
 if (dart.library.ffi) 'connection_native.dart'
 if (dart.library.js_interop) 'connection_web.dart';

// connection_native.dart (mobile + desktop)
import 'dart:io';
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;
import 'package:sqlite3_flutter_libs/sqlite3_flutter_libs.dart';

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

// connection_web.dart (web — uses sql.js / WASM)
import 'package:drift/wasm.dart';

QueryExecutor createDatabaseConnection() {
 return LazyDatabase(() async {
 final result = await WasmDatabase.open(
 databaseName: 'app_database',
 sqlite3Uri: Uri.parse('sqlite3.wasm'),
 driftWorkerUri: Uri.parse('drift_worker.js'),
 );
 return result.resolvedExecutor;
 });
}

For web support, follow Drift's web setup guide to configure the WASM build correctly.

15. Database Encryption with SQLCipher

For apps handling sensitive data (health records, financial info, personal notes), encrypt the database at rest using sqlcipher_flutter_libs:

# Replace sqlite3_flutter_libs with sqlcipher_flutter_libs
dependencies:
 drift: ^2.21.0
 sqlcipher_flutter_libs: ^0.6.4 # https://pub.dev/packages/sqlcipher_flutter_libs
 flutter_secure_storage: ^9.2.2 # https://pub.dev/packages/flutter_secure_storage
import 'package:drift/native.dart';
import 'package:flutter_secure_storage/flutter_secure_storage.dart';
import 'package:sqlcipher_flutter_libs/sqlcipher_flutter_libs.dart';
import 'package:sqlite3/open.dart';

QueryExecutor createEncryptedConnection(File dbFile) {
 return LazyDatabase(() async {
 // Load SQLCipher
 open.overrideFor(OperatingSystem.android, openCipherOnAndroid);

 // Get or generate encryption key
 const storage = FlutterSecureStorage();
 var key = await storage.read(key: 'db_encryption_key');
 if (key == null) {
 key = const Uuid().v4(); // Generate a strong random key
 await storage.write(key: 'db_encryption_key', value: key);
 }

 return NativeDatabase.createInBackground(
 dbFile,
 setup: (db) {
 // Set the encryption key — MUST be called before any other query
 db.execute("PRAGMA key = '$key'");
 },
 );
 });
}

🔒 Security Note

Never hardcode the encryption key. Store it in flutter_secure_storage which uses the iOS Keychain and Android EncryptedSharedPreferences. The database file is then unreadable even if someone extracts it from the device.

16. Background Sync with WorkManager

Your sync engine only works while the app is open. For true offline-first, you need background sync using workmanager: 

// lib/core/sync/background_sync.dart
import 'package:workmanager/workmanager.dart';

const backgroundSyncTask = 'com.flutterstudio.backgroundSync';

@pragma('vm:entry-point')
void callbackDispatcher() {
 Workmanager().executeTask((task, inputData) async {
 switch (task) {
 case backgroundSyncTask:
 // Initialize database and sync engine in the isolate
 final db = AppDatabase();
 final api = ApiClient();
 final engine = SyncEngine(db, api, ConnectivityService());

 try {
 await engine.processQueue();
 return true; // success
 } catch (e) {
 return false; // will retry
 } finally {
 await db.close();
 }
 default:
 return false;
 }
 });
}

// Register in main()
void main() async {
 WidgetsFlutterBinding.ensureInitialized();

 await Workmanager().initialize(callbackDispatcher);
 await Workmanager().registerPeriodicTask(
 'background-sync',
 backgroundSyncTask,
 frequency: const Duration(minutes: 15),
 constraints: Constraints(
 networkType: NetworkType.connected, // only when online
 ),
 existingWorkPolicy: ExistingWorkPolicy.keep,
 );

 runApp(const MyApp());
}

WorkManager uses Android's WorkManager and iOS's BGTaskScheduler under the hood — the OS decides exactly when to run the task based on battery level, network state, and system load.

17. Performance Optimization

Indexes for Faster Queries

class Notes extends Table {
 TextColumn get id => text()();
 TextColumn get title => text().withLength(min: 1, max: 200)();
 TextColumn get category => text().nullable()();
 DateTimeColumn get updatedAt => dateTime()();
 BoolColumn get isDeleted => boolean().withDefault(const Constant(false))();

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

 // Index on columns used in WHERE and ORDER BY clauses
 @override
 List<Set<Column>> get uniqueKeys => [];
}

// In your database class, add indexes via customStatement:
@override
MigrationStrategy get migration => MigrationStrategy(
 onCreate: (Migrator m) async {
 await m.createAll();
 await customStatement('CREATE INDEX idx_notes_category ON notes(category)');
 await customStatement('CREATE INDEX idx_notes_updated ON notes(updated_at DESC)');
 await customStatement('CREATE INDEX idx_notes_deleted ON notes(is_deleted)');
 },
);

Batch Operations

/// Insert 1000 notes in a single transaction — 30x faster than individual inserts.
Future<void> bulkInsert(List<NotesCompanion> notesList) async {
 await _db.batch((batch) {
 batch.insertAll(notes, notesList, mode: InsertMode.insertOrReplace);
 });
}

/// Bulk delete by IDs
Future<void> bulkDelete(List<String> ids) async {
 await (delete(notes)..where((n) => n.id.isIn(ids))).go();
}

Performance Benchmarks

Operation 1 Record 100 Records 1,000 Records 10,000 Records
Insert (individual) ~2ms ~200ms ~2,000ms ~20,000ms
Insert (batch) ~2ms ~8ms ~60ms ~550ms
Select all ~1ms ~3ms ~15ms ~120ms
Select with index ~1ms ~2ms ~5ms ~15ms

Always use batch operations when inserting/updating more than 10 records. The difference is dramatic because SQLite commits a transaction per individual insert, while batch wraps everything in one.

18. Testing Drift Databases

Drift makes testing easy with in-memory databases:

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

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

 setUp(() {
 // Fresh in-memory database for each test — fast and isolated
 db = AppDatabase.forTesting(NativeDatabase.memory());
 dao = NotesDao(db);
 });

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

 group('NotesDao', () {
 test('createNote inserts into database', () async {
 await dao.createNote(NotesCompanion.insert(
 id: 'note_1',
 title: 'Test Note',
 createdAt: DateTime(2026, 1, 1),
 updatedAt: DateTime(2026, 1, 1),
 ));

 final notes = await dao.getAllNotes();
 expect(notes.length, 1);
 expect(notes.first.title, 'Test Note');
 });

 test('createNote also queues pending operation', () async {
 await dao.createNote(NotesCompanion.insert(
 id: 'note_1',
 title: 'Test Note',
 createdAt: DateTime(2026, 1, 1),
 updatedAt: DateTime(2026, 1, 1),
 ));

 final pending = await db.getPendingOperations();
 expect(pending.length, 1);
 expect(pending.first.operationType, 'create');
 expect(pending.first.recordId, 'note_1');
 });

 test('softDeleteNote sets isDeleted flag', () async {
 await dao.createNote(NotesCompanion.insert(
 id: 'note_1',
 title: 'Test Note',
 createdAt: DateTime(2026, 1, 1),
 updatedAt: DateTime(2026, 1, 1),
 ));

 await dao.softDeleteNote('note_1');

 // getAllNotes filters out deleted notes
 final visible = await dao.getAllNotes();
 expect(visible, isEmpty);

 // But the record still exists in the database
 final raw = await dao.getNoteById('note_1');
 expect(raw?.isDeleted, isTrue);
 });

 test('watchAllNotes emits updates reactively', () async {
 final stream = dao.watchAllNotes();

 expectLater(
 stream,
 emitsInOrder([
 hasLength(0), // initial empty
 hasLength(1), // after insert
 ]),
 );

 await dao.createNote(NotesCompanion.insert(
 id: 'note_1',
 title: 'Reactive Test',
 createdAt: DateTime(2026, 1, 1),
 updatedAt: DateTime(2026, 1, 1),
 ));
 });
 });
}

For more testing patterns, see our Flutter Testing Strategy guide.

19. Production Example — Offline-First Notes App

Let's put it all together. Here's the complete architecture of an offline-first notes app:

lib/
├── core/
│ ├── database/
│ │ ├── app_database.dart ← Database class + tables
│ │ ├── app_database.g.dart ← Generated code
│ │ └── connection/
│ │ ├── connection_native.dart ← Mobile/Desktop backend
│ │ └── connection_web.dart ← Web (WASM) backend
│ ├── network/
│ │ ├── api_client.dart ← HTTP client wrapper
│ │ └── connectivity_service.dart ← Network state monitoring
│ ├── sync/
│ │ ├── sync_engine.dart ← Queue processing + pull
│ │ ├── conflict_resolver.dart ← Conflict resolution logic
│ │ └── background_sync.dart ← WorkManager integration
│ └── providers.dart ← Riverpod providers
├── features/
│ └── notes/
│ ├── data/
│ │ └── notes_dao.dart ← CRUD + operation queuing
│ └── presentation/
│ ├── notes_list_screen.dart ← Main list view
│ ├── note_editor_screen.dart ← Create/edit note
│ └── widgets/
│ ├── note_card.dart
│ └── sync_status_bar.dart← Shows pending ops count
└── main.dart

Sync Status Indicator

// Show users how many operations are pending sync
final pendingCountProvider = StreamProvider<int>((ref) {
 final db = ref.watch(databaseProvider);
 return db.watchPendingOperationCount();
});

class SyncStatusBar extends ConsumerWidget {
 const SyncStatusBar({super.key});

 @override
 Widget build(BuildContext context, WidgetRef ref) {
 final pendingAsync = ref.watch(pendingCountProvider);
 final isOnline = ref.watch(connectivityStreamProvider).value ?? true;

 return pendingAsync.when(
 data: (count) {
 if (count == 0 && isOnline) return const SizedBox.shrink();

 return Container(
 padding: const EdgeInsets.symmetric(horizontal: 16, vertical: 8),
 color: isOnline ? Colors.orange.shade50 : Colors.red.shade50,
 child: Row(
 children: [
 Icon(
 isOnline ? Icons.sync : Icons.cloud_off,
 size: 16,
 color: isOnline ? Colors.orange : Colors.red,
 ),
 const SizedBox(width: 8),
 Text(
 isOnline
 ? 'Syncing $count change${count == 1 ? '' : 's'}...'
 : 'Offline — $count change${count == 1 ? '' : 's'} pending',
 style: TextStyle(
 fontSize: 13,
 color: isOnline ? Colors.orange.shade800 : Colors.red.shade800,
 ),
 ),
 ],
 ),
 );
 },
 loading: () => const SizedBox.shrink(),
 error: (_, __) => const SizedBox.shrink(),
 );
 }
}

20. Common Pitfalls & Anti-Patterns

Pitfall Problem Solution
Auto-increment IDs IDs collide when two devices create records offline Use UUID v4 for primary keys
Hard deletes Deleted records can't sync — the server never learns they were deleted Use soft deletes (isDeleted flag), sync the deletion, then purge after confirmation
No operation queue Changes made offline are lost when the app restarts Persist every write in a PendingOperations table within a transaction
Trusting connectivity_plus Reports "connected" on captive portals with no internet Ping your actual API endpoint to verify reachability
No migrations Schema changes crash existing installs Always increment schemaVersion and add migration steps
Individual inserts in loops Inserting 1,000 records takes 20 seconds instead of 0.5s Use batch((b) => b.insertAll(...)) for bulk operations
Forgetting to close the database Memory leaks, file lock issues on iOS Call db.close() in Riverpod's ref.onDispose()
No conflict strategy Data is silently overwritten or duplicated Choose last-write-wins, field-level merge, or conflict queue

21. Best Practices Checklist

  1. Use UUID primary keys — never auto-increment in offline-first apps.
  2. Always use transactions — wrap data writes + operation queue inserts in a single transaction.
  3. Soft delete everything — use an isDeleted flag; purge after confirmed server sync.
  4. Track timestamps — every record needs createdAt and updatedAt for conflict resolution.
  5. Use NativeDatabase.createInBackground() — run SQLite on a separate isolate.
  6. Batch bulk operations — use batch() for any operation touching 10+ records.
  7. Index queried columns — add indexes on columns used in WHERE and ORDER BY clauses.
  8. Test migrations — use Drift's schema testing to verify upgrades from every previous version.
  9. Encrypt sensitive data — use SQLCipher for databases containing personal or financial data.
  10. Show sync status — users should see when they're offline and how many changes are pending.
  11. Handle retry limits — fail permanently after N retries and log the issue, don't retry forever.
  12. Close the database — always close on dispose to prevent file locks and memory leaks.

Key Takeaways

  1. Offline-first is a necessity — 68% of mobile sessions have connectivity issues. Build for reality.
  2. Drift is the best SQL option for Flutter — type-safe, reactive, cross-platform, with excellent migration support.
  3. Architecture matters — local-first reads, optimistic writes, operation queue, background sync.
  4. Use UUID primary keys — auto-increment IDs collide across offline devices.
  5. Reactive streams eliminate refresh logic — watch() queries auto-update the UI when data changes.
  6. Conflict resolution is inevitable — choose last-write-wins for simplicity or field-level merging for fidelity.
  7. Test with in-memory databases — NativeDatabase.memory() makes database testing fast and isolated.
  8. Background sync completes the picture — WorkManager ensures data syncs even when the app is closed.

🚀 What's Next?

Build on this foundation: see our Clean Architecture guide for structuring your app's layers, our Testing Strategy guide for testing Drift databases and sync logic, or our E-Commerce with Stripe guide for a full production app. Need help building an offline-first app? Contact our team for a free consultation.

📚 Related Articles

Frequently Asked Questions

What is Drift in Flutter and how does it work?

Drift (formerly Moor) is a reactive, type-safe persistence library for Flutter built on top of SQLite. It uses Dart code generation to create type-safe table definitions, queries, and data classes. You define tables as Dart classes, and drift_dev generates all the boilerplate — DAO methods, companion objects, and reactive stream wrappers. It supports complex joins, transactions, custom SQL, and schema migrations.

How do I build an offline-first Flutter app with Drift?

Follow these steps: (1) Add drift, drift_dev, and build_runner to pubspec.yaml. (2) Define your database tables as Dart classes. (3) Create a database class extending GeneratedDatabase. (4) Implement an operation queue that stores pending changes locally. (5) Use connectivity_plus to detect network state. (6) Build a sync engine that processes the queue when online. (7) Handle conflicts with timestamp-based or version-based resolution.

What is the difference between Drift, Hive, Isar, and ObjectBox?

Drift is SQL-based (SQLite) with type-safe queries — ideal for relational data with complex queries. Hive is a lightweight key-value store, great for simple data but no relations. Isar is a NoSQL database with full-text search and excellent performance. ObjectBox is a high-performance NoSQL database with built-in sync. Choose Drift for relational data; Hive for simple caching; Isar or ObjectBox for NoSQL with high insert throughput.

How do I handle database migrations in Drift?

Increment schemaVersion in your database class and implement MigrationStrategy with an onUpgrade callback. Use the Migrator to add columns, create tables, or run custom SQL. Drift also supports step-by-step migrations where each version bump has its own migration function, making it easy to handle upgrades from any previous version. See the Drift migration docs.

How do I sync local Drift data with a remote server?

Implement an operation queue that records every local write (create, update, delete) with timestamps. When connectivity resumes, process the queue sequentially — push each operation to the API. For conflict resolution, compare local and server timestamps: last-write-wins is simplest, but field-level merging preserves more data. Use workmanager for background sync on Android/iOS.

Can Drift work on Flutter Web and Desktop?

Yes. Drift supports all Flutter platforms. For mobile, it uses sqlite3_flutter_libs (native SQLite). For web, it uses sql.js — SQLite compiled to WebAssembly. For desktop (macOS, Linux, Windows), it uses sqlite3 via FFI. You configure the database backend per platform using conditional imports.

How do I encrypt a Drift database?

Use sqlcipher_flutter_libs instead of sqlite3_flutter_libs. SQLCipher provides transparent 256-bit AES encryption for your SQLite database. Pass your encryption key when opening the database using PRAGMA key. Store the key securely using flutter_secure_storage — never hardcode it.

How do I test Drift databases in Flutter?

Use NativeDatabase.memory() for in-memory test databases — they're fast and automatically cleaned up. Create a fresh database instance in setUp() for each test. Test CRUD operations, reactive streams (using expectLater with emitsInOrder), migrations (by opening a database at an older schemaVersion and upgrading), and edge cases like concurrent writes.

Share this article:

Have an App Idea?

Let our team turn your vision into reality with Flutter.

Get a Free Quote