es6-sql

warning: in development, do not use, but do contribute.

Stop wasting your life with embedded DSLs [1], and just write SQL.

sql`SQL...`

Interpolate scope safely into a SQL statement, using ES6's tagged template strings.

var sql = require('es6-sql');

var table = 'interp_user_table',
    user_id = 100;

var query = sql`
    SELECT *
    FROM ${table}
    WHERE
        user_id = ${user_id}
        OR user_id IN (1,2,3)
        OR user_id IN (${sql`SELECT user_id FROM all_users WHERE is_active = True`})`;

Composition

Easily combine SQL programmatically.

query.column`SQL...`

query = query.column`COUNT(id) as total`;

query.join`SQL...`

query = query.join`
    LEFT JOIN user_tweets ON tweet_id = user_id
        AND tweeted_on > now`;

query.and`SQL...`

query = query.and`hello = ${123}`

query.or`SQL...`

query = query.or`foo IN (SELECT foo FROM foo)`;

query.group`SQL...`

query = query.group`user_id`;

query.having`SQL...`

query = query.having`COUNT(tweeted.id) > 2`;

query.order`SQL...`

query = query.order`tweeted_on DESC`;

query.limit(Number)

query = query.limit(25);

query.offset(Number)

query = query.offset(125);

function paginate(page, page_size) {
    query = query.limit(page).offset(page * page_size);
}

Composition through Interpolation

function queryCount(query) {
    return sql`SELECT count(*) as count FROM (${query}) a`;
}
function queryUnion(a, b) {
    return sql`${a} UNION ${b}`;
}

Reuse utilities at hand like lodash for assembling our queries.

function queryUnionList(query_list) {
    return query_list.reduce(queryUnion, query_list);
}

Serialization

sql.toString()

Serialize your SQL statement into a string with values interpolated.

console.log(query.toString());

SELECT *, COUNT(id) AS total
  FROM interp_user_table
  LEFT JOIN user_tweets
    ON ((tweet_id = user_id) AND (tweeted_on > now))
  WHERE (((((user_id = $1) OR (user_id IN (1, 2, 3))) OR (user_id IN (SELECT user_id
    FROM all_users
    WHERE (is_active = TRUE)))) AND (hello = $2)) OR (foo IN (
    SELECT foo
      FROM foo
  )))
  GROUP BY user_id
  HAVING (COUNT(tweeted_id) > 2)
  ORDER BY tweeted_on DESC
  LIMIT 25
  OFFSET 125

sql.parameters

console.log(query.parameters);

[100, 123]

[1]

• http://www.sqlalchemy.org/
• https://docs.djangoproject.com/en/1.8/topics/db/queries/
• http://www.rubydoc.info/gems/activerecord/4.2.4
• http://datamapper.org/
• http://sequel.jeremyevans.net/
• https://hackage.haskell.org/package/esqueleto
• https://hackage.haskell.org/package/haskelldb
• http://docs.sequelizejs.com/en/latest/
• http://dresende.github.io/node-orm2/
• https://github.com/1602/jugglingdb
• http://propelorm.org/
• http://stainless-steel.github.io/sql/sql/index.html