Postgres enums and query_as!

[TmLev]

I have the following enum and struct:

#[derive(sqlx::Type, Debug)]
#[sqlx(rename = "service_state", rename_all = "SCREAMING_SNAKE_CASE")]
pub enum ServiceState {
    Available,
    NotAvailable,
}

pub struct ServiceStatus {
    pub ip: String,
    pub state: ServiceState,
}

Corresponding type and table:

CREATE TYPE service_state AS ENUM (
    'AVAILABLE', 'NOT_AVAILABLE'
);

CREATE TABLE service_statuses (
    ip TEXT PRIMARY KEY,
    state service_state NOT NULL
);

I'm trying to fetch all rows like so:

sqlx::query_as!(ServiceStatus, "SELECT * FROM service_statuses")
    .fetch_all(&pool)
    .await
    .expect("Failed to execute query");

But compiler fails with the following message:

error: unsupported type service_state of column #2 ("status")

Am I doing something wrong?

I've tried to search through issues first and got an impression that there are no compile-time checks for this now, is that right?

[ZacharyLeBlanc]

I was running into something similar. I found that this worked.

sqlx::query_as!(ServiceStatus, r#"SELECT ip, state as "state: _" FROM service_statuses"#)
    .fetch_all(&pool)
    .await
    .expect("Failed to execute query");

or you can make the type explicit.

sqlx::query_as!(ServiceStatus, r#"SELECT ip, state as "state: ServiceState" FROM service_statuses"#)
    .fetch_all(&pool)
    .await
    .expect("Failed to execute query");

https://docs.rs/sqlx/0.4.2/sqlx/macro.query.html#force-a-differentcustom-type

[TmLev]

@ZacharyLeBlanc, thanks, this works! Do you happen to know whether this is runtime or compile-time check?

[ZacharyLeBlanc]

I just picked up sqlx for a side project so I'm no expert but reading the docs it looks like this would be a runtime error. If there is a better solution I would love to hear it as I was running into this as well. I just found that this worked for me.

[jplatte]

This disables the compile-time type check for the specific column. Checking custom types for compatibility at compile time is not currently supported.

[TmLev]

I'm also interested in how one can insert UDT with an update on the conflict, like so:

INSERT INTO service_statuses (ip, state) VALUES ($1, $2)
ON CONFLICT (ip) DO UPDATE SET state = $1

The compiler says that I need to cast value, but I'm not sure how to do it:
error returned from database: column "state" is of type service_state but expression is of type text

[mehcode]

INSERT INTO service_statuses (ip, state) VALUES ($1, $2)
ON CONFLICT (ip) DO UPDATE SET state = $1

Are you sure that's right?

You are setting ip to $1 in line 1 but state to $1 in the second

[TmLev]

Sorry, it's a typo. Actual message: error: unsupported type service_state for param #2

[mehcode]

Ah you need https://docs.rs/sqlx/0.4.2/sqlx/macro.query.html#type-overrides-bind-parameters-postgres-only

query!(
  r#"
INSERT INTO service_statuses (ip, state) VALUES ($1, $2)
ON CONFLICT (ip) DO UPDATE SET state = $1
  "#,
  one as _,
  two as _,
)

It is a similar concept but in the inverse direction for parameters.

[TmLev]

Yep, that works, appreciate it!

Is there any issue about compatibility for UDT at compile time I can follow?

[TatriX]

I'm getting this error from sqlx::query_file_as!(, but not from sqlx::query_as!(. I assume it's not supported yet?

[ilmoi]

I'm running into the same issue except with an INSERT statement. The advice above helped with SELECT, but not with INSERT. Specifically, my code compiles fine but at runtime I get a TypeNotFound error:

thread 'actix-rt|system:0|arbiter:0' panicked at 'called `Result::unwrap()` on an `Err` value: TypeNotFound { type_name: "TweetClass" }', src/twitter/domain/tweet.rs:178:6
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

My query code:

    sqlx::query!(
        r#"
        INSERT INTO tweets
        (id, created_at,
        tweet_id, tweet_created_at, tweet_text, tweet_url,
        replied_to_tweet_id, quoted_tweet_id, tweet_class, 
        like_count, quote_count, reply_count, retweet_count, total_retweet_count, popularity_count,
        user_id)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
        "#,
        Uuid::new_v4(),
        Utc::now(),
        tweet_id,
        tweet_created_at,
        tweet["text"].as_str(),
        tweet_url,
        replied_to_tweet_id,
        quoted_tweet_id,
        tweet_class as TweetClass, // also tried "as _" - neither works
        tweet_metrics.like_count,
        tweet_metrics.quote_count,
        tweet_metrics.reply_count,
        tweet_metrics.retweet_count,
        tweet_metrics.total_retweet_count,
        tweet_metrics.popularity_count,
        author.id,
    )
    .execute(pool)
    .await
    .unwrap();

TweetClass (defined in the same file):

#[allow(non_camel_case_types)]
#[derive(Debug, sqlx::Type)]
pub enum TweetClass {
    normal,
    rt_original,
    helper,
}

What am I missing?

[jplatte]

@ilmoi Your enum definition is likely wrong. What you need:

// assuming your postgres type name is in snake_case like the variants seem to be
#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "tweet_class", rename_all = "snake_case")]
pub enum TweetClass {
    Normal,
    RtOriginal,
    Helper
}

[mattoni]

I'm having a similar issue but with an array of enums, trying to insert in bulk using unnest like the FAQ calls for:

sqlx::query!(
     r#"
                INSERT INTO "Expansion"(region) 
                SELECT * FROM UNNEST($1::"Region"[])
       "#,
         &region[..] as _,
        )
.execute(&ctx.db)
.await?;

where Region is

pub enum Region {
    #[sqlx(rename = "intl")]
    International,
    #[sqlx(rename = "jp")]
    Japan,
}

the trait bound `[db::models::region::Region]: sqlx::Type<sqlx::Postgres>` is not satisfied
the following implementations were found:
  <[&[u8]] as sqlx::Type<sqlx::Postgres>>
  <[&str] as sqlx::Type<sqlx::Postgres>>
  <[(T1, T2)] as sqlx::Type<sqlx::Postgres>>
  <[(T1, T2, T3)] as sqlx::Type<sqlx::Postgres>>
and 36 others

How can we bulk insert enums? Thanks!

[jplatte]

As of the latest release, to make arrays of custom types work you just have to implement PgHasArrayType for the enum, returning PgTypeInfo::with_name("_foo") (where foo is the name of the postgres enum type) from the array type info method.

[LukeMathWalker]

Is there a reason why #[derive(sqlx::Type)] doesn't produce an implementation of PgHasArrayType, returning the type name prefixed by _?

[jplatte]

I didn't try implementing that. It would probably only make sense when a #[sqlx(type_name = "...")] attribute is used.

[lescuer97]

As of the latest release, to make arrays of custom types work you just have to implement PgHasArrayType for the enum, returning PgTypeInfo::with_name("_foo") (where foo is the name of the postgres enum type) from the array type info method.

Hi!, I had the exact problem and this worked, but my question is why the underscore? is this because of postgres or sqlx?

[LukeMathWalker]

It's Postgres' default naming convention for array types.

[djc]

Are these solutions documented? I always have trouble finding documentation for the enum handling, and I need it rarely enough (so far) that I haven't memorized it.

[rex-remind101]

It's Postgres' default naming convention for array types.

@LukeMathWalker where is this documented in Postgres so I can understand that convention better?

[LukeMathWalker]

Yes, it is documented here:

Array types

Whenever a user-defined type is created, PostgreSQL automatically creates an associated array type, whose name consists of the element type's name prepended with an underscore, and truncated if necessary to keep it less than NAMEDATALEN bytes long. (If the name so generated collides with an existing type name, the process is repeated until a non-colliding name is found.) This implicitly-created array type is variable length and uses the built-in input and output functions array_in and array_out. Furthermore, this type is what the system uses for constructs such as ARRAY[] over the user-defined type. The array type tracks any changes in its element type's owner or schema, and is dropped if the element type is.

[rminami]

As of the latest release, to make arrays of custom types work you just have to implement PgHasArrayType for the enum, returning PgTypeInfo::with_name("_foo") (where foo is the name of the postgres enum type) from the array type info method.

I'm trying to go with this approach and use the PgHasArrayType trait, but sqlx still seems to return an error. As a minimal example, I've defined the struct and enum for the table I'm using like this:

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "user_type", rename_all = "snake_case")]
pub enum UserType {
    Viewer,
    Editor,
    Admin,
}

impl PgHasArrayType for UserType {
    fn array_type_info() -> PgTypeInfo {
        PgTypeInfo::with_name("_user_type")
    }
}

#[derive(Debug, sqlx::Type)]
pub struct User {
    pub username: String,
    pub user_type: UserType,
}

And this is the corresponding SQL:

create type user_type as enum ('viewer', 'editor', 'admin');

create table users (
    user_id serial not null primary key,
    username text not null,
    user_type user_type not null
);

However, when I try to query it like this,

#[tokio::main]
async fn main() {
    dotenv().ok();

    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL not set");
    let pool = PgPool::connect(&database_url)
        .await
        .expect("Could not connect to DATABASE_URL");

    let users = sqlx::query!(r#"select user_id, username, user_type from users;"#)
        .fetch_all(&pool)
        .await
        .unwrap();

    println!("{:?}", users);
}

I get unsupported type user_type of column #3 ("user_type"), and I still have to write sqlx::query!(r#"select user_id, username, user_type as "user_type: UserType" from users;"#) to get it to stop complaining.

Is there something else I have to do here?

[eboody]

I'd love some clarity on this.

I wish the reason I can't turn query_as() to query_as!() when using enums was more intuitive but I'm struggling.

[altanbgn]

any updates on this for how you guys handled it?

[mike-lloyd03]

I've just been using the non-macro forms of query and query_as 🙁

[altanbgn]

Well that works but it kinda bugs me that macro's are giving me error. Feels like it's a wrong practice.

[mike-lloyd03]

It's not ideal but it is what it is.

[ewrogers]

Running into this issue as well with query_as!. It seems to work fine with INSERT using Enum::Value as Enum syntax with captured.

For example:

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "job_status", rename_all = "lowercase")]
pub enum JobStatus {
    Queued,
    Running,
    Completed,
}

But when I want to SELECT * FROM on this table, I get that job_status is an unsupported type. I believe it is possible to override it, if I were to manually specify every column and AS "status: JobStatus" on that particular one but it does become painful on larger tables.

I tried to override "just that one" column with something like:

SELECT *, status AS "status: JobStatus" FROM job_history

But it gives me that status has been defined twice in the SELECT and that is invalid.

Is there a way to annotate this on the type being query_as!(type, ...)?

Ex:

use crate::enums::JobStatus;

pub struct JobHistory {
    pub id: Uuid,
    // ...
    #[sqlx(as_type=JobStatus)]
    pub status: JobStatus
}

I admit that I'm relatively new to Rust and so I am not sure of the macro limitations and nuance that goes into making this work. It does feel like magic at times, but things like this become head scratchers for mapping.

For the time being, I'm also going with the non-macro versions of query and query_as::<_, JobHistory>(...) since they do seem to work seamlessly although I lose compile-time checking unfortunately.

[simanacci]

@ewrogers r#"SELECT id, status AS "status!: JobStatus" FROM job_history"#

[ewrogers]

@ewrogers r#"SELECT id, status AS "status!: JobStatus" FROM job_history"#

Yeah that works if I just need the two properties, but if I need "everything" I can't use * if I need to AS cast something unfortunately...

[TmLev]

Using SELECT * is rarely a good idea

[ewrogers]

That's a fair point, and I admit that I'm "getting back into SQL" after recent years of Mongo/DynamoDB land so there's a good chance I'm re-learning best practices there.

Though is it a matter of "we don't support this because it's not a good idea and you shouldn't do it" (opinionated), or simply "we haven't found a way to support it yet"?

I'm not arguing either side, just more or less to manage expectations of people who run into this.

[TmLev]

Unfortunately, it's not supported yet. Personally, I'd love to skip casting each enum column in the future.

[lostb1t]

Any workaround for inserts?

[TmLev]

Any workaround for inserts?

#1004 (comment)

[alexanderameye]

Just wanted to add for reference how I got enum of custom type + query_as! working.

#[derive(Debug, Deserialize, Serialize, ToSchema, sqlx::Type)]
pub enum LightCondition {
    DirectSunlight,
    BrightIndirectSunlight,
    MediumIndirectSunlight,
    LowIndirectSunlight
}

impl PgHasArrayType for LightCondition {
    fn array_type_info() -> PgTypeInfo {
        PgTypeInfo::with_name("_light_condition")
    }
}

// SQL query in query_as!
SELECT  s.light_conditions AS "light_conditions: _" FROM species s

The custom type was created as follows.

CREATE TYPE light_condition AS ENUM('bright_indirect_sunlight','direct_sunlight','low_indirect_sunlight','medium_indirect_sunlight');

The light_conditions column was created as follows (it is an array of enums).

light_conditions light_condition[] DEFAULT ARRAY []::light_condition[],

[xpe]

@alexanderameye Would you please edit your comment to show the schema for your column light_conditions? Is it an array of enums? The pluralization suggests so.

This part may have gotten lost in the discussion above: if a column type isn't an array, then impl PgHasArrayType isn't needed. That impl is only needed when you need an array of an enum.

[rahulkp220]

Also had the same issue using query_as! with enums.
Ended up using something like

#[derive(sqlx::Type, Serialize, Deserialize, Debug)]
#[sqlx(type_name = "git_source", rename_all = "lowercase")]
pub enum GitSource {
  Github,
  Gitlab,
  Bitbucket,
}

let project = Project {
    id: Uuid::new_v4(),
    git_source: GitSource::Github,
  };

sqlx::query_as!(
      Project,
      r#"INSERT INTO projects (id, git_source)
       VALUES ($1, $2)
       RETURNING id, git_source AS "git_source!: GitSource"#",
      project.id,
      project.git_source as GitSource,
    ).fetch_one(pool).await;

Hope this helps anyone wanting to do inserts.

[RAprogramm]

            let updated_user = sqlx::query_as!(
                UserModel,
                r#"UPDATE users SET role=$1 as "role:UserRole",updated_at=$2 WHERE id=$3 RETURNING id,name,email,verified,created_at,password,updated_at,role as "role: UserRole""#,
                role.into(),
                now,
                user_id,
            )
            .fetch_optional(&self.pool)
            .await?;

does it possible to UPDATE role ? in my example syntax error. i've tryed without as "role:UserRole" after $1 but it's not working

i opened issue here

[RAprogramm]

            let updated_user = sqlx::query_as!(
                UserModel,
                r#"UPDATE users SET role=$1 as "role:UserRole",updated_at=$2 WHERE id=$3 RETURNING id,name,email,verified,created_at,password,updated_at,role as "role: UserRole""#,
                role.into(),
                now,
                user_id,
            )
            .fetch_optional(&self.pool)
            .await?;

does it possible to UPDATE role ? in my example syntax error. i've tryed without as "role:UserRole" after $1 but it's not working

i opened issue here

YES!
Here is right query

r#"UPDATE users SET role=($1::text)::user_role, updated_at=$2 WHERE id=$3 RETURNING id, name, email, verified, created_at, password, updated_at, role as "role:UserRole""#,

[heksesang]

Polluting the SQL with weirdly named columns isn't ideal, as this might cause issues if you want to reuse the same SQL files for other stuff.

What stops the macro from type-checking these fields without further annotation, considering the type of the field in the rust struct has a type_name set?

[abonander]

@heksesang #514