Conditionally add WHERE clause?

[rlcurrall]

I've been trying out SQLX in the context of building a REST API as opposed to using an ORM like Diesel or Sea ORM. One friction I'm running into that I don't see an obvious solution for is how to conditionally add filters to the query (i.e. where conditions).

I tried working with the QueryBuilder to achieve this but struggled to find a clean implementation that avoided any SQL injection vulnerabilities. I finally landed on this solution using the query_as! macro, but was wondering if anyone else has solved this in a more elegant way:

pub async fn get_all(
    conn: &PgPool,
    page: i64,
    per_page: i64,
    active: Option<bool>,
    age_gt: Option<i32>,
) -> Result<Vec<User>> {
    let offset = (page - 1) * per_page;

    let res = sqlx::query_as!(
        User,
        "SELECT * FROM users
        WHERE ($1 OR active = $2) AND ($3 OR age > $4)
        LIMIT $5 OFFSET $6",
        active.is_none(),
        active.unwrap_or(true),
        age_gt.is_none(),
        age_gt.unwrap_or(0),
        per_page,
        offset
    )
    .fetch_all(conn)
    .await?;

    Ok(res)
}

[mxchist]

Hello, and welcome to necroposting.

There are sqlx_conditional_queries for this purpose (41 stars on github)

[adering-cw]

you can do something like ($1::boolean is null OR active = $1) AND ($2::integer is null OR age > $2) then you can pass the option type into the query