Trying to write a manual `FromRow` implementation for a struct that contains nested structs. 9 fields limit? Is it possible?

I'm trying for the first time sqlx these days.

I'm trying to retrieve a nested struct using the below code but a srange thing is happening. If I comment-out a field in PgCoachTuple I get an error (see below).

Apparently I can have a tuple with max 9 fields. Is that right or am I doing it wrong?

Note:
As you can see I'm using a Box for pub coach: Option<Box<PgCoach>> because in other cases structs like PgCoach can have non-scalar fields (structs).

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>,
}

type PgCoachTuple = (
    String,
    String,
    Time::OffsetDateTime,
    Option<Time::OffsetDateTime>,
    Option<String>,
    Option<String>,
    Option<String>,
    Option<String>,
    Option<String>,
    // Option<String>, // It works all good until I comment-out this field. If I do I get the below error! Crazy! Why?
    // Option<Time::OffsetDateTime>,
    // Option<i64>,
    // Option<String>,
    // Option<String>,
    // Option<String>,
    // Option<String>,
    // 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>>,
}

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::<PgCoachTuple, &str>("coach")?.1,
                firstname: row.try_get::<PgCoachTuple, &str>("coach")?.4,
                lastname: row.try_get::<PgCoachTuple, &str>("coach")?.5,
                ..Default::default()
            }));
        }

        Ok(res)
    }
}

The error is:

   Compiling project v0.1.0 (C:\projects\project\backend\src\crates\project)
error[E0277]: the trait bound `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>): sqlx::Decode<'_, Postgres>` is not satisfied
   --> src\pg.rs:539:21
    |
539 |                 id: row.try_get::<PgCoachTuple, &str>("coach")?.1,
    |                     ^^^ ------- required by a bound introduced by this call
    |                     |
    |                     the trait `sqlx::Decode<'_, Postgres>` is not implemented for `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>)`
    |
    = help: the following other types implement trait `sqlx::Decode<'r, DB>`:
              ()
              (T1, T2)
              (T1, T2, T3)
              (T1, T2, T3, T4)
              (T1, T2, T3, T4, T5)
              (T1, T2, T3, T4, T5, T6)
              (T1, T2, T3, T4, T5, T6, T7)
              (T1, T2, T3, T4, T5, T6, T7, T8)
            and 2 others
note: required by a bound in `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 `try_get`

error[E0277]: the trait bound `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>): sqlx::Type<Postgres>` is not satisfied
   --> src\pg.rs:539:21
    |
539 |                 id: row.try_get::<PgCoachTuple, &str>("coach")?.1,
    |                     ^^^ ------- required by a bound introduced by this call
    |                     |
    |                     the trait `sqlx::Type<Postgres>` is not implemented for `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>)`
    |
    = help: the following other types implement trait `sqlx::Type<DB>`:
              ()
              (T1, T2)
              (T1, T2, T3)
              (T1, T2, T3, T4)
              (T1, T2, T3, T4, T5)
              (T1, T2, T3, T4, T5, T6)
              (T1, T2, T3, T4, T5, T6, T7)
              (T1, T2, T3, T4, T5, T6, T7, T8)
            and 2 others
note: required by a bound in `try_get`
   --> C:\Users\Fred\.cargo\registry\src\github.com-1ecc6299db9ec823\sqlx-core-0.6.2\src\row.rs:114:41
    |
114 |         T: Decode<'r, Self::Database> + Type<Self::Database>,
    |                                         ^^^^^^^^^^^^^^^^^^^^ required by this bound in `try_get`

error[E0277]: the trait bound `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>): sqlx::Decode<'_, Postgres>` is not satisfied
   --> src\pg.rs:540:28
    |
540 |                 firstname: row.try_get::<PgCoachTuple, &str>("coach")?.4,
    |                            ^^^ ------- required by a bound introduced by this call
    |                            |
    |                            the trait `sqlx::Decode<'_, Postgres>` is not implemented for `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>)`
    |
    = help: the following other types implement trait `sqlx::Decode<'r, DB>`:
              ()
              (T1, T2)
              (T1, T2, T3)
              (T1, T2, T3, T4)
              (T1, T2, T3, T4, T5)
              (T1, T2, T3, T4, T5, T6)
              (T1, T2, T3, T4, T5, T6, T7)
              (T1, T2, T3, T4, T5, T6, T7, T8)
            and 2 others
note: required by a bound in `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 `try_get`

error[E0277]: the trait bound `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>): sqlx::Type<Postgres>` is not satisfied
   --> src\pg.rs:540:28
    |
540 |                 firstname: row.try_get::<PgCoachTuple, &str>("coach")?.4,
    |                            ^^^ ------- required by a bound introduced by this call
    |                            |
    |                            the trait `sqlx::Type<Postgres>` is not implemented for `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>)`
    |
    = help: the following other types implement trait `sqlx::Type<DB>`:
              ()
              (T1, T2)
              (T1, T2, T3)
              (T1, T2, T3, T4)
              (T1, T2, T3, T4, T5)
              (T1, T2, T3, T4, T5, T6)
              (T1, T2, T3, T4, T5, T6, T7)
              (T1, T2, T3, T4, T5, T6, T7, T8)
            and 2 others
note: required by a bound in `try_get`
   --> C:\Users\Fred\.cargo\registry\src\github.com-1ecc6299db9ec823\sqlx-core-0.6.2\src\row.rs:114:41
    |
114 |         T: Decode<'r, Self::Database> + Type<Self::Database>,
    |                                         ^^^^^^^^^^^^^^^^^^^^ required by this bound in `try_get`

error[E0277]: the trait bound `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>): sqlx::Decode<'_, Postgres>` is not satisfied
   --> src\pg.rs:541:27
    |
541 |                 lastname: row.try_get::<PgCoachTuple, &str>("coach")?.5,
    |                           ^^^ ------- required by a bound introduced by this call
    |                           |
    |                           the trait `sqlx::Decode<'_, Postgres>` is not implemented for `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>)`
    |
    = help: the following other types implement trait `sqlx::Decode<'r, DB>`:
              ()
              (T1, T2)
              (T1, T2, T3)
              (T1, T2, T3, T4)
              (T1, T2, T3, T4, T5)
              (T1, T2, T3, T4, T5, T6)
              (T1, T2, T3, T4, T5, T6, T7)
              (T1, T2, T3, T4, T5, T6, T7, T8)
            and 2 others
note: required by a bound in `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 `try_get`

error[E0277]: the trait bound `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>): sqlx::Type<Postgres>` is not satisfied
   --> src\pg.rs:541:27
    |
541 |                 lastname: row.try_get::<PgCoachTuple, &str>("coach")?.5,
    |                           ^^^ ------- required by a bound introduced by this call
    |                           |
    |                           the trait `sqlx::Type<Postgres>` is not implemented for `(std::string::String, std::string::String, time::OffsetDateTime, Option<time::OffsetDateTime>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>, Option<std::string::String>)`
    |
    = help: the following other types implement trait `sqlx::Type<DB>`:
              ()
              (T1, T2)
              (T1, T2, T3)
              (T1, T2, T3, T4)
              (T1, T2, T3, T4, T5)
              (T1, T2, T3, T4, T5, T6)
              (T1, T2, T3, T4, T5, T6, T7)
              (T1, T2, T3, T4, T5, T6, T7, T8)
            and 2 others
note: required by a bound in `try_get`
   --> C:\Users\Fred\.cargo\registry\src\github.com-1ecc6299db9ec823\sqlx-core-0.6.2\src\row.rs:114:41
    |
114 |         T: Decode<'r, Self::Database> + Type<Self::Database>,
    |                                         ^^^^^^^^^^^^^^^^^^^^ required by this bound in `try_get`

For more information about this error, try `rustc --explain E0277`.

What does all this mean?

Is there another simpler way to do the same?

Rust has no variadic generics or an equivalent way of referencing tuples of arbitrary size (yet). This means that the best trait impls for tuples can do for the time being is to implement the trait for tuples up to a certain, fixed size. Some crates choose the maximal size to be 16 (as it's a nice and round number for computers), others do 26 (so that one-letter type variables can be used), yet others use 10 (because it's a nice and round number for humans). Apparently, sqlx chose 9. There's nothing you can do about this, except for opening an issue or pull request implementing the trait for larger tuples.

2 Likes

Thank you very much. Is there another way without using tuple at all?

I'm not familiar with sqlx, but in database/ORM APIs, there is usually a method for retrieving an arbitrary dynamic value from a result set by index or column name. You should look for something like get, get_column, column, or an Index<usize> or Index<&str> etc. implementation.

I'm trying to understand but it's hard. Especially for me that I come from handyman ORMs.

What amazed me is the absolute absence of solutions for this problem at least for the results of my research.

SeaORM team also (not) faced this problem by explaining their reasons here: SeaORM FAQ Q01 | SeaQL - Building data intensive applications in Rust.

Basically they say that since they don't want to either waste space or cycles THEY DON'T DECIDE and don't make it possible at all, even if the user wants one of the two solutions.

So I decided to use sqlx but for 1-to-many there isn't enough documentation with examples and as you can see there are limits like the one I just found that I didn't even know existed.

I will try to understand better. If I misunderstood something, please help me understand. Thanks again.

Diesel does not restrict the tuple size to 16, 26, 10 or 9. The default limit in diesel is 32. There are optional feature flags to increase this limit up to 128. In addition diesel offers an API for 1-to-n relations. So it might be worth to try that as alternative.

Wow. Thanks.

Diesel has always scared me for the async/sync thing (I started with Rust async and I would like to continue with it). Do you still recommend it?