SQL shortcuts.
var sqlcut = require('sqlcut');
var db = sqlcut('sqlcut-pg', 'postgres://user:password@server/db');
db.query(
'select * from users where email = ? and is_active = ?',
'[email protected]',
true
).then(console.log);
Note that API construction method takes DB adapter module name. At the moment options are:
You can implement your own module,
the only requirement for it is to have query(sql, paramsArray)
method
which returns promise with query results (special case is insert query, it must return id
of inserted record).
Notes:
- "promises" means "returns promise"
- to enable audit, call methods with context containing
user
object (withid
property)
Predefined context for calling methods on behalf of system.
Promises query result. If param1
is an array, its elements will be treated as params.
db.query('select * from products where name = ?', ['beer']).then(console.log);
Promises first row of results.
Promises identifier of record that is being inserted.
db.insert('products', { name: 'beer' }).then(console.log);
Promises identifier of record that is being updated. Record must have identifier value.
db.update('products', { id: 20, name: 'fish' }).then(console.log);
Promises removal of record with given identifier from specified table.
db.remove('products', 20).then(function () {
console.log('Product 20 was removed');
});
Promises record with given identifier taken from specified table.
Builds function aggregate(query)
which does aggregation (grouping with further processing) with filtering option.
Options is object with following properties:
tableName
- name of target tableallowedGroupables
- optional, object where keys are columns that can participate in GROUP BY clause and values are additional fields that should be included in query
{
'date': null,
'salesman': 'salesman_id',
'country': 'country_id'
}
allowedFilterables
- optional, array of names of columns that can participate in WHERE clause
[
'salesman_id',
'is_cold'
]
firstLevelColumns
- optional, columns that should be selected from table (often they are result of aggregation)secondLevelColumns
- optional, columns that should be build on top offirstLevelColumns
(for example, total averages)
Query is object with following properties:
date_range
- object with 2 properties: start and end which are dates in ISO formatgroups
- array of column namesfilters
- object: key is column name, value is array of allowed values
{
"groups": ["salesman"],
"date_range": {
"start": "2014-06-23T00:00:00.000Z",
"end": "2014-06-29T23:59:59.999Z"
},
"filters": {
"salesman_id": ["14"]
}
}
Builds function associate(masterValue, slaveValuesArray)
which does association (many to many) of certain record with given children.
var associate = tools.associate.build('ad_photos', 'ad_id', 'photo_id');
associate(12, [55, 12, 32]).then(function () {
console.log('12th ad was associated with 55th, 12th and 32d photos.');
});
Builds create(row)
function which promises id of record which will be created. Columns
argument defines list of allowed column names, can be omitted to skip such filtering.
Builds function findOrCreate(discriminantValue1, discriminantValue2, ..., newObject)
which promises extraction or creation of record which match discrimination condition. Note that newObject
optional parameter is used for providing more properties for "create" part of function.
var findOrCreate = tools.findOrCreate.build('products', 'name');
findOrCreate('milk').then(console.log);
Builds function lookup(value)
which promises array of id-value objects.
tableName
- name of table to look upcolumnName
- optional column name ('name' by default)
Builds function remove(id)
which promises record deletion by id
.
Builds update(record)
function which promises update of record
, which must have id
property among updated ones. Columns
argument defines list of allowed column names, can be omitted to skip such filtering.
Builds function createOrUpdate(record)
which promises creation (if record doesn't have id
property) or update of corresponding record in DB.
var createOrUpdate = tools.createOrUpdate.build('products');
createOrUpdate({ name: 'milk' }).then(console.log);
Builds function find(discriminantValue1, discriminantValue2, ...)
which promises record with satisfies discrimination condition.
var find = tools.find.build('products', 'name');
find('milk').then(console.log);
If no discriminant name was passed, then id
will be used.
var find = tools.find.build('products');
find(10).then(console.log);
Builds function createIfNotExists(row)
which promises creation of record in case if it doesn't exist (no record with same id
).
var createIfNotExists = tools.createIfNotExists.build('products', ['id', 'name']);
createIfNotExists({id: 1, name: 'milk'});
Builds function upsert(row)
which promises udpate of record if it exists or its creation if no such record can be found in table (by id
).
var upsert = tools.upsert.build('products', ['id', 'name']);
upsert({id: 1, name: 'milk 2'});
BSD