More useful examples #1014
Comments
|
That is all completely fair. I agree that we need to make this a priority. I think a potentially good solution here is to take a public SQL dataset, and setup several guided examples using that dataset.
Postgres
We can plan to get a mdbook or similar setup with a lot of this and then iterate on it. Is there anything large I'm missing from the above? |
|
This looks like a very helpful list of examples. I don't know if its better suited for a "tips and tricks" section but i often like to use some kind of A Transactions-Example could be useful? Maybe one that recovers from a Do you plan to have this as examples and a corresponding mdbook (guide)? |
Maybe a first step would be just examples. Trying to reign myself in so this gets done. # guide for mysql
# activate SQLx with async-std and mysql, with explicit bin paths to avoid the src/bin/x path
examples/guide/mysql/Cargo.toml
examples/guide/mysql/connect.rs
examples/guide/mysql/select_many.rs
examples/guide/mysql/select_one.rs
examples/guide/mysql/count.rs
examples/guide/mysql/select_many_1_to_1.rs
examples/guide/mysql/select_many_1_to_n.rs
# ...
# quickstart for mysql with tokio
examples/quickstart/mysql+tokio/Cargo.toml
examples/quickstart/mysql+tokio/src/main.rs
# quickstart for mysql with blocking IO
examples/quickstart/mysql+blocking/Cargo.toml
examples/quickstart/mysql+blocking/src/main.rsI only show mysql above but the idea is we would have I'm thinking that the larger examples should probably move to another repo like
Definitely. At least something like "Start a transaction, insert into two different tables and then commit it".
It might be nice to do this automatically now that we have a working |
|
I'm interested in helping with this. As a recent newcomer, I wished there were introductory on-boarding information, as a supplement to just one example to help folks get from zero familiarity to having one running example for one database. |
|
Hey, I might help with sqlite examples. The idea of showing features and possibilities through examples is a very good point. |
|
Would like to contribute to more examples |
|
Hi - this library is great! 😄 I've been using it for a project and ran into some similar issues as described here when you start to get into table joins with 1:Many etc. and/or using the My current work in progress is to simply define a database specific type and then flatten the rows into my final type: since I've many types to do this with I made a trait FromDBFlatten (that gist has an example with joins). It then gives me a nice usage: let _: Option<ReturnType> = sqlx::query_as!(DBType, r#"...SOME SQL..."#)
.fetch_all(&pool)
.await
.map(FromDBFlatten::flatten_one)?;
let _: Vec<ReturnType> = sqlx::query_as!(DBType, r#"...SOME SQL..."#)
.fetch_all(&pool)
.await
.map(FromDBFlatten::flatten)?;If anyone is looking for how to run multiple queries in one transaction (I couldn't find anything), I think this is the correct way: let pool = PgPool::connect("DATABASE_URL").unwrap()).await?;
let mut conn = pool.acquire().await?;
let mut tx = conn.begin().await?;
let _: ReturnType = sqlx::query_as!(DBType, r#"...SOME SQL..."#)
.fetch_one(&mut tx)
.await?;
let _: Vec<ReturnType> = sqlx::query_as!(DBType, r#"...SOME SQL..."#)
.fetch_all(&mut tx)
.await?;
tx.commit().await?; |
|
Love the enthusiasm everyone. I will commit to getting this started over the next couple days (in the @pauldorehill Very nice! Coincidentally (and coming from a lot of the 1:M requests) we're planning on adding something like: // impl Executor
fn fetch_group_map_by<T, F>(self, f: F) -> HashMap<T, Vec<O>>
where
T: Decode<DB>,
F: Fn(&O) -> TWhich would allow something like: /*: Vec<User> */
let mut users = query!(" ... ")
.map(|row| User {
id: row.id,
name: row.name,
products: Vec::new(),
// [...]
})
.fetch_all(&mut conn).await?;
let user_ids: Vec<_> = users.iter().map(|user| user.id).collect();
/*: HashMap<Uuid, Vec<Product>> */
let mut products = query_as!(Product, " ... WHERE user_id = any($1)", user_ids)
.fetch_group_map_by(&mut conn, |product| product.user_id).await?;
for user in &mut users {
users.products = products.remove(user.id);
}A large improvement to this pattern would be to query and ignore some fields. This would remove the need for a Perhaps something like: query_as!(User, "SELECT u.id, u.name, ...", skip: ["products"]) |
|
@mehcode that is an nice way of doing it too: I like domain transfer objects as way to over come the impedance miss match at boundaries. One other slightly cryptic error I've come across is CREATE TABLE public.giant (
id integer NOT NULL,
name text NOT NULL
);
CREATE TABLE public.meal (
id integer NOT NULL,
name text NOT NULL
);struct GiantDB {
id: i32,
name: String,
meal: Meal,
}
#[derive(sqlx::Type, Debug)]
struct Meal {
// id: i32, // If this field is not here, get self.remaining() >= dst.len() at runtime
name: String,
}I'm not sure if I should open an issue, or if its a know limitation, but there isn't the same level of compile checking for child structs? You get a runtime error along the lines of
Or a |
|
I think this issue is getting filled with great ideas all based around the topic of "idiomatic usage of sqlx" and how to improve the current state. Do we need to branch out some new issues for these?
|
|
I made a presentation for Rust Lang Los Angeles which uses SQLx, actix and async-graphql, it showcases UUIDs and datetime fields which I found missing from the documentation: |
|
Is there any new information, features or references for one-to-many or many-to-many relationships with |
Where you able to find any simple examples using array_agg? |
|
Hello, Yes, It'll be very interesting to have few examples of how to deal with relations between models. I use this library for my first "side-project" and It's difficult to find more complex example than a simple "todo list" :-) In my example, I have 2 models, Tracking and TrackingLink. Tracking can have few TrackingLink (cf. links: Vec). #[derive(Debug, Serialize, FromRow)]
pub struct Tracking {
product_id: Uuid,
product_name: String,
links: Vec<TrackingLink>
}
#[derive(Debug, Serialize, sqlx::Type, FromRow)]
pub struct TrackingLink {
merchant_product_url: String,
merchant: String,
is_in_stock: bool,
#[serde(with = "ts_seconds")]
tracked_at: DateTime<Utc>,
}Bellow the SQL part, with the use of the macro sqlx::query_as! with the use of postgresql array_agg function. impl Tracking {
pub async fn get_last(pool: &PgPool) -> Result<Vec<Tracking>> {
let products = sqlx::query_as!(
Tracking,
r#"WITH last_tracking AS (
SELECT
DISTINCT ON (t.merchant_product_id) t.merchant_product_id,
t.is_in_stock,
t.tracked_at
FROM instock.tracking AS t
ORDER BY t.merchant_product_id, t.tracked_at DESC
), tracked_products AS (
SELECT
p.id as product_id,
p.name as product_name,
m.name as merchant,
mp.url as product_merchant_url,
lt.is_in_stock,
lt.tracked_at
FROM last_tracking AS lt
JOIN instock.merchant_product AS mp ON mp.id = lt.merchant_product_id
JOIN instock.product AS p ON p.id = mp.product_id
JOIN instock.merchant AS m ON m.id = mp.merchant_id
)
SELECT
tp.product_id,
tp.product_name,
array_agg((
tp.product_merchant_url,
tp.merchant,
tp.is_in_stock,
tp.tracked_at
)) as "links!: Vec<TrackingLink>"
FROM tracked_products AS tp
GROUP BY tp.product_id, tp.product_name
"#
)
.fetch_all(pool)
.await?;
Ok(products)
}
}This first example works. After that, I wanted to add a filter to this SQL, and use a "non-macro" form of query_as : impl Tracking {
pub async fn get_last(pool: &PgPool) -> Result<Vec<Tracking>> {
// Just for the example
let mut sql_filter = "WHERE t.is_in_stock IS TRUE";
// Format the SQL string
let sql = format!(r#"WITH last_tracking AS (
SELECT
DISTINCT ON (t.merchant_product_id) t.merchant_product_id,
t.is_in_stock,
t.tracked_at
FROM instock.tracking AS t
{sql_filter}
ORDER BY t.merchant_product_id, t.tracked_at DESC
), tracked_products AS (
SELECT
p.id as product_id,
p.name as product_name,
m.name as merchant,
mp.url as product_merchant_url,
lt.is_in_stock,
lt.tracked_at
FROM last_tracking AS lt
JOIN instock.merchant_product AS mp ON mp.id = lt.merchant_product_id
JOIN instock.product AS p ON p.id = mp.product_id
JOIN instock.merchant AS m ON m.id = mp.merchant_id
)
SELECT
tp.product_id,
tp.product_name,
array_agg((
tp.product_merchant_url,
tp.merchant,
tp.is_in_stock,
tp.tracked_at
)) as "links!: Vec<TrackingLink>"
FROM tracked_products AS tp
GROUP BY tp.product_id, tp.product_name
"#
//
let products = sqlx::query_as::<_, Tracking(
&sql
)
.fetch_all(pool)
.await?;
Ok(products)
}
}It produces this error : I don't understand why the first example work and the second one not. Fabien. |
|
An important item to note from my own experiments: Using THIS IS NOT CAUGHT AT COMPILE TIME If your fields are out of order, you would get a runtime error something like this: I wonder if there is a way to catch this in the macro to at least prevent accidental runtime bugs. |
|
That's unlikely to be something we can typecheck at compile time without performing our own analysis of the query. |
|
Ah, thanks @abonander. It looked like there was another interesting proposal to do a |
|
Can anyone please let me know if the nested queries are possible by using the |
The exact same question I was asking my self to. Yesterday I struggle some hours with the It would be very useful to have this feature to, in the query_as function. At the moment is not possible to build a dynamic nested query. |
|
Just for others when they end up here, and like me and @seguidor777 don't know how to manage complex structures with query_as function: There is everything in the documentation, I just did not found it first. Lets say you have this tables: #[derive(Clone, Debug)]
pub struct MediaFile {
pub id: i32,
pub name: String,
pub media_language: Lang,
pub media_speaker: Vec<Speaker>,
}
#[derive(Clone, Debug, Default)]
pub struct Lang {
pub id: i32,
pub name: String,
}
#[derive(Clone, Debug, Default)]
pub struct Speaker {
pub id: i32,
pub name: String,
}For impl FromRow<'_, PgRow> for MediaFile {
fn from_row(row: &PgRow) -> sqlx::Result<Self> {
let lang = Lang {
id: row.get::<(i32, String), &str>("language").0,
name: row.get::<(i32, String), &str>("language").1,
};
let mut speaker = vec![];
for s in row
.get::<Vec<Option<(i32, String)>>, &str>("speaker")
.into_iter()
.flatten()
{
speaker.push(Speaker { id: s.0, name: s.1 })
}
Ok(Self {
id: row.get("id"),
name: row.get("name"),
media_language: lang,
media_speaker: speaker,
})
}
}Then you can query the table with: let ordering = "id ASC";
let query = format!(r#"SELECT mf.*,
(ml.id, ml."name") AS "language",
array_agg((case when ms.id > 0 then (ms.id, ms."name") end)) AS "speaker"
FROM media_files mf
INNER JOIN media_languages ml ON ml.id = mf.lang_id
LEFT JOIN media_file_speakers mfs ON mfs.media_file_id = mf.id
LEFT JOIN media_speakers ms ON ms.id = mfs.media_speaker_id
GROUP BY mf.id, ml.id
ORDER BY {}
"#, ordering);
let data: Vec<MediaFile> = sqlx::query_as(&query).fetch_all(conn).await?;In this example speaker is optional, that is why I use |
Did you find a way? @shanesveller |
Things I've learned today: * The [sqlx documentation](https://docs.rs/sqlx/0.6.2/sqlx/sqlite/types/index.html#uuid) is *lying* about its `uuid` support. Basically `query_as!` does not ser/deserialize `Uuid` properly in/out of Sqlite with `TEXT` _or_ `BLOB` * There are [no useful examples](launchbadge/sqlx#1014) of doing nested struct queries in sqlx at the moment
|
I was just needing to do a simple 1:many query and ended up using "array_agg" in the query and filtering empty values, then, I need to replace nulls with empty arrays: #[derive(Debug, Clone, sqlx::Type)]
pub struct WorkplaceSchedule {
pub id: Uuid,
pub start: NaiveTime,
pub finish: NaiveTime,
pub n_positions: i32,
}
#[derive(Debug, Clone)]
pub struct WorkplaceSchedules {
pub id: Uuid,
pub name: String,
pub schedules: Vec<WorkplaceSchedule>,
}
let res = sqlx::query_as!(
WorkplaceSchedules,
r#"
SELECT
w.id, w.name,
COALESCE(
array_agg((s.id, s."start", s.finish, ws.n_positions)) FILTER (WHERE s.id IS NOT NULL),
'{}'
) AS "schedules!: Vec<WorkplaceSchedule>"
FROM workplaces w
LEFT JOIN workplace_schedules ws
ON w.id = ws.workplace_id
LEFT JOIN schedules s
ON ws.schedule_id = s.id
GROUP BY w.id
"#
)
.fetch_all(&pool)
.await
.expect("Failed to fetch query."); |
|
@jb-alvarado |
|
@frantisek-heca there is a section about manuell implementation it not give a more advanced example, but is a starting point. Here in the issues you found also some examples and explanations, also about the issue:
I don't know if this problem still exists, as I remember was not an issue by sqlx itself. I implemented my own type for the problem, here an example: impl<'r> ::sqlx::decode::Decode<'r, ::sqlx::Postgres> for MediaSeries {
fn decode(
value: ::sqlx::postgres::PgValueRef<'r>,
) -> ::std::result::Result<
Self,
::std::boxed::Box<
dyn ::std::error::Error + 'static + ::std::marker::Send + ::std::marker::Sync,
>,
> {
let mut decoder = ::sqlx::postgres::types::PgRecordDecoder::new(value)?;
let id = decoder.try_decode::<i32>()?;
let name = decoder.try_decode::<String>()?;
let trailer_id = decoder.try_decode::<i32>()?;
let screening_pause = decoder.try_decode::<bool>()?;
let description = decoder.try_decode::<Option<String>>()?;
::std::result::Result::Ok(MediaSeries {
id,
name,
trailer_id,
screening_pause,
description,
})
}
} |
|
Yes, having examples of recommend ways of handling one-to-many and many-to-many relationships would greatly help newcomers! This is a must-have for anything SQLish! So far I've ended up doing this for one-to-many (DemoParent struct with a nested vector of DemoChildren, on PostgreSQL): Disclaimer: I'm rust/sqlx/postgres/axum noob mysef, this is from a toy project :) CREATE TABLE demo_parents (
id TEXT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE demo_children (
id TEXT PRIMARY KEY,
parent_id TEXT NOT NULL REFERENCES demo_parents(id) ON DELETE CASCADE,
name TEXT NOT NULL
);
CREATE INDEX idx_demo_children_parent_id ON demo_children (parent_id);
INSERT INTO demo_parents (id, name) VALUES
('P1', 'Parent 1'),
('P2', 'Parent 2'),
('P3', 'Parent 3');
INSERT INTO demo_children (id, name, parent_id) VALUES
('C1', 'Child 1', 'P1'),
('C2', 'Child 2', 'P2'),
('C3', 'Child 3', 'P2'),
('C4', 'Child 4', 'P3'),
('C5', 'Child 5', 'P3'),
('C6', 'Child 6', 'P3');use serde::{Deserialize, Serialize};
use sqlx::{postgres::PgRow, FromRow, Row};
#[derive(Clone, Debug, Default, Serialize, Deserialize)]
pub struct DemoParent {
id: String,
name: String,
children: Vec<DemoChild>,
}
#[derive(Clone, Debug, Default, Serialize, Deserialize)]
pub struct DemoChild {
id: String,
parent_id: String,
name: String,
}
impl FromRow<'_, PgRow> for DemoParent {
fn from_row(row: &PgRow) -> sqlx::Result<Self> {
let mut children = vec![];
for c in row
.get::<Vec<(String, String, String)>, &str>("children")
.into_iter()
{
children.push(DemoChild {
id: c.0,
name: c.1,
parent_id: c.2,
})
}
Ok(Self {
id: row.get("id"),
name: row.get("name"),
children,
})
}
}And this is how I get result from Axum endpoint (using shuttle.rs): async fn get_demo_parents(
State(state): State<MyState>,
) -> Result<impl IntoResponse, impl IntoResponse> {
println!("get_demo_parents endpoint invoked");
match sqlx::query_as::<_, DemoParent>(
r#"
SELECT p.*, array_agg((c.id, c.name, c.parent_id)) children
FROM demo_parents p JOIN demo_children c on p.id = c.parent_id
GROUP by p.id;"#,
)
.fetch_all(&state.pool)
.await
{
Ok(todo) => Ok((StatusCode::OK, Json(todo))),
Err(e) => Err((StatusCode::BAD_REQUEST, e.to_string())),
}
}It gives me such JSON: [
{
"id": "P1",
"name": "Parent 1",
"children": [
{
"id": "C1",
"parent_id": "P1",
"name": "Child 1"
}
]
},
{
"id": "P2",
"name": "Parent 2",
"children": [
{
"id": "C2",
"parent_id": "P2",
"name": "Child 2"
},
{
"id": "C3",
"parent_id": "P2",
"name": "Child 3"
}
]
},
{
"id": "P3",
"name": "Parent 3",
"children": [
{
"id": "C4",
"parent_id": "P3",
"name": "Child 4"
},
{
"id": "C5",
"parent_id": "P3",
"name": "Child 5"
},
{
"id": "C6",
"parent_id": "P3",
"name": "Child 6"
}
]
}
] |
It would be very helpful to get more examples that show the features and possibilities of this library. It would help tremendously to set boundaries of what to expect for newcomers. This could help to mitigate the currently missing guide. As a newcomer it is very frustrating to get to a starting point or even validate if this library is a good fit for what one is searching for. Yes the documentation is quite good but it is only useful for people that are familiar with the project and know what to look for. Chasing types might also be to hard in the beginning to interpret certain error messages.
If you want to achieve something – and thus having an expectation – it is very hard to
To give a concrete example and use case. As a newcomer i want to create an application that manages
accounts,addressesandpostswritten from these accounts. So that is your commonone to oneandone to manyrelationship. Oneaccounthas a singleaddressand has manyposts. How do i achieve that with SQLx? Digging through the documentation and the sparse examples you learn about query_as and sqlx::Type and end up with a starting point that has you covered withaccountsandaddressesThat alone could be one example that could save a tremendous amount of time for a newcomer. It shows what is possible and how to achieve it. Sprinkled with a little more explanatory text and hints to the specific places in the documentation that explains it more in detail could act as a starting point for a future guide.
Wanting to have your
one to manycase covered is a hole other deal. Now the documentation gets hard to understand and there are no examples. Looking through various issues ( #856, #714, #298, #552, ... etc ) only gets you more confused. So is it possible to get your data in a single query or not? All the issues that are closed have no conclusion or a definitive answer to it, they seem to just derail into details that are unintelligible by newcomers. So was issue #856 just a misplaced comma? Lets try this outOk, that looks like it does not work. Looking at the output that this query is making it looks like that this should not work anyway. Running this query in your sql tool of choice gives you
This looks absolutely wrong but isn't even mentioned in issue #856 . So for this to work it looks like we have to get the posts in a single row.
We can validate that this is working to some extend we can change
To get just a single Post this looks like it is working.
Changing the query to
should do the trick and gives us
however
Is not getting us anywhere.
And i don't really understand what the error message is trying to say to me. After digging a little bit further i found this issue #349 where i also don't understand if this is related or not and what the conclusion here is.
At this point i am into my third weekend playing around with sqlx and i don't even know how to use it for a simple
one to manyuse case. This is very frustrating as stated before. sqlx might have many cool features but i just can't get to know them. What is the "idiomatic" way to do such a simple task in sqlx? Am i supposed to do it with multiple different queries as mentioned in this issue #552 ? Is what i was trying to do in the first place even possible with sqlx and i am just not able to express it the right way or is it a limitation/boundary that sqlx has due to the way it is working (which i don't know as a beginner). I suspect issue #298 to be the current roadblocker but i just don't know.Having a variety of "simple" examples that show how to deal with them in sqlx could save a huge amount of time and frustration for people that just "wanted to take a look" at the project and it could make on boarding significantly easier as the only real way is to search the issues which is often not really helpful. Examples could show current limitations like
And the discord channel is not the right tool to search for answers. I can imagine that this sort of questions are asked many times before and is frustrating to answer.
I hope this gets a little insight on my process and experience. Love the project! ❤️
The text was updated successfully, but these errors were encountered: