squiggle
v1.0.0
Published
Query builder for use with mysql & mysql2
Downloads
7
Readme
Squiggle query builder
Squiggle allows for named placeholders and optional sub-expressions. It also generates a signature for the query that can be used for caching purposes.
The "?name" syntax is pretty self-explanatory it's just named placeholders.The "?(" syntax is more of an odd bird and allows to to create a chunk of sql (a group) is used conditionally "?( AND a=?one AND b=?more )" will only be included if the "one" and "more" parameters are provided. And groups can also be named the using the syntax "?(:named ...)" which and then switched on or off using the groups parameter.
Usage
The Query
constructor takes two parameters: the query name and the query itself. The query name is used for generating a query signature.
Query.query(spec)
generates the finished SQL and a parameter array that can be used with the mysql
or mysql2
modules.
Query.id(spec)
generates a stable identifier that can be used with caching systems.
var lib = {
squiggle: require('./')
};
var q = new lib.squiggle.Query('test',
"SELECT * FROM TABLE foobar " +
"WHERE id=?id ?(AND public=?public )" +
"?(:named AND arbitrary=42 )" +
"LIMIT $offset, $limit");
var examples = [
{
params: {id:12},
values: {offset:0, limit:20}
},
{
params: {id:2},
values: {offset:0, limit:20},
groups: {named:true}
},
{
params: {id:5, public:true},
values: {offset:0, limit:10}
}
];
examples.forEach(function(spec, idx) {
console.log('var id%d = %j;', idx, q.id(spec));
console.dir(q.query(spec));
console.log('');
});
Yields:
var id0 = "test:id=12:offset=0&limit=20:"
{ sql: 'SELECT * FROM TABLE foobar WHERE id=? LIMIT 0, 20',
params: [ 12 ] }
var id1 = "test:id=2:offset=0&limit=20:named=true"
{ sql: 'SELECT * FROM TABLE foobar WHERE id=? AND arbitrary=42 LIMIT 0, 20',
params: [ 2 ] }
var id2 = "test:id=5&public=true:offset=0&limit=10:"
{ sql: 'SELECT * FROM TABLE foobar WHERE id=? AND public=? LIMIT 0, 10',
params: [ 5, 1 ] }