Minimal ORM for mysql, postgresql and sqlite with complete arbitrary SQL query support (based on brianc's query builder sql)


Minimal ORM for mysql, postgresql and sqlite with complete arbitrary SQL query support.

Based on the node-sql query builder and node-anydb connection pool

examples and usage:

Initializing an instance also creates a connection pool. The url argument is the same as in node-anydb

var anydbsql = require('anydb-sql');
var db = anydbsql({
    url: 'postgres://user:pass@host:port/database',
    connections: { min: 2, max: 20 }

Defining a table is the same as in node-sql:

var user = db.define({
    name: 'users',
    columns: {
        id: {primaryKey: true},
        email: {},
        password: {}

You can also add properties to the table that are based on relationships between tables by adding a has property

var user = db.define({
    name: 'users',
    columns: { ... }
    has: {
        posts: {from: 'posts', many: true},
        group: {from: 'groups'}
// user.posts is now a "subtable" 

Read about joins and subobjects to see how you can use subtables with selectDeep

Queries have all the methods as in node-sql, plus the additional methods:

  • exec(function(err, rows)) - executes the query and calls the callback with an array of rows
  • all - same as exec
  • get(function(err, row)) - executes the query and returns the first result
  • execWithin(transaction, function(err, rows)) - execute within a transaction
  • allWithin(tx, cb), getWithin(tx, cb) - all/get within a transaction.
  • selectDeep(args) - deeply select join results (with grouping). Arguments can be fields, tables or subtables (created with relationships). More info in the section joins and subobjects below.

If you omit the callback from a querying method, a promise will be returned instead.

Use regular node-sql queries then chain one of the querying methods at the end:

user.where({email: email}).get(function(erruser) {

Join queries can be constructed using node-sql. The format of the results is the same as with anydb, post.content)
  .all(function(erruserposts) {
    // res[0].name and res[0].content 

When creating join queries, you can generate sub-objects in the result by using selectDeep

  .selectDeep(, post.content)
  .all(function(errres) {
    // res[0] and res[0].post.content 

With selectDeep you can also utilize has relationships to get full-blown result structures:

  .selectDeep(,, user.posts)
  .all(function(errres) {
    // res[0] is 
    // { id: id, name: name, posts: [postObj, postObj, ...] } 

selectDeep can accept tables, their fields, their has relationships, relationship fields, relationships' relationships etc (recursively)

    .selectDeep(,,, user.posts.content,
        user.posts.comments).all(function(errres) {
            // res[0] is 
            // {id: id, name: name: posts: [ 
            //     {id: pid, content: content, comments: [commentObj, ...]}, 
            //     {id: pid, content: content, comments: [commentObj, ...]}, 
            //     ... 
            // ]} 

To create a transaction and execute queries within it, use db.begin()

Execute constructed queries within that transaction using execWithin, getWithin or allWithin

var tx = db.begin()
user.insert({name: 'blah'}).returning(;
user.insert({name: 'bleh'}).returning(;
user.where({name: 'blah').getWithin(tx, function(errres) {
    // the user is there! 

When using promises, you can also use the safer API:

db.transaction(function(tx) { ... })

and you will get autocommit / autorollback depending on whether the promise returned within the passed function is fulfilled or rejected.

Transactions have the same API as anydb tranactions, but they're extended with the following methods:

Transactions support savepoints

var sp = tx.savepoint();

Will cause the queries executed within the transaction to be logged. This method should be useful for debugging purposes. The parameter is a boolean.

query building syntax

For more info on how to build queries, look at the node-sql test samples and their corresponding SQL


You can close the connection pool using db.close



To execute custom queries, use db.query

db.query(...anydb arguments...)

db.functions and db.makeFunction

db.makeFunction allows you to create a new function supported in the database.

db.functions contains a couple of predefined, common functions.


var max = db.functions.MAX
var avg = db.makeFunction('AVG');
var q ='maxage'), avg(user.age).as('avgage'));