Mohair NPM | npm.io

mohair

mohair is a simple and flexible sql builder with a fluent interface.

mesa builds on top of mohair and adds methods to execute queries, to declare and include associations (hasOne, belongsTo, hasMany, hasAndBelongsToMany) and more: go check it out.

background

get started

npm install mohair

put this line in the dependencies section of your package.json:

"mohair": "0.12.0"

then run:

npm install

use

mohair has a fluent interface where every method returns a new object. no method ever changes the state of the object it is called on. this enables a functional programming style:

var visibleUsers = mohair.table('user').where({is_visible: true});

var updateUser = visibleUsers.update({name: 'bob'}).where({id: 3});
updateUser.sql();       // => 'UPDATE user SET name = ? WHERE (is_visible = ?) AND (id = ?)'
updateUser.params();    // => ['bob', true, 3]

var deleteUser = visibleUsers.where({name: 'alice'}).delete();
deleteUser.sql();       // => 'DELETE FROM user WHERE (is_visible = ?) AND (name = ?)'
deleteUser.params();    // => [true, 'alice']

require

var mohair = require('mohair');

specify the table to use

var userTable = mohair.table('user');

insert a record

var query = userTable.insert({name: 'alice', email: 'alice@example.com'});

query.sql();        // => 'INSERT INTO user(name, email) VALUES (?, ?)'
query.params();     // => ['alice', 'alice@example.com']

insert with some raw sql

var query = userTable.insert({name: 'alice', created_at: mohair.raw('NOW()')});

query.sql();        // => 'INSERT INTO user(name, created_at) VALUES (?, NOW())'
query.params();     // => ['alice']

insert multiple records

var query = userTable.insertMany([{name: 'alice'}, {name: 'bob'}]);

query.sql();        // => 'INSERT INTO user(name) VALUES (?), (?)'
query.params();     // => ['alice', 'bob']

all records in the argument array must have the same properties.

delete

var query = userTable.where({id: 3}).delete();

query.sql();        // => 'DELETE FROM user WHERE id = ?'
query.params();     // => [3]

where can take any valid criterion.

update

var query = userTable.where({name: 'alice'}).update({name: 'bob'});

query.sql();        // => 'UPDATE user SET name = ? WHERE name = ?'
query.params();     // => ['bob', 'alice']

update with some raw sql

var query = userTable.where({name: 'alice'}).update({age: mohair.raw('LOG(age, ?)', 4)});

query.sql();        // => 'UPDATE user SET age = LOG(age, ?) WHERE name = ?'
query.params();     // => [4, 'alice']

where can take any valid criterion.

select

var query = userTable.select();

query.sql();        // => 'SELECT * FROM user'
query.params();     // => []

you can omit select() if you want to select *. select is the default action.

var query = userTable.select('name, timestamp AS created_at');

query.sql();        // => 'SELECT name, timestamp AS created_at FROM user'
query.params();     // => []

var query = userTable.select('name', 'timestamp AS created_at');

query.sql();        // => 'SELECT name, timestamp AS created_at FROM user'
query.params();     // => []

var query = userTable.select('name', {created_at: 'timestamp'});

query.sql();        // => 'SELECT name, timestamp AS created_at FROM user'
query.params();     // => []

var fragment = mohair.raw('SUM(total_sales/?)', 10);
var query = mohair
    .table('regional_sales')
    .select('region', {summed_sales: fragment});

query.sql();        // => 'SELECT region, (SUM(total_sales/?)) AS summed_sales FROM regional_sales'
query.params();     // => [10]

select with subquery

var subquery = mohair
    .table('order')
    .where('user_id = user.id')
    .select('count(1)');
var query = userTable.select('name', {order_count: subquery});

query.sql();        // => 'SELECT name, (SELECT count(1) FROM order WHERE user_id = user.id) AS order_count FROM user'
query.params();     // => []

select without a table

var query = mohair.select('now()')

query.sql();        // => 'SELECT now()'
query.params();     // => []

select with criteria

var query = userTable.where({id: 3}).where('name = ?', 'alice').select();

query.sql();        // => 'SELECT * FROM user WHERE (id = ?) AND (name = ?)'
query.params();     // => [3, 'alice']

where can take any valid criterion. multiple calls to where are anded together.

order

var query = userTable.order('created DESC, name ASC').select();

query.sql();        // => 'SELECT * FROM user ORDER BY created DESC, name ASC'
query.params();     // => []

limit and offset

var query = userTable.limit(20).offset(10).select();

query.sql();        // => 'SELECT * FROM user LIMIT ? OFFSET ?'
query.params();     // => [20, 10]

join

var query = userTable.join('JOIN project ON user.id = project.user_id');

query.sql();        // => 'SELECT * FROM user JOIN project ON user.id = project.user_id'
query.params();     // => []

join with criteria

var query = userTable.join('JOIN project ON user.id = project.user_id', {'project.column': {$null: true}});

query.sql();        // => 'SELECT * FROM user JOIN project ON user.id = project.user_id AND (project.column IS NULL)'
query.params();     // => []

group

var query = userTable
    .select('user.*, count(project.id) AS project_count')
    .join('JOIN project ON user.id = project.user_id')
    .group('user.id');

query.sql();        // => 'SELECT user.*, count(project.id) AS project_count FROM user JOIN project ON user.id = project.user_id GROUP BY user.id'
query.params();     // => []

mixins

var paginate = function(page, perPage) {
    return this
        .limit(perPage)
        .offset(page * perPage);
};

var query = mohair.table('posts')
    .mixin(paginate, 10, 100)
    .where(is_public: true);

query.sql();       // => 'SELECT * FROM posts WHERE is_public = ? LIMIT ? OFFSET ?'
query.params();    // => [true, 100, 1000]

extending

var posts = mohair.table('posts');

posts.paginate = function(page, perPage) {
    return this
        .limit(perPage)
        .offset(page * perPage);
};

var query = mohair.table('posts')
    .where(is_public: true)
    .paginate(10, 100);

query.sql();       // => 'SELECT * FROM posts WHERE is_public = ? LIMIT ? OFFSET ?'
query.params();    // => [true, 100, 1000]

common table expressions

see the postgres documentation

var regionalSales = mohair
    .select('region, SUM(amount) AS total_sales')
    .table('orders')
    .group('region');

var topRegions = mohair
    .select('region')
    .table('regional_sales')
    .where('total_sales > (SELECT SUM(total_sales/10 FROM regional_sales))');

var query = mohair
    .with(
        regional_sales: regionalSales
        top_regions: topRegions
    )
    .select("""
        region,
        product,
        SUM(quantity) AS product_units,
        SUM(amount) AS product_sales
    """)
    .table('orders')
    .where('region IN (SELECT region FROM top_regions)')
    .group('region, product');

query.sql();

returns

WITH
regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
 ), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
 )
SELECT
    region,
    product,
    SUM(quantity) AS product_units,
    SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

changelog

0.13.0

now uses criterion@0.4.0: criterion changelog applies to mohair as well click here to see it
.from() supports selecting from multiple tables, selecting from subqueries and has syntax for aliases
.sql()
mohair now conforms to sql-fragment interface
escapes more things that are escapable: aliases, names for common table expressions, ...
.mixin renamed to .call

license: MIT

TODO

test offset and limit with raw
fix union
update from
support raw everywhere
fix js conversion
better order
table can be an alias expression
test things in isolation

.with should also have an effect for insert, update, delete
test update from
criterion.wrapped()
better .using
- needs at least one table
- a seperate from object which is used to construct this
- support multiple tables in .table
- support alias syntax {foo: 'table'} in .table
- support subqueries in .table
- there must be at least one from item
test better tables
better testing of escaping
- test escaping for each and every query as in criterion !!!!
  - q.escape(...)
test that all parts of the queries get escaped
- select DONE
- insert
  - returning
- update
  - returning
- delete DONE
  - returning
test returning
- for update
- for delete
support more select syntax
better joins
- think about it !!! ...
- for lateral need to support subqueries
- similar to combination (union, ...)
- .join('LEFT JOIN LATERAL', subquery, 'ON', condition)
support row locks
join helper for select
better errors
- check error message in tests for error conditions
- test for every possible error condition
- throw correct errors (TypeError for example)
make updateFrom work
- https://github.com/snd/mohair/pull/29/files
support insert with subquery
- mohair.insert(['a', 'b', 'c'], mohair.table('user').select('id'))
README
- functional, immutable
better documentation
better description
better keywords
use lodash and replace helpers