Notes on diesel
I'm using diesel for work, but it's often difficult to find things in the documentation or to immediately understand how to achieve certain things.
Therefore, this article is a personal note to jot down things I've noticed while using it. I plan to add to it as I feel inclined.
Executing raw SQL
Diesel doesn't support window functions or subqueries.
As a result, I implement pagination by directly manipulating SQL.
While it's good to create such descriptions for frequently used queries, it can be quite cumbersome when you just want to get something working. Therefore, it's useful to know how to handle raw SQL.
To execute raw SQL, you can use sql_query, which will perform by-name mapping during loading.
For the struct to map to, derive QueryableByName.
#[derive(QueryableByName, Serialize, Debug)]
pub struct LastUpdate {
#[sql_type = "Datetime"]
pub dt: chrono::NaiveDateTime,
}
use diesel::sql_query;
use diesel::sql_types::Integer;
sql_query(
r#"
SELECT x.*, updated_at as dt
FROM hoge
LIMIT ?
"#
)
.bind::<Integer, _>(5)
.load::<(Hoge, Rank)>(&*conn)
.expect("cannot execute ....")
Association
When pulling related tables, you can add derive(Associations) and #[belongs_to(XXX)] to the struct and retrieve by specifying the ID with belonging_to.
This performs a WHERE IN filter.
SELECT * FROM hoge
WHERE IN (1,2,3....)
Therefore, the ID needs to be determined at the time of executing the corresponding SQL, and cannot be executed in a subquery.
Since it executes the query twice, if you're concerned about performance, you should use raw SQL or other methods.
grouped_by
Unlike SQL's group_by, grouped_by is a function that groups already loaded data.
It connects with foreign keys.
By zipping afterwards, you can obtain a structure that pairs parent data with an array of child data.
For more details, see ↓.
Retrieving a single record
You can simply use find.
Load with a struct that has derive(Identifiable) attached.
SELECT restrictions when using GROUP BY
QueryBuilder has a stronger constraint than MySQL 8.0's engine, where only columns that appear in GROUP BY can be specified in SELECT.
Therefore, descriptions like the following need to be written in raw SQL (A and B have a 1:many relationship).
SELECT A.*
FROM B
LEFT JOIN A on B.a_id = A.id
GROUP BY B.A_id
Useful URLs
- diesel-rs/diesel - Github: Official
- Crate diesel: When you're curious about the API.
- Diesel Tag - κeen's Happy Hacκing Blog: I'm always indebted to this.
- Used diesel for work: Because it was missing from the above
Summary
Diesel is fun.
I'll add more as I notice things.
