Getting Started with Local Databases in Flutter with Moor Part 1

When it comes to building mobile applications we usually get to a point where we need to save data related to the person using the application. There are countless ways to do this, but the most relevant is using a local database with SQL, and in this case SQLite.

But working with SQLite in Flutter does not make it easy to write or read data from the local database. That’s where Moor comes in. It helps you write queries and define models for your data in a composable, scalable and maintainable way.

We will see how we can use the Moor library to help us do create, read, update and delete (CRUD) operations in an easy and performant way.

Prerequisite

Before we start building we will need to have the following setup.

• Flutter. You will need to have Flutter installed on your machine. For more info on how you can install Flutter and have it available to run in your terminal click here..

• VSCode. This is the code editor we will be using to write our code. You can also use IntelliJ to run the app.

Getting Started

We will start by creating a new project. VSCode and IntelliJ can create a new one for you or you can use a command to generate a new Flutter project.

$ flutter create myapp

You can name your project any way you like. After creating the project, we can start by adding the following dependencies in your pubspec.yaml file.

dependencies:

• moor: ^4.5.0

• sqlite3_flutter_libs: ^0.5.0

• path_provider: ^2.0.0

• path: ^1.8.0

dev_dependencies:

• moor_generator: ^4.5.1

• build_runner: ^2.1.1

• Moor. This is our core library

• Sqlite3_flutter_libs: This plugin will help in handling SQLite native libraries in our app

• Path_provider and path: This will help in defining access and storage of the database within the application.

• Moor_generator: Required to generate extra files needed to work well with Moor and Flutter.

• Build_runner: This will allow us to generate files required by Moor.

Connection

Here I show creating a connection and instantiating the database for a way to connect for reading and writing. Moor allows us to create a connection using annotations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));
    return VmDatabase(file);
  });
}

@UseMoor(tables: [])
class AppDB extends _$AppDB {
  AppDB(): super(_openConnection());

  @override
  int get schemaVersion => 1;
}

Please don’t forget to add the line part ‘db.g.dart’ after the imports to ensure we can access the generated code. The connection has to be established first by checking the existence of the SQLite database and returning it for access. The @UseMoor helps to generate code that will be used within the class using it. In this case tables, converters, and so on.

Defining our Tables

All our data has to be stored in a formatted way for better querying. The best way is by using a model structure for all data required to be inserted. This will reduce duplicates, conflicts and/or bugs that may arise.

Moor allows us to define tables using the following types. Strings, Integers, Booleans, Doubles, DateTime and Blobs, i.e Uint8List or files/images.

The following code shows how we can use Dart types in SQLite using Moor for handling CRUD operations:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
class User extends Table {

  // Recognised as the default primary key if it has an auto increment.
  IntColumn get id => integer()
    .autoIncrement()();
  TextColumn get firstName => text()
    .withLength(min: 3, max: 15)();
  TextColumn get secondName => text()();
  TextColumn get phoneNumber => text()();
  TextColumn get emailAddress => text()();
  // Working with blobs in this case files: images, documents etc
  BlobColumn get thumbnail => blob()
    .nullable()();
  // Working with nullable types
  DateTimeColumn get approvalDate => dateTime()
    .nullable()();
  // Working with default values
  BoolColumn get isApproved => boolean()
    .withDefault(const Constant(false))();
  // Multiple primary keys.
  // Will use this when the data associated with the value is unavailable on
  // insertion is not available. But will be used on the return value on access.
  TextColumn get referenceId => text()
    .clientDefault(() => Uuid()
      .v1())();
}

Moor uses the class as the default name for the table. But we can provide a custom one if we want. Add at the top within the table class.

String get tableName => "users";

We are also allowed to give our properties different names for later access if we want to set a standard for naming in the database. For example, you might decide you want to have a pascal case for Dart code in naming table property names and snake case for column names.

TextColumn get firstName => text().named("username")()

Primary Keys

Moor allows us to set more than one primary key by overriding the primaryKey set. You can define the properties you need to be unique throughout the database by adding them to the set.

By default autoIncrement applied to any IntColumn applies as the primary key.

Default Values

More than once we would like to have a default value for a particular property. For instance, you would want to have all users who are not approved as isApproved to false. We can also use enum values by using TypeConverters and setting a default enum. We will revisit this later in the article.

Nullability

We can also set variables that are null. For instance, a user might prefer not to add their phone number but we would still want them to add it later on. We can add the nullable function to the type like so.

TextColumn get phoneNumber => text().nullable()()

Please make sure you add an extra parentheses to the type. Otherwise it will fire an error.

Type Converters

Usually, not all types have been covered, so we are allowed to add our own types. For example, you might have seen lists have not been covered, or types we ourselves have created. We can create our own custom types using Moor’s type converters.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
class ArrayConverter < T > extends TypeConverter < List < T > , String > {
  @override
  List < T > ? mapToDart(String ? fromDb) {
    if (fromDb == null) {
      return null;
    }

    return List < T > .from(fromDb.split(","));
  }

  @override
  String ? mapToSql(List < T > ? value) {
    if (value == null) {
      return null;
    }

    return value.toString();
  }
}

We can later use the converter as a type in the user table to save a list of likes the person has

1
2
3
4
TextColumn get likes =>
  text()
  .map(const ArrayListConverter < String > ())
  .nullable()();

We can also use enums. When using enums be careful when adding a new one. If you find a need to add a new one add it last in line so that if the index falls at one it won’t find an unrelated value because it was not captured before. Otherwise you might need to handle migrations.

1
2
3
4
5
6
7
8
9
enum Role {
  client,
  sales,
  supervisor,
  admin
}

// Later on in the table class.
IntColumn get role => intEnum < Role > ()();

Custom Row Classes

You might need to abstract the table to be used with another class for parsing values to and from. Especially when working with a REST API we need to accept and respond with data formatted into JSON. We can handle this by using annotations provided by the Moor package.

1
2
@UseRowClass(UserM, constructor: "fromDB")
class Users extends Table {

Our JSON mapping class would look like the following.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
@JsonSerializable()
class UserM extends Equatable {
  int ? id;
  final String firstName;
  final String secondName;
  final String phoneNumber;
  final String emailAddress;
  DateTime ? approvalDate;
  final bool isApproved;

  @override
  List < Object ? > get props => [
       this.firstName,
       this.secondName,
       this.emailAddress,
       this.approvalDate,
       this.isApproved
     ];

  UserM({
    required this.firstName,
    required this.secondName,
    required this.emailAddress,
    this.approvalDate,
    required this.phoneNumber,
    required this.isApproved
  });

  UserM.fromDB({
    required this.firstName,
    required this.secondName,
    required this.emailAddress,
    this.approvalDate,
    required this.phoneNumber,
    required this.isApproved
  });

  @override
  bool get stringify => true;

  factory UserM.fromJson(Map < String, dynamic > json) => _$UserMFromJson(json);
  Map < String, dynamic > toJson() => _$UserMToJson(this);
}

We can use this class with a library like DIO to get a JSON response of a user and then we can easily add it to the database by simply calling the fromDB constructor.

Database Access Objects (DAOs)

Database access objects are a way to group your queries based on a particular relationship. This is beneficial because we reduce bugs and the complexities of handling all your requests in one class.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@UseDao(tables: [Users])
class UserDAO extends DatabaseAccessor < FlumoorDatabase > with _$UserDAOMixin implements IUser {
  UserDAO(FlumoorDatabase db): super(db);

  @override
  Future < int > updateUser(UsersCompanion usr) {
    return into(users)
      .insert(usr);
  }

  @override
  SingleSelectable < User > getUserData(int id) => select(users).
    .where((usr) => usr.id.equals(id));

  @override
  Future < void > deleteUserRecord(UsersCompanion user) async {
    return await deleteUserById(user.id.value);
  }

  @override
  Future < void > deleteUserById(int id) {
    return (delete(users).
        .where((tbl) => tbl.id.equals(id)))
      .go();
  }
}

Our DAO implements function from an abstract class IUser. This will also help in creating more readable and maintainable code.

1
2
3
4
5
6
abstract class IUser {
  SingleSelectable < User > getUserData(int id);
  Future < int > updateUser(UsersCompanion user);
  Future < void > deleteUserRecord(UsersCompanion user);
  Future < void > deleteUserById(int id);
}

After creating our DAO and tables we need to register them to our database so Moor can generate code we can use to handle queries. After adding the DAO, table, and JSON model, run the build runner to clear up any missing code that would have been generated. Don’t forget to add your part import at the top of the file so that build_runner will know where to add the generated files and which file needs it. This will be needed in our user.json.dart file too. The name of the part must be the same as the file name where it’s being referenced. In the case of user.json the part file would be part ‘user.json.g.dart’

$ flutter pub run build_runner build --delete-conflicting-outputs

1
2
3
4
5
6
7
@UseMoor(tables: [User], daos: [UserDao])
class FlumoorDatabase extends _$FlumoorDatabase {
  FlumoorDatabase(): super(_openConnection());

  @override
  int get schemaVersion => 1;
}

In this article we have learned how to set up our local database easily and in a robust way using Moor. In the next article we will learn how to make complex queries and use them as futures or streams within Flutter.