Moor (Room for Flutter) #3 – Foreign Keys, Joins & Migrations – Fluent SQLite Database

14  comments

Your app's database will probably have more than one table. You want to store tasks together with their tags, or maybe recipes and their authors. No matter what you store, it's usually the case that some of the tables are quite interconnected and it would be perfect to get just the right data from both tables at the same time. That's what SQL joins are for.

To make joins safe, it's a good idea to use foreign keys. Otherwise you might end up trying to get a tag or a recipe which doesn't exist... and that's not all that good.

Finally, adding new columns or whole tables requires you to provide a migration to let the previous versions of the database upgrade themselves.

We're building a task list application in this series, so we will add tags to the mix. They will be stored inside a separate table. Every task can have a tag and to get the tasks together with tags, we will utilize foreign keys and table joins. The user interface will also change a bit (I know, it's not glamorous ?).

New and updated packages

With the updated UI comes the need for a new package - a material color picker. Version of the Moor package has also gotten updated since the last part, so let's use the latest one for good measure. 

pubspec.yaml

dependencies:
  flutter:
    sdk: flutter
  moor_flutter: ^1.6.0
  # For the UI
  provider: ^3.0.0+1
  flutter_slidable: ^0.5.3
  flutter_material_color_picker: ^1.0.0

...

dev_dependencies:
  flutter_test:
    sdk: flutter
  moor_generator: ^1.6.0
  build_runner:

Adding tags to the DB

Similar to how we have a table definition for Tasks, we will have one for Tags. A tag will have a name and a color represented as an integer. Name will be set as the primary key of the tag which, among other things, will make tag names unique.

moor_database.dart

class Tags extends Table {
  TextColumn get name => text().withLength(min: 1, max: 10)();
  IntColumn get color => integer()();

  // Making name as the primary key of a tag requires names to be unique
  @override
  Set<Column> get primaryKey => {name};
}
Of course, you can add the classic auto-incrementing id column to the tags table. We're using the name as the key for reducing the amount of lines of code in this tutorial.

Now is a good time to kick off the build process as we will make a lot of changes to this file down the line.

flutter packages pub run build_runner watch

Having a table, it will be beneficial if we can add and access its contents. We will create a TagDao to keep the queries separate from the TaskDao. There will be only two queries - watchTags and insertTag.

moor_database.dart

@UseDao(tables: [Tags])
class TagDao extends DatabaseAccessor<AppDatabase> with _$TagDaoMixin {
  final AppDatabase db;

  TagDao(this.db) : super(db);

  Stream<List<Tag>> watchTags() => select(tags).watch();
  Future insertTag(Insertable<Tag> tag) => into(tags).insert(tag);
}

Finally, update the @UseMoor annotation on the AppDatabase with the new table and DAO.

@UseMoor(tables: [Tasks, Tags], daos: [TaskDao, TagDao])

Referencing tags

Individual tasks will use the name of a tag to reference it. Also, a task doesn't necessarily have to have a tag, in which case it will simply reference null. Joining tasks together with their tags will be done by looking up the referenced tag name in the tags table. Of course, we won't write this lookup code ourselves, we will let SQL and Moor do their job. Let's add a new column to the Tasks table definition.

moor_database.dart

class Tasks extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get tagName => text().nullable()();
  TextColumn get name => text().withLength(min: 1, max: 50)();
  DateTimeColumn get dueDate => dateTime().nullable()();
  BoolColumn get completed => boolean().withDefault(Constant(false))();
}

Do you see a problem in the code above? The tagName column can contain values (apart from null) which don't reference any tag at all. How should then the join statement know what to join if the task's tag doesn't exist?

Foreign key

To add some security to the tasks table's data, we will use a foreign key constraint on the tagName column. This way, when we try to insert a new task and there isn't already a tag with the specified name in the tags table, an exception will be thrown and the whole join disaster will be halted before it even started.

Let's apply a customConstraint on the tagName property to make sure it references values present in the tags table's name column.

TextColumn get tagName =>
    text().nullable().customConstraint('NULL REFERENCES tags(name)')();
Why does the tagName column have NULL constraints applied twice - once in fluent syntax and another time in the customConstraint?

It's because custom constraints disable the fluent ones in the actual DB, but we still want the generated Tag data class to have the tagName field be not required.

Migrations

At this point, before we happily join tasks with tags and run the app, we have to think about letting the already existing database version know about the changed schema. Sure, we could just create a brand new database, just as it's done automatically on the first launch of the app, but that would wipe all of the stored data, and we definitely don't want that to happen.

Instead, we will provide a migration strategy for going from version 1 of the database without tags to version 2 with tags. Together with this, we will actually bump up the schemaVersion property. This will all happen inside the database class.

moor_database.dart

@UseMoor(tables: [Tasks, Tags], daos: [TaskDao, TagDao])
class AppDatabase extends _$AppDatabase {
  AppDatabase()
      : super((FlutterQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
          logStatements: true,
        )));

  @override
  int get schemaVersion => 2;

  @override
  MigrationStrategy get migration => MigrationStrategy(
        // Runs if the database has already been opened on the device with a lower version
        onUpgrade: (migrator, from, to) async {
          if (from == 1) {
            await migrator.addColumn(tasks, tasks.tagName);
            await migrator.createTable(tags);
          }
        },
      );
}

Enabling foreign keys

This migration property ties in nicely with the foreign key we've added previously. It turns out that foreign keys are actually not enabled by default in SQLite - we have to enable them ourselves with a custom statement. We want to run this statement before any other queries are run to prevent the chance of  "unchecked data" from entering the database. This is a perfect use-case for the beforeOpen callback.

moor_database.dart

@override
MigrationStrategy get migration => MigrationStrategy(
      ...
      // Runs after all the migrations but BEFORE any queries have a chance to execute
      beforeOpen: (db, details) async {
        await db.customStatement('PRAGMA foreign_keys = ON');
      },
    );

Joining the tables

With all of the very necessary setup in place, we can finally jump onto the icing on the cake - the actual table join. First, we'll create a simple class to group a task with its tag, hence the name TaskWithTag.

moor_database.dart

// We have to group tasks with tags manually.
// This class will be used for the table join.
class TaskWithTag {
  final Task task;
  final Tag tag;

  TaskWithTag({
    @required this.task,
    @required this.tag,
  });
}

The join itself will happen inside the TaskDao, so we have to update it to also access the tags table, and we'll also remove all of the code from the previous part which would now just clutter up the class (you can always get the old code from the link or from the git repository).

The method watchAllTasks will no longer return a simple List<Task>, but a List<TaskWithTag> instead. Previously, we only "selected, ordered and watched". Now, we will "select, order, join and watch". Watching on a join statement gets us a List<TypedResult>, so we'll have to manually map it to our desired List<TaskWithTag>.

moor_database.dart

// Also accessing the Tags table for the join
@UseDao(
  tables: [Tasks, Tags],
)
class TaskDao extends DatabaseAccessor<AppDatabase> with _$TaskDaoMixin {
  final AppDatabase db;

  // Called by the AppDatabase class
  TaskDao(this.db) : super(db);

// Return TaskWithTag now
  Stream<List<TaskWithTag>> watchAllTasks() {
    // Wrap the whole select statement in parenthesis
    return (select(tasks)
          // Statements like orderBy and where return void => the need to use a cascading ".." operator
          ..orderBy(
            ([
              // Primary sorting by due date
              (t) =>
                  OrderingTerm(expression: t.dueDate, mode: OrderingMode.asc),
              // Secondary alphabetical sorting
              (t) => OrderingTerm(expression: t.name),
            ]),
          ))
        // As opposed to orderBy or where, join returns a value. This is what we want to watch/get.
        .join(
          [
            // Join all the tasks with their tags.
            // It's important that we use equalsExp and not just equals.
            // This way, we can join using all tag names in the tasks table, not just a specific one.
            leftOuterJoin(tags, tags.name.equalsExp(tasks.tagName)),
          ],
        )
        // watch the whole select statement including the join
        .watch()
        // Watching a join gets us a Stream of List<TypedResult>
        // Mapping each List<TypedResult> emitted by the Stream to a List<TaskWithTag>
        .map(
          (rows) => rows.map(
            (row) {
              return TaskWithTag(
                task: row.readTable(tasks),
                tag: row.readTable(tags),
              );
            },
          ).toList(),
        );
  }

  Future insertTask(Insertable<Task> task) => into(tasks).insert(task);
  Future updateTask(Insertable<Task> task) => update(tasks).replace(task);
  Future deleteTask(Insertable<Task> task) => delete(tasks).delete(task);
}
We're using a leftOuterJoin for this particular situation. Moor also supports an innerJoin and crossJoin. Learn more about joins and the differences between them on Wikipedia.

Updating the UI

The user interface will be changed to work with tags. Since we've removed the "completed tasks only" queries from the TaskDao to keep the code simpler, we'll also remove the UI for this functionality.

As usual, I won't really explain the UI-coding part in much detail. If making UIs (which look much better than this one ?) is something you're interested in, let me know!

First, we will add a Provider for the TagDao using a MultiProvider widget inside main.dart.

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) {
    final db = AppDatabase();
    return MultiProvider(
      providers: [
        Provider(builder: (_) => db.taskDao),
        Provider(builder: (_) => db.tagDao),
      ],
      child: MaterialApp(
        title: 'Material App',
        home: HomePage(),
      ),
    );
  }
}

We'll add a NewTagInputWidget which will very much copy the NewTaskInputWidget. This is where we'll use the MaterialColorPicker inside a popup AlertDialog. New tags will be added to the database using the TagDao from the onSubmitted callback on a TextField.

new_tag_input_widget.dart

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

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

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

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

class _NewTagInputState extends State<NewTagInput> {
  static const Color DEFAULT_COLOR = Colors.red;

  Color pickedTagColor = DEFAULT_COLOR;
  TextEditingController controller;

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

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

  Flexible _buildTextField(BuildContext context) {
    return Flexible(
      flex: 1,
      child: TextField(
        controller: controller,
        decoration: InputDecoration(hintText: 'Tag Name'),
        onSubmitted: (inputName) {
          final dao = Provider.of<TagDao>(context);
          final task = TagsCompanion(
            name: Value(inputName),
            color: Value(pickedTagColor.value),
          );
          dao.insertTag(task);
          resetValuesAfterSubmit();
        },
      ),
    );
  }

  Widget _buildColorPickerButton(BuildContext context) {
    return Flexible(
      flex: 1,
      child: GestureDetector(
        child: Container(
          width: 25,
          height: 25,
          decoration: BoxDecoration(
            shape: BoxShape.circle,
            color: pickedTagColor,
          ),
        ),
        onTap: () {
          _showColorPickerDialog(context);
        },
      ),
    );
  }

  Future _showColorPickerDialog(BuildContext context) {
    return showDialog(
      context: context,
      builder: (context) {
        return AlertDialog(
          content: MaterialColorPicker(
            allowShades: false,
            selectedColor: DEFAULT_COLOR,
            onMainColorChange: (colorSwatch) {
              setState(() {
                pickedTagColor = colorSwatch;
              });
              Navigator.of(context).pop();
            },
          ),
        );
      },
    );
  }

  void resetValuesAfterSubmit() {
    setState(() {
      pickedTagColor = DEFAULT_COLOR;
      controller.clear();
    });
  }
}

The NewTaskInputWidget will also change a bit. It will now have a "tag selector" implemented as a DropdownButton. Items in this DropdownButton will be populated by watching tags on the TagDao.

new_task_input_widget.dart

import 'package:flutter/material.dart';
import 'package:moor/moor.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;
  Tag selectedTag;
  TextEditingController controller;

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

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

  Expanded _buildTextField(BuildContext context) {
    return Expanded(
      flex: 1,
      child: TextField(
        controller: controller,
        decoration: InputDecoration(hintText: 'Task Name'),
        onSubmitted: (inputName) {
          final dao = Provider.of<TaskDao>(context);
          final task = TasksCompanion(
            name: Value(inputName),
            dueDate: Value(newTaskDate),
            tagName: Value(selectedTag?.name),
          );
          dao.insertTask(task);
          resetValuesAfterSubmit();
        },
      ),
    );
  }

  StreamBuilder<List<Tag>> _buildTagSelector(BuildContext context) {
    return StreamBuilder<List<Tag>>(
      stream: Provider.of<TagDao>(context).watchTags(),
      builder: (context, snapshot) {
        final tags = snapshot.data ?? List();

        DropdownMenuItem<Tag> dropdownFromTag(Tag tag) {
          return DropdownMenuItem(
            value: tag,
            child: Row(
              children: <Widget>[
                Text(tag.name),
                SizedBox(width: 5),
                Container(
                  width: 15,
                  height: 15,
                  decoration: BoxDecoration(
                    shape: BoxShape.circle,
                    color: Color(tag.color),
                  ),
                ),
              ],
            ),
          );
        }

        final dropdownMenuItems =
            tags.map((tag) => dropdownFromTag(tag)).toList()
              // Add a "no tag" item as the first element of the list
              ..insert(
                0,
                DropdownMenuItem(
                  value: null,
                  child: Text('No Tag'),
                ),
              );

        return Expanded(
          child: DropdownButton(
            onChanged: (Tag tag) {
              setState(() {
                selectedTag = tag;
              });
            },
            isExpanded: true,
            value: selectedTag,
            items: dropdownMenuItems,
          ),
        );
      },
    );
  }

  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;
      selectedTag = null;
      controller.clear();
    });
  }
}

Finally, we'll also change HomePage to display the NewTagInputWidget and also to show the tag of a task in the ListView.

home_page.dart

import 'package:floor_introduction_prep/ui/widget/new_tag_input_widget.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(),
            NewTagInput(),
          ],
        ));
  }

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

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

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

  Column _buildTag(Tag tag) {
    return Column(
      mainAxisAlignment: MainAxisAlignment.center,
      crossAxisAlignment: CrossAxisAlignment.start,
      children: <Widget>[
        if (tag != null) ...[
          Container(
            width: 10,
            height: 10,
            decoration: BoxDecoration(
              shape: BoxShape.circle,
              color: Color(tag.color),
            ),
          ),
          Text(
            tag.name,
            style: TextStyle(
              color: Colors.black.withOpacity(0.5),
            ),
          ),
        ],
      ],
    );
  }
}

Conclusion

This part was probably the most advanced one in this series. You've learned how to perform table joins safely by using a foreign key and also how to use migrations to upgrade the schema of an already existing database. With all this knowledge, nothing can stop you from building your own fully-functional apps with local databases.

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

    • Since tag name is also the foreign key, you’d have to update the tasks too. One option to solve this is to have an auto-incrementing ID field even for the tags and use that as the foreign key in the tasks table.

  • I love yours toturial, please give an exemple how can i do back up my database or sincronazer it in other place like firebase or mysql or in online driver.

  • Hi, thanks for your helpful guides! I’ve tried to adapt your project to my own and encountered a problem. Setting an attribute as the primary key like you did doesn’t prevent duplicates from being added to the table. Why could that be?

  • Now to enable foreign keys, instead of

    beforeOpen: (db, details) async {
    await db.customStatement(‘PRAGMA foreign_keys = ON’);
    },

    we have to write:

    beforeOpen: (details) async {
    await customStatement(‘PRAGMA foreign_keys = ON’);
    },

    With version 2.0.0

    • thank you..how are you handling exceptions? I am having some problems handling SQLite exceptions…I have left a comment for Reso Coder regarding this…pls feel free to reply if u like.

  • Hello Is it possible to add a virtual list in a moor data class and save the header and detail at same time using key.

    C# Example
    public string j3name { get; set; }
    public virtual List NonGlobalPreference { get; set; }

  • hi Reso Coder,

    I am getting SQLite exceptions printed out on the console.

    How can I handle these exceptions in a try catch block?

    Wrapping, for example dao.insert() method, inside try catch block doesn’t catch the exception and also, it is printed to the console.

  • Hello, first of all I would like to thank you for your great and awesome tutorials.

    I’m new to Moor and Flutter, I’m facing an issue while trying to use DAO with ElevatedButton.icon

    Here is my code

    Widget _buildSubmitButton(BuildContext context) {
    return ElevatedButton.icon(
    style: ButtonStyle(
    backgroundColor: MaterialStateProperty.all(Color(0xFF133C3E))
    ),
    icon: Icon(
    Icons.check,
    color: Colors.white70,
    ),
    label: Text(
    ‘Enregistrer’,
    style: TextStyle(color: Colors.white70, fontSize: 17.0),
    ),
    onPressed: () {
    // Validate returns true if the form is valid, or false otherwise.
    if (_formKey.currentState!.validate()) {

    final dao = Provider.of(context, listen: false);
    final fee = FeesCompanion(
    name: Value(name),
    amount: Value(amount),
    );
    dao.insertFee(fee);
    //resetValuesAfterSubmit();

    // If the form is valid, display a snackbar. In the real world,
    // you’d often call a server or save the information in a database.
    // ScaffoldMessenger.of(context)
    // .showSnackBar(SnackBar(content: Text(‘Processing Data’)));
    }
    },
    );
    }

    Flutter debugger throws the following exception:
    Error: Could not find the correct Provider above this FeeForm Widget

    Any idea how to fix this?

    Thank you once again!

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