Would sqlx add support for appends a piece of SQL code at the struct sqlx::Query?

[ilovelll]

Like Diesel's query_builder, it's useful when we want to build a conditional where sql statement.
Detail link: https://docs.diesel.rs/diesel/query_builder/struct.SqlQuery.html#method.sql

[audunhalland]

Something I do quite often with SQL databases is to build dynamic WHERE clauses. Typically, some REST api with very liberal filtering options. Often these kinds of queries are hard to precompile. I've used Kotlin string interpolation to build these, something like:

"
SELECT stuff FROM stuff
WHERE
  ${when (idFilter) {
     null -> "TRUE"
     emptySet() -> "FALSE"
     else -> "stuff.id IN (:idFilter)"
  }}
  AND
  ${when (otherFilter) {
    null -> "TRUE"
    emptySet() -> "FALSE"
    else -> "stuff.other IN (:otherFilter)
  }}
"

and bind the SQL parameters (:idFilter and :otherFilter) to the query somewhere else. This represents 9 different queries and of grows exponentially with the number of potential clauses.

But in this case the "dynamicness" doesn't really change the input and output types of the query. E.g. the :idFilter input is just an Option<Set<Id>>. It's just that it is unused in the actual query for some values of the type.

SQL queries in applications are often simple "SQL compilers" like this, in my experience. I've always preferred working with pure SQL over DSL-like generators, and I'm currently looking into how to best replicate this pattern in Rust and sqlx.

[abonander]

We've been debating on the best design for a dynamic query builder but haven't really reached consensus. We definitely have come across a need for it in our own uses of SQLx and ended up hand-rolling something that could serve as a jumping-off point.

However, we'd prefer not to add this to sqlx::Query since if we just add a .push_sql() method it becomes much easier for a lazy or naive user to introduce SQL injection vulnerabilities into their app (if they use it to add data that should be in a bind parameter instead). Taking a string slice in query() was a deliberate choice in defensive design as the easiest thing to pass there is a string literal which is safe from SQL injection, and it's a very obvious code smell to do something like sqlx::query(&format!("select * from my_table where foo = {}", untrusted_data)).

I would kind of like to close this issue in favor of one that's specifically for discussing the design of such a query builder.

[audunhalland]

Yes, I was not really looking for something directly like "push", but instead exactly as you say, some builder pattern with a way to bind data ergonomically (without a lot of code repetition).

It would be interesting to see and maybe contribute to such a discussion!

[abonander]

closing in favor of #291