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

What does you existing "manually concatenate" code look like? Does it use `push` and `push_bind`? Or are you working with strings manually? — PitaJ, Dec 29 '22 at 21:29
I don't think `push` and `push_bind` can be used to build query of the form "where field :condition value" especially when the predicate can be optional — Finlay Weber, Dec 29 '22 at 21:39
Nothing in the docs is telling me that they won't work for this case. Why don't you try it? — PitaJ, Dec 29 '22 at 21:46
Unless I am reading it wrong, the doc clearly says how they can be used and it does not fit the form in my usecase — Finlay Weber, Dec 29 '22 at 21:52
To the contrary, everything I'm seeing looks very general-purpose. Sure, certain examples are more specific but they're not on `push` or `push_bind`. Even if they were, those are just examples for a common use-case. Please, just try it. — PitaJ, Dec 29 '22 at 21:56
"A builder type for constructing queries at runtime." "Append a SQL fragment to the query." "Push a bind argument placeholder ... and bind a value to it." These are all very generic statements that don't imply any such restriction in my understanding. — PitaJ, Dec 29 '22 at 21:58
the examples are concrete. and I have also played around with the API, the possibility for usage I see is exactly as it is shown in the example. Maybe if you attempt to build the query for my use case its shortcomings will become obvious to you — Finlay Weber, Dec 29 '22 at 22:03

score 11 · Accepted Answer · answered Dec 29 '22 at 22:12

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.

Thanks. My confusion stemmed from the sql query that get's printed when I call .sql. I could see the bindings but not the part that included the values. Apparently that is built in. — Finlay Weber, Dec 30 '22 at 21:16
"How can I bind an array to a VALUES() clause? How can I do bulk inserts?" in the official FAQ may also come in handy as an alternative method https://github.com/launchbadge/sqlx/blob/main/FAQ.md#how-can-i-bind-an-array-to-a-values-clause-how-can-i-do-bulk-inserts — BinaryButterfly, Apr 22 '23 at 01:59

How to build safe dynamic query with sqlx in rust?

1 Answers1