Dynamic queries SQLx for updates

help
1

Hey!

I am new to rust, building a Rust CRUD api to play around and I am trying to build SQL queries dynamically. Is that possible?

Something like:

async fn update_query<T, DB, A>(
    table_name: &str,
    id: Uuid,
    user_id: Uuid,
    request: &T,
) -> Result<Query<'static, DB,  <DB as HasArguments<'_>>::Arguments>, String>
    where
        T: Serialize,
        DB: Database,
    // Ensure the database can encode/bind the types we use
        for<'q> &'q str: Encode<'q, DB> + Type<DB>,
        for<'q> String: Encode<'q, DB> + Type<DB>,
        for<'q> bool: Encode<'q, DB> + Type<DB>,
{
    let request_json = serde_json::to_value(request)
        .map_err(|e| e.to_string())?;
    let mut parameters: Vec<Value> = Vec::new();
    let mut set_parts: Vec<String> = Vec::new();

    if let Some(obj) = request_json.as_object() {
        for (key, value) in obj {
            if value != &Value::Null {
                let param_index = parameters.len() + 1; // SQL parameters are 1-indexed
                set_parts.push(format!("{} = ${}", key, param_index));
                parameters.push(value.clone());
            }
        }
    }

    parameters.push(json!(id));
    parameters.push(json!(user_id));
    let query_str = format!(
        "UPDATE {} SET {} WHERE id = ${} AND user_id = ${}",
        table_name,
        set_parts.join(", "),
        parameters.len() - 1,
        parameters.len()
    );

    let mut query = sqlx::query(&query_str);

    for parameter in parameters.iter() {
        match parameter {
            Value::String(s) => query = query.bind(s),
            Value::Bool(b) => query = query.bind(b),
            // Add other types as necessary
            _ => return Err("Unsupported parameter type".into()),
        }
    }

    Ok(query)
}

Whre I would have different requests, lets imagine my object car:

#[derive(Serialize, Deserialize)]
pub struct UpdateCarRequest {
    license_plate: Option<String>,
    name: Option<String>,
    country: Option<String>,
    state: Option<String>,
    enabled: Option<bool>,
}

and I could call the main function such as any other query:

    update_query("car", car_id, user_id, &car_request.into_inner())
        .execute(pool)
        .await

Does any of this makes sense? I want essentially be able to define structs with optional values, that if present, can be added to the SQL statement.

1 Like
2

You don't want to use sqlx::query with an interpolated query string, unless you have already sanitized the input. Use the query! macro instead.

Yes, it's common practice to aggregate updates :slightly_smiling_face:.

By the way, you might want to look into JSON deserialization for your web framework. Deserializing the request payload manually is too much boilerplate.

1 Like
3

Yes, it's possible but it's deliberately made non-trivial, because you are not, usually, supposed to do that.

If you really need to do that then you would need to ensure that query string outlives the Query object. IOW: your query_string must come exist outside of your update_query function (instead of being created inside).

Something like this:

async fn update(…, query_string: &'a mut String, …) -> Result<Query<'a. …
   …
   *query_str = format!(…)

In that form your string is created outside (but filled inside!) and thus outlives Query object.

There was discussion about making this less tedious, but the temporary conclusion is that you are not supposed to use SQLx like that, normally, and the workaround works for rare cases where you really need that.

1 Like
4

@firebits.io

I dont think I can use, query! - it requires a string literal, which I cannot pass since I am constructing the string dynamically.

By the way, you might want to look into JSON deserialization for your web framework. Deserializing the request payload manually is too much boilerplate.

What do you mean? I think I already use those. In the example above, variables are parsed automatically such as:

#[post("/car/<car_id>", format = "json", data = "<car_request>")]
pub async fn update_car(
    car_id: Uuid,
    car_request: Json<UpdateCarRequest>,
....

@khimru

Thanks, so essentially add an empty string parameter. Will try it out.

You mention SQLx users are not supposed to do that. Let's say I have 10 APIs with 10 different structs like the one I mentioned above.

What would the ideal solution look like?

I don't think a custom massive SQL statement for each API with coalesce or similar SQL logic is ideal.

5

There are no ideal solution because it depends on why do you have all these structs. If your are trying to use SQLx and poor man's diesel then it's probably better to use the real thing.

If you don't use it like that then the first question to ask is: why do you have so many different structs and why do you want to make these requests automatically?

Sometimes the answer is macro which builds bunch of quesries with query!, sometimes the proper way to pass sgtring and create query dynamically, feature exists, after all, it wouldn't exist if it was 100% not needed.

6

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.