Sqlx querying and casting of query parameter values

Question

Edit: to clarify, with the goal of decoupling the API layer and the DB layer I prefer the IDs to be of type string. The current implementation of the DB layer with PostgresSQL and sqlx is cumbersome due to the need of casting back and forth from string to uuid. Looking for a solution on how to extend the data types / sqlx / etc for a better developer experience.

Modifying the API layer structs to hold Uuid types, is not the solution I'm considering at the moment.

---

Having constant pains with sqlx and PostgreSQL when it comes to table types and nullable types, mainly around Uuid's

Given the following table:

    Column    |           Type           | Collation | Nullable |      Default
--------------+--------------------------+-----------+----------+--------------------
 id           | uuid                     |           | not null | uuid_generate_v4()
 country_id   | uuid                     |           | not null |
 city_id      | uuid                     |           |          |
 type_ids     | uuid[]                   |           | not null |
 name         | character varying        |           | not null |

The data is coming through an HTTP request from an API layer, so all types are type String in the input struct.

pub struct LocationInput {
    pub country_id: Option<String>,
    pub city_id: Option<String>,
    pub type_ids: Vec<String>,
    pub name: String
}

async fn create_location(&self, country_id: &str, input: LocationInput) -> anyhow::Result<Location> {
    query_as!(
        Location,
        r#"
        insert into locations (country_id, city_id, type_ids, name)
        values ($1, $2, $3, $4)
        returning *
     "#,
        country_id,
        input.city_id,
        input.type_ids,
        input.name
    )
      
        .fetch_one(&self.pool)
        .await
        .map_err(|e| {
            println!("SQL Error: {}", e);
            anyhow::Error::new(e)
        })
}

query_as! - will not accept:

country_id, input.city_id, input.type_ids,input.name

Since it expects Uuid / Option<Uuid> / Vec<Uuid>

Which I mitigate by casting every variable manually

let id = Uuid::from_str(id)?;
let optional_id = optional_id
    .as_ref()
    .map(|s| s.parse::<Uuid>())
    .transpose()
    .context("Invalid city_id UUID")?;

Which is not a viable path forward, in reality there are far more variables involved.

The best case scenario would be to have sqlx convert String and Uuid including Option<T> on it's own (provided some type casting code).

Is that possible? Is there a better solution?

From the docs it seems sqlx can handle casting using $1::type but how can that be applied in the case of

• String -> Uuid
• Option<String> -> Option<Uuid>
• Vec<String> -> Vec<Uuid>

Answer 1

Looks like the query_as!() macro does not support type casting, but the query_as function do.

So using 'in-query' type casting everything works fine, the use case described in the original post can be achieved using sqlx::query_as().

async fn test(&self) {

    let id = "4c598948-fbee-4004-bdc2-a85fe717cf50";
    let type_ids = ["4c598948-fbee-4004-bdc2-a85fe717cf50",
                    "4c598948-fbee-4004-bdc2-a85fe717cf50"];
    let optional: Option<String> = None;
    let query = r#"
    insert into locations (fleet_id, name, type_ids, city_id)
        values ($1::uuid, $2, $3::uuid[], $4::uuid)
        returning *
    "#;

    let y = sqlx::query_as::<_, Location>(query)
        .bind(id)
        .bind("test name")
        .bind(type_ids)
        .bind(optional)
        .fetch_one(&self.pool)
        .await
        .map_err(|e| {
            println!("SQL Error: {}", e);
            anyhow::Error::new(e)
        });

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