4 ms·

I found yesql inadaquete for use with anything other than simple queries. Writing plain SQL is definitely nicer than an ORM, however, I find that manipulating S

I found yesql inadaquete for use with anything other than simple queries. Writing plain SQL is definitely nicer than an ORM, however, I find that manipulating SQL as data (honeysql) is far superior to .sql files with added magic (yesql). As soon as I had to dynamically build queries (essential when moving beyond the complexity of "select this user record") I ran into issues with yesql's lack of power. It also fits closer to one of the main principles of clojure, to be data-driven/data-first/just-use-data etc etc

brandonbloom 9y ago
> 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.
- hyperpape 9y ago
  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?
  - brandonbloom 9y ago
    It's explained a bit here: https://www.postgresql.org/docs/9.6/static/plpgsql-implementation.html https://www.postgresql.org/docs/9.6/static/plpgsql-implement... 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.

brandonbloom 9y ago

> 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.

hyperpape 9y ago
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?
- brandonbloom 9y ago
  It's explained a bit here: https://www.postgresql.org/docs/9.6/static/plpgsql-implementation.html https://www.postgresql.org/docs/9.6/static/plpgsql-implement... 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.