Question 230830 in PostgreSQL Question posted in
The official documentation can be found here.

Sqlx: Convert / Retrieve Postgresql column of type RECORD[] into a struct field of type Vec<_>

I have the following two structs:

#[derive(Serialize, Deserialize)]
struct Login {
    email: String,
    oidc_id: String
}

#[derive(Serialize, Deserialize)]
struct Account {
    id: Uuid,
    created: NaiveDateTime,
    private_key: String,
    public_key: String,
    logins: Vec<Login>
}

I want to use them to read data from Postgres via Sqlx like this:

let result = sqlx::query_as!(
    Account,
    "select *, ARRAY(
        select ROW(email, oidc_id)
            from logins where logins.account_id = accounts.id
        ) as logins from accounts"
).fetch_all(pool).await?;

Is it possible to get this working without creating a custom record type in Postgres? I tried to implement FromRow but Sqlx still complained about the unsupported type RECORD[]. I also tried to derive sqlx::Type but without success.

Retrieving data like this would be very convenient, but not if I would have to declare a custom record type for each possible query.

Tags:

2

Answers


  1. 0

    From my experience with sqlx I can say that 99% of issues can be solved by type casts, including this one. From the postgres docs:

    By default, the value created by a ROW expression is of an anonymous record type. If necessary, it can be cast to a named composite type — either the row type of a table, or a composite type created with CREATE TYPE AS. An explicit cast might be needed to avoid ambiguity.

    CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);

SELECT CAST(ROW(11, 'this is a test', 2.5) AS myrowtype);

    Giving it a name allows sqlx to match the type on the rust side, so that may work. The other way around is a conversion to a table type, syntax: ROW(...)::table:

    SELECT
  *,
  ARRAY(
    SELECT ROW(email, oidc_id)::logins
      FROM logins
      WHERE logins.account_id = accounts.id
  ) AS logins
FROM accounts;
    Login or Signup to reply.
  2. 0

    Try this:

    #[derive(Serialize, Deserialize)]
struct RawAccount {
    id: Uuid,
    created: NaiveDateTime,
    private_key: String,
    public_key: String,
}

let raw_accounts: Vec<RawAccount> = sqlx::query_as!(
    RawAccount,
    "SELECT id, created, private_key, public_key FROM accounts"
)
.fetch_all(pool)
.await?;

let mut accounts: Vec<Account> = Vec::new();

for raw_account in raw_accounts {
    let logins: Vec<Login> = sqlx::query_as!(
        Login,
        "SELECT email, oidc_id FROM logins WHERE account_id = $1",
        raw_account.id
    )
    .fetch_all(pool)
    .await?;

    let account = Account {
        id: raw_account.id,
        created: raw_account.created,
        private_key: raw_account.private_key,
        public_key: raw_account.public_key,
        logins,
    };

    accounts.push(account);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search