Proposal - Morgul/omega-db GitHub Wiki
API
A couple of design points:
- OmegaDB itself is not stateful; instead, all configuration resides in the connection object.
In the examples below, we are assuming the user calls the imported omega-db
module od
.
CollectionSelector
cSel
.db('...') // Optional database selection (returns a new CollectionSelector)
.ns('...') // Optional namespace selection (returns a new CollectionSelector)
.schema('...') // Alias for `ns`
.collection('...') // Collection selection (returns a new Collection)
.table('...') // Alias for `collection`
.bucket('...') // Alias for `collection`
Connection
od(driver, config) // Create a new connection with the given driver and config
// Connection inherits CollectionSelector, and contains all the same methods; see CollectionSelector for details.
Queryable
queryable
// Selecting Fields //
.fields(field, [...]) // Only return the given fields from the current queryable.
.columns(field, [...]) // Alias for `fields`.
// Filtering Items //
.filter(filter) // Only return items that match the given filter
.between(field, lowerVal, upperVal, [leftBoundOpen, rightBoundOpen])
// Selects all items that have `field` between `lowerVal` and `upperVal`.
// Supports specifying whether the left bound or right bound is open. (default for both is true)
// Joins //
.as(alias) // Create an alias for the current queryable. (useful when it is a subquery of another queryable)
// You can then refer to this queryable's fields using dot notation: "alias.fieldName"
.join(queryable or collectionName, filter, ['left'|'inner'|'full'])
// Join the current queryable with the given queryable (or the given collection's contents)
.union(queryable) // Concatenates `queryable` to the current queryable's results
// Transformations //
.groupBy(field, [...]) // Groups items by the specified field(s)
// After calling groupBy, only the fields specified here may be selected directly; others may only be used with aggregate functions
.orderBy(field, [...]) // Sorts items by the specified field(s); prepend "-" to the field name to reverse the sort order
.limit(limit) // Limit the number of results
.offset(offset) // Skip the first `offset` number of results
.slice(start, [end]) // limit the sequence to only those items between `start` and `end`
// Special //
.execute() // Begins executing the current queryable, and returns a ResultSet
Collection
collection
// Collection inherits Queryable, and contains all the same methods; see Queryable for details.
// Modification //
.delete(filter) // Delete all items that match `filter`.
.insert(queryable | object | array(object)) // Inserts new items into the table.
.upsert(filter, queryable | object | array(object)) // Update items in the table, inserting new items for keys which don't exist yet.
.update(filter, queryable | object) // Update the matched items with either the results of queryable, or with object (treated as a partial).
.replace(filter, queryable | object) // Replace the matched items with either the results of queryable, or with object.
Query
query
// Query inherits Queryable, and contains all the same methods; see Queryable for details.
Expression
Expression
s are objects that accept an item and return a value.
The main omega-db
module contains several functions which create Expression
s:
od
// Generic //
.field(fieldName) // Select a field from the current item
// Aggregate Functions // (if .groupBy has been called, these apply across each group of items)
.count([fieldName | expr, ...]) // Select the number of distinct values in the given field(s) / expression(s) (or the total number of items, if omitted)
.sum(fieldName | expr) // Select the sum of the given field or expression
.min(fieldName | expr) // Select the minimum value of the given field or expression
.max(fieldName | expr) // Select the maximum value of the given field or expression
.avg(fieldName | expr) // Select the average value of the given field or expression
// Possible future additions: //
// Arithmetic //
.sqrt(expr) // Yields the square root of `expr`
.log(expr) // Yields the logarithm base 10 of `expr`
.log(expr, [baseExpr]) // Yields the logarithm base `baseExpr` (or 10 if not given) of `expr`
.log2(expr) // Yields the logarithm base 2 of `expr`
.ln(expr) // Yields the logarithm base e of `expr`
// Trigonometry //
.sin(expr) // Yields the sine of `expr`
.cos(expr) // Yields the cosine of `expr`
.tan(expr) // Yields the tangent of `expr`
// Rounding //
.round(expr) // Yields `expr`, rounded to the nearest integer
.floor(expr) // Yields `expr`, rounded down
.ceil(expr) // Yields `expr`, rounded up
After any aggregate function is used with a Query
, you may only use other aggregate functions, or fields passed to groupBy
.
Expression
objects contain several methods which return another Expression
:
expression
// Arithmetic //
.plus(expr) // Yields this expression plus `expr`
['+'](expr) // Alias for `plus`
.minus(expr) // Yields this expression minus `expr`
['-'](expr) // Alias for `minus`
.mul(expr) // Yields this expression times `expr`
['*'](expr) // Alias for `mul`
.div(expr) // Yields this expression divided by `expr`
['/'](expr) // Alias for `div`
// Possible future additions: //
.mod(expr) // Yields this expression modulo `expr`
['%'](expr) // Alias for `mod`
.pow(expr) // Yields this expression to the power of `expr`
Also, Expression
s contain methods which return a Filter
:
expression
// Comparisons //
.eq(expr) // `true` if the evaluation of this expression is equal to the evaluation of `expr`
['='](expr) // Alias for `eq`
.le(expr) // `true` if the evaluation of this expression is less than or equal to the evaluation of `expr`
['<='](expr) // Alias for `le`
.lt(expr) // `true` if the evaluation of this expression is less than the evaluation of `expr`
['<'](expr) // Alias for `lt`
// ...similar for `gt`/`>`, `ge`/`>=`, `ne`/`!=`
// Other Tests //
.contains(expr, [...]) // `true` if the evaluation of this expression contains any of the given values
.in(queryable | expr | array(expr))
// `true` if the evaluation of this expression is contained in the given value
.isNull() // `true` if this expression evaluates to `null`
Value
Value
s represent literal values in an expression.
The main omega-db
module contains several functions which create Expression
s:
od
.value(val) // Create a new `Value` for the given literal value
Value
inherits Expression
, and contains all the same methods; see Expression
for details.
Filter
Filters are similar to expressions, but always return a boolean rather than an arbitrary value.
In addition to the aforementioned Expression
methods (see above), there are also some functions on the omega-db
module which return a Filter
:
od
// Logical Connectives //
.and(filter, [...]) // `true` if all of the passed filters evaluate to `true`
.or(filter, [...]) // `true` if any of the passed filters evaluate to `true`
Filter
inherits Expression
, and contains all the same methods; see Expression
for details.
ResultSet
Using Events
resultSet
.on('item', function(item){ ... }); // Emitted with each returned item
.on('row', function(item){ ... }); // Alias for 'item'
.on('finished', function(){ ... }); // Emitted when the query finishes successfully
.on('error', function(error){ ... }); // Emitted when the query generates an error
Using Callbacks
resultSet
// Get a count of total items in the result set.
.count(function(error, numItems){ ... });
// Accumulate all items into an array, and handle them all in one callback:
.all(function(errors, items){ ... });
// Retrieve exactly one item, by index
.nth(index, function(error, item){ ... });
// Retrieve only the first item (same as `.nth(0, ...)`)
.first(function(error, item){ ... });
// Handle each item individually, with no item accumulation:
.each(function(item, callback){ ...; callback(error); },
function(errors){ ... });
// Handle each item individually, yielding an array of transformed item values from each item handler call:
.map(function(item, callback){ ...; callback(error, item); },
function(errors, items){ ... });
// Handle each item individually, yielding the final value of an accumulator value:
.reduce({}, function(accum, item, callback){ ...; callback(error, accum); },
function(errors, accum){ ... });
Using Promises
resultSet
// Get a count of total items in the result set.
.count()
.then(function(error, numItems){ ... });
// Accumulate all items into an array, and handle them all via the returned promise:
.all()
.then(function(items){ ... }, function(errors){ ... });
// Retrieve exactly one item, by index
.nth(index)
.then(function(item){ ... }, function(error){ ... });
// Retrieve only the first item (same as `.nth(0, ...)`)
.first()
.then(function(item){ ... }, function(error){ ... });
// Handle each item individually, with no item accumulation:
.each(function(item, callback){ ...; callback(error); })
.then(function(){ ... }, function(errors){ ... });
// Handle each item individually, yielding an array of transformed item values from each item handler call:
.map(function(item, callback){ ...; callback(error, item); })
.then(function(items){ ... }, function(errors){ ... });
// Handle each item individually, yielding the final value of an accumulator value:
.reduce({}, function(accum, item, callback){ ...; callback(error, accum); })
.then(function(accum){ ... }, function(errors){ ... });
Item handler functions
The per-item methods above each expect an item handler function, which will be called once per item in the result set. The method will not finish successfully (i.e., resolve the returned promise object, or call the finished callback without an error) until all item handler calls have completed.
Item handler functions may return different types of values, which influence when that result handler call is considered "completed":
- a promise object - the handler call is completed when the returned promise is resolved.
undefined
- the handler call is completed when the passedcallback
is called.
Examples
Find the top 5 highest rated reviews in a product database.
var results = od('postgres', "tcp://user:p@ssw0rd@example-svr")
.db('example_db')
.schema('product_schema')
.table('review')
.between('score', 80, 100)
.orderBy('-score', 'created')
.limit(5)
.execute();
// Print the first review
results.first(function(error, firstReview)
{
console.log("FIRST!!!", firstReview);
});
// Or handle all the reviews (Don't try and do both)
results.all(function(error, reviews)
{
reviews.forEach(function(review)
{
console.log('Review:', review);
});
});
Joins
Find all reviews for the user 'Bob'.
var results = od('postgres', "tcp://user:p@ssw0rd@example-svr")
.db('example_db')
.schema('product_schema')
.table('account')
.join('review', f.field('review.account_id').eq(f.field('account.id')), 'inner')
.filter(f.field('name').eq('Bob'))
.execute();
The above query, when given to the PostgreSQL backend, should translate to something roughly equivalent to:
SELECT *
FROM account
INNER JOIN review
ON review.account_id = account.id
WHERE account.name = 'Bob'
Order does matter; note that if you swap the position of the .join
call and the .filter
call, i.e.:
var results = od('postgres', "tcp://user:p@ssw0rd@example-svr")
.db('example_db')
.schema('product_schema')
.table('account')
.filter(f.field('name').eq('Bob'))
.join('review', f.field('review.account_id').eq(f.field('account.id')), 'inner')
.execute();
then the generated SQL will look more like:
SELECT *
FROM
(
SELECT *
FROM account
WHERE account.name = 'Bob'
) AS _sub_1
INNER JOIN review
ON review.account_id = _sub_1.id