mohair
mohair is a simple and flexible sql builder with a fluent interface
Last updated 5 years ago by snd .
MIT · Repository · Bugs · Original npm · Tarball · package.json
$ cnpm install mohair 
SYNC missed versions from official npm registry.

mohair

NPM version Build Status Dependencies

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

or

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

  • 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

Current Tags

  • 0.13.0-rc.2                                ...           latest (5 years ago)

35 Versions

  • 0.13.0-rc.2                                ...           5 years ago
  • 0.13.0-rc.1                                ...           6 years ago
  • 0.12.0                                ...           6 years ago
  • 0.11.2                                ...           6 years ago
  • 0.11.0                                ...           7 years ago
  • 0.10.4                                ...           7 years ago
  • 0.10.3                                ...           7 years ago
  • 0.10.2                                ...           7 years ago
  • 0.10.1                                ...           7 years ago
  • 0.10.0                                ...           7 years ago
  • 0.9.4                                ...           7 years ago
  • 0.9.3                                ...           8 years ago
  • 0.9.2                                ...           8 years ago
  • 0.9.1                                ...           8 years ago
  • 0.9.0                                ...           8 years ago
  • 0.8.4                                ...           8 years ago
  • 0.8.3                                ...           8 years ago
  • 0.8.2                                ...           8 years ago
  • 0.8.1                                ...           8 years ago
  • 0.8.0                                ...           8 years ago
  • 0.7.6                                ...           8 years ago
  • 0.6.6                                ...           8 years ago
  • 0.6.5                                ...           8 years ago
  • 0.6.4                                ...           8 years ago
  • 0.6.2                                ...           8 years ago
  • 0.6.1                                ...           8 years ago
  • 0.6.0                                ...           8 years ago
  • 0.5.2                                ...           8 years ago
  • 0.4.2                                ...           8 years ago
  • 0.3.2                                ...           8 years ago
  • 0.2.2                                ...           8 years ago
  • 0.2.1                                ...           8 years ago
  • 0.1.1                                ...           8 years ago
  • 0.1.0                                ...           8 years ago
  • 0.0.1                                ...           8 years ago
Maintainers (1)
Downloads
Today 0
This Week 0
This Month 0
Last Day 0
Last Week 0
Last Month 2
Dependencies (2)
Dev Dependencies (2)
Dependents (4)

Copyright 2014 - 2016 © taobao.org |