`query!` macro doesn't work with composite types

[ahmed-said-jax]

Bug Description

Using the query! macro to insert a row does not work with a composite type defined in the schema and as a Rust struct, but it works using the query function (which obviously does not perform compile-time checking). I've found similar issues, but I don't think any that directly explain why I can't do this.

Minimal Reproduction

In my schema file:

CREATE TYPE metadata AS (
    name text,
    age integer
);

CREATE TABLE person (
    id serial PRIMARY KEY,
    metadata metadata,
    favorite_food text
);

and in main.rs:

use sqlx::{postgres::PgPool, query};

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "metadata")]
struct Metadata {
    name: String,
    age: i32,
}

struct Person {
    metadata: Metadata,
    favorite_food: String,
}

#[tokio::main]
async fn main() {
    let metadata = Metadata {
        name: "ahmed".to_string(),
        age: 100,
    };

    let person = Person {
        metadata: metadata,
        favorite_food: "pb&j".to_string(),
    };

    let pool = PgPool::connect("postgres://localhost/test").await.unwrap();

    // This works fine
    query("INSERT INTO person (metadata, favorite_food) VALUES ($1, $2)")
        .bind(&metadata)
        .bind(&person.favorite_food)
        .execute(&pool)
        .await
        .unwrap(); // This works

    // This does not work
    query!(
        "INSERT INTO person (metadata, favorite_food) VALUES ($1, $2)",
        metadata,
        person.favorite_food
    )
    .execute(&pool)
    .await
    .unwrap();
}

The latter, using the query! macro, throws the following error:

error: unsupported type metadata for param #1
  --> src/main.rs:36:5
   |
36 | /     query!(
37 | |         "INSERT INTO person (metadata, favorite_food) VALUES ($1, $2)",
38 | |         metadata,
39 | |         person.favorite_food
40 | |     )
   | |_____^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `scamplers-sql` (bin "scamplers-sql") due to 1 previous error

Am I just using sqlx fundamentally wrong, or is this an actual bug? I have a really complex data model, and I can save myself a lot of code by factoring out common metadata fields into these composite types.

Thanks!

Info

• SQLx version: 0.7.4
• SQLx features enabled: ["migrate", "macros", "json", "postgres", "tls-rustls", "runtime-tokio", "time"]
• Database server and version: Postgres 16.3
• Operating system: macOS
• rustc --version: 1.79.0

[ClasicRando]

From what I can see, this looks like an issue that wouldn't be a straight forward fix since the current parameter type checking is done against a static list of known types which cannot include your custom compile time derived type.

Where your error originates https://github.com/launchbadge/sqlx/blob/0eb2ee93650eaf10d9cadc107a88bfabfdf377a7/sqlx-macros-core/src/query/args.rs#L58-L73

This uses a database's TypeChecking trait to verify the parameter types. For each database that trait is generated using a macro_rules! block. https://github.com/launchbadge/sqlx/blob/0eb2ee93650eaf10d9cadc107a88bfabfdf377a7/sqlx-core/src/type_checking.rs#L130-L188

The macro essentially builds a giant if-else if-else block checking all database specified types for a match on TypeInfo. For postgres (and any other database) the list of accepted types is known at compile time from within the crate so your type is never going to be verified. https://github.com/launchbadge/sqlx/blob/0eb2ee93650eaf10d9cadc107a88bfabfdf377a7/sqlx-postgres/src/type_checking.rs#L10-L213

My guess is that the only solution would involve a special provision using the postgres feature to allow for composite types to bypass the checking.

[KairuDeibisu]

I have the same issue but different error.

error returned from database: prepared statement "sqlx_s_1" does not exist

query_as!(
            User,
            r#"
            INSERT INTO user_mstr (username, email, password, audit_info)
            VALUES ($1, $2, $3, ROW($4, $5, $6, $7))
            RETURNING *;
            "#,
            username,
            email,
            hashed_password,
            audit_info.created_by,
            audit_info.created_on,
            audit_info.modified_by,
            audit_info.modified_on,
        );

#[derive(Serialize, Deserialize, Debug, FromRow)]
pub struct User {
    pub user_id: i32,
    pub username: String,
    pub email: String,
    pub password: Option<String>,
    pub audit_info: Audit,
}

#[derive(sqlx::Type, Serialize, Deserialize, Debug, FromRow)]
#[sqlx(type_name = "audit")]
pub struct Audit {
    pub created_by: i32,
    pub created_on: NaiveDateTime,
    pub modified_by: i32,
    pub modified_on: NaiveDateTime,
}

[ClasicRando]

@KairuDeibisu You're problem is 2 fold:

1. The query macros can not understand composite types because otherwise you query would be fine to parse out.
2. The query macros do not currently use FromRow derived traits implementations so your query would not work.

Furthermore, you query would not even work if the query macros used the FromRow implementation because you are returning a row with the composite type in it, not a flattened row. You would need to modify your query to something like:

INSERT INTO user_mstr (username, email, password, audit_info)
VALUES ($1, $2, $3, ROW($4, $5, $6, $7))
RETURNING username, email, password, (audit_info).*

But again, even if you modified your query to the above and provided FromRow implementations for both types and used the #sqlx(flatten) attribute on the User.audit_info field, the query macros do not work with those implementations of FromRow, rather the query macros desugar to code that resembles the FromRow derived implementation. This desugaring does not respect the flatten attribute and therefore will attempt to construct a User struct instance with the expanded composite type fields included as well which will fail.

You can try to update your code to include my modified SQL query and check the macro expansion of the query_as! to see it attempt to build a row with the audit_info attributes included in the User type.

[ahmed-said-jax]

I was just being dumb. The proper way to do what I was trying to do is to just deconstruct the Metadata struct:

query!(
    "INSERT INTO person (metadata.name, metadata.age, favorite_food) VALUES ($1, $2)",
    metadata.name,
    metadata.age,
    person.favorite_food
)
.execute(&pool)
.await
.unwrap();

just like any other insertion you might do (until sqlx has support for inserting structs in their entirety)