Getting "no binary output function available for type ulid" using SQLx from Rust

[jlandahl]

I just installed pgx_ulid into a Postgres 15 database, and while inserting and viewing in a database tool (DataSpell) works fine, when I try to query from Rust using SQLx I get a "no binary output function available for type ulid" error. I thought it might be due to how I was implementing SQLx's Encode and Decode traits, but I reworked those to be based on strings and I'm still getting the error. I'll paste some example code shortly.

[jlandahl]

Here's a minimal program in Rust that exhibits the error:

use sqlx::postgres::PgPoolOptions;
use sqlx::Row;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let connect = "postgres://foo:bar@localhost/db";
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(connect)
        .await?;

    let rows = sqlx::query("SELECT * from sources")
        .fetch_all(&pool)
        .await?;

    println!(
        "rows: {:?}",
        rows.into_iter()
            .map(|row| row.get::<String, _>("source_id"))
            .collect::<Vec<_>>()
    );

    Ok(())
}

And here's the full error:

Error: Database(PgDatabaseError { severity: Error, code: "42883", message: "no binary output function available for type ulid", detail: None, hint: None, position: None, where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("lsyscache.c"), line: Some(2946), routine: Some("getTypeBinaryOutputInfo") })

[jlandahl]

I noticed in the source code that there are implicit conversions to/from UUIDs / ULIDs, so I found this workaround:

create table foo (
    foo_id uuid primary key default gen_ulid(),
    name text not null
);

In other words, store the ULID as a UUID, retrieve it through SQLx as a UUID, and convert to/from ULID in the application. Not ideal, but it works.

[pksunkara]

@workingjubilee Can I get some help from you to understand this issue? I store the type as binary but the InOutFuncs use string.

Reading https://github.com/launchbadge/sqlx/issues/1269, Does sqlx need another set of InOutFuncs to be in binary? If yes, how to add another impl since I already have one?

[workingjubilee]

I believe PGRX simply does not adequately support this case unfortunately: we do not have a way to describe the typsend and typreceive functions for a type.

[orefalo]

I am reading all these issues... I wonder if this extension, which looks really good on paper, is prod ready?

[pksunkara]

I use it in production.

[orefalo]

indeed, I've been playing with it and it's neat and handy. thank you.

[nbari]

Hi @pksunkara

InOutFuncs

How are you storing created ULids (when not using the extension to generate them)?

I started to use the extension and found this issue, In my case I am creating the Ulid with:

let token = Ulid::new();

And trying to store it with (sqlx)

let query = "INSERT INTO tokens (id, client_id) VALUES ($1, $2) RETURNING id";
let insert_token = sqlx::query(query)
     .bind(token.to_string())
     .bind(client_id)
     .fetch_one(&mut *tx)
     .await;

But I get this error:

Failed to insert token into database: error returned from database: column "id" is of type ulid but expression is of type text

I managed to insert by casting the id, for example:

let query = "INSERT INTO tokens (id, client_id) VALUES ($::ulid, $2)";

But then for retrieving the ID I get the same error from the title of this issue:

let query = "INSERT INTO tokens (id, client_id) VALUES ($::ulid, $2) RETURNING id"

no binary output function available for type ulid

Manage to make it work using:

  let query = "INSERT INTO tokens (id, client_id) VALUES ($1::ulid, $2) RETURNING id::text";
  let insert_token = sqlx::query(query)
        .bind(token.to_string())
        .bind(client_id)
        .fetch_one(&mut *tx)
        .await;

 let result = match insert_token {
        Ok(row) => {
            let token_id: String = row.get("id");

            let metadata_query =
                "INSERT INTO metadata (id, ip_address, country, user_agent) VALUES ($1::ulid, $2, $3, $4)";
            sqlx::query(metadata_query)
                .bind(token_id)
                .bind(ip_address)
                .bind(country)
                .bind(ua)
                .execute(&mut *tx)
                .await
        }
        Err(err) => Err(err),
    };

But don't know if casting the result to test RETURNING id::text"; is the best way, any thoughts?

[kelvincesar]

I'm having the same problem.

To fix in the meanwhile, I updated my struct "UserData.id" to type String. And during the query applied a cast "id::text":

      match sqlx::query_as::<_, UserData>(
            r#"
            INSERT INTO users (name, email,) VALUES ($1, $2) RETURNING
            id::text, name, email
            "#,
        )