Hacker News Reader

Comment by jdc0589 | Hacker News Reader

I don't get the point. I have always had a "sql" directory in my project with named `.sql` files for all my queries. Then just load them up with a `require.extensions["sql"]` override, or another mechanism. You should be using sql parameters anyway, formatting/interpolation shouldn't even be a consideration.

Plus, keeping queries in their own files makes the change history nicer.

View on HN

So much this.

There are even helper libraries to make keeping your SQL separate and executing it safely easy. I use yesql (https://github.com/krisajenkins/yesql) in Clojure, and a quick google shows that sqlt (https://github.com/eugeneware/sqlt) exists for the same purpose in javascript.

There are benefits to having the sql in with the js code. You can see how they integrate.

Hey you do you a middleware you use for this? I'd be interested in trying it out for cases where I have large, semi-static queries and want the entire file to be sql-language and not mixed with node code.

Also, how do you handle queries that are not pre-defined? I have a lot of screens where the user can select 'categories' (a collect of join-statements basically) and pick their own columns (including creating formulas so I can't just select *), sort, group-by clauses, and "pivot" (using CTEs or the crosstab feature) dynamically.

In clojure, I define any queries that are semi-complicated in their own .sql files with simple ? parameters (using yesql to load them and send them across parameterized). The really dynamic stuff (off an open-ended HTTP API for example) tends to get built out on top of a SQL abstraction layer (not quite an ORM, but close) called HoneySQL that turns data structures (easy to manipulate) into SQL. Doing a similar thing with Python using SQLalchemy (the other language I write DB code in sometimes) for the highly dynamic queries is reasonable. I don't write db queries in other languages to be familiar enough with their database libraries, but you should be able to accomplish something similar.

Reply on news.ycombinator.com