Manager

With generated code, drift allows writing SQL queries in type-safe Dart. While this is provides lots of flexibility, it requires familiarity with SQL. As a simpler alternative, drift 2.18 introduced a new set of APIs designed to make common queries much easier to write.

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.

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 categoriesWithReferences = await managers.todoItems
        .withReferences(
          (prefetch) => prefetch(category: true),
        )
        .get();
    for (final (todo, refs) in categoriesWithReferences) {
      final category = refs.category?.prefetchedData?.firstOrNull;
      // No longer needed
      // final category = await refs.category?.getSingle();
    }

    /// This also works in the reverse
    final todosWithRefs = await managers.todoCategory
        .withReferences((prefetch) => prefetch(todoItemsRefs: true))
        .get();
    for (final (category, refs) in todosWithRefs) {
      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();
}