(parametrized) raw SQL Queries with diesel (Postgres)

Sometimes the diesel abstraction is not enough if you want to execute more complex queries. This is totally possible, but the documentation is a bit brief on that topic, and the “SqlQuery bind” example shows MySQL syntax (which threw me off, since my postgres-query-writing-know-how is a bit rusty ATM).

I wanted to execute the following query:

insert into store_traffic_uploads (store_id, batch_id)
                select id as store_id, $1 from stores
                    where id not in
                        (select store_id from store_traffic_uploads)
                limit $2
        ;

The corresponding Rust Code then looks like this:


const QUERY: &str = r#"… above query …"#;

fn create_batch(c: &PgConnection, job_id: i32) -> QueryResult<usize> {
    let query = diesel::sql_query(QUERY)
        .bind<Integer, _>(job_id)
        .bind::<Integer, _>(25_000);

    query.execute(c)
}

The diesel documentation also suggests to put the query into a separate file.


const QUERY: &str = r#"… above query …"#;

fn create_batch(c: &PgConnection, job_id: i32) -> QueryResult<usize> {
    diesel::sql_query(include_str!("create_batch.sql"))
        .bind<Integer, _>(job_id)
        .bind::<Integer, _>(25_000)
        .execute(c)
}

Conclusion

  • use sql_query for raw/custom queries/inserts
  • parameterize the query with the correct placeholders for your database (“?” for MySQL and “$N” for postgres)
  • use bind::<…>() to set these parameters