Moor is a powerful library for using an SQLite database from your Flutter apps by writing pure Dart code. In the first part of this series, we've only touched the basics of this package. Let's now take a look at some of the more advanced queries and also how to keep your code clean by separating it into Data Access Objects.
Updating the package
If you're reading this in the future, Moor might as well be at the version 3.0.0. Currently though, it just got updated to 1.5.0. This brings an opportunity to learn how to deal with certain breaking changes in packages using source generation.
pubspec.yaml
dependencies:
flutter:
sdk: flutter
moor_flutter: ^1.5.0
# Specify the moor "core" package too
moor: ^1.5.1
...
dev_dependencies:
flutter_test:
sdk: flutter
moor_generator: ^1.5.0
build_runner:Doing this will break the generated Moor class. That's no problem! We can run the build_runner as always and it will generate new code conformable to the new version.
Version 1.5.0 has added something called UpdateCompanions, which are generated companions to the data classes defined by us. If you're interested in what this precisely means, check out the package changelog. Whenever we want to insert/update/delete a Task, we can either specify a DataClass directly or use its UpdateCompanion.
Data Access Object
All of the code for queries currently lives inside the AppDatabase class itself. At the moment, that's perfectly fine - we are only dealing with Tasks. We have to think into the future though. Once we would start adding comments, habits and who knows what else into the app, placing all of the queries together would create a mess.
To separate the queries dealing with tasks (and in the future, with comments and habits), we can use data access objects (DAOs). Functionality of the queries will remain unchanged, they will only be moved to a new and cleaner place.
Creating a DAO
Every DAO has to extend a DatabaseAccessor class. The possibility to access the database tables will come from a generated mixin, which, in our case, will be called _$TaskDaoMixin.
The benefit of using a DAO is the separation of queries, so we will move all of the queries from the AppDatabase to the newly created TaskDao. Additionally, we will annotate the TaskDao with @UseDao - we want to tell it which tables it can access. Finally, DAOs are instantiated by the AppDatabase class, so we the TaskDao to add it to its annotation.
moor_database.dart
...
@UseMoor(tables: [Tasks], daos: [TaskDao])
// _$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;
}
// Denote which tables this DAO can access
@UseDao(tables: [Tasks])
class TaskDao extends DatabaseAccessor<AppDatabase> with _$TaskDaoMixin {
final AppDatabase db;
// Called by the AppDatabase class
TaskDao(this.db) : super(db);
Future<List<Task>> getAllTasks() => select(tasks).get();
Stream<List<Task>> 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);
}Queries
Before we write any more advanced fluent or even custom SQL queries, let's make the already present simple queries leverage the power of the new UpdateCompanions from version 1.5.0.
To be able to use both the Task data class and its generated TasksCompanion, the method parameter's type should be an Insertable<Task>.
moor_database.dart
...
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);
...Advanced fluent queries
Until now, we've been getting all of the tasks out of the database either as a single List or as a Stream. What about ordering them by due date? And what if we want to see only the completed tasks? Simple! We can use the Moor's fluent syntax and write everything in Dart.
Let's change the watchAllTasks method to also order tasks primarily by due date and then alphabetically. Also, we will add a watchCompletedTasks method which, in addition to ordering, will also return only the completed ones.
moor_database.dart
...
// Updated to use the orderBy statement
Stream<List<Task>> 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.desc),
// Secondary alphabetical sorting
(t) => OrderingTerm(expression: t.name),
]),
))
// watch the whole select statement
.watch();
}
Stream<List<Task>> watchCompletedTasks() {
// where returns void, need to use the cascading operator
return (select(tasks)
..orderBy(
([
// Primary sorting by due date
(t) =>
OrderingTerm(expression: t.dueDate, mode: OrderingMode.desc),
// Secondary alphabetical sorting
(t) => OrderingTerm(expression: t.name),
]),
)
..where((t) => t.completed.equals(true)))
.watch();
}
...Writing custom SQL
Sometimes, Moor's fluent syntax just doesn't cut it. Even though with each update the functionality of this package increases quite a lot, not everything is supported yet. If such thing happens, you can always fall back on writing pure SQL.
Typesafe generation
Starting with Moor 1.5.0, even SQL strings are typesafe! This is a blessing especially with complex queries. Also, defining typesafe queries is shorter than the option described below.
While you can do all kinds of SQL craziness by writing custom queries, we will simply duplicate the functionality of the fluent watchCompletedTasks function. SQL strings are specified in the @UseDao or @UseMoor annotations.
moor_database.dart
...
@UseDao(
tables: [Tasks],
queries: {
// An implementation of this query will be generated inside the _$TaskDaoMixin
// Both completeTasksGenerated() and watchCompletedTasksGenerated() will be created.
'completedTasksGenerated':
'SELECT * FROM tasks WHERE completed = 1 ORDER BY due_date DESC, name;'
},
)
...Non-typesafe custom SQL
Source generation queries currently support most, but not all statements. If you don't mind dealing with dynamic data, you can always write a custom method. The following uses the same SQL string as the generated method above.
moor_database.dart
...
// Watching complete tasks with a custom query
Stream<List<Task>> watchCompletedTasksCustom() {
return customSelectStream(
'SELECT * FROM tasks WHERE completed = 1 ORDER BY due_date DESC, name;',
// The Stream will emit new values when the data inside the Tasks table changes
readsFrom: {tasks},
)
// customSelect or customSelectStream gives us QueryRow list
// This runs each time the Stream emits a new value.
.map((rows) {
// Turning the data of a row into a Task object
return rows.map((row) => Task.fromData(row.data, db)).toList();
});
}
...Updating the UI code
We've changed quite a lot, from migrating queries over to the TaskDao class to adding a query for watching completed tasks. Let's reflect all of the changes and add new functionality to the UI.
The first thing is, we're no longer to provide the whole AppDatabase from main.dart, but only the TaskDao.
main.dart
...
class MyApp extends StatelessWidget {
@override
Widget build(BuildContext context) {
return Provider(
builder: (_) => AppDatabase().taskDao,
child: MaterialApp(
title: 'Material App',
home: HomePage(),
),
);
}
}As for the home_page.dart, in addition to using the TaskDao, we will add a Switch to the AppBar which will operate on a showCompleted boolean. Then, as we're building the List of Tasks in the _buildTaskList method, we will either watchAllTasks or watchCompletedTasks based on the value of the showCompleted boolean. Also, feel free to use the other "versions" of watchCompletedTasks which utilize custom SQL.
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> {
bool showCompleted = false;
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: Text('Tasks'),
actions: <Widget>[
_buildCompletedOnlySwitch(),
],
),
body: Column(
children: <Widget>[
Expanded(child: _buildTaskList(context)),
NewTaskInput(),
],
));
}
Row _buildCompletedOnlySwitch() {
return Row(
children: <Widget>[
Text('Completed only'),
Switch(
value: showCompleted,
activeColor: Colors.white,
onChanged: (newValue) {
setState(() {
showCompleted = newValue;
});
},
),
],
);
}
StreamBuilder<List<Task>> _buildTaskList(BuildContext context) {
final dao = Provider.of<TaskDao>(context);
return StreamBuilder(
stream: showCompleted ? dao.watchCompletedTasks() : dao.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, dao);
},
);
},
);
}
Widget _buildListItem(Task itemTask, TaskDao dao) {
return Slidable(
actionPane: SlidableDrawerActionPane(),
secondaryActions: <Widget>[
IconSlideAction(
caption: 'Delete',
color: Colors.red,
icon: Icons.delete,
onTap: () => dao.deleteTask(itemTask),
)
],
child: CheckboxListTile(
title: Text(itemTask.name),
subtitle: Text(itemTask.dueDate?.toString() ?? 'No date'),
value: itemTask.completed,
onChanged: (newValue) {
dao.updateTask(itemTask.copyWith(completed: newValue));
},
),
);
}
}Things will get interesting in the new_task_input_widget.dart file. We're currently instantiating a Task data class, but we omit two fields - id and completed, which is perfectly reasonable, since id is an auto incrementing integer and completed has a default value set to false.
new_task_input_widget.dart
// Current state of affairs...
final task = Task(
name: inputName,
dueDate: newTaskDate,
);Omitting these fields gives us warnings in the code - they will be set to null. Since the version 1.5.0, we should use the TaskCompanion class instead. This change happened for a sound reason - nulls should be reserved for real null values in the columns, not for to-be-initialized fields. Non-initialized fields in a class should instead be set to an "absent" value and this is done using UpdateCompanion classes.
new_task_input_widget.dart
...
Expanded _buildTextField(BuildContext context) {
return Expanded(
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),
);
dao.insertTask(task);
resetValuesAfterSubmit();
},
),
);
}
...Conclusion
We've covered a lot of ground in this part, there is still more to learn though. In the next part, you will learn how to perform table joins which is an important operation on relational SQL databases, so you definitely don't want to miss it!
Can you please tell how How can I consider either column as unique value or used Conflict strategy while inserting data?
Can you please guide me how to retrive distcint column .return Future<list>. in customselect query
Can we have different files for Each Table say Product will have
1. product_table.dart (Product Table declaration)
2. product_dao.dart (Product Dao)
3. my_database.dart (Database file)
Similarly, for other tables say Orders, Product Category..
Is it possible or no? I tried but I am getting error. I am still trying I will post my findings soon but would appreciate if you have any insight.