7

Moor (Room for Flutter) #1 – Tables & Queries – Fluent SQLite Database

Mobile apps and local databases go well together. If you want to take the relational database route, SQLite is the winner. However, the problem with bare bones SQL databases of any kind is writing queries and then integrating them with your other code.

Android solves this with the beloved ROOM library, on Flutter though, you are stuck with the low-level SQFLite package... Not anymore! MOOR is a library allowing you to work with the Flutter's SQLite database fluently and in pure Dart. Behind the scenes, it uses the SQFLite package.  Oh, and if you're wondering, MOOR is just ROOM spelled backwards.

Preparing the project

Moor works by using Dart's source generation. At the end of the day, everything you write with its fluent syntax gets converted to plain old SQL with which the low level SQFLite can work. That's why we need both moor_flutter and moor_generator dependencies together with the build_runner.

Other dependencies include provider and flutter_slidable which are here purely for making the UI possible.

pubspec.yaml

...
dependencies:
  flutter:
    sdk: flutter
  moor_flutter: ^1.4.0
  # For the UI
  provider: ^3.0.0+1
  flutter_slidable: ^0.5.3

...

dev_dependencies:
  flutter_test:
    sdk: flutter
  moor_generator: ^1.4.0
  build_runner:
...

What we will build

Project-based approach to learning is the best approach. In this series, we are going to build a task list app. At the end of this part, you are going to have an app which can create new tasks, complete them & display them. All of this will happen persistently using the Moor's fluent query syntax.

Creating a table

The biggest benefit of Moor is that you don't have to leave Dart in order to work with the database. This also applies to defining SQL tables. All you need, is to create a class representing the table. Subsequently,  columns are specified as get-only properties of the class.

What's more, Moor takes this Table class and creates a data class out of it! Moor's data classes support value equality, simple deep copies, and even converting to & from JSON.

All the Moor-related code will be inside a file moor_database.dart to keep it organized.

Location of the file

moor_database.dart

import 'package:moor/moor.dart';
import 'package:moor_flutter/moor_flutter.dart';

// Moor works by source gen. This file will all the generated code.
part 'moor_database.g.dart';

// The name of the database table is "tasks"
// By default, the name of the generated data class will be "Task" (without "s")
class Tasks extends Table {
  // autoIncrement automatically sets this to be the primary key
  IntColumn get id => integer().autoIncrement()();
  // If the length constraint is not fulfilled, the Task will not
  // be inserted into the database and an exception will be thrown.
  TextColumn get name => text().withLength(min: 1, max: 50)();
  // DateTime is not natively supported by SQLite
  // Moor converts it to & from UNIX seconds
  DateTimeColumn get dueDate => dateTime().nullable()();
  // Booleans are not supported as well, Moor converts them to integers
  // Simple default values are specified as Constants
  BoolColumn get completed => boolean().withDefault(Constant(false))();
}

More on defining tables

The code above is all we need for the app we're building. However there are some additional things you might want to know - how to define custom primary keys and how to change the name of the generated data class.

moor_database.dart

// The default data class name "Tasks" would now be "SomeOtherNameIfYouWant"
@DataClassName('SomeOtherNameIfYouWant')
class Tasks extends Table {
  ...
  // Custom primary keys defined as a set of columns
  @override
  Set<Column> get primaryKey => {id, name};
}

The Database class

With the table definition done, we need to get the actual database running. Moor makes this simple. Create a class, annotate it and specify the location of the database file.

moor_database.dart

// This annotation tells the code generator which tables this DB works with
@UseMoor(tables: [Tasks])
// _$AppDatabase is the name of the generated class
class AppDatabase extends _$AppDatabase {
  AppDatabase()
      // Specify the location of the database file
      : super((FlutterQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
          // Good for debugging - prints SQL in the console
          logStatements: true,
        )));

  // Bump this when changing tables and columns.
  // Migrations will be covered in the next part.
  @override
  int get schemaVersion => 1;
}

At this point, it's good if we finally generate the code. As usual, it's done through the build_runner command. We will use watch instead of build, so that we don't have to constantly rerun the command.

flutter packages pub run build_runner watch

Queries

Moor supports all kinds of queries in the fluent syntax and it also allows you to write custom SQL. Most of the time though, you don't have to leave the comfort of Dart. In this part, we will take a look at the basic queries and leave the more advanced ones for the next part. Queries can be put into the AppDatabase class.

The last sentence above may give you seizures. Don't worry! Moor fully supports separating your queries into multiple classes (data access objects). You'll learn about them in the next part.

moor_database.dart

@UseMoor(tables: [Tasks])
class AppDatabase extends _$AppDatabase {
  ...

  // All tables have getters in the generated class - we can select the tasks table
  Future<List<Task>> getAllTasks() => select(tasks).get();

  // Moor supports Streams which emit elements when the watched data changes
  Stream<List<Task>> watchAllTasks() => select(tasks).watch();

  Future insertTask(Task task) => into(tasks).insert(task);

  // Updates a Task with a matching primary key
  Future updateTask(Task task) => update(tasks).replace(task);

  Future deleteTask(Task task) => delete(tasks).delete(task);
}

Making the UI

Once you have the database class fully set up, you can use it throughout the app however you please. It doesn't require any additional setup. One thing you should keep in mind is that the AppDatabase class should be a singleton. In this app, we will accomplish it with the Provider package.

main.dart

import 'package:flutter/material.dart';
import 'package:provider/provider.dart';

import 'data/moor_database.dart';
import 'ui/home_page.dart';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return Provider(
      // The single instance of AppDatabase
      builder: (_) => AppDatabase(),
      child: MaterialApp(
        title: 'Material App',
        home: HomePage(),
      ),
    );
  }
}

Since this is only a simple app, we won't use any fancy state management solution. Simple Stateful widgets will suffice. If you'd like to step up your state management game, I highly recommend the BLoC library.

This is not a tutorial about Flutter's widgets, so the rest of the UI code will be here without any real explanation. Check out the video tutorial for more info.

home_page.dart

import 'package:flutter/material.dart';
import 'package:provider/provider.dart';
import 'package:flutter_slidable/flutter_slidable.dart';

import '../data/moor_database.dart';
import 'widget/new_task_input_widget.dart';

class HomePage extends StatefulWidget {
  @override
  _HomePageState createState() => _HomePageState();
}

class _HomePageState extends State<HomePage> {
  @override
  Widget build(BuildContext context) {
    return Scaffold(
        appBar: AppBar(
          title: Text('Tasks'),
        ),
        body: Column(
          children: <Widget>[
            Expanded(child: _buildTaskList(context)),
            NewTaskInput(),
          ],
        ));
  }

  StreamBuilder<List<Task>> _buildTaskList(BuildContext context) {
    final database = Provider.of<AppDatabase>(context);
    return StreamBuilder(
      stream: database.watchAllTasks(),
      builder: (context, AsyncSnapshot<List<Task>> snapshot) {
        final tasks = snapshot.data ?? List();

        return ListView.builder(
          itemCount: tasks.length,
          itemBuilder: (_, index) {
            final itemTask = tasks[index];
            return _buildListItem(itemTask, database);
          },
        );
      },
    );
  }

  Widget _buildListItem(Task itemTask, AppDatabase database) {
    return Slidable(
      actionPane: SlidableDrawerActionPane(),
      secondaryActions: <Widget>[
        IconSlideAction(
          caption: 'Delete',
          color: Colors.red,
          icon: Icons.delete,
          onTap: () => database.deleteTask(itemTask),
        )
      ],
      child: CheckboxListTile(
        title: Text(itemTask.name),
        subtitle: Text(itemTask.dueDate?.toString() ?? 'No date'),
        value: itemTask.completed,
        onChanged: (newValue) {
          database.updateTask(itemTask.copyWith(completed: newValue));
        },
      ),
    );
  }
}

The following code is for the bottom bar where new tasks can be inputted.

new_task_input_widget.dart

import 'package:flutter/material.dart';
import 'package:provider/provider.dart';

import '../../data/moor_database.dart';

class NewTaskInput extends StatefulWidget {
  const NewTaskInput({
    Key key,
  }) : super(key: key);

  @override
  _NewTaskInputState createState() => _NewTaskInputState();
}

class _NewTaskInputState extends State<NewTaskInput> {
  DateTime newTaskDate;
  TextEditingController controller;

  @override
  void initState() {
    super.initState();
    controller = TextEditingController();
  }

  @override
  Widget build(BuildContext context) {
    return Container(
      padding: const EdgeInsets.all(8.0),
      child: Row(
        mainAxisSize: MainAxisSize.max,
        children: <Widget>[
          _buildTextField(context),
          _buildDateButton(context),
        ],
      ),
    );
  }

  Expanded _buildTextField(BuildContext context) {
    return Expanded(
      child: TextField(
        controller: controller,
        decoration: InputDecoration(hintText: 'Task Name'),
        onSubmitted: (inputName) {
          final database = Provider.of<AppDatabase>(context);
          final task = Task(
            name: inputName,
            dueDate: newTaskDate,
          );
          database.insertTask(task);
          resetValuesAfterSubmit();
        },
      ),
    );
  }

  IconButton _buildDateButton(BuildContext context) {
    return IconButton(
      icon: Icon(Icons.calendar_today),
      onPressed: () async {
        newTaskDate = await showDatePicker(
          context: context,
          initialDate: DateTime.now(),
          firstDate: DateTime(2010),
          lastDate: DateTime(2050),
        );
      },
    );
  }

  void resetValuesAfterSubmit() {
    setState(() {
      newTaskDate = null;
      controller.clear();
    });
  }
}

Conclusion

You've learned how to use Moor to fluently create tables and queries in your Flutter apps. There's a lot more that Moor can handle, so be sure to stick around for the future parts of this series.

Matej Rešetár
 

Matej is an app developer with a knack for teaching others. If he's not programming, making tutorials or doing other business, he's mostly working out, listening to audiobooks and taking cold showers.

  • Floki says:

    Very good tutorial! Is there any way to use your own model? Cause i have a model that i use for an api service and i want to store that model to the db.

  • vivek says:

    Hello @resocoder is it moor_flutter supports json data column and we can make select query on the json data?

  • Gonzalo says:

    Hi @resocoder! Very good tutorial! Is there any way to use your own model class? because i have a model that i use for an api service and i want to store that model to the db.

    • Hmm, I don’t think that’s possible. You have to use Moor’s generated classes for the DB. You could, however, convert your existing model objects into Moor objects.

  • >