---

title: Custom queries
description: Write SQL for advanced queries that drift can't express in Dart yet.

---



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:

<Snippet href="/lib/src/snippets/drift_files/custom_queries.dart" name="setup" />

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 `Selectable`s in drift, you can use `get()` to run the query once or `watch()` to get an auto-updating
stream of results:

!!! note "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](drift_files.md), which can be included into the database, are a really great fit for this, and may be easier
    to use.
    



<Snippet href="/lib/src/snippets/drift_files/custom_queries.dart" name="run" />

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](drift_files.md).

!!! info "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](drift_files.md) 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](../dart_api/daos.md),
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](../setup.md), we can
write this query which will load the amount of todo entries in each category:

<Snippet href="/lib/src/snippets/drift_files/custom_queries.dart" name="manual" />

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:

<Snippet href="/lib/src/snippets/drift_files/custom_queries.dart" name="amountOfTodosInCategory" />


Of course, you can also use indexed variables (like `?12`) - for more information on them, see
[the sqlite3 documentation](https://sqlite.org/lang_expr.html#varparam).

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