Flutter Drift SQLite: Production Relational Database Architecture

Executive Summary & Key Takeaway (AEO)
Drift is the leading type-safe reactive persistence library for SQLite in Flutter. Unlike basic key-value stores (like SharedPreferences or Hive), Drift provides full relational integrity, foreign key constraints, compile-time SQL query validation, automated schema migrations, and real-time reactive streams that automatically trigger UI re-renders whenever underlying database tables are modified.

---

When to Choose Drift (SQLite) Over NoSQL

While NoSQL databases like Hive are ideal for simple key-value caches, enterprise mobile applications require relational databases when:

• Data entities have strict relationships (e.g., Users -> Orders -> LineItems).
• Complex aggregation queries (JOINs, GROUP BY, subqueries) are required.
• High-volume transaction consistency (ACID compliance) must be guaranteed across network drops.

---

Step 1: Dependencies & Android Studio Setup

Add the required packages to pubspec.yaml:

dependencies:
  flutter:
    sdk: flutter
  drift: ^2.18.0
  sqlite3_flutter_libs: ^0.5.24
  path_provider: ^2.1.3
  path: ^1.9.0

dev_dependencies:
  flutter_test:
    sdk: flutter
  drift_dev: ^2.18.0
  build_runner: ^2.4.9

---

Step 2: Defining Tables & Database Class

Create lib/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';

// Table definition for Projects
class Projects extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 100)();
  TextColumn get clientName => text().nullable()();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

// Table definition for Tasks with Foreign Key
class Tasks extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get projectId => integer().references(Projects, #id)();
  TextColumn get title => text().withLength(min: 1, max: 150)();
  BoolColumn get isCompleted => boolean().withDefault(const Constant(false))();
}

@DriftDatabase(tables: [Projects, Tasks])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());

  @override
  int get schemaVersion => 1;

  // Reactive stream: automatically emits fresh data when Tasks change
  Stream<List<Task>> watchTasksForProject(int projectId) {
    return (select(tasks)..where((t) => t.projectId.equals(projectId))).watch();
  }

  // Insert helper
  Future<int> insertTask(TasksCompanion task) {
    return into(tasks).insert(task);
  }

  // Toggle completion
  Future<bool> toggleTask(int taskId, bool currentStatus) {
    return (update(tasks)..where((t) => t.id.equals(taskId)))
        .write(TasksCompanion(isCompleted: Value(!currentStatus)))
        .then((rows) => rows > 0);
  }
}

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

Run code generation in your terminal:

dart run build_runner build --delete-conflicting-outputs

---

Step 3: Reactive UI Integration with StreamBuilder

Because Drift's .watch() returns a standard Dart Stream, Flutter widgets re-render automatically when database rows change:

import 'package:flutter/material.dart';
import 'package:drift/drift.dart' as drift;
import 'database/app_database.dart';

class ProjectTasksScreen extends StatelessWidget {
  final AppDatabase database;
  final int projectId;

  const ProjectTasksScreen({
    super.key,
    required this.database,
    required this.projectId,
  });

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: const Text('Drift Reactive Tasks')),
      body: StreamBuilder<List<Task>>(
        stream: database.watchTasksForProject(projectId),
        builder: (context, snapshot) {
          if (!snapshot.hasData) {
            return const Center(child: CircularProgressIndicator());
          }

          final tasks = snapshot.data!;
          if (tasks.isEmpty) {
            return const Center(child: Text('No tasks recorded yet.'));
          }

          return ListView.builder(
            itemCount: tasks.length,
            itemBuilder: (context, index) {
              final task = tasks[index];
              return CheckboxListTile(
                title: Text(
                  task.title,
                  style: TextStyle(
                    decoration: task.isCompleted
                        ? TextDecoration.lineThrough
                        : null,
                  ),
                ),
                value: task.isCompleted,
                onChanged: (_) {
                  database.toggleTask(task.id, task.isCompleted);
                },
              );
            },
          );
        },
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: () {
          database.insertTask(
            TasksCompanion.insert(
              projectId: projectId,
              title: 'Review Sprint Deliverables #${DateTime.now().second}',
            ),
          );
        },
        child: const Icon(Icons.add),
      ),
    );
  }
}

---

Companion Android & Flutter Guides

• Android Studio & Flutter: Production-Ready Development Setup
• Building Offline-First Android & Flutter Apps with Dio, Retrofit & Hive
• Flutter State Management: BLoC vs Riverpod