13 ms·

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

by jdc0589 12y ago

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.

qooleot 12y ago
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.
- emidln 12y ago
  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.
emidln 12y ago
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 https://github.com/krisajenkins/yesql) in Clojure, and a quick google shows that sqlt (https://github.com/eugeneware/sqlt https://github.com/eugeneware/sqlt) exists for the same purpose in javascript.
deleted 12y ago
[deleted]
ape4 12y ago
There are benefits to having the sql in with the js code. You can see how they integrate.
deleted 12y ago
[deleted]
deleted 12y ago
[deleted]

by jdc0589 12y ago

qooleot 12y ago

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.

emidln 12y ago
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.

emidln 12y ago

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 https://github.com/krisajenkins/yesql) in Clojure, and a quick google shows that sqlt (https://github.com/eugeneware/sqlt https://github.com/eugeneware/sqlt) exists for the same purpose in javascript.

deleted 12y ago

[deleted]

ape4 12y ago

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

deleted 12y ago

[deleted]

deleted 12y ago

[deleted]