Writing queries https://drift.simonbinder.

eu/docs/getting-started/writing_queries/

: This assumes that you've already completed the setup.

For each table you've speci�ed in the @DriftDatabase annotation on your database class, a corresponding getter for a table will
be generated. That getter can be used to run statements:

// inside the database class, the `todos` getter has been created by drift.
@DriftDatabase(tables: [Todos, Categories])
class MyDatabase extends _$MyDatabase {

// the schemaVersion getter and the constructor from the previous page
// have been omitted.

// loads all todo entries

Future<List<Todo>> get allTodoEntries => select(todos).get();

// watches all todo entries in a given category. The stream will automatically
// emit new items whenever the underlying data changes.
Stream<List<Todo>> watchEntriesInCategory(Category c) {
return (select(todos)..where((t) => t.category.equals(c.id))).watch();
}
}

Select statements
You can create select statements by starting them with select(tableName) , where the table name is a �eld generated for you
by drift. Each table used in a database will have a matching �eld to run queries against. Any query can be run once with get() or
be turned into an auto-updating stream using watch() .

Where
You can apply �lters to a query by calling where() . The where method takes a function that should map the given table to an
Expression of boolean. A common way to create such expression is by using equals on expressions. Integer columns can also
be compared with isBiggerThan and isSmallerThan . You can compose expressions using a & b, a | b and a.not() . For
more details on expressions, see this guide.

Limit
You can limit the amount of results returned by calling limit on queries. The method accepts the amount of rows to return and
an optional o�set.

Future<List<Todo>> limitTodos(int limit, {int offset}) {

return (select(todos)..limit(limit, offset: offset)).get();
}

Ordering
You can use the orderBy method on the select statement. It expects a list of functions that extract the individual ordering terms
from the table. You can use any expression as an ordering term - for more details, see this guide.

1 of 5 1/20/23, 18:18
Writing queries https://drift.simonbinder.eu/docs/getting-started/writing_queries/

Future<List<Todo>> sortEntriesAlphabetically() {
return (select(todos)..orderBy([(t) => OrderingTerm(expression: t.title)])).get();
}

You can also reverse the order by setting the mode property of the OrderingTerm to OrderingMode.desc .

Single values
If you know a query is never going to return more than one row, wrapping the result in a List can be tedious. Drift lets you work
around that with getSingle and watchSingle :

Stream<Todo> entryById(int id) {

return (select(todos)..where((t) => t.id.equals(id))).watchSingle();
}

If an entry with the provided id exists, it will be sent to the stream. Otherwise, null will be added to stream. If a query used with
watchSingle ever returns more than one entry (which is impossible in this case), an error will be added instead.

Mapping
Before calling watch or get (or the single variants), you can use map to transform the result.

Stream<List<String>> contentWithLongTitles() {
final query = select(todos)
..where((t) => t.title.length.isBiggerOrEqualValue(16));

return query
.map((row) => row.content)
.watch();
}

Deferring get vs watch

If you want to make your query consumable as either a Future or a Stream , you can re�ne your return type using one of the
Selectable abstract base classes;

// Exposes `get` and `watch`

MultiSelectable<Todo> pageOfTodos(int page, {int pageSize = 10}) {
return select(todos)..limit(pageSize, offset: page);
}

// Exposes `getSingle` and `watchSingle`

SingleSelectable<Todo> entryById(int id) {
return select(todos)..where((t) => t.id.equals(id));
}

// Exposes `getSingleOrNull` and `watchSingleOrNull`

SingleOrNullSelectable<Todo> entryFromExternalLink(int id) {
return select(todos)..where((t) => t.id.equals(id));
}

These base classes don't have query-building or map methods, signaling to the consumer that they are complete results.

If you need more complex queries with joins or custom columns, see this site.

Updates and deletes

You can use the generated classes to update individual �elds of any row:

2 of 5 1/20/23, 18:18
Writing queries https://drift.simonbinder.eu/docs/getting-started/writing_queries/

Future moveImportantTasksIntoCategory(Category target) {

// for updates, we use the "companion" version of a generated class. This wraps the
// fields in a "Value" type which can be set to be absent using "Value.absent()". This
// allows us to separate between "SET category = NULL" (`category: Value(null)`) and not
// updating the category at all: `category: Value.absent()`.
return (update(todos)
..where((t) => t.title.like('%Important%'))
).write(TodosCompanion(
category: Value(target.id),
),
);
}

Future updateTodo(Todo entry) {

// using replace will update all fields from the entry that are not marked as a primary key.
// it will also make sure that only the entry with the same primary key will be updated.
// Here, this means that the row that has the same id as entry will be updated to reflect
// the entry's title, content and category. As its where clause is set automatically, it
// cannot be used together with where.
return update(todos).replace(entry);
}

Future feelingLazy() {
// delete the oldest nine tasks
return (delete(todos)..where((t) => t.id.isSmallerThanValue(10))).go();
}

� If you don't explicitly add a where clause on updates or deletes, the statement will a�ect all rows in the table!

Entries, companions - why do we need all of this?

You might have noticed that we used a TodosCompanion for the �rst update instead of just passing a Todo . Drift generates
the Todo class (also called data class for the table) to hold a row with all its data. For partial data, prefer to use
companions. In the example above, we only set the the category column, so we used a companion. Why is that necessary?
If a �eld was set to null , we wouldn't know whether we need to set that column back to null in the database or if we
should just leave it unchanged. Fields in the companions have a special Value.absent() state which makes this explicit.

Companions also have a special constructor for inserts - all columns which don't have a default value and aren't nullable are
marked @required on that constructor. This makes companions easier to use for inserts because you know which �elds to
set.

Inserts
You can very easily insert any valid object into tables. As some values can be absent (like default values that we don't have to set
explicitly), we again use the companion version.

// returns the generated id

Future<int> addTodo(TodosCompanion entry) {
return into(todos).insert(entry);
}

All row classes generated will have a constructor that can be used to create objects:

addTodo(
TodosCompanion(
title: Value('Important task'),
content: Value('Refactor persistence code'),
),
);

3 of 5 1/20/23, 18:18
Writing queries https://drift.simonbinder.eu/docs/getting-started/writing_queries/

If a column is nullable or has a default value (this includes auto-increments), the �eld can be omitted. All other �elds must be set
and non-null. The insert method will throw otherwise.

Multiple insert statements can be run e�ciently by using a batch. To do that, you can use the insertAll method inside a batch :

Future<void> insertMultipleEntries() async{

await batch((batch) {
// functions in a batch don't have to be awaited - just
// await the whole batch afterwards.
batch.insertAll(todos, [
TodosCompanion.insert(
title: 'First entry',
content: 'My content',
),
TodosCompanion.insert(
title: 'Another entry',
content: 'More content',
// columns that aren't required for inserts are still wrapped in a Value:
category: Value(3),
),
// ...
]);
});
}

Batches are similar to transactions in the sense that all updates are happening atomically, but they enable further optimizations
to avoid preparing the same SQL statement twice. This makes them suitable for bulk insert or update operations.

Upserts
Upserts are a feature from newer sqlite3 versions that allows an insert to behave like an update if a con�icting row already exists.

This allows us to create or override an existing row when its primary key is part of its data:

class Users extends Table {

TextColumn get email => text()();
TextColumn get name => text()();

@override
Set<Column> get primaryKey => {email};
}

Future<int> createOrUpdateUser(User user) {

return into(users).insertOnConflictUpdate(user);
}

When calling createOrUpdateUser() with an email address that already exists, that user's name will be updated. Otherwise, a
new user will be inserted into the database.

Inserts can also be used with more advanced queries. For instance, let's say we're building a dictionary and want to keep track of
how many times we encountered a word. A table for that might look like

class Words extends Table {

TextColumn get word => text()();
IntColumn get usages => integer().withDefault(const Constant(1))();

@override
Set<Column> get primaryKey => {word};
}

By using a custom upserts, we can insert a new word or increment its usages counter if it already exists:

4 of 5 1/20/23, 18:18
Writing queries https://drift.simonbinder.eu/docs/getting-started/writing_queries/

Future<void> trackWord(String word) {

return into(words).insert(
WordsCompanion.insert(word: word),
onConflict: DoUpdate((old) => WordsCompanion.custom(usages: old.usages + Constant(1))),
);
}

Unique constraints and con�ict targets

Both insertOnConflictUpdate and onConflict: DoUpdate use an DO UPDATE upsert in sql. This requires us to provide a
so-called "con�ict target", a set of columns to check for uniqueness violations. By default, drift will use the table's primary
key as con�ict target. That works in most cases, but if you have custom UNIQUE constraints on some columns, you'll need
to use the target parameter on DoUpdate in Dart to include those columns.

Note that this requires a fairly recent sqlite3 version (3.24.0) that might not be available on older Android devices when using
moor_flutter . NativeDatabases and sqlite3_flutter_libs includes the latest sqlite on Android, so consider using it if you
want to support upserts.

Also note that the returned rowid may not be accurate when an upsert took place.

Returning
You can use insertReturning to insert a row or companion and immediately get the row it inserts. The returned row contains all
the default values and incrementing ids that were generated.

This uses the RETURNING syntax added in sqlite3 version 3.35, which is not available on most operating systems by default.
When using this method, make sure that you have a recent sqlite3 version available. This is the case with sqlite3_flutter_libs .

For instance, consider this snippet using the tables from the getting started guide:

final row = await into(todos).insertReturning(TodosCompanion.insert(

title: 'A todo entry',
content: 'A description',
));

The row returned has the proper id set. If a table has further default values, including dynamic values like CURRENT_TIME , then
those would also be set in a row returned by insertReturning .

5 of 5 1/20/23, 18:18