architect-pg-pool

architect postgresql connection pool

architect-pg-pool

Expose a posgresql connection pool as architect plugin.

npm install --save architect-pg-pool
module.exports = [{
    packagePath: "architect-pg-pool",
    url: 'postgresql://postgresuser:postgrespwd@localhost:5435/dbname',
    checkOnStartUp : true
}];
  • url : Defines the postgres url to use for connection
  • checkOnStartUp : Defines if we must check connection validity on startup default is false.

Boot Architect :

var path = require('path');
var architect = require("architect");
 
var configPath = path.join(__dirname, "config.js");
var config = architect.loadConfig(configPath);
 
architect.createApp(config, function (errapp) {
    if (err) {
        throw err;
    }
    console.log("app ready");
});

Configure Architect with config.js :

module.exports = [{
    packagePath: "architect-pg-pool",
    url: 'postgresql://postgresuser:postgrespwd@localhost:5435/dbname'
}, './routes'];

Consume db plugin in your ./routes/package.json :

{
  "name": "routes",
  "version": "0.0.1",
  "main": "index.js",
  "private": true,
 
  "plugin": {
    "consumes": ["db"]
  }
}

Eventually use pg connection in your routes ./routes/index.js :

module.exports = function setup(optionsimportsregister) {
    var rest = imports.rest;
    var db = imports.db;
 
    // register routes  
    rest.get('/hello/:name', function (reqresnext) {
        db.connection(function (errclientdone) {
            client.query('SELECT * FROM Users WHERE id=$1', [req.params.name], 
                function(errres){
                    done();
                    res.write("{'message':'hello," + res.rows[0].name + "'}");
                    res.end();
            });
        });
    });
    
    register();
};

This module supports multiple pools.

Here is how to define 2 different pools :

module.exports = [{
    packagePath: "architect-pg-pool",
    first : {
        url: 'postgresql://postgresuser:postgrespwd@localhost:5435/dbname'
    },
    second : {
        url: 'postgresql://postgresuser:postgrespwd@localhost:5432/otherdb'
    },
    checkOnStartUp : true
}];

This will create 2 properties (first and second) in the db object.

module.exports = function setup(optionsimportsregister) {
    var db = imports.db;
    db.first.connection(function (errclient) {
      client.query(/*...*/);
    });    
    register();
};

A pool can be marked as default and will be available in db.connection. Here is how to define 2 different pools with the second as default :

module.exports = [{
    packagePath: "architect-pg-pool",
    first : {
        url: 'postgresql://postgresuser:postgrespwd@localhost:5435/dbname'
    },
    second : {
        url: 'postgresql://postgresuser:postgrespwd@localhost:5432/otherdb',
        'default' : true
    },
    checkOnStartUp : true
}];

This will create 2 properties (first and second) in the db object.

module.exports = function setup(optionsimportsregister) {
    var db = imports.db;
    // this will use second pool 
    db.connection(function (errclient) {
      client.query(/*...*/);
    });
    // second pool is also available 
    db.second.connection(function (errclient) {
      client.query(/*...*/);
    });
    register();
};

The pool object (db) has the following methods :

Retreive a connection from the pool. The method takes a callback as parameter. Once the connection is avaliable the callback is called with an :

  • err object if an error occured or null;
  • client the pg client object;
  • done, the close method.

The query method let you directly query the database without worrying about the database connection. Behind the scene the method retreive a connection from the pool and close it afterward. The method signature is similar to node-pg query.

  • string text: the query text;
  • optional array parameters: the query parameters;
  • optional function callback : the function called when data is ready.

Once the data is ready the callback is fired with an :

  • err object if an error occured or null;
  • rows the pg result set.

The queryStream method let you directly query the database without worrying about the database connection. This method passes a stream to the callback instead of a resultset. Behind the scene the method retreive a connection from the pool and close it afterward. The method signature is similar to node-pg query-stream.

  • string text: the query text;
  • optional array parameters: the query parameters;
  • optional function callback : the function called when stream is ready.

Once the stream is ready the callback is fired with an :

  • err object if an error occured or null;
  • stream the pg result stream.