Manager

The examples on this page use the database from the setup instructions.

When manager generation is enabled (default), drift will generate a manager for each table in the database.
A collection of these managers are accessed by a getter managers on the database class. Each table will have a manager generated for it unless it uses a custom row class.

Select

The manager simplifies the process of retrieving rows from a table. Use it to read rows from the table or watch for changes.

Future<void> selectTodoItems() async {
  // Get all items
  managers.todoItems.get();

  // A stream of all the todo items, updated in real-time
  managers.todoItems.watch();

  // To get a single item, apply a filter and call `getSingle`
  await managers.todoItems.filter((f) => f.id(1)).getSingle();
}

The manager provides a really easy to use API for selecting rows from a table. These can be combined with | and & and parenthesis to construct more complex queries. Use .not to negate a condition.

Future<void> filterTodoItems() async {
  // All items with a title of "Title"
  managers.todoItems.filter((f) => f.title("Title"));

  // All items with a title of "Title" and content of "Content"
  managers.todoItems.filter((f) => f.title("Title") & f.content("Content"));

  // All items with a title of "Title" or content that is not null
  managers.todoItems.filter((f) => f.title("Title") | f.content.not.isNull());
}

Every column has filters for equality, inequality and nullability. Type specific filters for int, double, Int64, DateTime and String are included out of the box.

Future<void> filterWithType() async {
  // Filter all items created since 7 days ago
  managers.todoItems.filter(
      (f) => f.createdAt.isAfter(DateTime.now().subtract(Duration(days: 7))));

  // Filter all items with a title that starts with "Title"
  managers.todoItems.filter((f) => f.title.startsWith('Title'));
}

Referencing other tables

The manager also makes it easy to query an entities referenced fields by using the withReferences method. This will return a record with the entity and a refs object which contains the referenced fields.

  Future<void> references() async {
    /// Get each todo, along with a its categories
    final todosWithRefs = await managers.todoItems.withReferences().get();
    for (final (todo, refs) in todosWithRefs) {
      final category = await refs.category?.getSingle();
    }

    /// This also works in the reverse
    final categoriesWithRefs =
        await managers.todoCategory.withReferences().get();
    for (final (category, refs) in categoriesWithRefs) {
      final todos = await refs.todoItemsRefs.get();
    }
  }

The problem with the above approach is that it will issue a separate query for each row in the result set. This can be very inefficient if you have a large number of rows.
If there were 1000 todos, this would issue 1000 queries to fetch the category for each todo.

Filter on foreign keys

When filtering on a reference column, drift will apply the filter to the column itself instead of joining the referenced table. For example, todos.filter((f) => f.category.id(1)) will filter on the category column on the todos table, instead of joining the two tables and filtering on the id column of the categories table.

How does this affect me?

If you have foreign keys contraints enabled (PRAGMA foreign_keys = ON) this won't affect you. The database will enfore that the id column on the categories table is the same as the category column on the todos table.

If you don't have foreign key constraints enabled, you should be aware that the above query will not check that the category with id 1 exists. It will only check that the category column on the todos table is 1.

Prefetching references

Drift provides a way to prefetch references in a single query to avoid inefficient queries. This is done by using the callback in the withReferences method. The referenced item will then be available in the referenced managers prefetchedData field.

  Future<void> referencesPrefetch() async {
    /// Get each todo, along with a its categories
    final todosWithRefs = await managers.todoItems
        .withReferences(
          (prefetch) => prefetch(category: true),
        )
        .get();
    for (final (todo, refs) in todosWithRefs) {
      final category = refs.category?.prefetchedData?.firstOrNull;
      // No longer needed
      // final category = await refs.category?.getSingle();
    }

    /// This also works in the reverse
    final categoriesWithRefs = await managers.todoCategory
        .withReferences((prefetch) => prefetch(todoItemsRefs: true))
        .get();
    for (final (category, refs) in categoriesWithRefs) {
      final todos = refs.todoItemsRefs.prefetchedData;
      // No longer needed
      //final todos = await refs.todoItemsRefs.get();
    }
  }

Filtering across tables

You can filter across references to other tables by using the generated reference filters. You can nest these as deep as you'd like and the manager will take care of adding the aliased joins behind the scenes.

  Future<void> relationalFilter() async {
    // Get all items with a category description of "School"
    managers.todoItems.filter((f) => f.category.description("School"));

    // These can be combined with other filters
    // For example, get all items with a title of "Title" or a category description of "School"
    await managers.todoItems
        .filter(
          (f) => f.title("Title") | f.category.description("School"),
        )
        .exists();
  }

You can also filter across back references. This is useful when you have a one-to-many relationship and want to filter the parent table based on the child table.

  Future<void> reverseRelationalFilter() async {
    // Get the category that has a todo item with an id of 1
    managers.todoCategory.filter((f) => f.todoItemsRefs((f) => f.id(1)));

    // These can be combined with other filters
    // For example, get all categories with a description of "School" or a todo item with an id of 1
    managers.todoCategory.filter(
      (f) => f.description("School") | f.todoItemsRefs((f) => f.id(1)),
    );
  }

The code generator will name this filterset using the name of the table that is being referenced. In the above example, the filterset is named todoItemsRefs, because the TodoItems table is being referenced. However, you can also specify a custom name for the filterset using the @ReferenceName(...) annotation on the foreign key. This may be necessary if you have multiple references to the same table, take the following example:

class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
}

class Groups extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  @ReferenceName("administeredGroups")
  IntColumn get admin => integer().nullable().references(Users, #id)();
  @ReferenceName("ownedGroups")
  IntColumn get owner => integer().references(Users, #id)();
}

We can now use them in a query like this:

  Future<void> reverseNamedRelationalFilter() async {
    // Get all users who are administrators of a group with a name containing "Business"
    // or who own a group with an id of 1, 2, 4, or 5
    managers.users.filter(
      (f) =>
          f.administeredGroups((f) => f.name.contains("Business")) |
          f.ownedGroups((f) => f.id.isIn([1, 2, 4, 5])),
    );
  }

In this example, had we not specified a custom name for the reference, the code generator would have named both filtersets userRefs for both references to the User table. This would have caused a conflict. By specifying a custom name, we can avoid this issue.

Name Clashes

Drift auto-generates filters and orderings based on the names of your tables and fields. However, many times, there will be duplicates.
When this happens, you will see a warning message from the generator.
To fix this issue, use the @ReferenceName() annotation to specify what we should name the filter/orderings.

Ordering

You can also order the results of a query using the orderBy method. The syntax is similar to the filter method. Use the & to combine multiple orderings. Orderings are applied in the order they are added. You can also use ordering across multiple tables just like with filters.

  Future<void> orderWithType() async {
    // Order all items by their creation date in ascending order
    managers.todoItems.orderBy((o) => o.createdAt.asc());

    // Order all items by their title in ascending order and then by their content in ascending order
    managers.todoItems.orderBy((o) => o.title.asc() & o.content.asc());
  }

Count and exists

The manager makes it easy to check if a row exists or to count the number of rows that match a certain condition.

  Future<void> count() async {
    // Count all items
    await managers.todoItems.count();

    // Count all items with a title of "Title"
    await managers.todoItems.filter((f) => f.title("Title")).count();
  }

  Future<void> exists() async {
    // Check if any items exist
    await managers.todoItems.exists();

    // Check if any items with a title of "Title" exist
    await managers.todoItems.filter((f) => f.title("Title")).exists();
  }

Updates

We can use the manager to update rows in bulk or individual rows that meet a certain condition.

Future<void> updateTodoItems() async {
  // Update all items
  await managers.todoItems.update((o) => o(content: Value('New Content')));

  // Update multiple items
  await managers.todoItems
      .filter((f) => f.id.isIn([1, 2, 3]))
      .update((o) => o(content: Value('New Content')));
}

We can also replace an entire row with a new one. Or even replace multiple rows at once.

Future<void> replaceTodoItems() async {
  // Replace a single item
  var obj = await managers.todoItems.filter((o) => o.id(1)).getSingle();
  obj = obj.copyWith(content: 'New Content');
  await managers.todoItems.replace(obj);

  // Replace multiple items
  var objs =
      await managers.todoItems.filter((o) => o.id.isIn([1, 2, 3])).get();
  objs = objs.map((o) => o.copyWith(content: 'New Content')).toList();
  await managers.todoItems.bulkReplace(objs);
}

Creating rows

The manager includes a method for quickly inserting rows into a table. We can insert a single row or multiple rows at once.

Future<void> createTodoItem() async {
  // Create a new item
  await managers.todoItems
      .create((o) => o(title: 'Title', content: 'Content'));

  // We can also use `mode` and `onConflict` parameters, just
  // like in the `[InsertStatement.insert]` method on the table
  await managers.todoItems.create(
      (o) => o(title: 'Title', content: 'New Content'),
      mode: InsertMode.replace);

  // We can also create multiple items at once
  await managers.todoItems.bulkCreate(
    (o) => [
      o(title: 'Title 1', content: 'Content 1'),
      o(title: 'Title 2', content: 'Content 2'),
    ],
  );
}

Deleting rows

We may also delete rows from a table using the manager. Any rows that meet the specified condition will be deleted.

Future<void> deleteTodoItems() async {
  // Delete all items
  await managers.todoItems.delete();

  // Delete a single item
  await managers.todoItems.filter((f) => f.id(5)).delete();
}

Computed Fields

Manager queries are great when you need to select entire rows from a database table along with their related data. However, there are situations where you might want to perform more complex operations directly within the database for better efficiency.

Drift offers strong support for writing SQL expressions. These expressions can be used to filter data, sort results, and perform various calculations directly within your SQL queries. This means you can leverage the full power of SQL to handle complex logic right in the database, making your queries more efficient and your code cleaner.

If you want to learn more about how to write these SQL expressions, please refer to the expression documentation.

// First create an computed field with an expression you want to use
final titleLengthField =
    db.managers.todoItems.computedField((o) => o.title.length);

/// Create a copy of the manager with the computed fields you want to use
final manager = db.managers.todoItems.withFields([titleLengthField]);

// Then use the computed field in a filter
// This will filter all items whose title has exactly 10 characters
manager.filter((f) => titleLengthField.filter(10));

// You can also use the computed field in an ordering
// This will order all items by the length of their title in ascending order
manager.orderBy((o) => titleLengthField.order.asc());

/// You can read the result of the computed field too
for (final (item, refs) in await manager.get()) {
  final titleLength = titleLengthField.read(refs);
  print('Item ${item.id} has a title length of $titleLength');
}

You can write expressions which reference other columns in the same table or even other tables. The joins will be created automatically by the manager.

// This computed field will get the name of the user of this todo
final todoUserName =
    db.managers.todoItems.computedField((o) => o.category.user.name);

/// Create a copy of the manager with the computed fields you want to use
final manager = db.managers.todoItems.withFields([todoUserName]);

/// You can read the result of the computed field too
for (final (item, refs) in await manager.get()) {
  final userName = todoUserName.read(refs);
  print('Item ${item.id} has a user with the name $userName');
}

You can also use aggregate functions too.

// You can aggregate over multiple rows in a related table
// to perform calculations on them
final todoCountcomputedField = db.managers.todoCategory
    .computedField((o) => o.todoItemsRefs((o) => o.id).count());

/// Create a copy of the manager with the computed fields you want to use
final manager = db.managers.todoCategory.withFields([todoCountcomputedField]);

/// Read the result of the computed field
for (final (category, refs) in await manager.get()) {
  final todoCount = todoCountcomputedField.read(refs);
  print('Category ${category.id} has $todoCount todos');
}