One approach is to create views for the required data and then just select the columns which are needed. The joins will be pruned by the query planner if they are not needed, so there is no need for conditional joins.

> The joins will be pruned by the query planner if they are not needed, so there is no need for conditional joins.

I always wondered about this. How reliable is that in your experience? Thank you in advance.

Yeah this definitely makes sense, and is good database API design as well.

> dynamic query building

it's not (really) addressed by sqlx (intentionally), in the same way most ORM features are not addressed

but to some degree this is what is so nice about sqlx it mainly(1) provides the basic SQL functionality and then let you decide what to use on top of it (or if to use anything on top).

If you need more e.g. the sea-* ecosystem (sea-query, sea-orm) might fulfill you needs.

(1): It can compile time check "static" queries (i.e. only placeholders) which is a bit more then "basic" features, but some projects have to 99+% only static queries in which case this feature can move SQLx from "a building block for other sql libs" to "all you need" to keep dependencies thinner.

Not rust, but I've been a pretty big fan of Dapper and Dapper.SqlBuilder in the C# space... have used it with MS-SQL and PostgreSQL very effectively, even with really complex query construction against input options.

https://github.com/DapperLib/Dapper/blob/main/Dapper.SqlBuil...

I find that interpolating strings works pretty well for this use case (which actually switchd TO string interpolation from ORMs at a previous job of mine).

But this is conditional on either your database or your minimal abstraction layer having support for bindings arrays of data with a single placeholder (which is generally true for Postgres).

Is something like SeaQuery[0] what you're talking about?

[0] https://github.com/SeaQL/sea-query/

SeaQuery looks like a similar dynamic query builder for Rust as Kysely is for JS/TS, so yeah, that'd probably solve the dynamic query problem. But I think parent wasn't so much asking for another library but for patterns.

How do people who choose to use a no-dsl SQL library, like SQLx, handle dynamic queries? Especially with compile-time checking. The readme has this example:

  ...
  WHERE organization = ?

But what if you have multiple possible where-conditions, let's say "WHERE organization = ?", "WHERE starts_with(first_name, ?)", "WHERE birth_date > ?", and you need to some combination of those (possibly also none of those) based on query parameters to the API. I think that's a pretty common use case.

I agree with you that dynamic query building can be tedious with a pure SQL approach. The use case you are describing can be solved with something alone the lines of:

  WHERE organization = $1
     AND ($2 IS NULL OR starts_with(first_name, $2)
     AND ($3 IS NULL OR birth_date > $3)

With SQLx you would have all the params to be Options and fill them according the parameters that were sent to your API.

Does that make sense?

That's relying a lot on the DB engine, which will struggle as the condition gets more complex. I've had MySQL make stupid choices of query plans for very similar queries, I had to break the OR into UNIONs

I think the dynamic part is where the clauses themselves are optional. For example, say you have a data table that a user can filter rows using multiple columns. They can filter by just `first_name` or by `birth_date` or both at the same time using AND / OR, and so on. So you’re dynamically needing to add more or less “WHERE” clauses and then it gets tricky when you have to include placeholders like `$1` since you have to keep track of how many parameters your dynamic query is actually including.

I generally avoid DSLs as they don't bring much... except for this exact use-case. Dynamic queries is pretty much what a query builder is for: you can avoid a dependency by rolling your own, but well it's not trivial and people out there have built some decent ones.

So, if I have this use-case I'd reach for a query builder library. To answer the question of "how to do dynamic queries without a query builder library", I don't think there's any other answer than "make your own query builder"

> Especially with compile-time checking.

no compile time checking and integration tests

in general sqlx only provides the most minimal string based query building so you can easily run into annoying edge cases you forgot to test, so if your project needs that, libraries like sea-query or sea-orm are the way to go (through it's still viable, without just a bit annoying).

in general SQLx "compile time query checking" still needs a concrete query and a running db to check if the query is valid. It is not doing a rem-implementation of every dialects syntax, semantics and subtle edge cases etc. that just isn't practical as sql is too inconsistent in the edge cases, non standard extensions and even the theoretical standardized parts due to it costing money to read the standard and its updates being highly biased for MS/Oracle databases).

This means compile time query checking doesn't scale that well to dynamic queries, you basically would need to build and check every query you might dynamically create (or the subset you want to test) at which point you are in integration test territory (and you can do it with integration tests just fine).

besides the sqlx specific stuff AFIK some of the "tweaked sql syntax for better composeability" experiments are heading for SQL standardization which might make this way less of a pain in the long run but I don't remember the details at all, so uh, maybe not???

---

EDIT: Yes there is an sqlx "offline" mode which doesn't need a live db, it works by basically caching results from the online mode. It is very useful, but still no "independent/standalone" query analysis.