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

33  comments

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.

About the author 

Matt Rešetár

Matt is an app developer with a knack for teaching others. Working as a freelancer and most importantly developer educator, he is set on helping other people succeed in their Flutter app development career.

You may also like

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

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

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

  • Thank you for the article.
    But I can’t understand – For example, you have an SQLite database on a VPS. How to connect to that database via Moor? Or moor – only for local databases?

  • Your tutorials seem great to me! Can you teach about how to do with several tables with SQLite and Flutter?

  • It’s not working for me, I m using latest 2.2.0 version of moor.

    eg Future insertTask(Insertable pin) => into(pins).insert(pin);
    it gives error
    Undefined name ‘pins’.
    Try correcting the name to one that is defined, or defining the name.

    Pin is table class

  • Hi, your tutorial is great, thanks, could you help me in the use of batch insert. I was reading but I Can’t achieve this.

  • Thank you for the tutorials. Did moor work on windows desktop application ? because am trying to get this example work with a windows application, but there is no auto generated classes. (for example : _$AppDatabase)

  • Hi Matt, thanks for the tutorial,
    but i am getting an error, once i run ‘ flutter packages pub run build_runner watch ‘ the file doesn’t get generated , i’ve tried couple of times but i cannot figure out what’s wrong.

    —— look at the codes

    import ‘package:moor_flutter/moor_flutter.dart’;

    part ‘moor_database.g.dart’;

    class Tasks extends Table {
    // Defining properties (Table columns)

    // by default the id id set as primary key when applying autoIncrement(),
    // but can be overriden using a setter
    IntColumn get id => integer().autoIncrement().call();
    TextColumn get name => text().withLength(min: 1, max: 50)();
    DateTimeColumn get dueDate => dateTime()();
    BoolColumn get completed => boolean().withDefault(Constant(false))();

    }

    // The database class
    @UseMoor(tables: [Tasks])
    class AppDatabase extends _$AppDatabase {
    AppDatabase() : super(FlutterQueryExecutor.inDatabaseFolder(path: ‘db.sqlite’, logStatements: true));

    }

    —– this is the error
    [SEVERE] moor_generator:moor_generator on lib/data/moor_database.dart:
    Error running MoorGenerator
    NoSuchMethodError: The getter ‘typeConverter’ was called on null.
    Receiver: null
    Tried calling: typeConverter

    Thanks for helping me out

      • LazyDatabase _openConnection() {
        // the LazyDatabase util lets us find the right location for the file async.
        return LazyDatabase(() async {
        // put the database file, called db.sqlite here, into the documents folder
        // for your app.
        final dbFolder = await getApplicationDocumentsDirectory();
        final file = File(p.join(dbFolder.path, ‘db.sqlite’));
        return VmDatabase(file);
        });
        }

        // this annotation tells moor to prepare a database class that uses both of the
        // tables we just defined. We’ll see how to use that database class in a moment.
        @UseMoor(tables: [Tasks])
        class AppDatabase extends _$AppDatabase {
        AppDatabase() : super(_openConnection());

        @override
        int get schemaVersion => 1;

        Future<List> getAllTasks() => select(tasks).get();
        Stream<List> watchAllTasks() => select(tasks).watch();
        Future insertTask(Task task) => into(tasks).insert(task);
        Future updateTask(Task task) => update(tasks).replace(task);
        Future deleteTask(Task task) => delete(tasks).delete(task);
        }

  • Hi, Thanks for the tutorial .
    as following your tutorial….
    the task is not been saved on textField submit, also giving no errors , so unable to figure out the actual problem .

    • i fixed with 2 things:
      – final database = Provider.of(context);
      to
      final database = Provider.of(context, listen: false);
      – final task = Task(
      name: inputName,
      dueDate: newTaskDate,
      );
      to
      final task = TasksCompanion(
      name: Value(inputName),
      dueDate: Value(newTaskDate),
      );

      Maybe because of more recent version of Provider package and moor_ffi instead of moor_flutter package like it’s recommand in the docs.

      • And in the moor_database.dart:
        Future insertTask(TasksCompanion task) => into(tasks).insert(task);

        Because we send partials data to the insert, so we use de Companion class

  • Thanks for another great tutorial for Flutter. I love them.

    My question is where would be place the database files using the clean architecture explained in the another tutorial?

    Thanks in advance
    Best regards

  • Thanks a lot.
    I’m using latest moor(^3.0.2) and provider(^4.1.1), everything works perfect except using Provider.of(context). It fails to get the provider ……

    I changed to use context.read(), it works!

    Just share it in case others have similar problems.

  • Does it make sense to use this in the infrastructure layer of the app – which would then notify the viewmodel of updates – which would transform whatever into what is needed for the UI?

  • Is there any good reason not to follow Singleton pattern in the constructor of the Database class and instead use Provider/IoC/Get_it to get Singleton instance of Database

  • A value of type ‘Future<List>’ can’t be returned from method ‘getAllTasks’ because it has a return type of ‘Future<List>’.

    I am getting these error message.
    version: 3.2.0

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    >