Loading presentation...
Prezi is an interactive zooming presentation

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Squel.js

Squel.js an SQL query string builder for Javascript
by

Ramesh Nair

on 5 May 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Squel.js

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. Simple SQL query SQL query string builder Squel.js SELECT * FROM table In applications we usually need to use more complex queries which aren't as easily readable. Complex query SELECT ... INNER JOIN ... LEFT JOIN ... WHERE ... GROUP BY ... ORDER BY ... LIMIT ... OFFSET ... Simple queries are easy to write and manipulate as single-line strings. The problems Relational databases We may want to use an RDBMS with:

- node.js
- PhoneGapclass The solution? An SQL query 'builder' API 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 What does Squel offer? Easy to start using squel.select().from('users')

// SELECT * FROM users Out-of-order chaining squel.delete().limit(1).where('id=1')
.from('users')

// DELETE FROM users WHERE id=1 LIMIT 1 overrides toString() method to construct the query Expression builder squel.expr()
.and('id < 5')
.and_begin()
.or('a = 2').or('b = 3')
.end()

// (id < 5) AND (A = 2 OR b = 3) node.js + browser npm install squel squel.min.js.gz = ~3.6KB Squel
Internals Dev infrastructure Coffeescript
Travis CI
Mocha tests
No third-party dependencies Cloning http://squeljs.org Ramesh Nair
@hiddentao https://github.com/hiddentao/squel Hands-off approach squel
.select()
.from("students")
.field("GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')") // SELECT GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ') FROM students Custom query types squel.pragma().flush('students')

// PRAGMA FLUSH students Built-in queries can be customized too! s1 = squel.select().from('table1')

s2 = s1.clone().field('id')

// s1: SELECT * FROM table1
// s2: SELECT id FROM table1 Overridable quote characters squel.select({
tableAliasQuoteCharacter: ':'
}.from('table', 't');

// SELECT * FROM table :t: Value placeholders squel.update({
usingValuePlaceholders: true
}.table('table').set('f', '?');

// UPDATE table SET f = ? 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),
... Queries and Blocks are customizable...
Full transcript