Dynamic SQL queries for tokio postgres in Rust

Question

I am working with tokio postgres for a Rust project where I have a table Home having two columns 'number' and 'address'. My queries are being sent from the rust source code using the sql client as shown below,

let rows = client.query(QUERY, &[&"number", "address"]).await?;

where

QUERY: &str =
"
SELECT * FROM Home 
WHERE number <= $1 
AND address = $2;
";

In the above case, both inputs are valid strings of non-zero length. Given, this information, I am trying to query the rows of the table following certain rules. I have provided them below.

1. If the query input 'address' is null, then the AND part of the string will not be there. In the problem only the 'address' parameter can be null only. The 'number' field is always consistently non-empty. Some ideas that I came across look like this but the ideas are not that concrete and in the current condition it does not work. One example,

   QUERY: &str = " SELECT * FROM Home WHERE number <= $1
   IF $2 IS NOT NULL THEN address = $2; ";

2. I will have to modify the rhs side SQL queries. I can still create a dynamic string so that at run time the queries will look different according to the case encountered, but the requirement is to handle it directly with the help of a SQL rather than rust.

Is there a way to achieve this?

Answer 1

You can use coalesce function in your query:

SELECT * FROM Home 
WHERE number <= $1 
AND address = COALESCE($2, address);

If input parameter $2 will be null, then the field address will will be compared against itself, which will always return true (if there are no nulls in the address field).

Answer 2

If anyone else comes accross this. I was able to achieve this by boxing dyn ToSql + Sync in a vec and converting it to a slice like &[&(dyn ToSql + Sync)] like this

async fn query<T>(
    client: &Client,
    query_str: &str,
    params: &Vec<Box<(dyn ToSql + Sync)>>,
    from_row: impl Fn(Row) -> T,
) -> Result<Vec<T>, Error> {
    let param_slice = &params.iter().map(|x| x.as_ref()).collect::<Vec<_>>();
    let rows = client.query(query_str, &param_slice).await?;
    Ok(rows.into_iter().map(from_row).collect())
}

You can build queries by populating a Vec of the boxed query conditions.

fn query_param_pair_from_args(args: Args) -> (String, Vec<Box<(dyn ToSql + Sync)>>) {
    let mut query: String = "".to_string();
    let mut idx = 1;
    let mut params: Vec<Box<(dyn ToSql + Sync)>> = vec![];
    if let Some(x) = args.id_eq {
        let cond = format!("id = ${idx}");
        if query.is_empty() {
            query = cond;
        } else {
            query = format!("{query} and {cond} ");
        }
        idx = idx + 1;
        params.push(Box::new(x));
    }
    if let Some(x) = args.name_eq {
        let cond = format!("name = ${idx}");
        if query.is_empty() {
            query = cond;
        } else {
            query = format!("{query} and {cond} ");
        }
        idx = idx + 1;
        params.push(Box::new(x));
    }
    if let Some(x) = args.name_like {
        let cond = format!("name like ${idx}");
        if query.is_empty() {
            query = cond;
        } else {
            query = format!("{query} and {cond} ");
        }
        idx = idx + 1;
        params.push(Box::new(x));
    }
    if let Some(x) = args.money_in_bank_lt {
        let cond = format!("money_in_bank < ${idx}");
        if query.is_empty() {
            query = cond;
        } else {
            query = format!("{query} and {cond} ");
        }
        idx = idx + 1;
        params.push(Box::new(x));
    }
    if let Some(x) = args.money_in_bank_gt {
        let cond = format!("money_in_bank > ${idx}");
        if query.is_empty() {
            query = cond;
        } else {
            query = format!("{query} and {cond} ");
        }
        params.push(Box::new(x));
    }
    (query, params)
}

you can append this to a select all statement with a where.

I have an example here https://github.com/decapo01/tokio-postgres-dynamic-query-example