Getting started with sql
The regular getting started guide explains how to get started with moor by declaring both tables and queries in Dart. This version will focus on how to use moor with SQL instead.
Adding the dependency
First, lets add moor to your project’s pubspec.yaml
.
At the moment, the current version of moor
is
and the latest version of
moor_generator
is
dependencies:
moor: # use the latest version
sqlite3_flutter_libs: # Also use the latest version.
path_provider:
path:
dev_dependencies:
moor_generator: # use the latest version
build_runner:
If you’re wondering why so many packages are necessary, here’s a quick overview over what each package does:
moor
: This is the core package defining most apissqlite3_flutter_libs
: Ships the latestsqlite3
version with your Android or iOS app.path_provider
andpath
: Used to find a suitable location to store the database. Maintained by the Flutter and Dart teammoor_generator
: Generates Dart apis for the sql queries and tables you wrotebuild_runner
: Common tool for code-generation, maintained by the Dart team
Changes to moor/ffi
Previous versions of this article recommended to use moor_flutter
or
the moor_ffi
package.
For new users, we recommend to use package:moor/ffi.dart
to open the database -
more on that below!
If you have an existing setup that works, there's no need to change anything.
Some versions of the Flutter tool create a broken settings.gradle
on Android, which can cause problems with moor_ffi
.
If you get a “Failed to load dynamic library” exception, see this comment.
Declaring tables and queries
To declare tables and queries in sql, create a file called tables.moor
next to your Dart files (for instance in lib/database/tables.moor
).
You can put CREATE TABLE
statements for your queries in there.
The following example creates two tables to model a todo-app. If you’re
migrating an existing project to moor, you can just copy the CREATE TABLE
statements you’ve already written into this file.
-- this is the tables.moor file
CREATE TABLE todos (
id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
title TEXT,
body TEXT,
category INT REFERENCES categories (id)
);
CREATE TABLE categories (
id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
description TEXT
) AS Category; -- see the explanation on "AS Category" below
/* after declaring your tables, you can put queries in here. Just
write the name of the query, a colon (:) and the SQL: */
todosInCategory: SELECT * FROM todos WHERE category = ?;
/* Here's a more complex query: It counts the amount of entries per
category, including those entries which aren't in any category at all. */
countEntries:
SELECT
c.description,
(SELECT COUNT(*) FROM todos WHERE category = c.id) AS amount
FROM categories c
UNION ALL
SELECT null, (SELECT COUNT(*) FROM todos WHERE category IS NULL)
On that AS Category
Moor will generate Dart classes for your tables, and the name of those classes is based on the table name. By default, moor just strips away the trailings
from your table. That works for most cases, but in some
(like the categories
table above), it doesn’t. We’d like to have a
Category
class (and not Categorie
) generated, so we tell moor to
generate a different name with the AS <name>
declaration at the end.
Generating matching code
After you declared the tables, lets generate some Dart code to actually
run them. Moor needs to know which tables are used in a database, so we
have to write a small Dart class that moor will then read. Lets create
a file called database.dart
next to the tables.moor
file you wrote
in the previous step.
import 'dart:io';
import 'package:moor/moor.dart';
// These imports are only needed to open the database
import 'package:moor/ffi.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;
part 'database.g.dart';
@UseMoor(
// relative import for the moor file. Moor also supports `package:`
// imports
include: {'tables.moor'},
)
class AppDb extends _$AppDb {
AppDb() : super(_openConnection());
@override
int get schemaVersion => 1;
}
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);
});
}
To generate the database.g.dart
file which contains the _$AppDb
superclass, run flutter pub run build_runner build
on the command
line.
What moor generates
Let’s take a look at what moor generated during the build:
- Generated data classes (
Todo
andCategory
) - these hold a single row from the respective table. - Companion versions of these classes. Those are only relevant when using the Dart apis of moor, you can learn more here.
- A
CountEntriesResult
class, it holds the result rows when running thecountEntries
query. - A
_$AppDb
superclass. It takes care of creating the tables when the database file is first opened. It also contains typesafe methods for the queries declared in thetables.moor
file:- a
Selectable<Todo> todosInCategory(int)
method, which runs thetodosInCategory
query declared above. Moor has determined that the type of the variable in that query isint
, because that’s the type of thecategory
column we’re comparing it to.
The method returns aSelectable
to indicate that it can both be used as a regular query (Selectable.get
returns aFuture<List<Todo>>
) or as an auto-updating stream (by using.watch
instead of.get()
). - a
Selectable<CountEntriesResult> countEntries()
method, which runs the other query when used.
- a
By the way, you can also put insert, update and delete statements in
a .moor
file - moor will generate matching code for them as well.
Learning more
Now that you know how to use moor together with sql, here are some further guides to help you learn more:
- The SQL IDE that provides feedback on sql queries right in your editor.
- Transactions
- Schema migrations
- Writing queries and expressions in Dart
- A more in-depth guide
on
moor
files, which explainsimport
statements and the Dart-SQL interop.