Migrating from Hive to Drift (SQLite) for Complex Relational Data in Flutter

When launching a new Flutter application, speed-to-market is everything. In the early stages, choosing a lightweight, key-value storage solution like Hive or Shared Preferences is standard practice. It is easy to set up, requires zero SQL knowledge, and works perfectly for simple local key-value stores.

However, as applications scale and introduce relational features—such as user accounts, group splits, shared balances, transactions, and synchronization queues—flat key-value data structures quickly become an architectural bottleneck.

During the development of Paisa Track (our production-grade personal finance manager), we reached the limits of Hive and executed a complete migration to Drift (a powerful, reactive SQLite library for Dart).

In this technical guide, we will break down why we made the switch, the architectural differences between Hive and Drift, and how to execute safe schema migrations without losing local offline user data.

1. The Bottleneck: Hive's Key-Value Limitations

In the early versions of our local expense module, Hive handled expenses and categories using simple flat boxes:

// The simple Hive model
@HiveType(typeId: 0)
class HiveExpense extends HiveObject {
  @HiveField(0)
  late String id;
  
  @HiveField(1)
  late double amount;
  
  @HiveField(2)
  late String categoryName; // Category relations as plain text strings
}

This works fine until you introduce complex relational business logic. In Paisa Track, an expense is not just a float and a label. An expense can:

• Belong to a Group or a Trip.
• Be paid by a specific Group Member.
• Be split among multiple Expense Splits (equal, percentage, or custom amounts).
• Be linked to an Investment or a Loan.

To do this in Hive, you have to load all boxes into memory, manually map IDs in Dart, and write custom filters. This is slow, error-prone, and prone to memory leaks when database size grows.

Furthermore, Hive does not support SQL features like CASCADE deletes, JOINs, or transactional safety.

2. The Solution: Drift (SQLite) for Relational Integrity

Drift is a reactive persistence library for Flutter and Dart. Under the hood, it uses native SQLite, giving you the full power of a relational database while letting you write queries in clean, type-safe Dart.

Here is how we defined our core relational tables using Drift for Paisa Track:

import 'package:drift/drift.dart';

class Expenses extends Table {
  TextColumn get id => text()();
  TextColumn get serverId => text().nullable()();
  TextColumn get title => text()();
  RealColumn get amount => real()();
  TextColumn get currency => text()();
  TextColumn get categoryId => text().nullable().references(Categories, #id)();
  
  // Relations
  TextColumn get groupId => text().nullable().references(Groups, #id, onDelete: KeyAction.cascade)();
  TextColumn get paidBy => text().nullable()(); // User ID who paid
  TextColumn get splitMethod => text().withDefault(const Constant('equal'))(); // equal, percentage, custom
  
  // Sync Meta
  TextColumn get syncStatus => text().withDefault(const Constant('pending'))(); // pending, synced
  DateTimeColumn get updatedAt => dateTime().withDefault(currentDateAndTime)();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();

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

By defining relational tables, SQLite handles integrity natively. For instance, if a user deletes a Group, Drift's onDelete: KeyAction.cascade automatically purges all child Expenses and Expense Splits locally, keeping the device's storage clean.

3. Designing a Safe SQLite Schema Migration

One of the biggest challenges when moving from version to version on production devices is schema migration. If you modify a table (e.g., adding a column or changing a constraint) and a user updates their app, the database will crash unless you guide SQLite through the change.

In Paisa Track, we handle this using Drift's MigrationStrategy. Here is our production-grade structure for handling safe incremental upgrades:

@DriftDatabase(tables: [Expenses, Groups, Categories])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());

  @override
  int get schemaVersion => 30; // Managed version control

  @override
  MigrationStrategy get migration => MigrationStrategy(
    onCreate: (Migrator m) async {
      await m.createAll();
    },
    onUpgrade: (Migrator m, int from, int to) async {
      if (from < 2) {
        // Upgrade v1 to v2: Add Groups table
        await m.createTable(groups);
      }
      if (from < 5) {
        // Upgrade v2 to v5: Add groupId column to expenses
        try {
          await m.addColumn(expenses, expenses.groupId);
        } catch (_) {
          // Gracefully ignore if column already exists due to hot-fixes
        }
      }
      if (from < 30) {
        // Advanced SQLite Migration: Re-create table to change constraints
        // SQLite doesn't allow dropping constraints directly on active tables.
        // We must perform a rename-create-copy-drop cycle:
        
        // 1. Rename existing table to backup
        await customStatement('ALTER TABLE group_members RENAME TO group_members_old;');
        
        // 2. Create the new table with correct constraints
        await m.createTable(groupMembers);
        
        // 3. Migrate local user data safely
        await customStatement('''
          INSERT INTO group_members (id, group_id, user_id, name, email, role, sync_status, updated_at)
          SELECT id, group_id, user_id, name, email, role, sync_status, updated_at
          FROM group_members_old;
        ''');
        
        // 4. Drop the old backup table
        await customStatement('DROP TABLE group_members_old;');
      }
    },
  );
}

Key Takeaway for Production Upgrades:

Since SQLite does not support advanced statements like ALTER TABLE DROP CONSTRAINT or MODIFY COLUMN, renaming the old table, creating a new one, migrating data, and dropping the old table is the only secure way to change relational constraints without corrupting the local file.

4. The Result: A 10x Performance Leap

By migrating to Drift, we achieved:

1. Instant Reactive UI: Drift uses Dart Streams natively. The moment an expense is logged or synced in a background task, the UI automatically updates without forcing a manual redraw or reloading boxes.
2. Simplified Backend Sync: Since Drift models mirror SQL schemas, writing sync logic to talk to our FastAPI and PostgreSQL backend was simplified into mapping identical SQL data models.
3. Robust Data Integrity: No more orphaned expenses or miscalculated group balances. SQLite guarantees transactions are atomic.

Need Help Scaling Your Flutter Application's Architecture?

At ScoRpii Tech, we build reliable, production-ready software systems. Whether you are transitioning an early MVP to a scalable relational database, building zero-latency offline-first applications, or setting up complex cloud sync architectures, our engineers have the experience to deliver.

Try the ScoRpii Tech App Cost Calculator to estimate your next Flutter development project, or Contact Us for a Technical Consultation.