Connect to PostgreSQL using JavaScript

One of the best practices of Automation testing is to leave the system from the state where you started your test from. While doing functional testing for an angularjs application using Protractor and Cucumber (BDD framework), there was a need to clean up whatever data was being generated in the Data setup (generally the Given) of the test after the test finishes.

Following is an example on how to connect to PostgreSQL using JavaScript. Here we try to delete all information from CustomerAddress table about a customer which was populated during the test.

1. Make sure postgres dependency is installed. If not you can do this by using following command:

npm install pg or npm install –g pg

2. Include the dependency into your code by:

var pg = require(‘pg’);

3. Provide connection string for the postgreSQL client, port generally is default one i.e. 5432:

var connectionString = "postgres://userName:password@serverName/ip:port/nameOfDatabase";

4. Instantiate the client for postgres database:

var pgClient = new pg.Client(connectionString);

5. Connect to database by using following command:

pgClient.connect();

6. Execute the query using following statement:

var query = pgClient.query("SELECT id from Customer where name = 'customername'");

7. Get the result set using:

query.on("row", function(row,result){
result.addRow(row);
});

8. You can delete row(s) by using the following piece of code:

query.on("end", function(result){
if(result.rows[0] === undefined){
return;
}
else{
var id = result.rows[0].id;
var query = "delete from CustomerAddress where customer_id = " + id ;
pgClient.query(query);
}
pgClient.end();
});

Steps 1, 2, 3, 4, 5 and 6 are pretty straight forward.
Point 6 and 7 need a bit of explanation:

The row event is dispatched whenever a row is received from db and we use it to append into an array of rows which is passed to a rows property in the end event’s result object. If we do not call the result.addRow() method, the rows array would be empty in the end event.

The end event is dispatched when all rows have been returned by the query or when an error is encountered. Here we are first checking if the result is undefined or not. If not, delete the row in the table with same id and then close the client’s connection using the end() method on the pgClient object.

You might need to open the port for outside connections for making calls to database through port 5432(being default port for postgreSQL) if you are connecting to the database remotely.