> when moving beyond the complexity of "select this user record") I ran into issues with yesql's lack of power.
This is definitely true, however, I'd like to point out that static queries (either Yesql-style, or even stored procedures) can go a lot further than you'd initially expect. Unless you have a pressing application need for dynamic queries, you may consider static queries as they are more predictable with respect to performance and debugging.
Here's the main trick: Most of the time, you want dynamic filters, not dynamic selections. When this happens, you can just include the total set of filters and provide default parameters that are always true.
For example:
SELECT id, name, created_at, score
FROM players
WHERE created_at BETWEEN '-infinity'::timestamp AND 'infinity'::timestamp
ORDER BY score * -1
This will return all players, regardless of when they joined and will sort by score descending. You can parameterize the time range as well as the sort direction between +1 and -1. You can use EXPLAIN to prove to yourself that Postgresql is smart enough to make proper use of indexes.
Does postgres' plan caching detect that these filters are useless, if they're sometimes filled in and other times not? Or do you end up with one bad plan?
In short, if your parameters are highly dynamic, plan caching will be disabled. This is still better than dynamic queries, because the results of parsing will be cached.
If you almost always use one particular set of filters, but just enough dynamic values that the cache plan is not useful, AND your bottleneck is planning, then you can prepare a fast path.
To do that, use a PREPARE statement or your language's SQL bindings for that. Essentially, you tell it "these are usually going to be the defaults, let's call that staticFoo". Then staticFoo and dynamicFoo are subject to independent plan caching. The fast path, staticFoo, will get a good cached query plan, and dynamicFoo will be subject to dynamic re-planning.
The braindead (and therefore "better", by some measure) approach is to just just create an additional query for your fast path. The tradeoff of course is violating the DRY principle.
This is definitely true, however, I'd like to point out that static queries (either Yesql-style, or even stored procedures) can go a lot further than you'd initially expect. Unless you have a pressing application need for dynamic queries, you may consider static queries as they are more predictable with respect to performance and debugging.
Here's the main trick: Most of the time, you want dynamic filters, not dynamic selections. When this happens, you can just include the total set of filters and provide default parameters that are always true.
For example:
This will return all players, regardless of when they joined and will sort by score descending. You can parameterize the time range as well as the sort direction between +1 and -1. You can use EXPLAIN to prove to yourself that Postgresql is smart enough to make proper use of indexes.