How to build safe dynamic query with sqlx in rust?

Question

sqlx has a query builder. Documentation can be seen here

I see it supports dynamically buidling queries of the form:

SELECT * FROM users WHERE (id, username) IN ((1, "test_user_1"), (2, "test_user_2"))

But I am interested in building more complex queries likle

SELECT * from users where id = "id" AND username = "username" AND age > "10" AND age < "70" AND last_visited < 12324235435 AND last_visited > 214324324234234

Where any of the where clause is optional. So following should also be dynamically built

SELECT * from users where id = "id" AND age > "10" AND last_visited < 12324235435

I can't seem to find a way to do this with sqlx except from having to manually concatenate the where string myself

Answer 1

I got the following to work locally. Of course, I don't have your database, but the constructed SQL looks correct. I just picked postgres because you didn't specify what database you're actually using.

use sqlx::{query_builder::QueryBuilder, Execute};

struct Search {
    id: i64,
    username: Option<String>,
    min_age: Option<i8>,
    max_age: Option<i8>,
}

fn search_query(search: Search) -> String {
    let mut query = QueryBuilder::new("SELECT * from users where id = ");
    query.push_bind(search.id);

    if let Some(username) = search.username {
        query.push(" AND username = ");
        query.push_bind(username);
    }

    if let Some(min_age) = search.min_age {
        query.push(" AND age > ");
        query.push_bind(min_age);
    }

    if let Some(max_age) = search.max_age {
        query.push(" AND age < ");
        query.push_bind(max_age);
    }

    query.build().sql().into()
}

fn main() {
    dbg!(search_query(Search {
        id: 12,
        username: None,
        min_age: None,
        max_age: None,
    })); // "SELECT * from users where id = $1"
    dbg!(search_query(Search {
        id: 12,
        username: Some("Bob".into()),
        min_age: None,
        max_age: None,
    })); // "SELECT * from users where id = $1 AND username = $2"
    dbg!(search_query(Search {
        id: 12,
        username: Some("Bob".into()),
        min_age: Some(10),
        max_age: Some(70),
    })); // "SELECT * from users where id = $1 AND username = $2 AND age > $3 AND age < $4"
}

I didn't make the id optional but I'm sure you can figure out how to omit the where entirely if no parameters are provided.