How do I load SQLx rows to a struct with a vector of structs?

Question

I have a struct that looks something like this:

struct Author {
   author_id: Uuid,
   name: String,
   dob: String,
   books: Vec<Book>,
}

struct Book {
   book_id: Uuid,
   author_id: Uuid,
   title: String,
   published: String,
}

Everything's a string here just to simplify. I'm trying to get a result that's a vector of Authors with their Books as one query. That's easy.

let results = sqlx::query_as::<Postgres, MyRows>(r#"
    select
        author.name,
        author.dob,
        book.title,
        book.published
    from
        book
        left join author using (author_id)
"#)
.fetch_all(&mut *dbc)
.await?;

The problem is that I only really know how to get this with MyRows being a struct that looks like Book but has the Author expanded into it since all rows will have that and then writing a bunch of boilerplate code to group the resulting book rows by author and rebuilding them as my struct of Author with a vector of Books in it.

Is there a simpler way to do this in Rust?

Answer 1

There is a way to leverage JSON support that lets you aggregate the Authors on the database side, either

1. by wrapping Vec<Book> in sqlx::types::Json:

   #[derive(Debug, Deserialize, Serialize, FromRow)]
   struct Author {
       author_id: Uuid,
       name: String,
       dob: String,
       books: Json<Vec<Book>>,
   }
   

2. or by hand writing the FromRow implementation:

   impl<'r> sqlx::FromRow<'r, PgRow> for Author {
       fn from_row(row: &'r PgRow) -> Result<Self, sqlx::Error> {
           use sqlx::Row;
           let author_id = row.try_get("author_id")?;
           let name = row.try_get("name")?;
           let dob = row.try_get("dob")?;
           let books: Json<_> = row.try_get("books")?;
           Ok(Author {
               author_id,
               name,
               dob,
               books: books.0,
           })
       }
   }
   

With either implementation you can fetch the aggregated results with:

let result = query_as::<_, Author>(
    "select
        author.author_id,
        author.name,
        author.dob,
        json_agg_strict(book.*) as books
    from
        author
    left join
        book using (author_id)
    group by
        author.author_id,
        author.name,
        author.dob;",
)
.fetch_all::<&mut PgConnection>(&mut db)
.await?;

Answer 2

I am no expert in Rust. But here is how I would solve this issue. Correct me if I am wrong. To get the required structure in more straightforward way I'd use the sqlx crate with some additional crates like uuid and serde for easier handling of UUIDs and serialization. We still have to perform some manual grouping but it can be streamlined.

Define your structs like this below.

use serde::{Deserialize, Serialize};
use uuid::Uuid;

#[derive(Debug, Deserialize, Serialize)]
struct Author {
    author_id: Uuid,
    name: String,
    dob: String,
    books: Vec<Book>,
}

#[derive(Debug, Deserialize, Serialize)]
struct Book {
    book_id: Uuid,
    author_id: Uuid,
    title: String,
    published: String,
}

#[derive(Debug, Deserialize, sqlx::FromRow)]
struct AuthorBookRow {
    author_id: Uuid,
    author_name: String,
    author_dob: String,
    book_id: Uuid,
    book_title: String,
    book_published: String,
}

To fetch the data from database,

use sqlx::PgPool;
async fn fetch_auth_books(pool: &PgPool) -> Result<Vec<Author>, sqlx::Error> {
 let rows: Vec<AuthorBookRow> = sqlx::query_as!(AuthorBookRow,
    r#"
    select
        author.author_id,
        author.name as author_name,
        author.dob as author_dob,
        book.book_id,
        book.title as book_title,
        book.published as book_published
    from
        book
        left join author on book.author_id = author.author_id
    "#)
    .fetch_all(pool)
    .await?;

 let mut author_map: HashMap<Uuid, Author> = HashMap::new();

 for row in rows {
     let book = Book {
         book_id: row.book_id,
         author_id: row.author_id,
         title: row.book_title,
         published: row.book_published,
     };

     author_map
         .entry(row.author_id)
         .and_modify(|author| author.books.push(book.clone()))
         .or_insert_with(|| Author {
             author_id: row.author_id,
             name: row.author_name,
             dob: row.author_dob,
             books: vec![book],
         });
  }

  Ok(author_map.into_values().collect())
 }

Here I used sqlx::FromRow derive macro to directly map your query results to a struct. Then iterate over the results, group them by the author_id and build the desired structure in a HashMap. Finally convert the HashMap into a Vec of Author structs.