Squel.js
- lightweight Javascript library for building SQL query strings.
- usable with node.js and in the browser.
- well tested.
npm install squel
Once done you can use require to access the API:
var squel = require("squel");
bower install squel
Include the following within your HTML body section, near the bottom:
<script type="text/javascript" src="/your/path/to/squel.min.js"></script>
Once loaded the squel API will be available in the global object:
log( squel.VERSION ); /* version string */
NOTE: It is recommended that you do NOT create queries browser-side to run on the server as this massively increases your exposure to SQL Injection attacks.
Squel helps you quickly and easily build SQL query strings through an object oriented API.
There are two main benefits to using Squel to build your queries:
Ok, now you're convinced! Before you get stuck in here are a few things to be aware of...
Squel uses method chaining to make things easy. For example, instead of writing:
var s = squel.select();
s.from("student");
log( s.toString() ); /* SELECT * FROM student */
Hint: You can run the example above by clicking it!
You can simply write:
log( squel.select().from("students").toString() ); /* SELECT * FROM students */
What's even better, the toString() method gets called automatically by the Javascript interpreter
whenever a string representation of an object is needed. So we can actually simplify the previous statement to:
log( '' + squel.select().from("students") ); /* SELECT * FROM students */
Note: All of Squel's builders provide the toString() method. It is this
method which constructs the final query string.
Squel does not check to see if your final query is semantically correct (i.e. that it makes sense). Your database engine already does a good enough job of that. On the plus side Squel allows you to use proprietary database functions and other complex constructs:
log(
squel.select({ separator: "\n" })
.from("students")
.field("name")
.field("MIN(test_score)")
.field("MAX(test_score)")
.field("GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')")
.group("name")
.toString()
);
/*
SELECT
name,
MIN(test_score),
MAX(test_score),
GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')
FROM
students
GROUP BY
name
*/
To get an instance of the SELECT query builder:
var q = squel.select();
The name of a table to fetch data from is provided:
log(
squel.select()
.from("students")
.toString()
);
/* SELECT * FROM students */
Squel usually outputs a single line string, but you can use a different separator to change this look:
log(
squel.select({ separator: "\n" })
.from("students")
.toString()
);
/*
SELECT
*
FROM students
*/
You can select data from multiple tables and provide aliases where needed:
log(
squel.select()
.from("students")
.from("lecturers", "l")
.from("admins")
.toString()
);
/* SELECT * FROM students, lecturers `l`, admins */
Instead of explicitly naming tables you can use SQL sub-queries as such:
log(
squel.select()
.from( squel.select().from('students'), 's' )
.field('s.id')
.toString()
);
/* SELECT s.id FROM (SELECT * FROM students) `s` */
You can specify field names on their own or by the table they belong to:
log(
squel.select()
.from("students")
.field("id")
.field("students.name")
.toString()
);
/* SELECT id, students.name FROM students */
Field names can be given aliases in order to customize the column names in the query results :
log(
squel.select()
.from("students")
.field("id", "Id")
.field("students.name", "Student Name")
.toString()
);
/* SELECT id AS "Id", students.name AS "Student Name" FROM students */
Database functions can be used in place of field names for more complex results:
log(
squel.select()
.from("students", "s")
.field("s.id")
.field("s.test_score", "Test score")
.field("DATE_FORMAT(s.date_taken, '%M %Y')", "Taken on")
.toString()
);
/*
SELECT
s.id,
s.test_score AS "Test score"
DATE_FORMAT(s.date_taken, '%M %Y') AS "Taken on"
FROM
students `s`
*/
You can even use a sub-query as a field specifier:
log(
squel.select()
.from("students", "s")
.field(squel.select().field("MAX(score)").from("scores"), 'score')
.toString()
);
/*
SELECT
(SELECT MAX(score) FROM scores) AS "score"
FROM
students `s`
*/
If you wish to obtain DISTINCT results you can do so:
log(
squel.select()
.from("students")
.field("id")
.distinct()
.toString()
);
/* SELECT DISTINCT id FROM students */
Squel supports INNER, OUTER, LEFT and RIGHT joins. To do an
INNER join:
log(
squel.select()
.from("students")
.join("teachers")
.toString()
);
/* SELECT * FROM students INNER JOIN teachers */
You can specify table aliases:
log(
squel.select()
.from("students")
.join("teachers", "t")
.outer_join("expelled")
.toString()
);
/* SELECT * FROM students
INNER JOIN teachers `t`
OUTER JOIN expelled
*/
You can specify sub-queries:
log(
squel.select()
.from("students")
.join(
squel.select().field('score').from('results'),
't'
)
.outer_join("expelled")
.toString()
);
/* SELECT * FROM students
INNER JOIN (SELECT score FROM results) `t`
OUTER JOIN expelled
*/
And you can specify JOIN-ing conditions:
log(
squel.select()
.field("students.id")
.from("students")
.left_join("teachers", null, "students.id = teachers.student_id")
.right_join("jailed", "j", "j.student_id = students.id")
.toString()
);
/* SELECT students.id FROM students
LEFT JOIN teachers ON (students.id = teachers.student_id)
RIGHT JOIN jailed `j` ON (j.student_id = students.id)
*/
For an optional parameter such as an alias name, passing null is the same as omitting it.
You can also specify sub-queries in joins:
log(
squel.select()
.from('marks', 'm')
.join( squel.select().from('students'), 's', 's.id = m.id' )
.toString()
);
/* SELECT * FROM marks `m` INNER JOIN (SELECT * FROM students) `s` ON (s.id = m.id) */
You can add WHERE clause filtering to your query very easily:
log(
squel.select()
.field("id")
.from("students")
.where("name = 'Thomas'")
.toString()
);
/* SELECT id FROM students WHERE (name = 'Thomas') */
Parameters substitution is supported:
log(
squel.select()
.field("id")
.from("students")
.where("score = ?", squel.select().field('MAX(score)').from('scores'))
.toString()
);
/* SELECT id FROM students WHERE (score = (SELECT MAX(score) FROM scores)) */
For more information on using parameterized queries see the parameters documentation section.
Multiple filters are automatically combined using AND:
log(
squel.select()
.field("id")
.from("students")
.where("name = 'Thomas'")
.where("age > 18")
.toString()
);
/* SELECT id FROM students WHERE (name = 'Thomas') AND (age > 18) */
Also filter using OR or any other operator you like:
log(
squel.select()
.field("id")
.from("students")
.where("name = 'Thomas' OR age > 18")
.where("id BETWEEN 200 and 300")
.toString()
);
/* SELECT id FROM students WHERE (name = 'Thomas' OR age > 18) AND (id BETWEEN 200 and 300) */
You can also pass in Expressions:
log(
squel.select()
.field("id")
.from("students")
.where(
squel.expr().and("name = 'Thomas'").or("age > 18")
)
.toString()
);
/* SELECT id FROM students WHERE (name = 'Thomas' OR age > 18) */
log(
squel.select()
.field("id")
.from("students")
.order("id")
.order("name")
.toString()
);
/* SELECT id FROM students ORDER BY id ASC, name ASC */
Use the second argument of order() to change the direction of a sort:
log(
squel.select()
.field("id")
.from("students")
.order("id")
.order("name", false) /* using TRUE instead would be the same as omitting it. */
.toString()
);
/* SELECT id FROM students ORDER BY id ASC, name DESC */
Additional arguments count as parameter values, e.g. for custom sort functions::
log(
squel.select()
.field("id")
.from("students")
.order("DIST(?, ?)", true, 1, 2)
.toString()
);
/* SELECT id FROM students ORDER BY DIST(1, 2) ASC */
log(
squel.select()
.field("id")
.from("students")
.group("id")
.toString()
);
/* SELECT id FROM students GR0UP BY id */
Grouping on multiple fields is supported:
log(
squel.select()
.field("id")
.from("students")
.group("id")
.group("students.name")
.toString()
);
/* SELECT id FROM students GR0UP BY id, students.name */
log(
squel.select()
.field("id")
.from("students")
.group("id")
.having("a = ?", 2)
.toString()
);
/* SELECT id FROM students GR0UP BY id HAVING (a = 2) */
To LIMIT the number of results:
log(
squel.select()
.from("students")
.limit(10)
.toString()
);
/* SELECT * FROM students LIMIT 10 */
To remove the previously set limit:
log(
squel.select()
.from("students")
.limit(10)
.limit(null)
.toString()
);
/* SELECT * FROM students */
To specify the OFFSET of the first row to return:
log(
squel.select()
.from("students")
.offset(102)
.toString()
);
/* SELECT * FROM students OFFSET 102 */
Same as for limit(), you can remove a previously set offset:
log(
squel.select()
.from("students")
.offset(1)
.offset(0)
.toString()
);
/* SELECT * FROM students */
UNION query:
log(
squel.select()
.from("students")
.union(
squel.select()
.from('scores')
)
.union(
squel.select()
.from('batches')
)
.toString()
);
/* SELECT * FROM students UNION (SELECT * FROM scores) UNION (SELECT * FROM batches) */
log(
squel.select()
.function('1')
.toString()
);
/* SELECT 1 */
And this applies to database functions too:
log(
squel.select()
.function('MAX(?, ?)', 45, 87)
.toString()
);
/* SELECT MAX(45, 87) */
To get an instance of the UPDATE query builder:
var q = squel.update();
You need to supply at least one table name and one field to update:
log(
squel.update()
.table("students")
.set("name", "Thomas")
.toString()
);
/* UPDATE students SET name = "Thomas" */
Multiple table names can be supplied along with aliases:
log(
squel.update()
.table("students")
.table("teachers", "t")
.set("t.name", "Fred")
.toString()
);
/* UPDATE students, teachers `t` SET t.name = "Fred" */
Not all field values within a query need to be surrounded by quotes. Squel figures out how to represent each field value based on its runtime type:
log(
squel.update()
.table("students")
.set("name", "Fred")
.set("age", 29)
.set("score", 1.2)
.set("graduate", false)
.set("level", squel.select().field('MAX(level)').from('levels'))
.toString()
);
/* UPDATE students SET name = "Fred", age = 29, score = 1.2, graduate = FALSE, level = (SELECT MAX(level) FROM levels) */
You can turn off automatic quoting on a per-field basis:
log(
squel.update()
.table("students")
.set("time", "GETDATE()", {
dontQuote: true
})
.set("city", "London")
.toString()
);
/* UPDATE students SET time = GETDATE(), city = "London" */
You can even use NULL values:
log(
squel.update()
.table("students")
.set("name", "Fred")
.set("age", 29)
.set("nickname", null)
.toString()
);
/* UPDATE students SET name = "Fred", age = 29, nickname = NULL */
Hint: Squel can also be configured to handle Object instances by registering custom value types.
If the second parameter to set() is not provided then the field name is assumed to be an operation:
log(
squel.update()
.table("students")
.set("age = age + 1")
.toString()
);
/* UPDATE students SET age = age + 1 */
You can also set multiple fields in a single call:
log(
squel.update()
.table("students")
.setFields({ age: 23, name: 'Fred' })
.toString()
);
/* UPDATE students SET age = 23, name = 'Fred' */
Parameter substitution for parameterized queries is also supported, see the section on Parameters.
The where(), order() and limit() methods are supported the same
way they are by the SELECT builder:
log(
squel.update()
.table("students")
.set("name", "Fred")
.set("age", 29)
.where("id > 5")
.where("id < 102")
.order("id", false)
.limit(5)
.toString()
);
/* UPDATE students SET name = "Fred", age = 29 WHERE (id > 5) AND (id < 102) ORDER BY id DESC LIMIT 5 */
To get an instance of the DELETE query builder:
var q = squel.delete();
You can also use the alias remove():
var q = squel.remove();
You need to supply at least the table name:
log(
squel.delete()
.from("students")
.toString()
);
/* DELETE FROM students */
The join() and related methods are supported the same way
they are by the SELECT builder:
log(
squel.delete()
.from("students", "s")
.outer_join("marks", "m", "m.student_id = s.id")
.toString()
);
/* DELETE FROM students `s` OUTER JOIN marks `m` ON (m.student_id = s.id) */
The where(), order() and limit() methods are supported the same way
they are by the SELECT builder:
log(
squel.delete()
.from("students")
.where("id > 5")
.where("id < 102")
.order("id", false)
.limit(5)
.toString()
);
/* DELETE FROM students WHERE (id > 5) AND (id < 102) ORDER BY id DESC LIMIT 5 */
To get an instance of the INSERT query builder:
var q = squel.insert();
At least the table name and one field must be supplied:
log(
squel.insert()
.into("students")
.set("name", "Thomas")
.toString()
);
/* INSERT INTO students (name) VALUES ("Thomas") */
Squel out how to treat each field value based on its runtime type:
log(
squel.insert()
.into("students")
.set("name", "Thomas")
.set("age", 29)
.set("score", 90.2)
.set("graduate", true)
.set("nickname", null)
.set("level", squel.select().field('MAX(level)').from('levels'))
.toString()
);
/* INSERT INTO students (name, age, score, graduate, nickname, level)
VALUES ('Thomas', 29, 90.2, TRUE, NULL, (SELECT MAX(level) FROM levels)) */
You can turn off automatic quoting on a per-field basis:
log(
squel.insert()
.into("students")
.set("time", "GETDATE()", {
dontQuote: true
})
.set("city", "London")
.toString()
);
/* INSERT INTO students (time, city) VALUES(GETDATE(), "London") */
You can set multiple fields in a single call:
log(
squel.insert()
.into("students")
.setFields({ name: "Thomas", age: 29 })
.toString()
);
/* INSERT INTO students (name, age) VALUES ('Thomas', 29) */
You can even do a multi-row insert:
log(
squel.insert()
.into("students")
.setFieldsRows([
{ name: "Thomas", age: 29 },
{ name: "Jane", age: 31 }
])
.toString()
);
/* INSERT INTO students (name, age) VALUES ('Thomas', 29), ('Jane', 31) */
Note: When doing a multi-row insertion every row specified in the parameter to setFieldsRows should
have the same field names specified. Squel will throw an error if this is not the case.
Insert from a selection result:
log(
squel.insert()
.into("students")
.fromQuery(
['username'],
squel.select().field('name').from('candidates')
)
.toString()
);
/* INSERT INTO students (username) (SELECT name FROM candidates) */
Squel makes it very easy to build and use parameterized queries. Every query builder provides a
toParam() method which gives you a final query string separate to any parameters values previously passed in:
log(
squel.select()
.from("students")
.where("a = ? AND b = ?", "test", true)
.order("CALC(?, ?)", true, 1.2, false)
.limit(10)
.offset(3)
.toParam()
);
/*
{
text: SELECT * FROM students WHERE (a = ? AND b = ?) ORDER BY CALC(?, ?) ASC LIMIT ? OFFSET ?,
values: [ 'test', true, 1.2, false, 10, 3 ]
}
*/
Using sub-queries as parameter values:
log(
squel.select()
.from("students")
.where("a = ? AND b IN ?", "test", squel.select().field('score').from('results').where('c IN ?', [5, 6, 7]))
.toParam()
);
/*
{
text: SELECT * FROM students WHERE (a = ? AND b IN (SELECT score FROM results WHERE c IN (?, ?, ?)))),
values: [ 'test', 5, 6, 7 ]
}
*/
A more complex example involving sub-queries and unions:
log(
squel.select()
.from("students")
.where("a IN ?", squel.select().field('score').from('results').where('c = ?', 5))
.union(
squel.select().field('age').from('states').where('person = ?',
squel.select().field('id').from('students').where('name = ?', 'John').limit(1)
)
)
.toParam()
);
/*
{
text: SELECT * FROM students WHERE (a IN (SELECT score FROM results WHERE (c = ?))) UNION (SELECT age FROM states WHERE (person = (SELECT id FROM students WHERE (name = ?) LIMIT 1))),
values: [ 5, 'John' ]
}
*/
As you can see the parameter values are not specially formatted as they normally are when toString()
is called. Also note that the parameter placeholders are non-numbered by default. To enable numbering use the
numberedParameters option:
log(
squel.insert({ numberedParameters: true })
.into("students")
.set("a", "test")
.set("b", 1)
.set("c", null)
.toParam()
);
/*
{
text: INSERT INTO students (a, b, c) VALUES ($1, $2, $3)
values: [ 'test', 1, null ]
}
*/
Note: For the Postgres flavour numbered parameters are enabled by default.
You can change the start number:
log(
squel.insert()
.into("students")
.set("a", "test")
.set("b", 1)
.set("c", null)
.toParam({ numberedParameters: true, numberedParametersStartAt: 3 })
);
/*
{
text: INSERT INTO students (a, b, c) VALUES ($3, $4, $5)
values: [ 'test', 1, null ]
}
*/
The values array returned is always flat, even for multi-row inserts:
log(
squel.insert()
.into("students")
.setFieldsRows([
{ name: "Thomas", age: 29 },
{ name: "Jane", age: 31 }
])
.toParam()
);
/*
{
text: INSERT INTO students (name, age) VALUES (?, ?), (?, ?)
values: [ 'Thomas', 29, 'Jane', 31 ]
}
*/
Array values can be used to create IN clauses:
log(
squel.select()
.field("id")
.from("students")
.where("age IN ?", [18, 19, 20])
.toParam()
);
/*
{
text: SELECT id FROM students WHERE (age IN (?, ?, ?)),
values: [ 18, 19, 20 ]
}
*/
In general it is better to use toParam() rather than toString() when constructing the final query
as it gives you the opportunity to use your database engine methods to safely escape and interpolate your parameters.
Sometimes you may wish to use SQL functions as parameter values, e.g.:
log(
squel.update()
.table('students')
.set('modified', 'NOW()')
.toString()
);
/* UPDATE students SET modified = 'NOW()' */
As you can see Squel automatically adds quotes since it treats the value as a string. To workaround this you can use the
.str() method:
log(
squel.update()
.table('students')
.set('modified', squel.str('NOW()'))
.toString()
);
/* UPDATE students SET modified = (NOW()) */
And if you don't want Squel to auto-add paranthesis () you can use
.rstr() instead:
log(
squel.update()
.table('students')
.set('modified', squel.rstr('NOW()'))
.toString()
);
/* UPDATE students SET modified = NOW() */
You can pass parameters to the functions too:
log(
squel.select()
.from("students")
.where("age IN ?", squel.str('RANGE(?, ?)', 1, 1.2))
.toParam()
);
/*
{
text: SELECT * FROM students WHERE (age IN (RANGE(?, ?))),
values: [ 1, 1.2 ]
}
*/
By default Squel does not add quotes around table names and field names. But you can tell it to do so using the
autoQuoteTableNames and autoQuoteFieldNames options:
log(
squel.select({ autoQuoteTableNames: true, autoQuoteFieldNames: true })
.from("students", "s")
.field("s.name", "Student name")
.toString()
);
/* SELECT `s`.`name` AS "Student name" FROM `students` `s` */
Notice how squel was smart enough to detect the period separator within s.name when quoting.
You can turn off this behaviour if you wish:
log(
squel.select({ autoQuoteTableNames: true, autoQuoteFieldNames: true })
.from("students", "s")
.field("s.name", "Student name", { ignorePeriodsForFieldNameQuotes: true })
.toString()
);
/* SELECT `s.name` AS "Student name" FROM `students` `s` */
You can also customize the quote character using the nameQuoteCharacter option:
log(
squel.select({ autoQuoteTableNames: true, autoQuoteFieldNames: true, nameQuoteCharacter: '|' })
.from("students")
.field("name", "Student name")
.toString()
);
/* SELECT |name| AS "Student name" FROM |students| */
As you can see above, Squel does however automatically add quotes to alias names. These too can be customized:
log(
squel.select({ tableAliasQuoteCharacter: '|', fieldAliasQuoteCharacter: '~' })
.from("students", "s")
.field("name", "Student name")
.toString()
);
/* SELECT name AS ~Student name~ FROM students |s| */
And you can turn off automatic alias quoting if you so desire:
log(
squel.select({ autoQuoteAliasNames: false })
.from("students", "s")
.field("name", "Student_name")
.toString()
);
/* SELECT name AS Student_name FROM students s */
Squel lets you use use complex expressions strings in WHERE and ON clauses. For
example:
log(
squel.select()
.from("students")
.where("(id < 500 AND (id > 100 OR name <> 'Thomas') AND "
+ "(age BETWEEN 20 AND 25 OR (name <> RANDOMNAME(?)))) OR (nickname = 'Hardy')", 10)
.toString()
);
/* SELECT * FROM students WHERE
((id < 500 AND (id > 100 OR name <> 'Thomas') AND (age BETWEEN 20 AND 25 OR (name <> RANDOMNAME(10))))
OR (nickname = 'Hardy')) */
But having to change an expression string like that later on will be error-prone. It would be nice if you could build it the same way you build queries. Luckily Squel provides an expression builder just for this purpose. To get an instance of the builder:
var q = squel.expr();
To build the above expression string these are the calls you would make:
log(
squel.expr()
.and("id < ?", 500)
.and(
squel.expr()
.or("id > ?", 100)
.or("name <> ?", 'Thomas')
)
.and(
squel.expr()
.or("age BETWEEN ? AND ?", 20, 25)
.or("name <> ?", squel.str('RANDOMNAME(?)', 10))
)
.or("nickname = ?", 'Hardy')
.toString()
);
/* id < 500 AND (id > 100 OR name <> 'Thomas') AND (age BETWEEN 20 AND 25 OR name <> (RANDOMNAME(10)))
OR nickname = 'Hardy'
*/
Straight away the benefit of using the expression builder can be seen as it has simplified the
expression (AND has precendence over OR). Not to mention that it's easier to
figure out the meaning of the expression looking at this code than from the big string we were
using earlier.
As a convenience expression builder instances can be used in calls to where() and
join() when using the query builders:
log(
squel.select()
.field("s.id")
.from("students", "s")
.where(
squel.expr()
.and("s.name <> 'Fred'")
.and(
squel.expr()
.or("s.id = 5")
.or("s.id = 6")
)
)
.join("teachers", "t",
squel.expr()
.and("s.id = t.sid")
.and("t.name = 'Frances'")
)
.toString()
);
/*
SELECT s.id FROM students `s`
INNER JOIN teachers `t` ON (s.id = t.sid AND t.name = 'Frances')
WHERE (s.name <> 'Fred' AND (s.id = 5 OR s.id = 6))
*/
log(
squel.update({
stringFormatter: function(str) {
return "u'" + str + "'";
}
})
.table("students")
.set("name", "Jack")
.toString()
);
/* UPDATE students SET name = u'Jack' */
Sometimes you might want to override how Squel formats non-string values, e.g. Date instances. You could do the following:
var myDate = new Date(2012, 4, 22),
myDate2 = new Date(2013, 5, 30);
log(
squel.update()
.table("students")
.set("start_date", myDate.getFullYear() + '-' + (myDate.getMonth()+1) + '-' + myDate.getDate())
.set("end_date", myDate2.getFullYear() + '-' + (myDate2.getMonth()+1) + '-' + myDate2.getDate())
.toString()
);
/* UPDATE students SET start_date = '2012-5-22', end_date = '2013-6-30' */
Functional but no so elegant. A better approach is to register the Date object type
with Squel as a _custom value type_. We will need to supply a handler function which will get called whenever an
object of type Date is encountered:
var myDate = new Date(2012, 4, 22),
myDate2 = new Date(2013, 5, 30);
/* Tell Squel how to handle Date objects */
squel.registerValueHandler(Date, function(date) {
return '"' + date.getFullYear() + '-' + (date.getMonth()+1) + '-' + date.getDate() + '"';
});
log(
squel.update()
.table("students")
.set("start_date", myDate)
.set("end_date", myDate2)
.toString()
);
/* UPDATE students SET start_date = ("2012-5-22"), end_date = ("2013-6-30") */
We can register any type of object as a custom value type, not just Date. Squel checks to see if
the runtime type of a passed-in value matches one of the registered value types - meaning that sub-types are automatically
supported.
/* OOP Inheritance mechanism (substitute your own favourite library for this!) */
Function.prototype.inheritsFrom = function( parentClassOrObject ) {
this.prototype = new parentClassOrObject;
this.prototype.constructor = this;
this.prototype.parent = parentClassOrObject.prototype;
};
/* Base type */
var ClassA = function() { this.a = 1; };
/* Sub-type */
var ClassB = function() { this.a = 2; };
ClassB.inheritsFrom(ClassA);
/* Register base type */
squel.registerValueHandler(ClassA, function(obj) {
return obj.a;
});
log(
squel.update()
.table("students")
.set("value", new ClassB())
.toString()
);
/* UPDATE students SET value = 2 */
We can even register handlers for primitive types such as strings and booleans. Simply pass in the primitive type name as a string:
squel.registerValueHandler('boolean', function(v) {
return v ? 'YES': 'NO';
});
log(
squel.update()
.table("students")
.set("value", true)
.toString()
);
/* UPDATE students SET value = (YES) */
Notice how the string value returned by the handler automatically gets nested in the final query string. To prevent this modify your handler as such:
squel.registerValueHandler('boolean', function(v) {
return {
value: v ? 'YES': 'NO',
rawNesting: true
}
});
log(
squel.update()
.table("students")
.set("value", true)
.toString()
);
/* UPDATE students SET value = YES */
We can even override the registered handler for a value type on a per-instance basis.
/* Global handler */
squel.registerValueHandler(Date, function(date) {
return date.getFullYear();
});
log(
squel.update()
.registerValueHandler(Date, function(date) {
return '"[' + date.getFullYear() + ']"';
})
.table('students')
.set('value', new Date(2013,5,1))
.toString()
);
/* UPDATE students SET value = ("[2013]") */
Custom value types are processed correctly for parameterised queries:
var myDate = new Date(2012, 4, 22),
myDate2 = new Date(2013, 5, 30);
/* Tell Squel how to handle Date objects */
squel.registerValueHandler(Date, function(date) {
return date.getFullYear() + '-' + (date.getMonth()+1) + '-' + date.getDate();
});
log(
squel.select()
.from("students")
.where("start_date >= ?", myDate)
.where("end_date <= ?", myDate2)
.toParam()
);
/*
{
text: SELECT * FROM students WHERE (start_date >= ?) AND (end_date <= ?),
values: [ '2012-5-22', '2013-6-30' ]
}
*/
You can customize Squel's query building mechanism quite easily. For instance, if you wanted to add
OFFSET clauses to UPDATE queries you could do it as such:
/* We create a convenience method to make it easy to instantiate our customized UPDATE builder */
squel.myupdate = function(options) {
return squel.update(options, [
new squel.cls.StringBlock(options, 'UPDATE'),
new squel.cls.UpdateTableBlock(options),
new squel.cls.SetFieldBlock(options),
new squel.cls.WhereBlock(options),
new squel.cls.OrderByBlock(options),
new squel.cls.OffsetBlock(options),
new squel.cls.LimitBlock(options)
]);
};
log(
squel.myupdate()
.table('students')
.set('status', 'active')
.limit(10)
.offset(2)
.toString()
);
/* UPDATE students SET status = 'active' OFFSET 2 LIMIT 10 */
Hopefully you can see that it is possible to totally override the built-in query building logic. Also notice
that each building block within a query can have a different set of options applied to it.
Check out core.js to see
the available building blocks.
You can also build other types of queries and query clauses not already included in Squel. Let's say you wanted to build a query structured as follows:
CREATE TABLE {tableName} ({fieldName} {fieldType}, ...);:
/*
NOTE: All methods prefixed with '_' are internal and not exposed via the
query builder.
*/
class CreateTableBlock extends squel.cls.Block {
/** The method exposed by the query builder */
table (name) {
this._name = name;
}
/** The method which generates the output */
_toParamString (options) {
return {
text: this._name,
values: [], /* values for paramterized queries */
};
}
}
class CreateFieldBlock extends squel.cls.Block {
constructor (options) {
super(options);
this._fields = [];
}
/** The method exposed by the query builder */
field (name, type) {
this._fields.push({
name: name, type: type
});
}
/** The method which generates the output */
_toParamString (options) {
let str = this._fields.map((f) => {
return `${f.name} ${f.type.toUpperCase()}`;
}).join(', ');
return {
text: `(${str})`,
values: [], /* values for paramterized queries */
};
}
}
class CreateTableQuery extends squel.cls.QueryBuilder {
constructor (options, blocks) {
super(options, blocks || [
new squel.cls.StringBlock(options, 'CREATE TABLE'),
new CreateTableBlock(options),
new CreateFieldBlock(options),
]);
}
}
/** Convenience method */
squel.create = function(options) {
return new CreateTableQuery(options);
};
/* Try it out! */
log(
squel.create()
.table("pet")
.field("name", "varchar(20)")
.field("owner", "varchar(20)")
.field("species", "varchar(20)")
.field("sex", "char(1)")
.toString()
);
/*
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1))
*/
var _createClass = function () { function defineProperties(target, props) { for (var i = 0; i < props.length; i++) { var descriptor = props[i]; descriptor.enumerable = descriptor.enumerable || false; descriptor.configurable = true; if ("value" in descriptor) descriptor.writable = true; Object.defineProperty(target, descriptor.key, descriptor); } } return function (Constructor, protoProps, staticProps) { if (protoProps) defineProperties(Constructor.prototype, protoProps); if (staticProps) defineProperties(Constructor, staticProps); return Constructor; }; }();
function _classCallCheck(instance, Constructor) { if (!(instance instanceof Constructor)) { throw new TypeError("Cannot call a class as a function"); } }
function _possibleConstructorReturn(self, call) { if (!self) { throw new ReferenceError("this hasn't been initialised - super() hasn't been called"); } return call && (typeof call === "object" || typeof call === "function") ? call : self; }
function _inherits(subClass, superClass) { if (typeof superClass !== "function" && superClass !== null) { throw new TypeError("Super expression must either be null or a function, not " + typeof superClass); } subClass.prototype = Object.create(superClass && superClass.prototype, { constructor: { value: subClass, enumerable: false, writable: true, configurable: true } }); if (superClass) Object.setPrototypeOf ? Object.setPrototypeOf(subClass, superClass) : subClass.__proto__ = superClass; }
/*
NOTE: All methods prefixed with '_' are internal and not exposed via the
query builder.
*/
var CreateTableBlock = function (_squel$cls$Block) {
_inherits(CreateTableBlock, _squel$cls$Block);
function CreateTableBlock() {
_classCallCheck(this, CreateTableBlock);
return _possibleConstructorReturn(this, Object.getPrototypeOf(CreateTableBlock).apply(this, arguments));
}
_createClass(CreateTableBlock, [{
key: 'table',
/** The method exposed by the query builder */
value: function table(name) {
this._name = name;
}
/** The method which generates the output */
}, {
key: '_toParamString',
value: function _toParamString(options) {
return {
text: this._name,
values: [] };
}
}]);
return CreateTableBlock;
}(squel.cls.Block);
/* values for paramterized queries */
var CreateFieldBlock = function (_squel$cls$Block2) {
_inherits(CreateFieldBlock, _squel$cls$Block2);
function CreateFieldBlock(options) {
_classCallCheck(this, CreateFieldBlock);
var _this2 = _possibleConstructorReturn(this, Object.getPrototypeOf(CreateFieldBlock).call(this, options));
_this2._fields = [];
return _this2;
}
/** The method exposed by the query builder */
_createClass(CreateFieldBlock, [{
key: 'field',
value: function field(name, type) {
this._fields.push({
name: name, type: type
});
}
/** The method which generates the output */
}, {
key: '_toParamString',
value: function _toParamString(options) {
var str = this._fields.map(function (f) {
return f.name + ' ' + f.type.toUpperCase();
}).join(', ');
return {
text: '(' + str + ')',
values: [] };
}
}]);
return CreateFieldBlock;
}(squel.cls.Block);
/* values for paramterized queries */
var CreateTableQuery = function (_squel$cls$QueryBuild) {
_inherits(CreateTableQuery, _squel$cls$QueryBuild);
function CreateTableQuery(options, blocks) {
_classCallCheck(this, CreateTableQuery);
return _possibleConstructorReturn(this, Object.getPrototypeOf(CreateTableQuery).call(this, options, blocks || [new squel.cls.StringBlock(options, 'CREATE TABLE'), new CreateTableBlock(options), new CreateFieldBlock(options)]));
}
return CreateTableQuery;
}(squel.cls.QueryBuilder);
/** Convenience method */
squel.create = function (options) {
return new CreateTableQuery(options);
};
/* Try it out! */
log(squel.create().table("pet").field("name", "varchar(20)").field("owner", "varchar(20)").field("species", "varchar(20)").field("sex", "char(1)").toString());
/*
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1))
*/
You can create a copy of a query builder instance using its clone() method. For example:
var select1 = squel.select().from('students');
var select2 = select1.clone().field('id').where('name = "david"');
log( select1.toString() );
/* SELECT * FROM students */
log( select2.toString() );
/* SELECT id FROM students WHERE (name = "david") */
Here is an example using an INSERT builder:
var ins = squel.insert().into('students').set('name', 'David');
var insMathew = ins.clone().set('name', 'Mathew');
var insMark = insMathew.clone().set('name', 'Mark');
log( insMathew.toString() );
/* INSERT INTO students (name) VALUES('Mathew') */
log( insMark.toString() );
/* INSERT INTO students (name) VALUES('Mark') */
Although you can use Squel's query customization mechanism to add support for your desired commands it would be nice if Squel supported non-standard SQL commands for common database engines out of the box.
Well it actually does. Squel supports of the concept of SQL flavours, whereby a flavour is a non-standard version of SQL which can be loaded and used at runtime. At the moment Squel provides support for the Postgres, MSSQL/Transact-SQL and MySQL flavours of SQL.
Note: The API docs have more details on the specific features supported by each flavour.postgres flavor uses numbered query parameters by default. It also provides the RETURNING clause:
squelPostgres = squel.useFlavour('postgres');
log(
squelPostgres.insert()
.into('table')
.set('field', 5)
.returning('*')
.toParam()
);
/*
{
text: 'INSERT INTO table (field) VALUES ($1) RETURNING *'
values: [5]
}
*/
Queries containing Common Table Expressions (CTEs) are also supported using the WITH clause:
squelPostgres = squel.useFlavour('postgres');
log(
squelPostgres.insert()
.into('table')
.set('field', 5)
.with('someAlias', squelPostgres.insert().into('otherTable').set('otherField', 3))
.toParam()
);
/*
{
text: 'WITH someAlias AS (INSERT INTO otherTable (otherField) VALUES ($1)) INSERT INTO table (field) VALUES ($2)'
values: [3, 5]
}
*/
The DISTINCT ON clause is also supported, by passing one or more fields:
log(
squelPostgres.select()
.distinct('field1', 'field2')
.from('table')
.order('field1')
.order('field2')
.order('field3')
.toString()
);
/* SELECT DISTINCT ON (field1, field2) * FROM table ORDER BY field1 ASC, field2 ASC, field3 ASC */
mysql flavour provides the ON DUPLICATE KEY UPDATE clause:
squelMysql = squel.useFlavour('mysql');
log(
squelMysql.insert()
.into('table')
.setFields({
field1: 'abc',
field2: 3
})
.onDupUpdate('field1', 'upd')
.toParam()
);
/*
{
text: 'INSERT INTO table (field1, field2) VALUES (?, ?) ON DUPLICATE KEY UPDATE field1 = ?'
values: ['abc', 3, 'upd']
}
*/
Default date formatting:
squelMssql = squel.useFlavour('mssql');
log(
squelMssql.insert()
.into('table')
.setFields({
field1: new Date('2012-12-12T04:30:00Z')
})
.toString()
);
/*
INSERT INTO table (field1) VALUES(('2012-12-12 4:30:0'))
*/
TOP:
squelMysql = squel.useFlavour('mssql');
log(
squelMysql.select()
.from('table')
.field('field')
.top(10)
.toString()
);
/*
SELECT TOP (10) field FROM table
*/
OFFSET and FETCH NEXT:
squelMysql = squel.useFlavour('mssql');
log(
squelMysql.select()
.from('table')
.field('field')
.limit(10)
.offset(5)
.toString()
);
/*
SELECT field FROM table OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY
*/
OUTPUT INSERTED:
squelMssql = squel.useFlavour('mssql');
log(
squelMssql.update()
.table('table')
.output('id', 'blah')
.set('field', 1)
.toString()
);
/*
UPDATE table SET field = 1 OUTPUT INSERTED.id AS blah
*/
squelMssql = squel.useFlavour('mssql');
log(
squelMssql.insert()
.into('table')
.output('id')
.set('field', 1)
.toString()
);
/*
INSERT INTO table (field) OUTPUT INSERTED.id VALUES (1)
*/