Properly dealing with hierarchies in Rust & SQLx

Question

I am working on a REST API written in Rust using actix-web, SQLx and PostgreSQL for storage. Let's suppose this is my schema (expressed as Rust structs):

struct User {
    pub id: Uuid,
    pub email: String
    // And so on...
}

struct Customer {
    pub id: Uuid,
    pub user_id: Uuid,
    pub name: String,
    // And so on...
}

My current goal is to implement an endpoint, that returns all users with their customers nested. I.e. like this:

// GET /users
// Response from endpoint
[{
  "id": "uuid-1",
  "email": "[email protected]",
  "customers": [{
    "id": "uuid-customer-1",
    "name": "Customer 1"
  }, {
    "id": "uuid-customer-2",
    "name": "Customer 2"
  }]
}]

The payload above could be expressed using the following structs:

#[derive(Serialize)]
struct CustomerData {
    pub id: Uuid,
    pub name: String
}

#[derive(Serialize)]
struct UserData {
    pub id: Uuid,
    pub email: String,
    pub customers: Vec<CustomerData>
}

Using SQLx macro query_as! I came up with following solution attempt:

let result = sqlx::query_as!(
    UserData,
    r#"
    SELECT U.id, U.email, array_agg((C.id, C.name)) as "customers" FROM users U 
    INNER JOIN customers C ON user_id = U.id
    GROUP BY U.id
    "#
)
    .fetch_all(pool.as_ref())
    .await?;

This, however fails, because the result returned by array_agg is of type RECORD[], which SQLx apparently does not yet support.

This issue got me wondering:

• Is there a way to get SQLx to properly map the result of array_agg to customers?
• Given, that my actual schema has at least few more levels in the hierarchy, is this even the right way to go? Would it be "better" to split it in several queries and piecewise construct the response?

Answer 1

Explanation ahead; TL;DR at the bottom.

After more digging, I found a solution, that is actually quite obvious once you get used to SQLx and Rust.

So, the issue is, that SQLx considers the return value of ARRAY_AGG() to be of type RECORD[]. Luckily, SQLX allows us to tell which type we expect by type casting in their DSL.

So, to solve the issue, we first need to cast our RECORD[] to Vec<CustomerData> in the query, which can be done like this:

    SELECT 
        id, 
        email, 
        ARRAY_AGG((C.id, C.name)) as "customers: Vec<CustomerData>" 
    FROM users 
    JOIN customers C ON user_id = U.id
    GROUP BY id, email

Additionally, we need to implement the Trait sqlx::Type for CustomerData. Luckily there's a macro for that:

#[derive(sqlx::Type, Serialize)]
struct CustomerData {
    // ...
}

Last but not least, there is a final issue to resolve: ARRAY_AGG either returns NULL or an array. This can be solved in three ways:

• Nullable types are treated as Option<T> by SQLx. So the field customers should be of type Option<Vec<CustomerData>> in the Struct.

• Tell SQLx to not worry, be happy and assert, that the value is not null, by using an exclamation mark in the query cast. I.e. like this: ... as "customers!: Vec<CustomerData>

• Use SQL to return an empty array (other solutions can be found here), when ARRAY_AGG returns NULL:

    SELECT 
        id, 
        email, 
        COALESCE(NULLIF(ARRAY_AGG((C.id, C.name)), '{NULL}'), '{}') as "customers: Vec<CustomerData>" 
    FROM users 
    JOIN customers C ON user_id = U.id
    GROUP BY id, email

---

This solution works, if hierarchies depth is no more than 1. You'll hit a wall due to a bug in the SQLx' type resolver, if you nest deeper. The Issue regarding this can be found here.

---

TL;DR:

• Derive sqlx::Type on the nested type.
• Cast the query result using SQLx' DSL using the following "as" part

SELECT
  ARRAY_AGG(JOINED.id) as "field_name!: Vec<AggregateType>"
FROM ...

EDIT: To my dismay, custom enums additionally count as a level in the hierachy. Thus, if your nested type has an enum, this is not supported. Even for the current version 0.7.1.