A year a go I wrote a post where I migrated my code from provider to riverpod. It has been about 2 years I've been code in flutter and I've learn a lot. What I can say a bout my old post is that it is bad code, don't follow it.

In this article, I want to share a better code from my previous with a better project structure. This structure are also higly testable as long as flutter test framework support as I use it in most of my project now that follow TDD approach.

This how the project structure are going to look like, at least partially.

.
└── lib/
    ├── src/
    │   ├── models/
    │   │   └── todo.dart
    │   ├── database/
    │   │   └── app_database.dart
    │   ├── repositories/
    │   │   ├── repository.dart
    │   │   └── todo_repository.dart
    │   └── app.dart
    └── main.dart

Packages

flutter pub add sqflite flutter_riverpod path freezed_annotation json_annotation
flutter pub add --dev build_runner freezed json_serializable

Model

Let's define a simple model to work with since we need to deal with database and we need some data to store. But first we need to add the dependency for this which I'm using freezed in this case. But the standard json_serializable works fine if you don't need equality override and the copyWith method. Since sqlite store boolean value as integer of 0 (false) or 1 (true) we need to explicity define a different transformer for boolean field.

// ignore_for_file: invalid_annotation_target

import 'package:freezed_annotation/freezed_annotation.dart';
import 'package:flutter/foundation.dart';

part 'todo.freezed.dart';
part 'todo.g.dart';

bool _boolFromJson(int value) => value.isOdd;
int _boolToJson(bool value) => value ? 1 : 0;

@freezed
class Todo with _$Todo {
  const factory Todo({
    required int id,
    required String title,
    @JsonKey(fromJson: _boolFromJson, toJson: _boolToJson) required bool done,
  }) = _Todo;

  factory Todo.fromJson(Map<String, Object?> json) => _$TodoFromJson(json);
}

Schema

This how our table schema going to like. The id will be alias to the rowid. This is helpful to speed up the database locally. If you need a differend uid to identify the item that need to be sync with a server, I sugggest to add a different column called uid instead and use some kind of base64 or any uid implementation and do not remove the id to be use locally.

CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0);

Database

Now that we have a data to work with, we can start define how the database will look like. From the official sqflite structure, it's not very clean but it serve as a well understand example.

Since sqflite is quite low level we have to define our own database class. Let's call it AppDatabase. And let's define the provider as well while we add it. We don't need to add Provider to very provider name. Sometimes provider act as state provider, sometimes it is a singleton object, so the naming better to suit the behavior.

I'm going to create this file under lib/src/database/app_database.dart.

import 'package:flutter_riverpod/flutter_riverpod.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart' as path;
import 'package:sqflite_riverpod_example/repositories/todos_repository.dart';

const dbFilename = 'todo_app.db';

// provider singleton database object throughout the app lifetime
final appDatabase = Provider<AppDatabase>((ref) => throw UnimplementedError());

class AppDatabase {
  AppDatabase(this._database);

  final Database _database;

  Future<int> insert(String tableName, Map<String, Object> placeData) async {
    return _database.insert(tableName, placeData);
  }

  Future<int> update(String tableName, Map<String, Object?> placeData) async {
    return _database.update(tableName, placeData);
  }

  Future<List<Map<String, Object?>>> selectAll(
    String tableName,
  ) async {
    return _database.query(tableName);
  }

  Future<List<Map<String, Object?>>> select(
    String tableName,
    int id,
  ) async {
    return _database.query(tableName, where: 'id = ?', whereArgs: [id]);
  }

  Future<int> delete(
    String tableName,
    int id,
  ) async {
    return _database.delete(tableName, where: 'id = ?', whereArgs: [id]);
  }
}

But wait where is the database ? Since the database is a feature it can introduce a race condition if we define in an async init method. Plus it is better we put the dependency out from the database class, so we can easily switch or test it with a mock database.

Future<Database> createDatabase() async {
  final dbFolder = await getDatabasesPath();
  final dbPath = path.join(dbFolder, dbFilename);

  return openDatabase(
    version: 1,
    dbPath,
    onCreate: (db, version) async {
      await _createAll();
    },
  );
}

// this should be run when the database is being created
Future<void> _createAll(Database db) async {
  // we will work on this next
  await db.execute(TodosRepository.createTableStmt);
}

Now let's plugged it in the database in the main method. With this we have access to the same database instance throughout the app without having to check if the database is already defined or not.

// lib/main.dart

void main() async {
  // this is important to make sure flutter binding is initialized before runApp
  WidgetsFlutterBinding.ensureInitialized();
  // initialize database
  final db = await createDatabase();

  final databaseProvider = Provider<AppDatabase>((ref) {
    ref.onDispose(() {
      db.close();
    });

    return AppDatabase(db);
  });

  runApp(ProviderScope(
    overrides: [
      appDatabase.overrideWithProvider(databaseProvider),
    ],
    child: const MyApp(),
  ));
}

Repository

It is important to separate the database and the repository for a cleaner code. This mean we can switch the database or use an api and we only need to change the repository which is the data layer. The role of repository here is to interface with the data source and transform the data as needed.

First we need to define base repository which will define all the base method so that all our repository will share the same interface. I'm going to create this file under lib/src/repositories/repository.dart.

abstract class Repository {
  Future create(Map<String, Object> placeData) async {}

  Future update(Map<String, Object?> placeData) async {}

  Future getSingle(int id) async {}

  Future getAll() async {}

  Future delete(int id) async {}
}

In the todos repository, we define required providers and the TodoRepository class. I'm going to define everything about the table as well in this repository which located at lib/src/repositories/todo_repository.dart.

import 'package:flutter_riverpod/flutter_riverpod.dart';
import 'package:sqflite_riverpod_example/database/app_database.dart';
import 'package:sqflite_riverpod_example/models/todo.dart';
import 'package:sqflite_riverpod_example/repositories/repository.dart';

// We use this to utilize riverpod AsyncValue
final todosProvider =
    FutureProvider((ref) => ref.watch(todosRepository).getAll());

// This is the repository provider or a singleton
final todosRepository = Provider((ref) {
  final db = ref.watch(appDatabase);

  return TodoRepository(db);
});

class TodoRepository implements Repository {
  TodoRepository(this.db);

  final AppDatabase db;

  static const tableName = 'todos';

  // we call this in the the databaseCreate method
  static const createTableStmt =
      'CREATE TABLE IF NOT EXISTS $tableName (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0);';

  @override
  Future<int> create(Map<String, Object> placeData) async {
    return db.insert(tableName, placeData);
  }

  @override
  Future<int> update(Map<String, Object?> placeData) async {
    return db.update(tableName, placeData);
  }

  @override
  Future<Todo?> getSingle(int id) async {
    final raw = (await db.select(tableName, id));

    if (raw.isEmpty) return null;
    return Todo.fromJson(raw.first);
  }

  @override
  Future<List<Todo>> getAll() async {
    final List<Map<String, Object?>> raw = await db.selectAll(tableName);

    return raw.map((item) => Todo.fromJson(item)).toList();
  }

  @override
  Future<int> delete(int id) async {
    return db.delete(tableName, id);
  }
}

Using the database

We define a global database provider that will default will throw an exception. Then we override it with an actual provider that defined when the app start. This allow us to explicitly close the databse. But from the sqflite documentation, it's not really necessary since the database connection will close when the app is terminated, so what this mean we can also use overrideWithValue instead of overrideWithProvider, but it's up to you. For me I like to explicitly close the databse.

void main() async {
  // this is important to make sure flutter binding is initialized before runApp
  WidgetsFlutterBinding.ensureInitialized();
  // initialize database
  final db = await createDatabase();

  final databaseProvider = Provider<Database>((ref) {
    ref.onDispose(() {
      db.close();
    });

    return db;
  });

  runApp(ProviderScope(
    overrides: [
      appDatabase.overrideWithProvider(databaseProvider),
    ],
    child: const MyApp(),
  ));
}

Now we can easily use the repository throughout the app.

class MyApp extends ConsumerWidget {
  const MyApp({Key? key}) : super(key: key);
  @override
  Widget build(BuildContext context, WidgetRef ref) {
    return MaterialApp(
      title: 'Todo App',
      home: Scaffold(
        appBar: AppBar(
          title: const Text('Todo App'),
        ),
        floatingActionButton: FloatingActionButton(
            child: const Icon(Icons.add),
            onPressed: () {
              final rand = Random.secure();
              ref
                  .read(todosRepository)
                  .create({'title': 'item ${rand.nextInt(100)}', 'done': 0});
              // this will trigger a rebuild in widget that listen to the todosProvider
              ref.refresh(todosProvider);
            }),
        body: Consumer(
          builder: (context, ref, child) {
            final todos = ref.watch(todosProvider);

            return todos.when(
              data: (data) => ListView.builder(
                itemCount: data.length,
                itemBuilder: (context, index) =>
                    ListTile(title: Text(data[index].title)),
              ),
              error: (error, stackTrace) {
                return Center(
                  child: Text(error.toString()),
                );
              },
              loading: () => const CircularProgressIndicator(),
            );
          },
        ),
      ),
    );
  }
}

Next step, we are going to see how to test this simple app in flutter. Reach me out if you need any help or question.