Sqlx field attributes difference between query_as<_, type> and query_as!

Hi!

I'm trying to map a SQL response to a custom struct using sqlx.

My structs are the following:

  #[derive(Serialize, Deserialize, Debug, sqlx::FromRow)]
  pub struct SaveSimulation {
    name: Option<String>,

    // #[serde(rename = "valid")]
    // #[sqlx(default)]
    // valid: bool,

    #[serde(rename = "routes")]
    routes: Option<Vec<Routing>>,
  }

  #[derive(Serialize, Deserialize, Debug, sqlx::Type, sqlx::FromRow)]
  struct Routing {
    #[serde(rename = "id")]
    route_id: i64,

    #[serde(rename = "name")]
    name: Option<String>,
  }

With these structs I tried to query data from the database. Which only works when field with #[sqlx(default)] isn't used.This query does work and gives me the desired data from the database but doesn't let me use the #[sqlx(default)]:

  let simulations = sqlx::query_as!(SaveSimulation,
    r#"
    SELECT S.name,
      ARRAY_AGG((R.route_id, R.name)) as "routes: Vec<Routing>"
    FROM simulations S
      JOIN routes R ON R.simulation_uid = S.simulation_uid
    GROUP BY S.simulation_uid
    "#
  )
  .fetch_one(&pool) // -> Vec<Country>
  .await?;

Now I enable the valid field in the structs:

  #[derive(Serialize, Deserialize, Debug, sqlx::FromRow)]
  pub struct SaveSimulation {
    name: Option<String>,

    #[serde(rename = "valid")]
    #[sqlx(default)]
    valid: bool,

    #[serde(rename = "routes")]
    routes: Option<Vec<Routing>>,
  }

  #[derive(Serialize, Deserialize, Debug, sqlx::Type, sqlx::FromRow)]
  struct Routing {
    #[serde(rename = "id")]
    route_id: i64,

    #[serde(rename = "name")]
    name: Option<String>,
  }

The same query now doesn't work anymore:

error[E0063]: missing field `valid` in initializer of `SaveSimulation`
   --> src\main.rs:494:37
    |
494 |   let simulations = sqlx::query_as!(SaveSimulation,
    |                                     ^^^^^^^^^^^^^^ missing `valid`

When I now switch to the usage of query_as::<_, SaveSimulation> instead of query_as! using the default field now works but I can't use the vec<> field anymore:

  let simulations = sqlx::query_as::<_, SaveSimulation>(
    r#"
      SELECT S.name      
      FROM simulations S
      GROUP BY S.simulation_uid
    "#,
  )
  .fetch_one(&pool).await?;

Now I tried to use vec<> again:

  let simulations = sqlx::query_as::<_, SaveSimulation>(
    r#"
      SELECT S.name,
        ARRAY_AGG((R.route_id, R.name)) as "routes: Vec<Routing>"
      FROM simulations S
        JOIN routes R ON R.simulation_uid = S.simulation_uid
      GROUP BY S.simulation_uid
    "#,
  )
  .fetch_one(&pool).await?;

When I try to use vec<> I get the following error:

error[E0599]: the method `fetch_one` exists for struct `QueryAs<'_, _, SaveSimulation, _>`, but its trait bounds were not satisfied
   --> src\main.rs:490:4
    |
368 |     pub struct SaveSimulation {
    |     ------------------------- doesn't satisfy `SaveSimulation: FromRow<'r, _>`
...
481 |     let simulations = sqlx::query_as::<_, SaveSimulation>(
    |  _____________________-
482 | |     r#"
483 | |       SELECT S.name,
484 | |         ARRAY_AGG((R.route_id, R.name)) as "routes: Vec<Routing>"
...   |
489 | |   )
490 | |   .fetch_one(&pool).await?;
    | |   -^^^^^^^^^ method cannot be called on `QueryAs<'_, _, SaveSimulation, _>` due to unsatisfied trait bounds
    | |___|

Does anyone knows why the sqlx field attributed only work with the QueryAs<'_, _, SaveSimulation, _> approach? And how to use this combined with the vec<> usage?

I haven't used Sqlx, but to me it looks like you are holding it wrong™. The method fetch_one, as the name implies, retrieves only 1 record from your database, but you are expecting to receive many records instead.

You might want to check the documentation for other methods such as fetch_many.

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.