mesa

mesa

mesa provides easy access to postgres databases

every call to a chainable configuration method (table, connection, attributes, where, ...) returns a new mesa object and doesn't change the state of the mesa object it is called on.

install

npm install mesa

configure

pg = require 'pg'
mesa = require 'mesa'

getConnection = (cb) -> pg.create 'tcp://foo@localhost/bar', cb

# the user object will be used in all following examples
user = mesa
    .table('user')
    .connection(getConnection)
    .attributes(['name', 'email'])
    .primaryKey('my_id') # optional, defaults to 'id'

connection() either takes a connection object or a function, which is supposed to take a callback and call it with a connection object. providing a connection object explictely is useful for transactions.

attributes() sets the properties to pick from data in create and update. it must be called before using create or update

command

insert

user.insert {
    name: 'foo'
}, (err, userId) -> # ...

insert multiple records

user.insertMany [
    {name: 'foo'}
    {name: 'bar'}
], (err, userIds) -> # ...

delete

user.where(id: 3).delete (err) -> # ...

where can take any valid criterion

update

user.where(id: 3).where(name: 'foo').update {name: 'bar'}, (err) -> # ...

multiple calls to where are anded together.

query

find the first

user.where(id: 3).first (err, user) -> # ...

where can take any valid criterion

test for existence

user.where(id: 3).exists (err, exists) -> # ...

find all

user.where(id: 3).find (err, users) -> # ...

select, join, group, order, limit, offset

user
    .select('user.*, count(project.id) AS project_count')
    .where(id: 3)
    .where('name = ?', 'foo')
    .join('JOIN project ON user.id = project.user_id')
    .group('user.id')
    .order('created DESC, name ASC')
    .limit(10)
    .offset(20)
    .find (err, users) ->

mesa uses mohair for where, select, join, group, order, limit and order. look here for further documentation.

associations

has one

use hasOne if the foreign key is in the other table (address in this example)

user.hasOne 'address', address,
    primaryKey: 'id'                # optional with default: "id"
    foreignKey: 'user_id'           # optional with default: "#{user.getTable()}_id"

the second argument can be a function which must return a model. this can be used to resolve models which are not yet created when the association is defined. it's also a way to do self associations.

belongs to

use belongsTo if the foreign key is in the table of the model that belongsTo is called on (project in this example)

project.belongsTo 'user', user,
    primaryKey: 'id'                # optional with default: "id"
    foreignKey: 'user_id'           # optional with default: "#{user.getTable()}_id"

has many

use hasMany if the foreign key is in the other table (user in this example) and there are multiple associated records

user.hasMany 'projects', project,
    primaryKey: 'id'                # optional with default: "id"
    foreignKey: 'user_id'           # optional with default: "#{user.getTable()}_id"

has and belongs to many

use hasAndBelongsToMany if the association uses a join table

user.hasAndBelongsToMany 'projects', project,
    joinTable: 'user_project'       # required
    primaryKey: 'id'                # optional with default: "id"
    foreignKey: 'user_id'           # optional with default: "#{user.getTable()}_id"
    otherPrimaryKey: 'id'           # optional with default: "id"
    otherForeignKey: 'project_id'   # optional with default: "#{project.getTable()}_id"

include associated

associations are only fetched if you include them

user.includes(address: true)

includes can be nested (arbitrarily deep)

user.includes(shipping_address: {street: true, town: true}, billing_address: true, friends: {billing_address: true})

extending mesa

add your own functions

user = mesa.table('user')

user.activeAdmins = -> @where(visible: true, role: 'admin')

user.activeAdmins().find (err, activeAdmins) -> # ...

overwrite existing functions

when inserting a user, also insert his address. do both in the same transaction:

address = mesa
    .table('address')
    .attributes(['name', 'street', 'user_id']

user = mesa.table('user').attributes(['email', 'password'])

user.insert = (data, cb) ->
    @getConnection (err, connection) =>
        return cb err if err?

        connection.query 'BEGIN;', (err) =>
            return cb err if err?

            # do the original insert but on the transactional connection
            mesa.insert.call @connection(connection), data, (err, userId) =>
                return cb err if err?

                address = data
                address.user_id = userId

                # insert the address portion of data on the transactional connection
                address.connection(connection).insert data, (err) ->
                    return cb err if err?

                    connection.query 'COMMIT;', [], (err) ->
                        return cb err if err?
                        cb null, userId


user.insert {
    password: 'bar'
    email: '[e-mail]'
    name: 'foo'
    address: 'foostreet'
}, (err, userId) -> # ...

license: MIT