Plus, keeping queries in their own files makes the change history nicer.
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.
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.
That said, I kind of view them as an anti-pattern unless you treat your development of functions and stored procedures as rigorously as you handle all the rest of your development. That means using proper migration tools, using versioning, and especially rigorous testing.
Towards the testing end, we've found pgTAP to be extremely helpful: http://pgtap.org/documentation.html .
I'd love to hear other folks feedback about their practices here.
Postgre, MySQL and Oracle all have severely limited stored procedure capabilities compared to SQL Server. Postgre can't even return multiple result sets from a procedure or use transactions.
Some bits in MS-SQL are somewhat painful... powerful, but painful.. dealing with XML types is particularly nasty. I can't really compare that on PostgreSQL's side... What gets me with MS-SQL is that I can setup and run with very little need for digging into advanced configuration options. Replication is relatively simple, and easy to do (though expensive).
On the other side, having played with PostgreSQL + plv8, it's NICE ... if I could get a full node environment inside PostgreSQL (node modules as function libries), that would be amazing.
Most SQL libraries in other languages that have interpolation seem to offer a named param option where parameters can be passed as a hash, which gets around the indexed-parameter issue while still properly escaping values.
That said, I'm thrilled that ES6 is finally getting string interpolation.
http://ivc.com/blog/better-sql-strings-in-io-js-nodejs-part-...
That's pretty awesome - I am using a SQL generation library (knex.js), which I'm admittedly happy with, but primarily because I don't like dealing with $1 $2 $3 in my own code.
Now if you had a way to programmatically expand an array (for an IN ($1 $2 $3) type of query where the array length isn't known ahead of time), I really wouldn't need to use knex.
the sql`` template is actually creating a prepared statement.
var query = "select id, name from table where id = #{id:int}"A similar library which implements Postgres format() for Node: https://github.com/datalanche/node-pg-format
Disclaimer: I wrote it.
I'd thought about:
"select * from %I${my_table} where ..."
I think I'd probably feel more comfortable having a whitelist of allowed words rather than just a blacklist, as there are probably ways around it. Either new syntaxes in future Postgres versions, or mid-sql comments. Take this for example:
Edit: sorry the * before the word "comment" below is being escaped by hacker news. There is probably some hackernews-injection to get around this:}
select/comment/ * from foo;
if "select/comment/" was the identifier, that's valid sql, but:
function isReserved(value) { if (reservedMap[value.toUpperCase()]) {
does a comparison on an exact match, not contains.
https://github.com/datalanche/node-pg-format/blob/master/lib...
As for your specific example, I am not sure I understand the issue. The SQL keyword list (reserved list) is only used to determine if the identifier needs to be quoted. So if your identifier is 'select', the library will quote that because you can use SQL keywords as identifiers as long as they are quoted. Your specific example produces this:
JS: format('%I * from foo;', 'select/comment/')
SQL: "select/comment/" * from foo;
Alternatively I could add an option to quote all identifiers regardless. I might do that. :)
EDIT:
I didn't see your comment edit before responding. I would appreciate it you created a Github issue where you can get the SQL formatting right. If the library is doing something wrong, I want to fix it.
Not saying that this isn't nice, I've always appreciated long string support that in Perl/Python/Ruby.
sql` insert into `example table`...
var foo = "string "with quotes" done."
You can escape the inner backquotes \` but then its just a backquote character in the string. It doesn't do anything special like call the template string again.
Still, being able to `yield` queries was the motivation to use generators + harmony for me.
Sometimes, and especially on an intranet/admin tool thats not meant for widespread consumption, I start with a sql query (or an analyst hands me one) and it just needs to be a report/chart on a web page and isn't mean to be part of a product's API and flexible. Converting it to knex, and especially if the analyst changes their mind and gives me a brand new sql query, isn't really worth the effort (opportunity cost of working on that code vs something more fruitful).
The specific gripe of "What if you want a new parameter near the beginning?" is not an new problem: it has been addressed in SQL clients by implementing named parameters. There is not a universal standard sadly but competent solutions exist for node and postgres (cf. https://github.com/bwestergard/node-postgres-named).
var multiline = (function() {/*
I am
a multi-
line string.
*/}).toString().slice(16, -4);
Couple this with a proper parser for the syntax and a template library and it has served me well enough.