A lightweight PostgreSQL data mapper that likes SQL.


A lightweight PostgreSQL data mapper that likes SQL.

To install

npm install mapper-pg --save

To test it

npm install -d
make test

To run Backbone example

make test             # creates the necessary database and table
node example/app.js   # runs the server, browse http://localhost:3000
  • Transaction support

See comprehensive tests in test/integration/integrationTest.js.

Require it

var Mapper = require('mapper-pg');

Define Data Access Objects (DAO) for each table

// simple, only table name with optional primary key
var Comment ='Comments');
var Post ='Posts', 'id');

Define relationships beetween DAOS, see lib/relation.js

Post.hasMany('comments', Comment, 'postId');
Comment.belongsTo('post', Post, 'postId');


// set `verbose`  to trace SQL, set `strict` for invalid column warnings
var config = { user: 'boo', password: 'secret', database: 'app_dev', verbose: true, strict: false };

Mapper.initialize(config, function(err) {
    // setup express, etc


var insertId;

// insert a new post
Post.insert({title: 'First Post'}).exec(function(err, result) {
    insertId =;

// OR sugar
Post.create({title: 'First Post'}, function(err, result) {
    insertId =;


// select inserted post
Post.where({id: insertId}).one(function(err, post) {
    assert.equal(post.title, 'First Post,');

// OR sugar
Post.findById(insertId, function(err, post) {});


// update inserted post
  .update()                         // optional since set() is used
  .set({title: 'New Title'})
  .where({id: insertId})
  .exec(function (err, result) {
    assert.equal(result.rowCount, 1);

// OR sugar, updates based on id{title: 'New Title', id: insertId}, function(err, result) {});


// delete all posts with a specific title
Post.delete().where({title: 'New Title'}).exec(function(err, result) {
    assert.equal(result.rowCount, 1);

// OR sugar
Post.deleteById(insertId, function(err, result) {});

Gets the first page of posts and populate comments property with the second page of comments for each post retrieved.

  .select('id', 'title', 'excerpt')
  .page(0, 25)
  .order('id DESC')
  .load('comments', function(c) {'comment', 'created_at')
     .order('id DESC')
     .page(1, 50);
  .all(function(err, posts) {
    // boo-yah!

Or, mix SQL

var sql = ('SELECT id, title, excerpt FROM Posts ORDER BY id DESC LIMIT 25';

Post.all(sql, function(err, posts) {
  Post.load('comments', function(c) {
    c.sql('SELECT comment, createdAt FROM Comments ORDER BY id DESC LIMIT 50 OFFSET 50');
  }).in(posts, function(err) {
    // boo-yah!

Execute multiple statements in a series

  'SELECT * FROM posts WHERE author = ?;', [1],

  // use commas to break up SQL
  'SELECT * ',
  'FROM comments WHERE author = ?;', [1]
], function(err, results) {
    // posts are in results[0][0..n]
    // comments are in results[1][0..n]

Execute multiple statements in parallel

  'SELECT * FROM posts WHERE author = ?;', [1],
  'SELECT * FROM comments WHERE author = ?;', [1],
], function(err, results) {