Introducing
Your new presentation assistant.
Refine, enhance, and tailor your content, source relevant images, and edit visuals quicker than ever before.
Trending searches
200
The solution?
An SQL query 'builder' API
The problems
- End up needing to use multi-line strings for readability.
- Query logic may depend on business logic in different parts of the code.
- String concatenation is messy and error-prone.
Existing one: Rel
- Port of Rails' Arel to CoffeeScript.
- Problem: Cumbersome syntax
users = new Rel.Table 'users'
users.project(Rel.star()).toSql()
// SELECT * FROM users
Complex query
SELECT ... INNER JOIN ... LEFT JOIN ... WHERE ... GROUP BY ... ORDER BY ... LIMIT ... OFFSET ...
Squel.js
In applications we usually need to use more complex queries which aren't as easily readable.
SQL query string builder
http://squeljs.org
https://github.com/hiddentao/squel
Simple SQL query
SELECT * FROM table
Ramesh Nair
@hiddentao
Simple queries are easy to write and manipulate as single-line strings.
Relational databases
Value placeholders
Overridable quote characters
squel.update({
usingValuePlaceholders: true
}.table('table').set('f', '?');
// UPDATE table SET f = ?
We may want to use an RDBMS with:
- node.js
- PhoneGapclass
squel.select({
tableAliasQuoteCharacter: ':'
}.from('table', 't');
// SELECT * FROM table :t:
Custom query types
squel.pragma().flush('students')
// PRAGMA FLUSH students
Built-in queries can be customized too!
Cloning
s1 = squel.select().from('table1')
s2 = s1.clone().field('id')
// s1: SELECT * FROM table1
// s2: SELECT id FROM table1
What does Squel offer?
Easy to start using
Expression builder
squel.select().from('users')
// SELECT * FROM users
squel.expr()
.and('id < 5')
.and_begin()
.or('a = 2').or('b = 3')
.end()
// (id < 5) AND (A = 2 OR b = 3)
overrides toString() method to construct the query
Dev infrastructure
Hands-off approach
Out-of-order chaining
- Coffeescript
- Travis CI
- Mocha tests
- No third-party dependencies
squel
.select()
.from("students")
.field("GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')")
squel.delete().limit(1).where('id=1')
.from('users')
// DELETE FROM users WHERE id=1 LIMIT 1
// SELECT GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ') FROM students
Squel
Internals
Building blocks
cls.Select extends cls.QueryBuilder
constructor: (options, blocks = null) ->
blocks or= [
new cls.StringBlock(options, 'SELECT'),
new cls.DistinctBlock(options),
new cls.GetFieldBlock(options),
...
node.js + browser
npm install squel
squel.min.js.gz = ~3.6KB
Queries and Blocks are customizable...