> just wrote actual SQL in files with .sql at the end and load them at run-time
I like this idea, but I'm interested to learn how you are handling dynamic queries -- e.g., when you want to change the order-by field? You can use CASE in some situations, but it has limitations. Another option is to construct the dynamic query in SQL rather than in your outside-the-db application code. Just curious how you approached and solved this?
Sorry if reply is shorter than I would like due to hand, but the dynamic aspects I have encountered have been solved by case when as mentioned, boolean expressions on parameters (where $1 is true or id = $2), or in the worst cast just not caring about DRY in this scenario. Also I use json_agg etc for ORM-like mapping to arrays and objects. I do some other tricks too like bypassing a bunch of redundant and slow parsing steps by sending the result in certain queries back to app server as a single text column that is in JSON (this is via wrapping queroes, not manually) so it can send that to the client. No db driver parsing, converting to objects, back to JSON, etc etc.
Thanks for writing what you have. These all sound like reasonable approaches. Just to add some extra options/thoughts because I’ve been thinking about this lately, for cases where CASE doesn’t work well on order by, you can use dynamic queries to construct the final query in SQL. Another option might be to include a splash of templating to your .sql files, and parse them at run time, or use them to generate the final .sql files beforehand so that they can be tested directly via something like pgtap (repeat yourself via templates instead of by hand). The main reason I like the sound of including the queries in separate .sql files is precisely so that they can be easily and directly included in tests. The downside is the query isn’t included next to the code that uses it, so a little hunting needs to be done to find it (always tradeoffs!).
I like this idea, but I'm interested to learn how you are handling dynamic queries -- e.g., when you want to change the order-by field? You can use CASE in some situations, but it has limitations. Another option is to construct the dynamic query in SQL rather than in your outside-the-db application code. Just curious how you approached and solved this?