Custom queries

Although drift includes a fluent api that can be used to model most statements, advanced features like WITH clauses or some subqueries aren't supported yet. However, you can use methods like customSelect and customStatement to run advanced statements on the database by writing the SQL manually.

For most custom queries, drift can analyze their SQL at compile time, make sure they're valid and generate a type-safe API for them. This approach can be much safer than writing custom SQL at runtime.

This page describes both approaches: The first section introduces methods generated by drift, the second section gives an example for a custom query defined at runtime.

Statements with a generated api

You can instruct drift to automatically generate a type-safe API for your select, update and delete statements. Of course, you can still write custom sql manually. See the sections below for details.

To use this feature, all you need to is define your queries in your DriftDatabase annotation:

@DriftDatabase(
  tables: [TodoItems, Categories],
  queries: {
    'categoriesWithCount': 'SELECT *, '
        '(SELECT COUNT(*) FROM todo_items WHERE category = c.id) AS "amount" '
        'FROM categories c;'
  },
)
class MyDatabase extends $MyDatabase {
  // rest of class stays the same
}

After running the build step again, drift will have written the CategoriesWithCountResult class for you - it will hold the result of your query. Also, the _$MyDatabase class from which you inherit will have a Selectable<CategoriesWithCountResult> categoriesWithCount() method which can be used to run the query. Like all Selectables in drift, you can use get() to run the query once or watch() to get an auto-updating stream of results:

Better support for custom queries in drift files

Defining SQL in the @DriftDatabase annotation is a great way to define a few custom queries. For apps that use lots of custom queries, extracting them into separate files may be more manageable. Drift files, which can be included into the database, are a really great fit for this, and may be easier to use.

Future<void> useGeneratedQuery() async {
  // The generated query can be run once as a future:
  await categoriesWithCount().get();

  // Or multiple times as a stream
  await for (final snapshot in categoriesWithCount().watch()) {
    print('Found ${snapshot.length} category results');
  }
}

Queries can have parameters in them by using the ? or :name syntax. For parameters in queries, drift will figure out an appropriate type and include them in the generated methods. For instance, 'categoryById': 'SELECT * FROM categories WHERE id = :id' will generate the method categoryById(int id). Drift also supports additional convenience features in custom queries, like embededding Dart expressions in SQL. For more details, see the documentation on drift files.

On table names

To use this feature, it's helpful to know how Dart tables are named in sql. For tables that don't override tableName, the name in sql will be the snake_case of the class name. So a Dart table called Categories will be named categories, a table called UserAddressInformation would be called user_address_information. The same rule applies to column getters without an explicit name. Tables and columns declared in Drift files will always have the name you specified.

You can also use UPDATE or DELETE statements here. Of course, this feature is also available for daos, and it perfectly integrates with auto-updating streams by analyzing what tables you're reading from or writing to.

Custom select statements

If you don't want to use the statements with an generated api, you can still send custom queries by calling customSelect for a one-time query or customSelectStream for a query stream that automatically emits a new set of items when the underlying data changes. Using the todo example introduced in the getting started guide, we can write this query which will load the amount of todo entries in each category:

class CategoryWithCount {
  final Category category;
  final int count; // amount of entries in this category

  CategoryWithCount({required this.category, required this.count});
}

// then, in the database class:
Stream<List<CategoryWithCount>> allCategoriesWithCount() {
  // select all categories and load how many associated entries there are for
  // each category
  return customSelect(
    'SELECT *, (SELECT COUNT(*) FROM todos WHERE category = c.id) AS "amount"'
    ' FROM categories c;',
    // used for the stream: the stream will update when either table changes
    readsFrom: {todoItems, categories},
  ).watch().map((rows) {
    // we get list of rows here. We just have to turn the raw data from the
    // row into a CategoryWithCount instnace. As we defined the Category table
    // earlier, drift knows how to parse a category. The only thing left to do
    // manually is extracting the amount.
    return rows
        .map((row) => CategoryWithCount(
              category: categories.map(row.data),
              count: row.read<int>('amount'),
            ))
        .toList();
  });
}

For custom selects, you should use the readsFrom parameter to specify from which tables the query is reading. When using a Stream, drift will be able to know after which updates the stream should emit items.

You can also bind SQL variables by using question-mark placeholders and the variables parameter:

Stream<int> amountOfTodosInCategory(int id) {
  return customSelect(
    'SELECT COUNT(*) AS c FROM todo_items WHERE category = ?',
    variables: [Variable.withInt(id)],
    readsFrom: {todoItems},
  ).map((row) => row.read<int>('c')).watchSingle();
}

Of course, you can also use indexed variables (like ?12) - for more information on them, see the sqlite3 documentation.

Custom update statements

For update and delete statements, you can use customUpdate. Just like customSelect, that method also takes an SQL statement and optional variables. You can also tell drift which tables will be affected by your query using the optional updates parameter. That will help with other select streams, which will then update automatically.