pgsql-api

PostgreSQL client for Nodejs

This node module provides a set of methods to interact with PostgreSQL databases.

The module was created as part of KidoZen project, as a connector for its Enterprise API feature.

Installation

Use npm to install the module:

> npm install node-pgsql-api

Runing tests

Use npm to run the set of tests

> npm test

API

Due to the asynchrounous nature of Nodejs, this module uses callbacks in requests. All callbacks have 2 arguments: err and data.

function callback (err, data) {
	// err contains an Error class instance, if any
	// data contains the resulting data
} 

Constructor

The module exports a class and its constructor requires a configuration object with the following properties

• host: Required string. PostgreSQL's host name. By instance: localhost
• port: Required integer. PostgreSQL's port. By instance: 5432
• database: Required string. PostgreSQL's database name. By instance: MyAppDB
• timeout: Optional integer for the session timeout in milliseconds. Default 15 minutes.
• username: Optional String. PostgreSQL's username.
• password: Optional String. User's password.

var PgConnector = require("node-pgsql-api");
var pg = new PgConnector({host: 'localhost', 
							port: 5432, 
							database: 'TestDB',
                            username: 'postgres',
                            password: 'postgres'};
});

Authentication

Username and Password Login

If no username and password were provided during the creation (instantiation) of the PostgreSQL connector, they will be required in the authorization method or in the query method.

Methods

All public methods have the same signature, their have two arguments: options and callback.

• options must be an object instance containig all parameters for the method.
• callback must be a function.

authenticate(credentials, callback)

This method should be used for authenticate user's credentials. A successed authentication will return an object intance containing the auth property. The value of this property is the athentication token that could be required by other methods.

Parameters:

• credentials: A required object instance containing authentication's parameters:
  • username: A string with a PostgreSQL's user name.
  • password: A string containing the user's password.
• callback: A required function for callback.

var PgConnector = require('node-pgsql-api');
var con = new PgConnector({host: 'localhost', 
                            port: 5432, 
                            database: 'TestDB'};
con.authenticate({username: 'postgres', 
                    password: 'postgres'}, 
                    function(error, authToken){
                        //error should be null if everything went ok. The error code otherwise.
                        //authToken is your authorization token.
                    });

#####Access Token (authorization token)

After the authenticate API call, you get the access token as the 2nd parameter in the callback. This token allows you to reuse the existing connection for the username/password provided.

query(data, callback)

This method executes the SQL query. It can be any valid SQL (DDL or DML). It can even start a new transaction, commit an active one or rollback it. It should be invoked with an authorization token or with a username/password credential.

Parameters:

• data: A required object instance containing the SQL sentence.
  • sql: A required string with the SQL sentence. It can have placeholders to avoid SQL injection attacks.
  • values: An optional array with the same arity as many placeholders the SQL sentence has.
  • username: An optional string with a PostgreSQL's user name.
  • password: An optional string containing the user's password.
  • auth: An optional string containing the authorization token
• callback: A required function for callback.

var data = {sql: "select * from products",
            auth: "550e8400-e29b-41d4-a716-446655440000"};

            con.query(data, function(error, result){
                //error should be null if everything went ok. The error code otherwise.
                //result it's an object with the query's result
            });