Can't get Riverpod Stream to emit a new value

Question

I have a simple widget that listens to expenses recorded for the current month. This is set up with the Riverpod and Drift packages in Flutter. The data pattern I'm using is Provider > Repository > DAO.

However for some reason, when I add or delete transactions matching the current month in the local database (verified by looking at the sqlite table), the widget does not update with the latest expenses to date value. It seems as though no new value was emitted by the Stream. I've been trying to debug this for ages but can't figure out what's wrong.

Expected behaviour:

1. Record in transactions table is modified (added or deleted)
2. expensesToDateProvider emits a new value
3. Widget rebuilds to reflect the new value

// Widget to display latest expenseToDate
class DebugExpensesWidget extends ConsumerWidget {
  const DebugExpensesWidget({super.key});

  @override
  Widget build(BuildContext context, WidgetRef ref) {
    final expensesAsyncValue =
        ref.watch(expensesToDateProvider(budgetDate: DateTime(DateTime.now().year, DateTime.now().month)));

    return expensesAsyncValue.when(
      data: (data) {
        return Text('Expenses to date: $data');
      },
      loading: () => CircularProgressIndicator(),
      error: (error, _) {
        return Text('Error: $error');
      },
    );
  }
}

// Provider that watches spentToDate for the month
@riverpod
Stream<double> expensesToDate(ExpensesToDateRef ref, {required DateTime budgetDate}) {
  return ref.watch(budgetsRepositoryProvider).getExpensesToDate(budgetDate);
}

// Repository to call the DAO to return total expenses to date based on a given budgetDate
Stream<double> getExpensesToDate(DateTime budgetDate) {
  return database.appDatabaseDao.calculateExpensesToDateDao(budgetDate.year, budgetDate.month);
  }

  // DAO to calculate Total Expenses to date based on a given month and year
  Stream<double> calculateExpensesToDateDao(int year, int month) {
    final yearStr = year.toString();
    final monthStr = month.toString().padLeft(2, '0');

    const sql = '''
      SELECT SUM(amount) as total
      FROM transactions
      WHERE is_income = 0
      AND strftime('%m', datetime(transaction_date, 'unixepoch')) = ?
      AND strftime('%Y', datetime(transaction_date, 'unixepoch')) = ?
    ''';

    return customSelect(sql, variables: [
      Variable.withString(monthStr),
      Variable.withString(yearStr),
    ]).watch().map((rows) {
      final row = rows.first;
      final total = row.read<double?>('total') ?? 0.0;
      return total;
    });
  }

Answer 1

You need to specify readsFrom when using customSelect with watch in Drift. From the docs:

For an auto-updating query stream, the readsFrom parameter needs to be set to the tables the SQL statement reads from - drift can't infer it automatically like for other queries constructed with its Dart API.

Assuming the transactions table is available on your DAO, the following should make the stream update properly:

return customSelect(
  sql,
  variables: [
    Variable.withString(monthStr),
    Variable.withString(yearStr),
  ],
  readsFrom: {transactions},
).watch().map((rows) {
  // ...