Flutter: Using sqflite with riverpod using clean approach - Part 1
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.