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.
- install
- use
- require
- specify a table to use
- insert a record
- insert with some raw sql
- insert multiple records
- delete
- update
- update with some raw sql
- select
- select with subquery
- select without a table
- select with criteria
- order
- limit and offset
- join
- join with criteria
- group
- mixins
- extending
- common table expressions
- license: MIT
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; var updateUser = visibleUsers;updateUser; // => 'UPDATE user SET name = ? WHERE (is_visible = ?) AND (id = ?)'updateUser; // => ['bob', true, 3] var deleteUser = visibleUsers;deleteUser; // => 'DELETE FROM user WHERE (is_visible = ?) AND (name = ?)'deleteUser; // => [true, 'alice']
require
var mohair = ;
specify the table to use
var userTable = mohair;
insert a record
var query = userTable; query; // => 'INSERT INTO user(name, email) VALUES (?, ?)'query; // => ['alice', 'alice@example.com']
insert with some raw sql
var query = userTable; query; // => 'INSERT INTO user(name, created_at) VALUES (?, NOW())'query; // => ['alice']
insert multiple records
var query = userTable; query; // => 'INSERT INTO user(name) VALUES (?), (?)'query; // => ['alice', 'bob']
all records in the argument array must have the same properties.
delete
var query = userTable; query; // => 'DELETE FROM user WHERE id = ?'query; // => [3]
where
can take any valid criterion.
update
var query = userTable; query; // => 'UPDATE user SET name = ? WHERE name = ?'query; // => ['bob', 'alice']
update with some raw sql
var query = userTable; query; // => 'UPDATE user SET age = LOG(age, ?) WHERE name = ?'query; // => [4, 'alice']
where
can take any valid criterion.
select
var query = userTable; query; // => 'SELECT * FROM user'query; // => []
you can omit select()
if you want to select *
. select is the default action.
var query = userTable; query; // => 'SELECT name, timestamp AS created_at FROM user'query; // => []
var query = userTable; query; // => 'SELECT name, timestamp AS created_at FROM user'query; // => []
var query = userTable; query; // => 'SELECT name, timestamp AS created_at FROM user'query; // => []
var fragment = mohair;var query = mohair ; query; // => 'SELECT region, (SUM(total_sales/?)) AS summed_sales FROM regional_sales'query; // => [10]
select with subquery
var subquery = mohair ;var query = userTable; query; // => 'SELECT name, (SELECT count(1) FROM order WHERE user_id = user.id) AS order_count FROM user'query; // => []
select without a table
var query = mohair query; // => 'SELECT now()'query; // => []
select with criteria
var query = userTable; query; // => 'SELECT * FROM user WHERE (id = ?) AND (name = ?)'query; // => [3, 'alice']
where
can take any valid criterion.
multiple calls to where
are anded together.
order
var query = userTable; query; // => 'SELECT * FROM user ORDER BY created DESC, name ASC'query; // => []
limit and offset
var query = userTable; query; // => 'SELECT * FROM user LIMIT ? OFFSET ?'query; // => [20, 10]
join
var query = userTable; query; // => 'SELECT * FROM user JOIN project ON user.id = project.user_id'query; // => []
join with criteria
var query = userTable; query; // => 'SELECT * FROM user JOIN project ON user.id = project.user_id AND (project.column IS NULL)'query; // => []
group
var query = userTable ; query; // => 'SELECT user.*, count(project.id) AS project_count FROM user JOIN project ON user.id = project.user_id GROUP BY user.id'query; // => []
mixins
var { return this ;}; var query = mohair ; query; // => 'SELECT * FROM posts WHERE is_public = ? LIMIT ? OFFSET ?'query; // => [true, 100, 1000]
extending
var posts = mohair; posts { return this ;}; var query = mohair ; query; // => 'SELECT * FROM posts WHERE is_public = ? LIMIT ? OFFSET ?'query; // => [true, 100, 1000]
common table expressions
see the postgres documentation
var regionalSales = mohair ; var topRegions = mohair ; var query = mohair ;
query;
returns
WITHregional_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_salesFROM ordersWHERE 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 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 -
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