How to handle state and database in Flutter Riverpod AsyncNotifier

In my app I have a ListView the shows some items. The list is received from a local database (sqlite/drift) and the according state is managed in an Riverpod AsyncNotifierProvider. The AsyncNotifierProvider uses an AsyncNotifier class which provides a method that allows the user to reorder the items in the list (change position).

However my problem and question here is: I have to manage the state (Riverpod) as well as the same information in the database (cause the position of the items has to be persisted). Now I´ve two options how to handle this:

1. Update the state and then update the database 2. Update the database and refresh the state from the database after the update

Option 1:

/// Changes the order of the list items
Future<void> changeOrder(ListModel list, int oldIndex, int newIndex) async {
  
  // Change the position in the List<ListModel> which is stored in the state
  final previousState = state.valueOrNull;
  if (previousState != null) {
    final newState = [...previousState];
    if (oldIndex < newIndex) {
      newIndex -= 1;
    }
    final item = newState.removeAt(oldIndex);
    newState.insert(newIndex, item);
    state = AsyncValue.data(newState);
  }

  // Change the position in the database
  await _repo.changeOrder(list, oldIndex, newIndex);
}

Option 2:

/// Changes the order of the list items
Future<void> changeOrder(ListModel list, int oldIndex, int newIndex) async {

  // Change the position in the database
  await _repo.changeOrder(list, oldIndex, newIndex);

  // Fetch the new state from the database
  state = AsyncValue.data(await _repo.getAll());
}

Option 2 ensures that database and state are in sync but it needs more performacne (additional query to fetch the data) and I´m struggling with displaying the changes (cause the items jump back to the original position till the state from the database is refreshed -> leads to an ugly "flickering").

Which option is the way to go? Or is there another alternative I don´t know yet?

Solution

The second option is preferable if the data is important to you. It provides a single source of truth. You might consider having the changeOrder query return the data (maybe this is possible in your case):

final newData = await _repo.changeOrder(list, oldIndex, newIndex);

state = AsyncValue.data(newData);

To keep the interface from flickering, consider using ReorderableListView.

https://api.flutter.dev/flutter/material/ReorderableListView-class.html