Flutter Drift (Moor)

🌊 What is Drift?

Drift (formerly Moor) is a reactive persistence library built on SQLite. It provides type-safe database access, automatic code generation, reactive queries with streams, and powerful migration tools for building robust Flutter applications.


// Add to pubspec.yaml
dependencies:
  drift: ^2.13.0
  sqlite3_flutter_libs: ^0.5.0

// Define tables with Dart classes
class Users extends Table { ... }

Key Features

🔒

Type Safe

Compile-time checked queries

select(users).get();

🔄

Reactive

Automatic UI updates with streams

select(users).watch();

⚙️

Code Generation

Auto-generates database code

part 'database.g.dart';

🔀

Migrations

Easy database version management

onUpgrade: (m, from, to) {
  // Migrate
}

🔹 Installation

Add Drift packages to your project:

# pubspec.yaml
dependencies:
  flutter:
    sdk: flutter
  drift: ^2.13.0
  sqlite3_flutter_libs: ^0.5.0
  path_provider: ^2.1.1
  path: ^1.8.3

dev_dependencies:
  drift_dev: ^2.13.0
  build_runner: ^2.4.6

drift_dev generates database code automatically

🔹 Define Tables

Create table definitions using Dart classes:

import 'package:drift/drift.dart';

// Define the users table
class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 50)();
  TextColumn get email => text().unique()();
  IntColumn get age => integer().nullable()();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

// Define the tasks table
class Tasks extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  BoolColumn get isCompleted => boolean().withDefault(const Constant(false))();
  IntColumn get userId => integer().references(Users, #id)();
}

🔹 Create Database

Set up your database class:

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 'database.g.dart'; // Generated file

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

  @override
  int get schemaVersion => 1;

  static LazyDatabase _openConnection() {
    return LazyDatabase(() async {
      final dbFolder = await getApplicationDocumentsDirectory();
      final file = File(p.join(dbFolder.path, 'app.db'));
      return NativeDatabase(file);
    });
  }
}

Run flutter pub run build_runner build to generate code

🔹 Insert Data

Add records to your database:

final database = AppDatabase();

// Insert a user
Future<int> addUser(String name, String email, int age) async {
  return await database.into(database.users).insert(
    UsersCompanion(
      name: Value(name),
      email: Value(email),
      age: Value(age),
    ),
  );
}

// Usage
int userId = await addUser('Alice', '[email protected]', 28);
print('User added with ID: $userId');

Output:

User added with ID: 1

🔹 Query Data

Retrieve data with type-safe queries:

// Get all users (one-time)
Future<List<User>> getAllUsers() async {
  return await database.select(database.users).get();
}

// Watch users (reactive - updates automatically)
Stream<List<User>> watchAllUsers() {
  return database.select(database.users).watch();
}

// Get user by ID
Future<User?> getUserById(int id) async {
  return await (database.select(database.users)
    ..where((u) => u.id.equals(id)))
    .getSingleOrNull();
}

// Get users with condition
Future<List<User>> getAdultUsers() async {
  return await (database.select(database.users)
    ..where((u) => u.age.isBiggerOrEqualValue(18))
    ..orderBy([(u) => OrderingTerm(expression: u.name)]))
    .get();
}

🔹 Update and Delete

Modify and remove records:

// Update user
Future<bool> updateUser(int id, String newName) async {
  return await (database.update(database.users)
    ..where((u) => u.id.equals(id)))
    .write(UsersCompanion(name: Value(newName)));
}

// Delete user
Future<int> deleteUser(int id) async {
  return await (database.delete(database.users)
    ..where((u) => u.id.equals(id)))
    .go();
}

// Delete all users
Future<int> deleteAllUsers() async {
  return await database.delete(database.users).go();
}

🔹 Reactive UI Example

Build UI that updates automatically:

class UserListPage extends StatelessWidget {
  final AppDatabase database = AppDatabase();

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Users')),
      body: StreamBuilder<List<User>>(
        stream: database.watchAllUsers(),
        builder: (context, snapshot) {
          if (!snapshot.hasData) {
            return Center(child: CircularProgressIndicator());
          }

          final users = snapshot.data!;

          if (users.isEmpty) {
            return Center(child: Text('No users found'));
          }

          return ListView.builder(
            itemCount: users.length,
            itemBuilder: (context, index) {
              final user = users[index];
              return ListTile(
                title: Text(user.name),
                subtitle: Text(user.email),
                trailing: Text('Age: ${user.age ?? "N/A"}'),
              );
            },
          );
        },
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: () {
          // Add user - UI updates automatically!
          database.addUser('New User', '[email protected]', 25);
        },
        child: Icon(Icons.add),
      ),
    );
  }
}

🔹 Joins and Complex Queries

Perform advanced queries with joins:

// Get users with their tasks
Future<List<UserWithTasks>> getUsersWithTasks() async {
  final query = database.select(database.users).join([
    leftOuterJoin(
      database.tasks,
      database.tasks.userId.equalsExp(database.users.id),
    ),
  ]);

  final results = await query.get();
  
  // Process results...
  return results.map((row) {
    return UserWithTasks(
      user: row.readTable(database.users),
      tasks: row.readTableOrNull(database.tasks),
    );
  }).toList();
}

🔹 Best Practices

Use Streams: Leverage reactive queries for automatic UI updates
Companions: Use Companion classes for inserts/updates
Transactions: Wrap multiple operations in transactions
Migrations: Plan schema changes carefully
Close Database: Dispose database when app closes