One of the main benefits of using a query builder over raw SQL is that you can pull bits of your query out into functions and reuse them. In this guide, we’ll look at common patterns for extracting your code into re-usable pieces. We’ll also look at best practices for how to structure your code.

    All of our code examples are based on code from crates.io, a real world application which uses Diesel extensively. All of our examples will be focused on functions which return queries or pieces of queries. None of these examples will include a function which takes a database connection. We will go into the benefits of this structure at the end of the guide.

    crates.io has a canon_crate_name SQL function which is always used when comparing crate names. Rather than continuously writing canon_crate_name(crates::name).eq("some name"), we can instead pull this out into a function.

    src/krate/mod.rs

    1. use diesel::dsl::Eq;
    2. use diesel::prelude::sql_function;
    3. use diesel::sql_types::Text;
    5. sql_function!(fn canon_crate_name(x: Text) -> Text);
    7. type WithName<'a> =
    8. Eq<canon_crate_name::HelperType<crates::name>, canon_crate_name::HelperType<&'a str>>;
    10. fn with_name(name: &str) -> WithName {
    11. canon_crate_name(crates::name).eq(canon_crate_name(name))
    12. }

    Now when we want to find a crate by name, we can write crates::table.filter(with_name("foo")) instead. If we want to accept types other than a string, we can make the method generic.

    src/krate/mod.rs

    1. use diesel::dsl::Eq;
    2. use diesel::prelude::sql_function;
    3. use diesel::sql_types::Text;
    5. sql_function!(fn canon_crate_name(x: Text) -> Text);
    7. type WithName<T> = Eq<canon_crate_name::HelperType<crates::name>, canon_crate_name::HelperType<T>>;
    9. fn with_name<T>(name: T) -> WithName<T>
    10. where
    11. T: AsExpression<Text>,
    12. {
    13. canon_crate_name(crates::name).eq(canon_crate_name(name))
    14. }

    It’s up to you whether you make your functions generic, or only take a single type. We recommend only making these functions generic if it’s actually needed, since it requires additional bounds in your where clause. The bounds you need might not be clear, unless you are familiar with Diesel’s lower levels.

    In these examples, we are using helper types from diesel::dsl to write the return type explicitly. Nearly every method in Diesel has a helper type like this. The first type parameter is the method receiver (the thing before the .). The remaining type parameters are the arguments to the method. If we want to avoid writing this return type, or dynamically return a different expression, we can box the value instead.

    src/krate/mod.rs

    1. use diesel::pg::Pg;
    2. use diesel::prelude::sql_function;
    3. use diesel::sql_types::Text;
    5. sql_function!(fn canon_crate_name(x: Text) -> Text);
    7. fn with_name<'a, T>(name: T) -> Box<BoxableExpression<crates::table, Pg, SqlType = Bool> + 'a>
    8. where
    9. T: AsExpression<Text>,
    10. T::Expression: BoxableExpression<crates::table, Pg>,
    11. {
    12. canon_crate_name(crates::name).eq(canon_crate_name(name))
    13. }

    In order to use BoxableExpression, Diesel needs to know three things:

    • The table you intend to use it on
    • The backend you plan to execute it against
    • The SQL type it represents

    This is all the information Diesel uses to type check your query. Normally we can get this information from the type, but since we’ve erased the type by boxing, we have to supply it.

    The table is used to make sure that you don’t try to use users::name on a query against posts::table. We need to know the backend you will execute it on, so we don’t accidentally use a PostgreSQL function on SQLite. The SQL type is needed so we know what functions this can be passed to.

    Boxing an expression also implies that it has no aggregate functions. You cannot box an aggregate expression in Diesel. As of Diesel 1.0, a boxed expression can only be used with exactly the from clause given. You cannot use a boxed expression for crates::table with an inner join to another table.

    In addition to extracting expressions, you can also pull out entire queries into functions. Going back to crates.io, the Crate struct doesn’t use every column from the crates table. Because we almost always select a subset of these columns, we have an all function which selects the columns we need.

    src/krate/mod.rs

    1. use diesel::backend::Backend;
    2. use diesel::dsl::{AsSelect, Select};
    4. #[derive(Selectable, Queryable)]
    5. #[diesel(table_name = crates)]
    6. struct Crate {
    7. id: i32,
    8. name: String,
    9. updated_at: NaiveDateTime,
    10. created_at: NaiveDateTime,
    11. }
    13. type All<DB> = Select<crates::table, AsSelect<Crate, DB>>;
    15. impl Crate {
    16. pub fn all<DB>() -> All<DB>
    17. where
    18. DB: Backend,
    19. {
    20. crates::table.select(Crate::as_select())
    21. }
    22. }

    We also frequently found ourselves writing Crate::all().filter(with_name(crate_name)). We can pull that into a function as well.

    src/krate/mod.rs

    1. use diesel::dsl::Filter;
    3. type ByName<T, DB> = Filter<All<DB>, WithName<T>>;
    5. impl Crate {
    6. fn by_name<T, DB>(name: T) -> ByName<T, DB> {
    7. Self::all().filter(with_name(name))
    8. }
    9. }

    And just like with expressions, if we don’t want to write the return types, or we want to dynamically construct the query differently, we can box the whole query.

    src/krate/mod.rs

    1. use diesel::expression::{Expression, AsExpression};
    2. use diesel::pg::Pg;
    3. use diesel::sql_types::Text;
    4. use diesel::dsl::{SqlTypeOf, AsSelect};
    6. type SqlType = SqlTypeOf<AsSelect<Crate, Pg>>;
    7. type BoxedQuery<'a> = crates::BoxedQuery<'a, Pg, SqlType>;
    9. impl Crate {
    10. fn all() -> BoxedQuery<'static> {
    11. crates::table.select(Crate::as_select()).into_boxed()
    12. }
    14. fn by_name<'a, T>(name: T) -> BoxedQuery<'a>
    15. where
    16. T: AsExpression<Text>,
    17. T::Expression: BoxableExpression<crates::table, Pg>,
    18. {
    19. Self::all().filter(by_name(name))
    20. }
    21. }

    Once again, we have to give Diesel some information to box the query:

    • The SQL type of the SELECT clause
    • The FROM clause
    • The backend you are going to execute it against

    The SQL type is needed so we can determine what structs can be deserialized from this query. The FROM clause is needed so we can validate the arguments to future calls to filter and other query builder methods. The backend is needed to ensure you don’t accidentally use a PostgreSQL function on SQLite.

    Note that in all of our examples, we are writing functions which return queries or expressions. None of these functions execute the query. In general you should always prefer functions which return queries, and avoid functions which take a connection as an argument. This allows you to re-use and compose your queries.

    For example, if we had written our by_name function like this:

    src/krate/mod.rs

    1. impl Crate {
    2. fn by_name(name: &str, conn: &mut PgConnection) -> QueryResult<Self> {
    3. Self::all()
    4. .filter(with_name(name))
    5. .first(conn)
    6. }
    7. }

    Then we would never be able to use this query in another context, or modify it further. By writing the function as one that returns a query, rather than executing it, we can do things like use it as a subselect.

    Example

    1. let version_id = versions
    2. .select(id)
    3. .filter(crate_id.eq_any(Crate::by_name(crate_name).select(crates::id)))
    4. .filter(num.eq(version))
    5. .first(conn)?;

    Or use it to do things like get all of its downloads: Example

    Example

    1. let recent_downloads = Crate::by_name(crate_name)
    2. .inner_join(crate_downloads::table)
    3. .filter(CrateDownload::is_recent())
    4. .select(sum(crate_downloads::downloads))
    5. .get_result(conn)?;

    All code in this guide is based on real code from crates.io. You can find the source on GitHub