15.
Saving Data With SQLite
Written by Kevin D Moore
Heads up... You’re accessing parts of this content for free, with some sections shown as text.
Heads up... You’re accessing parts of this content for free, with some sections shown as text.
Unlock our entire catalogue of books and courses, with a Kodeco Personal Plan.
Unlock now
So far, you have a great app that can search the internet for recipes, bookmark the ones you want to make and show a list of ingredients to buy at the store. But what happens if you close the app, go to the store and try to look up your ingredients? They’re gone! As you might have guessed, having an in-memory repository means that the data doesn’t persist after your app closes.
One of the best ways to persist data is with a database. Both Android and iOS provide access to the SQLite database system. This allows you to insert, read, update and remove structured data that are persisted on disk.
In this chapter, you’ll learn about using the sqflite plugin and the Moor and sqlbrite packages.
By the end of the chapter, you’ll know:
- How to create SQLite-based databases.
- How to insert, fetch and remove recipes or ingredients.
- How to use the sqflite plugin.
- How to use the sqlbrite library and receive updates via streams.
- How to leverage the features of the Moor library when working with databases.
Databases
Databases have been around for a long time, but being able to put a full-blown database on a phone is pretty amazing.
What is a database? Think of it like a file cabinet that contains folders with sheets of paper in them. A database has tables (file folders) that store data (sheets of paper).
Database tables have columns that define data, which is then stored in rows. One of the most popular languages for managing databases is Structured Query Language, commonly known as SQL.
You use SQL commands to get the data in and out of the database. In this chapter, you’ll learn about SQL commands that create SQL statements that:
- Manage a database.
- Manage data in the database’s tables.
Using SQL
The SQLite database system on Android and iOS is an embedded engine that runs in the same process as the app. SQLite is lightweight, taking up less than 500 Kb on most systems.
Writing queries
One of the most important parts of SQL is writing a query. To make a query, use the SELECT command followed by any columns you want the database to return, then the table name. For example:
// 1
SELECT name, address FROM Customers;
// 2
SELECT * FROM Customers;
// 3
SELECT name, address FROM Customers WHERE name LIKE 'A%';
Adding data
You can add data using the INSERT statement:
INSERT INTO Customers (NAME, ADDRESS) VALUES (value1, value2);
Deleting data
To delete data, use the DELETE statement:
DELETE FROM Customers WHERE id = 1;
Updating data
You use UPDATE to update your data. You won’t need this command for this app, but for reference, the syntax is:
UPDATE customers
SET
phone = '555-12345',
WHERE id = 1;
sqflite
The sqflite plugin provides SQLite database access on iOS, Android and macOS. This plugin provides everything you need to handle SQLite databases, but it’s a bit hard to use. Later, you’ll use the Moor package, which makes things easier. First, however, it’s important to learn how to use the underlying plugin.
CREATE TABLE mytable (
id INTEGER PRIMARY KEY,
name TEXT,
value INTEGER,
NUM REAL
);
sqlbrite
The sqlbrite library is a reactive stream wrapper around sqflite. It allows you to set up streams so you can receive events when there’s a change in your database. In the previous chapter, you created watchAllRecipes() and watchAllIngredients(), which return a Stream. To create these streams from a database, sqlbrite uses watch() methods.
Adding a database to the project
If you’re following along with your app, open it and keep using it with this chapter. If not, locate the projects folder for this chapter and open the starter folder.
Adding sqflite
To use the sqflite plugin, open pubspec.yaml and add the following packages after the equatable package:
sqflite: ^2.0.3+1
path_provider: ^2.0.11
synchronized: ^3.0.0+3
sqlbrite: ^2.3.0
path: ^1.8.2
Setting up the database
In the data folder, create a new folder named sqlite. Inside that folder, create a new file called database_helper.dart. This class will handle all the SQLite database operations.
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqlbrite/sqlbrite.dart';
import 'package:synchronized/synchronized.dart';
import '../models/models.dart';
class DatabaseHelper {
}
// 1
static const _databaseName = 'MyRecipes.db';
static const _databaseVersion = 1;
// 2
static const recipeTable = 'Recipe';
static const ingredientTable = 'Ingredient';
static const recipeId = 'recipeId';
static const ingredientId = 'ingredientId';
// 3
static late BriteDatabase _streamDatabase;
// make this a singleton class
// 4
DatabaseHelper._privateConstructor();
static final DatabaseHelper instance = DatabaseHelper._privateConstructor();
// 5
static var lock = Lock();
// only have a single app-wide reference to the database
// 6
static Database? _database;
// TODO: Add create database code here
Creating tables
One of the most important parts of creating a database is creating the tables. Your app will have two tables: recipeTable and ingredientTable.
// SQL code to create the database table
// 1
Future _onCreate(Database db, int version) async {
// 2
await db.execute('''
CREATE TABLE $recipeTable (
$recipeId INTEGER PRIMARY KEY,
label TEXT,
image TEXT,
url TEXT,
calories REAL,
totalWeight REAL,
totalTime REAL
)
''');
// 3
await db.execute('''
CREATE TABLE $ingredientTable (
$ingredientId INTEGER PRIMARY KEY,
$recipeId INTEGER,
name TEXT,
weight REAL
)
''');
}
// TODO: Add code to open database
Opening the database
Before you can use the database, you have to open it. _initDatabase() uses sqflite’s openDatabase(). Such method requires a path where it should create the database, the current database version and a “create” method name.
// this opens the database (and creates it if it doesn't exist)
// 1
Future<Database> _initDatabase() async {
// 2
final documentsDirectory = await getApplicationDocumentsDirectory();
// 3
final path = join(
documentsDirectory.path,
_databaseName,
);
// 4
// TODO: Remember to turn off debugging before deploying app to store(s).
Sqflite.setDebugModeOn(true);
// 5
return openDatabase(
path,
version: _databaseVersion,
onCreate: _onCreate,
);
}
// TODO: Add initialize getter here
// 1
Future<Database> get database async {
// 2
if (_database != null) return _database!;
// Use this object to prevent concurrent access to data
// 3
await lock.synchronized(() async {
// lazily instantiate the db the first time it is accessed
// 4
if (_database == null) {
// 5
_database = await _initDatabase();
// 6
_streamDatabase = BriteDatabase(_database!);
}
});
return _database!;
}
// TODO: Add getter for streamDatabase
// 1
Future<BriteDatabase> get streamDatabase async {
// 2
await database;
return _streamDatabase;
}
// TODO: Add parseRecipes here
Converting data to classes
To convert the stored map of data from the database to classes, you’ll need to add fromJson() and toJson() to the model classes.
// Create a Recipe from JSON data
factory Recipe.fromJson(Map<String, dynamic> json) => Recipe(
id: json['recipeId'],
label: json['label'],
image: json['image'],
url: json['url'],
calories: json['calories'],
totalWeight: json['totalWeight'],
totalTime: json['totalTime'],
);
// Convert our Recipe to JSON to make it easier when you store
// it in the database
Map<String, dynamic> toJson() => {
'recipeId': id,
'label': label,
'image': image,
'url': url,
'calories': calories,
'totalWeight': totalWeight,
'totalTime': totalTime,
};
// Create a Ingredient from JSON data
factory Ingredient.fromJson(Map<String, dynamic> json) => Ingredient(
id: json['ingredientId'],
recipeId: json['recipeId'],
name: json['name'],
weight: json['weight'],
);
// Convert our Ingredient to JSON to make it easier when you
// store it in the database
Map<String, dynamic> toJson() => {
'ingredientId': id,
'recipeId': recipeId,
'name': name,
'weight': weight,
};
List<Recipe> parseRecipes(List<Map<String, dynamic>> recipeList) {
final recipes = <Recipe>[];
// 1
for (final recipeMap in recipeList) {
// 2
final recipe = Recipe.fromJson(recipeMap);
// 3
recipes.add(recipe);
}
// 4
return recipes;
}
List<Ingredient> parseIngredients(List<Map<String, dynamic>> ingredientList) {
final ingredients = <Ingredient>[];
for (final ingredientMap in ingredientList) {
// 5
final ingredient = Ingredient.fromJson(ingredientMap);
ingredients.add(ingredient);
}
return ingredients;
}
// TODO: Add findAppRecipes here
Implementing repository-like functions
Your next step is to create functions that return the information that the repository expects. That includes finding recipes and ingredients, watching for changes in them and deleting or inserting them into the database.
Future<List<Recipe>> findAllRecipes() async {
// 1
final db = await instance.streamDatabase;
// 2
final recipeList = await db.query(recipeTable);
// 3
final recipes = parseRecipes(recipeList);
return recipes;
}
// TODO: Add watchAllRecipes() here
Stream<List<Recipe>> watchAllRecipes() async* {
final db = await instance.streamDatabase;
// 1
yield* db
// 2
.createQuery(recipeTable)
// 3
.mapToList((row) => Recipe.fromJson(row));
}
// TODO: Add watchAllIngredients() here
Stream<List<Ingredient>> watchAllIngredients() async* {
final db = await instance.streamDatabase;
yield* db
.createQuery(ingredientTable)
.mapToList((row) => Ingredient.fromJson(row));
}
// TODO: Add findRecipeByID() here
Finding recipes
After the user has added recipes, they’ll want a quick way to find the one they have in mind for dinner. That’s what you’ll work on next.
Future<Recipe> findRecipeById(int id) async {
final db = await instance.streamDatabase;
final recipeList = await db.query(
recipeTable,
where: 'id = $id',
);
final recipes = parseRecipes(recipeList);
return recipes.first;
}
// TODO: Put findAllIngredients() here
Future<List<Ingredient>> findAllIngredients() async {
final db = await instance.streamDatabase;
final ingredientList = await db.query(ingredientTable);
final ingredients = parseIngredients(ingredientList);
return ingredients;
}
// TODO: findRecipeIngredients() goes here
Future<List<Ingredient>> findRecipeIngredients(int recipeId) async {
final db = await instance.streamDatabase;
final ingredientList =
await db.query(
ingredientTable,
where: 'recipeId = $recipeId',
);
final ingredients = parseIngredients(ingredientList);
return ingredients;
}
// TODO: Insert methods go here
Inserting data into tables
The user will want to add the delicious recipes they find to the app. To let them do this, you need a way to insert data into tables.
// 1
Future<int> insert(String table, Map<String, dynamic> row) async {
final db = await instance.streamDatabase;
// 2
return db.insert(
table,
row,
);
}
Future<int> insertRecipe(Recipe recipe) {
// 3
return insert(
recipeTable,
recipe.toJson(),
);
}
Future<int> insertIngredient(Ingredient ingredient) {
// 4
return insert(
ingredientTable,
ingredient.toJson(),
);
}
// TODO: Delete methods go here
Deleting data
Deleting data is just as easy as inserting it; you just need the table name and a row ID. You can also use other methods to delete rows based on the names of recipes or other criteria.
// 1
Future<int> _delete(String table, String columnId, int id) async {
final db = await instance.streamDatabase;
// 2
return db.delete(
table,
where: '$columnId = ?',
whereArgs: [id],
);
}
Future<int> deleteRecipe(Recipe recipe) async {
// 3
if (recipe.id != null) {
return _delete(
recipeTable,
recipeId,
recipe.id!,
);
} else {
return Future.value(-1);
}
}
Future<int> deleteIngredient(Ingredient ingredient) async {
if (ingredient.id != null) {
return _delete(
ingredientTable,
ingredientId,
ingredient.id!,
);
} else {
return Future.value(-1);
}
}
Future<void> deleteIngredients(List<Ingredient> ingredients) {
// 4
for (final ingredient in ingredients) {
if (ingredient.id != null) {
_delete(
ingredientTable,
ingredientId,
ingredient.id!,
);
}
}
return Future.value();
}
Future<int> deleteRecipeIngredients(int id) async {
final db = await instance.streamDatabase;
// 5
return db
.delete(
ingredientTable,
where: '$recipeId = ?',
whereArgs: [id],
);
}
// TODO: Add close() here
Initializing and closing
SQLite databases need to be opened and closed. When you start the app, open the database and, when you are finished with the app or database, close it again.
void close() {
_streamDatabase.close();
}
Adding an SQLite repository
Just like your MemoryRepository, you need a repository class for your database. You’ll create SqliteRepository to fulfill this need.
import 'dart:async';
// 1
import '../repository.dart';
import 'database_helper.dart';
import '../models/models.dart';
// 2
class SqliteRepository extends Repository {
// 3
final dbHelper = DatabaseHelper.instance;
// TODO: Add methods to use dbHelper here
}
@override
Future<List<Recipe>> findAllRecipes() {
return dbHelper.findAllRecipes();
}
@override
Stream<List<Recipe>> watchAllRecipes() {
return dbHelper.watchAllRecipes();
}
@override
Stream<List<Ingredient>> watchAllIngredients() {
return dbHelper.watchAllIngredients();
}
@override
Future<Recipe> findRecipeById(int id) {
return dbHelper.findRecipeById(id);
}
@override
Future<List<Ingredient>> findAllIngredients() {
return dbHelper.findAllIngredients();
}
@override
Future<List<Ingredient>> findRecipeIngredients(int recipeId) {
return dbHelper.findRecipeIngredients(recipeId);
}
// TODO: Add recipe insert here
Setting the recipe’s ID
If the ingredient’s recipeId isn’t set, the database won’t know which ingredient belongs to which recipe.
@override
Future<int> insertRecipe(Recipe recipe) {
// 1
return Future(() async {
// 2
final id = await dbHelper.insertRecipe(recipe);
// 3
recipe.id = id;
if (recipe.ingredients != null) {
recipe.ingredients!.forEach((ingredient) {
// 4
ingredient.recipeId = id;
});
// 5
insertIngredients(recipe.ingredients!);
}
// 6
return id;
});
}
// TODO: Insert ingredients
Inserting the ingredients
Next, you’ll insert the ingredients.
@override
Future<List<int>> insertIngredients(List<Ingredient> ingredients) {
return Future(() async {
if (ingredients.length != 0) {
// 1
final ingredientIds = <int>[];
// 2
await Future.forEach(ingredients, (Ingredient ingredient) async {
// 3
final futureId = await dbHelper.insertIngredient(ingredient);
ingredient.id = futureId;
// 4
ingredientIds.add(futureId);
});
// 5
return Future.value(ingredientIds);
} else {
return Future.value(<int>[]);
}
});
}
// TODO: Delete methods go here
Deleting recipes
Now that you have the delete methods in the helper class, you need to implement them in the repository. Most of these just call the helper’s methods.
@override
Future<void> deleteRecipe(Recipe recipe) {
// 1
dbHelper.deleteRecipe(recipe);
// 2
if (recipe.id != null) {
deleteRecipeIngredients(recipe.id!);
}
return Future.value();
}
@override
Future<void> deleteIngredient(Ingredient ingredient) {
// 3
dbHelper.deleteIngredient(ingredient);
return Future.value();
}
@override
Future<void> deleteIngredients(List<Ingredient> ingredients) {
// 4
dbHelper.deleteIngredients(ingredients);
return Future.value();
}
@override
Future<void> deleteRecipeIngredients(int recipeId) {
// 5
dbHelper.deleteRecipeIngredients(recipeId);
return Future.value();
}
// TODO: initialize and close methods go here
Initializing and closing
Remember that databases need to be opened and closed. To do this, your repository just needs to call dbHelper’s methods.
@override
Future init() async {
// 1
await dbHelper.database;
return Future.value();
}
@override
void close() {
// 2
dbHelper.close();
}
Running the app
After all that work, it’s time to see your changes. For your last change before you run, open main.dart.
import 'data/sqlite/sqlite_repository.dart';
final repository = SqliteRepository();
await repository.init();
runApp(MyApp(repository: repository));
final Repository repository;
const MyApp({
Key? key,
required this.repository,
}) : super(key: key);
// 1
create: (_) => repository,
// 2
dispose: (_, Repository repository) => repository.close(),
Using the Moor Library
As you saw, that was a lot of work. Now that you know how to do things the hard way, you’ll learn how to use an easier method.
moor_flutter: ^4.1.0
moor_generator: ^4.6.0+1
Database classes
For your next step, you need to create a set of classes that will describe and create the database, tables and Data Access Objects (DAOs).
import 'package:moor_flutter/moor_flutter.dart';
import '../models/models.dart';
part 'moor_db.g.dart';
// TODO: Add MoorRecipe table definition here
// TODO: Add MoorIngredient table definition here
// TODO: Add @UseMoor() and RecipeDatabase() here
// TODO: Add RecipeDao here
// TODO: Add IngredientDao
// TODO: Add moorRecipeToRecipe here
// TODO: Add MoorRecipeData here
// TODO: Add moorIngredientToIngredient and MoorIngredientCompanion here
Creating tables
To create a table in Moor, you need to create a class that extends Table. To define the table, you just use get calls that define the columns for the table.
// 1
class MoorRecipe extends Table {
// 2
IntColumn get id => integer().autoIncrement()();
// 3
TextColumn get label => text()();
TextColumn get image => text()();
TextColumn get url => text()();
RealColumn get calories => real()();
RealColumn get totalWeight => real()();
RealColumn get totalTime => real()();
}
Defining the Ingredient table
Now, find and replace // TODO: Add MoorIngredient table definition here with:
class MoorIngredient extends Table {
IntColumn get id => integer().autoIncrement()();
IntColumn get recipeId => integer()();
TextColumn get name => text()();
RealColumn get weight => real()();
}
Creating the database class
Moor uses annotations. The first one you need is @UseMoor. This specifies the tables and Data Access Objects (DAO) to use.
// 1
@UseMoor(
tables: [
MoorRecipe,
MoorIngredient,
],
daos: [
RecipeDao,
IngredientDao,
]
)
// 2
class RecipeDatabase extends _$RecipeDatabase {
RecipeDatabase()
// 3
: super(FlutterQueryExecutor.inDatabaseFolder(
path: 'recipes.sqlite', logStatements: true));
// 4
@override
int get schemaVersion => 1;
}
Creating the DAO classes
Your first step is to create the RecipeDao class. You’ll see more red squiggles, just ignore them for now. With moor_db.dart still open, replace // TODO: Add RecipeDao here with the following:
// 1
@UseDao(tables: [MoorRecipe])
// 2
class RecipeDao extends DatabaseAccessor<RecipeDatabase> with _$RecipeDaoMixin {
// 3
final RecipeDatabase db;
RecipeDao(this.db) : super(db);
// 4
Future<List<MoorRecipeData>> findAllRecipes() => select(moorRecipe).get();
// 5
Stream<List<Recipe>> watchAllRecipes() {
// TODO: Add watchAllRecipes code here
}
// 6
Future<List<MoorRecipeData>> findRecipeById(int id) =>
(select(moorRecipe)..where((tbl) => tbl.id.equals(id))).get();
// 7
Future<int> insertRecipe(Insertable<MoorRecipeData> recipe) =>
into(moorRecipe).insert(recipe);
// 8
Future deleteRecipe(int id) => Future.value(
(delete(moorRecipe)..where((tbl) => tbl.id.equals(id))).go());
}
// 1
@UseDao(tables: [MoorIngredient])
// 2
class IngredientDao extends DatabaseAccessor<RecipeDatabase>
with _$IngredientDaoMixin {
final RecipeDatabase db;
IngredientDao(this.db) : super(db);
Future<List<MoorIngredientData>> findAllIngredients() =>
select(moorIngredient).get();
// 3
Stream<List<MoorIngredientData>> watchAllIngredients() =>
select(moorIngredient).watch();
// 4
Future<List<MoorIngredientData>> findRecipeIngredients(int id) =>
(select(moorIngredient)..where((tbl) => tbl.recipeId.equals(id))).get();
// 5
Future<int> insertIngredient(Insertable<MoorIngredientData> ingredient) =>
into(moorIngredient).insert(ingredient);
// 6
Future deleteIngredient(int id) =>
Future.value((delete(moorIngredient)..where((tbl) =>
tbl.id.equals(id))).go());
}
Generating the part file
Now, you need to create the Moor part file. In Terminal, run:
flutter pub run build_runner build --delete-conflicting-outputs
Converting your Moor recipes
At the end of moor_db.dart, replace // TODO: Add moorRecipeToRecipe here with:
// Conversion Methods
Recipe moorRecipeToRecipe(MoorRecipeData recipe) {
return Recipe(
id: recipe.id,
label: recipe.label,
image: recipe.image,
url: recipe.url,
calories: recipe.calories,
totalWeight: recipe.totalWeight,
totalTime: recipe.totalTime,
);
}
Insertable<MoorRecipeData> recipeToInsertableMoorRecipe(Recipe recipe) {
return MoorRecipeCompanion.insert(
label: recipe.label ?? '',
image: recipe.image ?? '',
url: recipe.url ?? '',
calories: recipe.calories ?? 0,
totalWeight: recipe.totalWeight ?? 0,
totalTime: recipe.totalTime ?? 0,
);
}
Creating classes for Ingredients
Next, you’ll do the same for the ingredients models. Replace // TODO: Add moorIngredientToIngredient and MoorIngredientCompanion here with the following:
Ingredient moorIngredientToIngredient(MoorIngredientData ingredient) {
return Ingredient(
id: ingredient.id,
recipeId: ingredient.recipeId,
name: ingredient.name,
weight: ingredient.weight,
);
}
MoorIngredientCompanion ingredientToInsertableMoorIngredient(
Ingredient ingredient) {
return MoorIngredientCompanion.insert(
recipeId: ingredient.recipeId ?? 0,
name: ingredient.name ?? '',
weight: ingredient.weight ?? 0,
);
}
Updating watchAllRecipes()
Now that you’ve written the conversion methods, you can update watchAllRecipes().
// 1
return select(moorRecipe)
// 2
.watch()
// 3
.map((rows) {
final recipes = <Recipe>[];
// 4
for (final row in rows) {
// 5
final recipe = moorRecipeToRecipe(row);
// 6
if (!recipes.contains(recipe)) {
recipe.ingredients = <Ingredient>[];
recipes.add(recipe);
}
}
return recipes;
},
);
Creating the Moor repository
Now that you have the Moor database code written, you need to write a repository to handle it. You’ll create a class named MoorRepository that implements Repository:
import 'dart:async';
import '../models/models.dart';
import '../repository.dart';
import 'moor_db.dart';
class MoorRepository extends Repository {
// 1
late RecipeDatabase recipeDatabase;
// 2
late RecipeDao _recipeDao;
// 3
late IngredientDao _ingredientDao;
// 4
Stream<List<Ingredient>>? ingredientStream;
// 5
Stream<List<Recipe>>? recipeStream;
// TODO: Add findAllRecipes()
// TODO: Add watchAllRecipes()
// TODO: Add watchAllIngredients()
// TODO: Add findRecipeById()
// TODO: Add findAllIngredients()
// TODO: Add findRecipeIngredients()
// TODO: Add insertRecipe()
// TODO: Add insertIngredients()
// TODO: Add Delete methods
@override
Future init() async {
// 6
recipeDatabase = RecipeDatabase();
// 7
_recipeDao = recipeDatabase.recipeDao;
_ingredientDao = recipeDatabase.ingredientDao;
}
@override
void close() {
// 8
recipeDatabase.close();
}
}
Implementing the repository
As you did in past chapters, you’ll now add all the missing methods following the TODO: indications.
Replace // TODO: Add findAllRecipes() with:
@override
Future<List<Recipe>> findAllRecipes() {
// 1
return _recipeDao.findAllRecipes()
// 2
.then<List<Recipe>>(
(List<MoorRecipeData> moorRecipes) async {
final recipes = <Recipe>[];
// 3
for (final moorRecipe in moorRecipes) {
// 4
final recipe = moorRecipeToRecipe(moorRecipe);
// 5
if (recipe.id != null) {
recipe.ingredients = await findRecipeIngredients(recipe.id!);
}
recipes.add(recipe);
}
return recipes;
},
);
}
@override
Stream<List<Recipe>> watchAllRecipes() {
recipeStream ??= _recipeDao.watchAllRecipes();
return recipeStream!;
}
@override
Stream<List<Ingredient>> watchAllIngredients() {
if (ingredientStream == null) {
// 1
final stream = _ingredientDao.watchAllIngredients();
// 2
ingredientStream = stream.map((moorIngredients) {
final ingredients = <Ingredient>[];
// 3
for (final moorIngredient in moorIngredients) {
ingredients.add(moorIngredientToIngredient(moorIngredient));
}
return ingredients;
},);
}
return ingredientStream!;
}
Finding recipes
The find methods are a bit easier, but they still need to convert each database class to a model class.
@override
Future<Recipe> findRecipeById(int id) {
return _recipeDao
.findRecipeById(id)
.then((listOfRecipes) => moorRecipeToRecipe(listOfRecipes.first));
}
@override
Future<List<Ingredient>> findAllIngredients() {
return _ingredientDao.findAllIngredients().then<List<Ingredient>>(
(List<MoorIngredientData> moorIngredients) {
final ingredients = <Ingredient>[];
for (final ingredient in moorIngredients) {
ingredients.add(moorIngredientToIngredient(ingredient));
}
return ingredients;
},
);
}
@override
Future<List<Ingredient>> findRecipeIngredients(int recipeId) {
return _ingredientDao.findRecipeIngredients(recipeId).then(
(listOfIngredients) {
final ingredients = <Ingredient>[];
for (final ingredient in listOfIngredients) {
ingredients.add(moorIngredientToIngredient(ingredient));
}
return ingredients;
},
);
}
Inserting recipes
To insert a recipe, first you insert the recipe itself and then insert all its ingredients. Replace // TODO: Add insertRecipe() with:
@override
Future<int> insertRecipe(Recipe recipe) {
return Future(
() async {
// 1
final id =
await _recipeDao.insertRecipe(
recipeToInsertableMoorRecipe(recipe),
);
if (recipe.ingredients != null) {
// 2
for (final ingredient in recipe.ingredients!) {
ingredient.recipeId = id;
}
// 3
insertIngredients(recipe.ingredients!);
}
return id;
},
);
}
@override
Future<List<int>> insertIngredients(List<Ingredient> ingredients) {
return Future(
() {
// 1
if (ingredients.isEmpty) {
return <int>[];
}
final resultIds = <int>[];
for (final ingredient in ingredients) {
// 2
final moorIngredient =
ingredientToInsertableMoorIngredient(ingredient);
// 3
_ingredientDao
.insertIngredient(moorIngredient)
.then((int id) => resultIds.add(id));
}
return resultIds;
},
);
}
Deleting
Deleting is much easier. You need to call the DAO methods. Replace // TODO: Add Delete methods with:
@override
Future<void> deleteRecipe(Recipe recipe) {
if (recipe.id != null) {
_recipeDao.deleteRecipe(recipe.id!);
deleteRecipeIngredients(recipe.id!);
}
return Future.value();
}
@override
Future<void> deleteIngredient(Ingredient ingredient) {
if (ingredient.id != null) {
return _ingredientDao.deleteIngredient(ingredient.id!);
} else {
return Future.value();
}
}
@override
Future<void> deleteIngredients(List<Ingredient> ingredients) {
for (final ingredient in ingredients) {
if (ingredient.id != null) {
_ingredientDao.deleteIngredient(ingredient.id!);
}
}
return Future.value();
}
@override
Future<void> deleteRecipeIngredients(int recipeId) async {
// 1
final ingredients = await findRecipeIngredients(recipeId);
// 2
return deleteIngredients(ingredients);
}
Replacing the repository
Now, you just have to replace your SQL repository with your shiny new Moor repository.
import 'data/moor/moor_repository.dart';
final repository = SqliteRepository();
final repository = MoorRepository();
Running the app
Stop the running app, build and run. Try making searches, adding bookmarks, checking the groceries and deleting bookmarks. It will work just the same as with SqliteRepository. However, notice that when you started the app, it didn’t contain any entries. Do you know why?
Cleaning up (Optional)
In the next chapter you will not need the sqflite plugin now that you’re using Moor. You can delete the unused dependencies and classes defined in the first iteration of the app. Note that this is optional. To do so, delete the folder lib/data/sqlite and all its files. Then open pubspec.yml and remove the following libraries:
sqflite: ^2.0.3+1
path_provider: ^2.0.11
synchronized: ^3.0.0+3
Key points
- Databases persist data locally to the device.
- Data stored in databases are available after the app restarts.
- The sqflite plugin requires some SQL knowledge to set up the database.
- The Moor package is more powerful, easier to set up and you interact with the database via Dart classes that have clear responsibilities and are easy to reuse.
Where to go from here?
To learn about: