Bitbucket is a code hosting site with unlimited public and private repositories. We're also free for small teams!

Close

node-pg-query-builder

This is a fork of (node-db-query)[https://github.com/markselby/node-db-query]. But my focus is on the postgres query build only.

Create SQL queries programatically in Node.js. Loosely based on Rails' ActiveRelation.

Getting Started

Install the module with: npm install pg-query-builder

This example uses any-db, so you'll need to npm install any-db to run it.

Examples

var Query = require('pg-query-builder');
var anyDB = require('any-db');
var database = 'postgres://user@host:5432/database_name';

var dbPool = anyDB.createPool(database, {
  min: 5,
  max: 15,
  onConnect: function (conn, done) {
    done(null, conn);
  },
  reset: function (conn, done) {
    done(null);
  }
});

var options = { id: [1,2,3,4] };
// or options = { id: '1,2,3,4' };

var q = new Query(dbPool)
  .select('*')
  .from('users')
  .join('JOIN posts ON posts.user_id = users.id')
  .order('posts.updated_at DESC')
  .limit(10);

// Add some sample optional processing to the query
var food = 'cheese';

if (food) {
  // Get people by food
  q.join('JOIN foods ON foods.id = users.food_id')
  q.param(food);
  q.where('foods.name = ' + q.paramNo()); // This becomes : foods.name = $1
} else {
  // Get people by id(s)
  // q.ids is another form of q.param, but will join arrays and wraps the result in '{ }' braces.
  q.ids([1,2,3,4]); // q.ids('1,2,3,4'); as an alternative
  // This is using Postgres' ANY format rather than id IN blah because it's much more efficient
  q.where('id = ANY(' + q.paramNo() + '::int[])');
  // This becomes : id = ANY($1::int[]) 
}

// Prepare some event handlers and execute the query
q.on('row', function(row) { console.log(row); })
  .on('error', function(err) { console.log(err); })
  .on('end', function(data) { console.log(data); })
  .execute();

Contributing

In lieu of a formal styleguide, take care to maintain the existing coding style. Add unit tests for any new or changed functionality. Lint and test your code using Grunt.

Release History

v0.1.0

License

Copyright (c) 2013 Daisuke Yamashita Licensed under the MIT license.

Recent activity

Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.