MongoPg allows you to create queries using documents or javascript objects. It provides a mostly declarative interface for writing sql. You can do stuff like this:
db.users.findOne(27, function(error, result){
if (error) return res.error(error);
result.id == 27 // true;
});Install via npm:
npm install mongo-sql
You should use mongo-pg as your goto database module. Don't mess around with node-pg:
var db = require('mongo-pg');
db.init({
// Obviously your PG connection string as normal
connectionString: 'postgres://localhost:5432/my-db'
// Array of tables you want to interface with
, collections: [
'users'
, 'groups'
, 'posts'
]
})Node PG will queue up queries until a connection is established. So feel free to start using mongo-pg immediately after running the init function.
// Users where id greater than 50
db.users.find({ id: { $gt: 50 } }, function(error, results){
/* ... */
});Commands are the atomic unit of MongoPG and comprise the majority of the imperative behavior. On the inside, they're composed of strings that look like this:
-- find
'select {fields} from {collections} {joins} {where} {limit} {order} {groupBy}Each variable, denoted with {...} corresponds to a sql builder. The builders evaluate the passed in objects to produce a sql string. Note that when $query is a number, it is converted to an object of the form: { id: @input }. The following Commands are available:
collection.find - Find and return some documents
Various argument possibilities
- callback?
- $query, callback?,
- $query, fields, callback?
- $query, options, callback?
If $query is an object:
- $equals
- $lt, $lte
- $gt, $gte
- $null, $notNull
- $in, $nin
options:
- fields
- order
- offset
- limit
- groupBy
- joins
- join
- innerJoin
- leftJoin
- leftOuterJoin
- fullOuterJoin
- crossJoin
collection.findOne - Find and return one document
Various argument possibilities
- callback?
- $query, callback?,
- $query, fields, callback?
- $query, options, callback?
If $query is an object:
- $equals
- $lt, $lte
- $gt, $gte
- $null, $notNull
- $in, $nin
options:
- fields
- order
- offset
- limit
- groupBy
- joins
- join
- innerJoin
- leftJoin
- leftOuterJoin
- fullOuterJoin
- crossJoin
collection.delete - Remove documents
Various argument possibilities
- callback?
- $query, callback?,
- $query, callback?
- $query, callback?
If $query is an object:
- $equals
- $lt, $lte
- $gt, $gte
- $null, $notNull
- $in, $nin
collection.update - Update documents
Various argument possibilities
- $query, $update, callback?,
- $query, $update, options, callback?
If $query is an object:
- $equals
- $lt, $lte
- $gt, $gte
- $null, $notNull
- $in, $nin
$update:
This is the update document:
{ name: 'Jack' }Sets name = 'Jack'
options:
- fields
- order
- offset
- limit
- groupBy
- joins
- join
- innerJoin
- leftJoin
- leftOuterJoin
- fullOuterJoin
- crossJoin
Eventually, there will be custom command support. But for the time being, you'll have to get by with Conditional and Value Helpers.
All conditionals and value types are implemented as helpers. Things like $gt, $lt, $equals are all conditionals. They are helpers that construct the parts of queries that have conditions. Values are what's on the right-hand side of a condition. If you needed to do something like:
select * from users where last_visited > ( now() - interval '5' hour );Then you could register a value helper to help format hate right-hand value in parenthesis.
var db = require('mongo-pg');
// Optionally pass in an options object as the second parameter
db.registerHelper('$hours_ago', function(column, value, values, collection){
return "now() - interval $" + values.push(value.value) + " hour";
});
db.users.find({
'last_visited':
{ $gt: { $hours_ago: 5 } }
});The $hours_ago (along with minutes, days, years, etc.) helper is already implemented in mongo-sql. I realize that I've introduced two conflicting conventions. Camel-case and underscores. I'll fix it soon.
All helpers, conditional or otherwise, are passed 4 arguments to their implementation function
/**
* Helper functions are passed
* @param column {String} - Column name either table.column or column
* @param value {Mixed} - What the column should be equal to
* @param values {Array} - The values for the query
* @param collection {String} - The main table for the query
*/The value parameter can be anything. By default, if the query evaluation loop comes across a helper, it will chunk the value of the helper in the document to the function as the "value". However, if when registering the helper, you pass in:
{ customValues: false }Then the evaluation loop will assume you want the pre-parameterized value of the key. This is particularly useful for single-value or simple-value helpers like $gt or $equals:
db.registerConditionalHelper('$equals', { cascade: true, customValues: false }, function(column, value, values, collection){
return column + ' = ' + value;
});
db.registerConditionalHelper('$gt', { cascade: true, customValues: false }, function(column, value, values, collection){
return column + ' > ' + value;
});By default, helpers do not cascade. But what is cascading? Cascading is useful for swapping the order in which you apply conditionals or values. For instance, when performing a greater than query, there are multiple ways to order it:
db.users.find({ id: { $gt: 1 } });
db.users.find({ $gt: { id: 1 } });The way you write depends on the query. In the above example, if you need to perform multiple conditions on id then it would be useful to write it the first way:
db.users.find({ id: { $gt: 1, $lt: 100, $or: { $gt: 100 } } });On the other hand, if you were performing greater than conditionals to multiple columns, it would be beneficial to write it the second way:
db.users.find({ $gt: { id: 1, name: 'Tom', createdAt: { $months_ago: 1 } } });Most of the default conditional helpers cascade and most (if not all) of the value helpers do not. Getting the cascade right is hard. So, when writing helpers, it may be beneficial to not turn cascading on.
TODO. For now, you can look at the Mongo-SQL readme and tests.