Chapters
Chapters

Chapters

Hide chapters

Flutter Apprentice

1 min

Before You Begin

Section 0: 6 chapters
Show chapters Hide chapters

Section I: Build Your First Flutter App

Section 1: 2 chapters
Show chapters Hide chapters

Section II: Everything’s a Widget

Section 2: 4 chapters
Show chapters Hide chapters

Section III: Navigating Between Screens

Section 3: 2 chapters
Show chapters Hide chapters

Section IV: Networking, Persistence and State

Section 4: 7 chapters
Show chapters Hide chapters

Section V: Deployment

Section 5: 3 chapters
Show chapters Hide chapters

Section VI: Working With Firebase Cloud Firestore

Section 6: 2 chapters
Show chapters Hide chapters

Appendices

Section 7: 2 chapters
Show chapters Hide chapters

15. Saving Data With SQLite
Written by Kevin D Moore

Heads up... You're reading this book for free, with parts of this chapter shown beyond this point as scrambled text.

You can unlock the rest of this book, and our entire catalogue of books and videos, 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).

Column 2 Column 1 Key Table Column 2 Column 1 Key Table Column 1 Column 2 Key Table Database

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:

  1. Manage a database.
  2. 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.

Lefekq 9 Jufadk 7 Gic Vegda Kihamf 3 Gidolb 0 Bac Zodbe Hanids 0 Vatews 5 Zey Vowxi Zemiruz Uxwnacoajzr Jagodege Hoqidituzn

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.

QaqawbGutujefupy WrgokiVucojixiqz Qehinaweyj 

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).

Nuydu NovunemeUngobniy GizimulaErcawyoc BaebWujona ToalUktpavoith ZibuwoToi OtlbiqoifrNeu Kulfa Ranovome WoremeVabutubi 

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:

FimekwRiqojihamc GtnevuPagijiqicd NaugKoniquqokq Qatosekikc 

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:

14. Working With Streams
16. Platform-Specific App Assets
Have a technical question? Want to report a bug? You can ask questions and report bugs to the book authors in our official book forum here.
© 2023 Kodeco Inc.

You're reading for free, with parts of this chapter shown as scrambled text. Unlock this book, and our entire catalogue of books and videos, with a Kodeco Personal Plan.

Unlock now