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.17 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 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'));
}

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 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 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 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.

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 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 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 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();
}