4 ms·

I hate every new technology listed in this article. I recently removed all of my SQL-in-programming-language code (orm, strings of SQL) etc, and just wrote actu

I hate every new technology listed in this article. I recently removed all of my SQL-in-programming-language code (orm, strings of SQL) etc, and just wrote actual SQL in files with .sql at the end and load them at run-time. I am now, when my finger heels, going to do the same for the React. I hate React. I have been using it for years and I have never enjoyed it. I feel like everything made after the boomer technology just sucks. I am going to write html, css and JavaScript, and as intended, not fake JavaScript (React), not mangled css, etc. I noticed when setting out on this journey that vscode and I am assuming others, have little support for these things, which is hilarious. vscode can't even detect unclosed meta tags in html, yet can detect a million React syntax errors.

Winsaucerer 5y ago
> 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?
- stopcraeting 5y ago
  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.
  - Winsaucerer 5y ago
    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!).
    - Scarbutt 5y ago
      Check out https://www.hugsql.org/ https://www.hugsql.org/. Is a Clojure library but I'm sure I have seen Python and JS versions around.
9dev 5y ago
Old man yells at cloud
- stopcraeting 5y ago
  Yup that is me. I do what I like doing though, I don't yell at people or care what they do. I use lots of new things too like JSON web tokens, JSON SQL functions, vscode, new JavaScript/html/css features, etc.
mjbrusso 5y ago
Wouldn't it be better to put SQL statements in stored procedures rather than dynamically loaded text files?
- Winsaucerer 5y ago
  There's plenty of queries you may find yourself scattering throughout your application that you don't want to have in the database as a stored procedure, if for no other reason than it being overkill and extra work. If it's business logic in the query that might get re-used across independent applications (e.g., cancelling an order), then I would think a stored procedure is better. But if it's specific to that application (e.g., fetching title+description+publication date of the five most recent blog entries for a side panel), I wouldn't care to put that in the db as a stored procedure.
dmux 5y ago
As I've gotten older (about a decade's worth of professional experience at this point), I've started feeling the same way. It's so tempting to build up these abstractions and pipelines and introduce new tools but at the end of the day, what is it we're trying to achieve? It's often the case that the abstractions chosen were the wrong ones for the job and staying at a lower "layer" of abstraction results in more benefits in the longer term (i.e. lower layers are move at a slower pace). If a project's lifetime is longer than 2 hype cycles, it may be worth considering digging down a layer and doing things there. Whenever a subject like this comes up, I'm always reminded of Stewart Brand's "pace layering" [0]. [0] https://imgur.com/V5oL5WZ https://imgur.com/V5oL5WZ
_notathrowaway 5y ago
I don't understand the upsides of having your queries in separate sql files. May I ask you to elaborate a bit on this point? Thank you very much.

Winsaucerer 5y ago

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

stopcraeting 5y ago
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.
- Winsaucerer 5y ago
  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!).
  - Scarbutt 5y ago
    Check out https://www.hugsql.org/ https://www.hugsql.org/. Is a Clojure library but I'm sure I have seen Python and JS versions around.

9dev 5y ago

Old man yells at cloud

stopcraeting 5y ago
Yup that is me. I do what I like doing though, I don't yell at people or care what they do. I use lots of new things too like JSON web tokens, JSON SQL functions, vscode, new JavaScript/html/css features, etc.

mjbrusso 5y ago

Wouldn't it be better to put SQL statements in stored procedures rather than dynamically loaded text files?

Winsaucerer 5y ago
There's plenty of queries you may find yourself scattering throughout your application that you don't want to have in the database as a stored procedure, if for no other reason than it being overkill and extra work. If it's business logic in the query that might get re-used across independent applications (e.g., cancelling an order), then I would think a stored procedure is better. But if it's specific to that application (e.g., fetching title+description+publication date of the five most recent blog entries for a side panel), I wouldn't care to put that in the db as a stored procedure.

dmux 5y ago

As I've gotten older (about a decade's worth of professional experience at this point), I've started feeling the same way. It's so tempting to build up these abstractions and pipelines and introduce new tools but at the end of the day, what is it we're trying to achieve? It's often the case that the abstractions chosen were the wrong ones for the job and staying at a lower "layer" of abstraction results in more benefits in the longer term (i.e. lower layers are move at a slower pace). If a project's lifetime is longer than 2 hype cycles, it may be worth considering digging down a layer and doing things there. Whenever a subject like this comes up, I'm always reminded of Stewart Brand's "pace layering" [0]. [0] https://imgur.com/V5oL5WZ https://imgur.com/V5oL5WZ

_notathrowaway 5y ago

I don't understand the upsides of having your queries in separate sql files. May I ask you to elaborate a bit on this point? Thank you very much.