Offline-first Mode With Drift - Suggested doc improvements

[Musta-Pollo]

It would be a good idea to update documenation with handling this scenario. Here is AI generated info:

Drift Watch Streams with PowerSync Optional Sync Mode

The Problem

When using Drift with PowerSync's optional sync mode (local-only tables with viewName override), Drift's watch() streams don't receive table update notifications. This causes watched queries to never re-execute when data changes.

Why This Happens

PowerSync's optional sync mode uses a pattern where local-only tables have different internal names than their user-facing view names:

// In PowerSync schema
Table.localOnly(
  'local_items',    // Internal table name
  [...columns],
  viewName: 'items', // User-facing view name (different!)
)

This creates a table name mismatch in the update notification flow:

1. SQLite stores data in physical table: ps_data_local__local_items
2. PowerSync converts to friendly name: "local_items"
3. SqliteAsyncDriftConnection receives notification: {"local_items"}
4. Drift is listening for updates to: "items" (the viewName)
5. "local_items" ≠ "items" → NO MATCH → Watch stream never updates!

Why Synced Mode Works

In synced mode, the table name and view name are the same:

Table(
  'items',        // Internal table name
  [...columns],
  viewName: 'items', // Same as table name
)

So notifications arrive as "items" and Drift is listening for "items" → MATCH!

---

The Solution

Use the transformTableUpdates parameter in SqliteAsyncDriftConnection to convert the internal table names to their corresponding view names:

import 'package:drift/drift.dart' show TableUpdate;
import 'package:drift_sqlite_async/drift_sqlite_async.dart';

final connection = SqliteAsyncDriftConnection(
  powerSyncDatabase,
  transformTableUpdates: (notification) {
    // Convert local_* table names to their viewName equivalents
    // e.g., local_items → items, local_labels → labels
    return notification.tables.map((tableName) {
      if (tableName.startsWith('local_')) {
        // Remove 'local_' prefix to match the viewName
        return TableUpdate(tableName.substring(6));
      }
      return TableUpdate(tableName);
    }).toSet();
  },
);

final database = AppDatabase(connection);

How It Works

1. Notification arrives: {"local_items"}
2. transformTableUpdates converts: "local_items" → "items"
3. Drift receives: {"items"}
4. Drift is listening for: {"items"}
5. MATCH! → Watch stream re-executes query

---

Complete Example with PowerSync Optional Sync

// schema.dart - PowerSync schema with optional sync
Schema makeSchema({required bool synced}) {
  String localViewName(String table) {
    return synced ? 'inactive_local_$table' : table;
  }

  return Schema([
    // Local-only tables with viewName override
    Table.localOnly(
      'local_items',
      [...columns],
      viewName: localViewName('items'), // "items" when synced=false
    ),
    Table.localOnly(
      'local_labels',
      [...columns],
      viewName: localViewName('labels'),
    ),
    // ... more tables
  ]);
}

// database_providers.dart - Drift connection with fix
final driftDatabaseProvider = Provider<AppDatabase>((ref) {
  final powerSync = ref.watch(powerSyncInstanceProvider);

  return AppDatabase(
    SqliteAsyncDriftConnection(
      powerSync,
      transformTableUpdates: (notification) {
        return notification.tables.map((tableName) {
          if (tableName.startsWith('local_')) {
            return TableUpdate(tableName.substring(6));
          }
          return TableUpdate(tableName);
        }).toSet();
      },
    ),
  );
});

---

Key Takeaways

Scenario	Table Name	View Name	Notification	Drift Expects	Result
Synced mode	items	items	items	items	✅ Works
Local-only (no fix)	local_items	items	local_items	items	❌ Fails
Local-only (with fix)	local_items	items	items (transformed)	items	✅ Works

The transformTableUpdates parameter exists specifically for this use case: mapping internal table names to user-facing names when they differ.

[Musta-Pollo]

This solution works for me, its more for others and to help this project.

Thank you very much for your work!