Drift #

When building a Flutter app that needs transactional data storage, deep inter-entity relations, and very specific queries, a relational database is the best choice. In the mobile ecosystem, SQLite is the de facto standard for relational databases. However, writing SQL queries as raw text strings in Dart is very error-prone because there’s no data type checking at compile time. To bridge the full power of SQL with the comfort of safe (type-safe) Dart code, you have Drift.

Drift (previously known as Moor) is a reactive data persistence library for Flutter and Dart built on top of SQLite. Drift checks your database schema and queries at compile time, automatically generates Dart data container classes, and provides a data flow (Stream) system that reacts instantly when data in tables changes. In this article, we’ll thoroughly review Drift from installation, table modeling, multi-platform connection mechanisms, complex queries (JOINs), to complex schema migration strategies.

Introduction: Why Choose Drift? #

Amid the rise of NoSQL databases like Hive and ObjectBox, SQLite-based relational databases still hold a very special place in software architecture. There are several scenarios where SQLite is far superior to NoSQL:

Relational Integrity (Foreign Keys): Guarantees that child data can’t point to nonexistent parent data (e.g., order items must always reference a valid order ID).
Data Normalization: Avoids data duplication by dividing information into several interconnected tables.
Complex Aggregation Queries: Performing mathematical calculations directly inside the database engine, like calculating total spending per category per month using GROUP BY and SUM() functions.

Drift takes all of SQLite’s power and wraps it into a safe object-oriented paradigm. Some of Drift’s main advantages include:

Compile-Time Type Checking: If you mistype a column name or data type in your query, the Dart compiler immediately detects it as an error before the app runs.
Reactive Paradigm: Drift tracks which tables are read by active queries. When you write new data to a table, active queries reading that table automatically update their data streams.
Multi-Platform Support (Including Web): Drift can run on Android, iOS, macOS, Windows, Linux, and Web (via SQLite WASM technology). This is a big advantage over ObjectBox which doesn’t support Web.
Automatic Code Generation: Drift automatically creates Dart data container classes (Data Classes) and data update classes (Companion Classes) to speed up your CRUD code writing.

Installation & Project Setup #

To use Drift, you need runtime dependencies plus some additional tools in the development dependencies for the binding code generation process.

Add the following dependencies to your pubspec.yaml file:

dependencies:
  flutter:
    sdk: flutter
  drift: ^2.23.1
  drift_flutter: ^0.2.4   # Official helper for connection configuration in Flutter

dev_dependencies:
  drift_dev: ^2.23.1       # Drift code generator
  build_runner: ^2.4.13    # Standard Dart code generator

After adding that configuration, run the flutter pub get command in your project terminal.

Relational Table Definitions & Fluent Column Builder #

In Drift, you define database tables as Dart classes inheriting the Table class. You use the fluent builder syntax to determine column data types, character lengths, default values, and foreign key constraints.

Let’s design a simple retail store database schema consisting of Categories, Products, Orders, and the junction table OrderItems:

// lib/core/database/tables.dart

import 'package:drift/drift.dart';

// 1. Categories Table
class Categories extends Table {
  // Primary key auto-increment
  IntColumn get id => integer().autoIncrement()();
  
  // Text column with a minimum character length limit of 1 and maximum of 100
  TextColumn get name => text().withLength(min: 1, max: 100)();
  
  // Optional (nullable) text column
  TextColumn get description => text().nullable()();
  
  // Default value using the SQL built-in current time function
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

// 2. Products Table
class Products extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 255)();
  
  // Store decimal fractional numbers for prices
  RealColumn get price => real()();
  
  // Boolean property with a default value of true
  BoolColumn get isAvailable => boolean().withDefault(const Constant(true))();
  IntColumn get stock => integer().withDefault(const Constant(0))();

  // Foreign Key referring to the Categories table
  // references() guarantees relational data integrity at the SQLite level
  IntColumn get categoryId => integer().references(Categories, #id)();

  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

// 3. Orders Table
class Orders extends Table {
  IntColumn get id => integer().autoIncrement()();
  
  // Column with a value guaranteed unique across the entire database
  TextColumn get orderNumber => text().unique()();
  
  RealColumn get grandTotal => real()();
  TextColumn get status => text().withDefault(const Constant('pending'))();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

// 4. Many-to-Many Junction Table: Orders ↔ Products
class OrderItems extends Table {
  IntColumn get orderId => integer().references(Orders, #id)();
  IntColumn get productId => integer().references(Products, #id)();
  IntColumn get quantity => integer()();
  RealColumn get priceAtSale => real()();

  // Determine the Composite Primary Key
  @override
  Set<Column> get primaryKey => {orderId, productId};
}

[!NOTE] Why are there double parentheses ()() at the end of columns? This is often confusing for developers new to Drift. The first method call like text() returns a column builder object. Chained methods like withLength() add configuration to that builder. The empty double parentheses at the end () is a Dart function call to execute that builder and convert it into the actual column property object (Column).

Database Class & Multi-Platform Connections #

After defining those tables, you must create the main database class coordinating the physical file opening and schema initialization. The drift_flutter library provides the driftDatabase() method which intelligently detects the device platform and automatically selects the most optimal driver:

Mobile & Desktop: Uses the native C SQLite library (sqlite3).
Web: Uses the WebAssembly (WASM) compilation of SQLite leveraging the web browser’s virtual indexedDB storage.

Here’s the main database class implementation:

// lib/core/database/app_database.dart

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

// The generator file name we must include
part 'app_database.g.dart';

@DriftDatabase(tables: [Categories, Products, Orders, OrderItems])
class AppDatabase extends _$AppDatabase {
  // Constructor opens the physical database connection safely
  AppDatabase() : super(_openConnection());

  // 1. Determine the initial schema version number
  @override
  int get schemaVersion => 1;

  // Internal method to configure the storage path
  static QueryExecutor _openConnection() {
    // driftDatabase automatically handles storage encapsulation per platform
    return driftDatabase(name: 'store_database');
  }
}

Run the code generator to process the app_database.g.dart file:

flutter pub run build_runner build --delete-conflicting-outputs

Schema Migration Flow #

When you release app updates to the Play Store or App Store, you often need to change your table structures (e.g., adding new columns or creating new tables). Drift provides the MigrationStrategy class allowing you to manage database schema evolution structurally without deleting data users already have.

Migration Process Diagram #

Let’s see how Drift processes database version migrations when the app first starts:

graph TD
    Start["App Startup (Opening Database)"] --> Read["Read the Schema Version Stored on Disk"]
    Read --> Compare{"Compare with schemaVersion in Code"}
    
    Compare -->|Same| Open["Trigger beforeOpen (Seed Data / Set PRAGMAs)"]
    Compare -->|Code > Disk| Migrate["Trigger onUpgrade (Run Migration Steps)"]
    Compare -->|Code < Disk| Error["Tolerance / Rollback Error"]
    
    Migrate -->|Add Column / Table| Validate["Validate New Schema Structure"]
    Validate --> Open
    Open --> Ready["Database Ready to Use (Active Query List)"]

Complex Migration Strategy Implementation #

Here’s an example of handling migration from version 1 to version 2 (adding the new stock column to the Products table), and from version 2 to version 3 (creating the new OrderItems table):

// Modification inside our AppDatabase class

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

  // Raise the schema version to number 3
  @override
  int get schemaVersion => 3;

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      // Called the first time the database is created on an empty device
      onCreate: (Migrator m) async {
        await m.createAll();
      },
      
      // Called when a schema version difference is detected
      onUpgrade: (Migrator m, int from, int to) async {
        if (from < 2) {
          // Migration from Version 1 to Version 2: Add the stock column to the Products table
          // We use the 'stock' property auto-generated in _$AppDatabase
          await m.addColumn(products, products.stock);
        }
        
        if (from < 3) {
          // Migration from Version 2 to Version 3: Create the new OrderItems table
          await m.createTable(orderItems);
        }
      },
      
      // Called every time the database successfully opens
      beforeOpen: (OpeningDetails details) async {
        // Explicitly enable the Foreign Key Constraints feature on SQLite.
        // By default, SQLite disables Foreign Keys for backward compatibility.
        await customStatement('PRAGMA foreign_keys = ON;');

        if (details.wasCreated) {
          // Do initial data seeding if the database was just created
          await into(categories).insert(
            CategoriesCompanion.insert(name: 'General Category'),
          );
        }
      },
    );
  }

  static QueryExecutor _openConnection() {
    return driftDatabase(name: 'store_database');
  }
}

Through this migration strategy, Drift ensures data updates run smoothly on user devices without sacrificing existing data consistency.

CRUD Operations: Data Class vs Companion Class #

Drift’s code generation produces two different classes for each table:

Data Class (e.g., Product): A full row data representation class. All its properties are non-nullable (unless that column is indeed configured nullable in the table). Great for displaying data in the UI.
Companion Class (e.g., ProductsCompanion): A special class for insert and update operations. Its properties are wrapped in the Value<T> type. This type is used to tell Drift whether a column should be included in the update query, left empty to use defaults, or explicitly set to null.

Here’s a CRUD operation demonstration using those class types:

import 'package:drift/drift.dart';
import 'lib/core/database/app_database.dart'; // Import our database file

// 1. INSERT DATA
Future<int> insertNewProduct(AppDatabase db, String name, double price, int catId) async {
  // Companion.insert requires filling in all columns without defaults / autoIncrement
  return await db.into(db.products).insert(
    ProductsCompanion.insert(
      name: name,
      price: price,
      categoryId: catId,
      // The stock and isAvailable properties are optional because they have defaults in the table
    ),
  );
}

// 2. UPSERT DATA (Insert or Update on Conflict)
Future<void> upsertProduct(AppDatabase db, int id, String name, double price, int catId) async {
  await db.into(db.products).insertOnConflictUpdate(
    ProductsCompanion(
      id: Value(id), // If this ID already exists, Drift will update the data
      name: Value(name),
      price: Value(price),
      categoryId: Value(catId),
    ),
  );
}

// 3. READ DATA (Select with Filter & Sorting)
Future<List<Product>> getAvailableProducts(AppDatabase db) async {
  return await (db.select(db.products)
        ..where((p) => p.isAvailable.equals(true)) // Filter isAvailable = true
        ..orderBy([(p) => OrderingTerm.desc(p.price)])) // Sort from most expensive
      .get();
}

// 4. UPDATE DATA (Specific Updates)
Future<void> updateProductStock(AppDatabase db, int id, int newStock) async {
  // Update only the stock column for the data row with a specific ID
  await (db.update(db.products)..where((p) => p.id.equals(id)))
      .write(
        ProductsCompanion(
          stock: Value(newStock),
        ),
      );
}

// 5. DELETE DATA
Future<void> deleteProductById(AppDatabase db, int id) async {
  await (db.delete(db.products)..where((p) => p.id.equals(id))).go();
}

Reactive Queries: Leveraging watch() #

In Drift, you can easily convert regular read queries (get()) into reactive queries (watch()). Reactive queries return a Dart Stream object. Every time data modification occurs (whether through insert, update, or delete operations) on tables read by that query, the Stream automatically emits the latest data.

Here’s an example implementation in the UI using StreamBuilder:

// lib/features/inventory/presentation/widgets/product_stream_list.dart

import 'package:flutter/material.dart';
import '../../core/database/app_database.dart';
import '../../../../main.dart'; // Access the global database instance

class ProductStreamList extends StatelessWidget {
  const ProductStreamList({super.key});

  // Reactive query to monitor all products with nearly empty stock (< 5)
  Stream<List<Product>> _watchLowStockProducts() {
    return (database.select(database.products)
          ..where((p) => p.stock.lessThan(5)))
        .watch();
  }

  @override
  Widget build(BuildContext context) {
    return StreamBuilder<List<Product>>(
      stream: _watchLowStockProducts(),
      builder: (context, snapshot) {
        if (snapshot.connectionState == ConnectionState.waiting) {
          return const Center(child: CircularProgressIndicator());
        }
        if (snapshot.hasError) {
          return Center(child: Text('Error: ${snapshot.error}'));
        }

        final products = snapshot.data ?? [];
        return ListView.builder(
          itemCount: products.length,
          itemBuilder: (context, index) {
            final product = products[index];
            return ListTile(
              title: Text(product.name),
              trailing: Text('Remaining Stock: ${product.stock}'),
            );
          },
        );
      },
    );
  }
}

Your UI is now fully synchronized with the database condition. If there’s a background synchronization updating product stock from the API server, your user interface updates instantly without needing to manually trigger page reloads.

Multi-Table Search (JOIN) #

In relational databases, you often need to display combined information from several tables. For example, you want to display product names along with their product category names. Drift provides a declarative API simplifying JOIN operations.

Let’s see how to create a combined query safely using Drift:

// lib/core/database/models/product_with_category.dart

import '../app_database.dart';

// Container class for joined data results
class ProductWithCategory {
  final Product product;
  final Category category;

  ProductWithCategory({required this.product, required this.category});
}

Here’s the query to perform an innerJoin between the Products and Categories tables:

Future<List<ProductWithCategory>> getProductsWithCategory(AppDatabase db) async {
  // 1. Join the parent table to the relation table
  final query = db.select(db.products).join([
    innerJoin(
      db.categories,
      db.categories.id.equalsExp(db.products.categoryId),
    ),
  ]);

  // 2. Execute the query
  final List<TypedResult> rows = await query.get();

  // 3. Map the raw SQLite data rows into our custom Dart objects
  return rows.map((row) {
    return ProductWithCategory(
      product: row.readTable(db.products),
      category: row.readTable(db.categories),
    );
  }).toList();
}

The row.readTable(...) method intelligently parses the joined result column rows and reassembles them into each table’s data class objects automatically.

Tidying Code with the DAO Pattern #

If all your queries are written directly inside the main AppDatabase class, that file will quickly bloat and become hard to maintain. Drift provides the DAO (Data Access Object) pattern to separate query logic based on specific business domains (like ProductDao, OrderDao, etc.).

Here’s how to separate product queries into a DAO:

// lib/core/database/daos/product_dao.dart

import 'package:drift/drift.dart';
import '../app_database.dart';
import '../tables.dart';

// DAO helper file generation
part 'product_dao.g.dart';

@DriftAccessor(tables: [Products, Categories])
class ProductDao extends DatabaseAccessor<AppDatabase> with _$ProductDaoMixin {
  ProductDao(super.db);

  // All product query logic is centralized here
  Future<List<Product>> getAllProducts() => select(products).get();

  Stream<List<Product>> watchAvailableProducts() {
    return (select(products)..where((p) => p.isAvailable.equals(true))).watch();
  }

  Future<int> addProduct(ProductsCompanion entry) => into(products).insert(entry);

  Future<bool> updateProduct(ProductsCompanion entry) {
    return (update(products)..where((p) => p.id.equals(entry.id.value)))
        .write(entry)
        .then((rowsAffected) => rowsAffected > 0);
  }

  Future<void> deleteProduct(int id) {
    return (delete(products)..where((p) => p.id.equals(id))).go();
  }
}

// Add to the main AppDatabase
@DriftDatabase(
  tables: [Categories, Products, Orders, OrderItems],
  daos: [ProductDao], // Register the DAO here
)
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());

  // Getter to access the DAO from outside instantly
  ProductDao get productDao => ProductDao(this);

  @override
  int get schemaVersion => 1;
}

Through the DAO pattern, your database code structure becomes modular. Writing unit tests also becomes easier because you can test DAO functions in isolation using an in-memory SQLite database during testing.

State Management Integration (Riverpod & BLoC) #

The final step to perfecting Drift’s implementation in your app is integrating it with the state management provider.

Riverpod Integration #

You provide one single AppDatabase instance, provide its DAO classes, then monitor reactive data using StreamProvider.

// lib/core/providers/database_provider.dart

import 'package:flutter_riverpod/flutter_riverpod.dart';
import '../database/app_database.dart';
import '../database/daos/product_dao.dart';

// Provider for the main database instance
final databaseProvider = Provider<AppDatabase>((ref) {
  final db = AppDatabase();
  // Ensure the database is closed when the provider is destroyed
  ref.onDispose(() => db.close());
  return db;
});

// Provider for ProductDao
final productDaoProvider = Provider<ProductDao>((ref) {
  return ref.watch(databaseProvider).productDao;
});

// StreamProvider to monitor products reactively in the UI
final availableProductsProvider = StreamProvider<List<Product>>((ref) {
  final productDao = ref.watch(productDaoProvider);
  return productDao.watchAvailableProducts();
});

In UI widgets, you just use ref.watch(availableProductsProvider) to get an AsyncValue<List<Product>> object ready for consumption by cleanly handling the loading, error, and data conditions.

Summary #

Drift is a robust ORM library for SQLite in Dart, providing a declarative table writing system and compile-time query verification (compile-time type safety).
Tables & Columns: Define tables by inheriting the Table class and use chained builders to compose detailed column specifications.
Companion Classes: Use Companions for write and update operations to distinguish between absent values, explicit null values, or new values.
Built-in Reactivity: Just use the watch() method to convert regular read queries into reactive Stream flows that automatically update the UI when the database changes.
Type-Safe JOINs: Connect several tables with the join() method and parse the results using row.readTable() to keep data types safe.
DAO Pattern: Group database queries by business domain into DAO classes to keep the codebase modular, clean, and easy to test.
Migration Strategy: Manage database structure updates through the MigrationStrategy class by identifying version increments in onUpgrade to keep user data intact.
Web Support: Drift fully supports Flutter Web using SQLite WASM compilation, making it the best multi-platform relational database choice.

← Previous: ObjectBox Next: Best Practice →