How to retrieve struct with nested structs with sqlx and Rust?

I'm trying to use Rust and sqlx (v0.6.2) to retrieve from DB structs with nested structs.

I tried using the below code but I found out that there is a limit of 9 fields in tuple that I can use with FromRow.

Note:
As you can see I'm using a Box<T> for struct fields.

use sqlx::{postgres::PgRow, FromRow, QueryBuilder, Row};

#[derive(Debug, Default, sqlx::FromRow)]
pub struct PgCoach { //PgCoach has ONLY scalar values
    pub team_id: String,
    pub id: String,
    pub created_at: Time::OffsetDateTime,
    pub updated_at: Option<Time::OffsetDateTime>,
    pub firstname: Option<String>,
    pub lastname: Option<String>,
    pub motto: Option<String>,
    pub first_case: Option<String>,
    pub second_case: Option<String>,
    pub third_case: Option<String>,
    pub birth_date: Option<Time::OffsetDateTime>,
    pub sex: Option<i64>,
    pub phone: Option<String>,
    pub email_address: Option<String>,
    pub address: Option<String>,
    pub picture: Option<String>,
    pub notes: Option<String>,
}

#[derive(Debug, Default)]
pub struct PgPlayer {
    pub team_id: String,
    pub id: String,
    pub created_at: Time::OffsetDateTime,
    pub updated_at: Option<Time::OffsetDateTime>,
    pub score: i64,
    pub birth_date: Time::OffsetDateTime,
    pub code: String,
    pub payed: bool,
    pub coach_id: String,
    pub coach: Option<Box<PgCoach>>,
    pub skills: Option<Vec<PgSkill>>,
}

impl FromRow<'_, PgRow> for PgPlayer {
    fn from_row(row: &PgRow) -> sqlx::Result<Self> {
        let mut res = Self {
            team_id: row.get("team_id"),
            id: row.get("id"),
            created_at: row.get("created_at"),
            updated_at: row.get("updated_at"),
            score: row.get("score"),
            birth_date: row.get("birth_date"),
            code: row.get("code"),
            payed: row.get("payed"),
            coach_id: row.get("coach_id"),
            ..Default::default()
        };

        if row.try_get_raw("coach").is_ok() {
            res.coach = Some(Box::new(PgCoach {
                id: row.try_get::<PgCoach, &str>("coach")?.1,
                firstname: row.try_get::<PgCoach, &str>("coach")?.4,
                lastname: row.try_get::<PgCoach, &str>("coach")?.5,
                ..Default::default()
            }));
        }

        Ok(res)
    }
}

The error is:

error[E0277]: the trait bound `pg::PgCoach: sqlx::Decode<'_, sqlx::Postgres>` is not satisfied
   --> src\crates\project\pg.rs:656:21
    |
656 |                 id: row.try_get::<PgCoach, &str>("coach")?.id,
    |                     ^^^ ------- required by a bound introduced by this call
    |                     |
    |                     the trait `sqlx::Decode<'_, sqlx::Postgres>` is not implemented for `pg::PgCoach`
    |
    = help: the following other types implement trait `sqlx::Decode<'r, DB>`:
              <&'r [u8] as sqlx::Decode<'r, sqlx::Postgres>>
              <&'r [u8] as sqlx::Decode<'r, sqlx_core::any::database::Any>>
              <&'r sqlx::types::JsonRawValue as sqlx::Decode<'r, DB>>
              <&'r str as sqlx::Decode<'r, sqlx::Postgres>>
              <&'r str as sqlx::Decode<'r, sqlx_core::any::database::Any>>
              <() as sqlx::Decode<'r, sqlx::Postgres>>
              <(T1, T2) as sqlx::Decode<'r, sqlx::Postgres>>
              <(T1, T2, T3) as sqlx::Decode<'r, sqlx::Postgres>>
            and 43 others
note: required by a bound in `sqlx::Row::try_get`
   --> C:\Users\Fred\.cargo\registry\src\github.com-1ecc6299db9ec823\sqlx-core-0.6.2\src\row.rs:114:12
    |
114 |         T: Decode<'r, Self::Database> + Type<Self::Database>,
    |            ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `sqlx::Row::try_get`

I can use whatever SQL query the method needs.

I can return tuple from DB or single columns, I don't care. I only need a way to query with sqlx structs with nested Box<Struct> and I cannot understand how.

How can I retrieve these structs?

There's not really a great way to structure this kind of code just due to the nature of SQL.

The problem you're going to run into is that no matter how you write the query, you're going to have column name conflicts (both PgCoach and PgPlayer have columns named id, for example).

You could rename the columns so there's no overlap between the column names of the two tables, make a query that JOINs the coach table on the foreign key, and then just call PgCoach's FromRow implementation directly in PgPlayer's impl. That doesn't really scale though. The other problem with this approach is that you'll decode multiple copies of the same coach, one for each player that has that coach[1]. That may not be what you want.

The most common approach to solving this problem in general looks more like an ORM. You decode all the basic table values from the query, then go through and find all the ids of rows from other tables you need to fill in the object graph, make those queries, and then use the results of all those queries to build up an object graph.


  1. and of course, receive multiple copies of that row data from the database server ↩︎

1 Like

To understand if I have understood correctly: are you talking about multiple queries to the DB, right?

The sense of my searches so far has always been to try to avoid many queries by trying to use only one. And maybe I'm wrong in this way.

@semicoleon Does your disappointment with decoding multiple copies of the same coach also apply if I use the ARRAY_AGG() method in postgres?

It seems possible to implement all relevant traits by yourself.

impl FromRow<'_, PgRow> for PgPlayer {
    fn from_row(row: &PgRow) -> sqlx::Result<Self> {
        row.try_get::<PgCoach, &str>("coach")?; // Just simplified here
        todo!()
    }
}

impl Type<Postgres> for PgCoach {
    fn type_info() -> PgTypeInfo {
        PgTypeInfo::with_name("PgCoach")
    }
}

impl<'r> Decode<'r, Postgres> for PgCoach {
    fn decode(value: PgValueRef<'r>) -> Result<Self, BoxDynError> {
        let mut decoder = PgRecordDecoder::new(value)?;

        //$(let $idx: $T = decoder.try_decode()?;)*
        //Ok(($($idx,)*))
        todo!()
    }
}

Impls on PgCoach are stolen from sqlx's impl_type_for_tuple!.

1 Like

No, it'd be a JOIN. That's what JOINs are for. Alas, the result of the JOIN is not an object graph but a flat cartesian product that must be further filtered and decoded in order to become an object graph.

1 Like

And what about the use the ARRAY_AGG() method in postgres?

When is this needed?

You still have to do some decoding on the result. That's not the interesting part in interacting with a database, though. The interesting part is matching the representations – and that's certainly not trivial.

1 Like

Well, I just copy & paste & modify without checking it.
You might not need it.

@frederikhors I believe this is what's called Object–relational impedance mismatch. I read about that years ago, I believe the references on that Wikipedia page are well worth reading if they haven't changed.

Yes, it's exactly that, and it's not a solved problem as of today. ORMs can rather poorly approximate some object graph <-> table mapping, but it's far from ideal or convenient. (Mostly, they end up being a very leaky abstraction, basically.)

1 Like