mohair
v0.13.0-rc.2
Published
mohair is a simple and flexible sql builder with a fluent interface
Downloads
195
Readme
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
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: '[email protected]'});
query.sql(); // => 'INSERT INTO user(name, email) VALUES (?, ?)'
query.params(); // => ['alice', '[email protected]']
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 [email protected]: 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 escaping for each and every query as in criterion !!!!
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