Getting Started with Local Databases in Flutter Using Moor, Part 2

In part one of this article, we went through setting up moor for a Flutter app and how we can customize our data table for different file types. In this article, we will go through running simple to complex queries such as inner joins, relationships, aggregations, working with streams and futures, and how we can listen for them within a Flutter widget, and running migrations. Finally, as a bonus, we will be able to set up a database inspector for IntelliJ IDE. The database inspector will allow us to view data we write to an SQLite DB file.

Queries

Inserts

We can add a new document using a generated companion class. The companion class will allow us to add null values to data values that may be null using ValueAbsent.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Future < int > addUser(UserCompanion userEntry) {
  // users here represent the table name while userEntry represents the 
  return into(users)
    .insert(userEntry);
}

// Later on where you will be adding a new user
addUser(UserCompanion(
  firstName: Value(userEntry.firstName),
  secondName: Value(userEntry.secondName),
  emailAddress: Value(userEntry.emailAddress),
  thumbnail: Value.absent(),
  isApprovedDate: Value(user.isApprovedDate),
));

For multiple inserts, you can use batch inserts. This will prevent data locking when performing too many inserts or operations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Future < void > addFriends() async {
  await batch((batch) {

    batch.insertAll(todos, [
      UserCompanion.insert(
        firstName: 'Jacob',
        secondName: 'Sumac',
        email: 'jzumac@em.com',
      ),
      UserCompanion.insert(
        firstName: 'Arnold',
        secondName: 'Kirui',
        email: 'karnold@gmail.com',
      ),
      // ...
    ]);
  });
}

While adding documents/data into our database we might not want any conflicts, so moor provides conflict resolution for us. With conflict resolution in place, we will be able to prevent duplicates and conflict errors in batch updates like the one above. This can be done by either updating the current document that matches the incoming one, aborting the insert, and preserving the current one, or aborting the operation entirely.

Here’s how we do it:

1
2
3
4
Future < void > createOrUpdateUser(User user) {
  return into(users)
    .insertOnConflictUpdate(user);
}

If we want to get the data back after we insert it we use insertReturning.

1
2
3
4
5
6
final newuser = await into(users)
  .insertReturning(UsersCompanion.insert(
    firstName: 'Jerry',
    secondName: 'Thompsons',
    email: 'jerry@mailto.com',
  ));

The new user contains the data we inserted and can be used to make other joins and/or complex queries in a function.

Reading Data

Reading data from the local DB is quite simple and with moor, you have the leverage of using streams and/or futures. You can also do joins, which we will talk about later. To watch for a stream of data you can use the watchSingle function which will return a stream of data if it exists. If you want to watch for a list of data you can use watch instead.

1
2
3
4
5
Stream < User > favoriteFriend(int id) {
  return (select(users).
      .where((u) => u.id.equals(id)))
    .watchSingle();
}

You can refine the stream by using SingleSelectable and MultipleSelectable. SingleSelectable exposes the getSingle and watchSingle functions, while MultipleSelectable exposes get and watch. These will allow the queries to be consumable as either futures or streams.

Updates and Deletes

Updating and deleting documents can be done in the same way we create one. You can use classes that have been generated to update values in a document row. We can update a full document or partial row(s). The update and the preexisting one should have the same id, in this case, the same primary key is used to match up the documents.

1
2
3
4
Future updateFriend(User friend) {
  return update(users)
    .replace(friend);
}

To update a single value we will need to use the where clause, otherwise it would update all rows in the table.

1
2
3
4
5
6
7
8
Future updateFriendsName(int id, String name) {
  return (update(users).
      .where((u) => u.id.equals(id))
    )
    .write(UserCompanion(
      firstName: Value(name),
    ), );
}

Handling deletes is much the same with a very small difference.

1
2
3
4
5
Future removeFriend(int id) {
  return (delete(users).
      .where((u) => u.id.equals(id)))
    .go();
}

Transactions

Moor provides a lot of APIs that make creating, reading, deleting, and updating documents very easy. But, you may want to perform operations that would include huge updates to different tables at the same time. That’s where transactions come in, they provide a way to handle this kind of functionality without locking the database. A database lock is a situation where only one user/session can update particular data in a database. So imagine we have a table of companies and another table of employees and you want to delete the company and the employee at the same time. These are two sessions accessing resources from one database. Without transactions, the database would lock and an exception would be thrown.

1
2
3
4
5
6
7
8
Future deleteCategory(Company company, Employee employees) {
  return transaction((t) async {
    await delete(companies)
      .delete(company);
    await delete(employees)
      .delete(employee);
  });
}

Relationships

It’s possible to relate two different documents using relatable rows in both documents. For example, we can add a new table called the company and relate that with users, in this case, friends.

1
2
3
4
5
6
class FriendsWithCompany {
  FriendsWithCompany(this.friend, this.category);

  final User friend;
  final Company company;
}

We can then list the result into a stream using joins. In this case leftOuterJoin. Using get or watch will return a future or stream. We will be parsing our results before we can make the data consumable.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 Stream < List < FriendsWithCompany >> friendsInCompany() {
   final query = select(users)
     .join([
    leftOuterJoin(company, company.id.equalsExp(company...category)),
  ]);

   return query.watch()
     .map((rows) {
       return rows.map((row) {
           return EntryWithCategory(
             row.readTable(todos),
             row.readTableOrNull(categories),
           );
         })
         .toList();
     });
 }

Handing Migrations

We can update schema changes after increasing the schema version in a database class. For instance, if we were to add a new variable to the database we would need to override the migration getter function.
This is what we would use before bumping up the version;

1
2
3
4
5
6
7
class Users extends Table {
  // ..
  // Newly added row
  DateTimeColumn get birthday => dateTime()
    .nullable()();
  // ...
}

We can then update our database to take up the new change. We bump up the schema version.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@override
int get schemaVersion => 2; // increase to take up newly added row in users table

@override
MigrationStrategy get migration => MigrationStrategy(
  onCreate: (Migrator m) {
    return m.createAll();
  },
  onUpgrade: (Migrator migrator, from, to) async {
    if (from == 1) {
      // We added birthday row in the users’ table in version 1
      await m.addColumn(users, users.birthday);
    }
  }
);

As we have seen, we can make migrations and handle SQL operations quite easily in the long run.
Moor also provides a very good tool called the moor inspector that helps visualize your database. It comes in handy when running migrations and you need to check sure that they ran.