{"id":19149,"date":"2015-05-20T14:28:11","date_gmt":"2015-05-20T08:58:11","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=19149"},"modified":"2016-08-29T10:12:00","modified_gmt":"2016-08-29T04:42:00","slug":"connect-to-postgresql-using-javascript","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/connect-to-postgresql-using-javascript\/","title":{"rendered":"Connect to PostgreSQL using JavaScript"},"content":{"rendered":"

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

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.<\/p>\n

1. Make sure postgres dependency is installed. If not you can do this by using following command:
\n
\nnpm install pg or npm install \u2013g pg<\/strong>
\n<\/code>
\n2. Include the dependency into your code by:
\n
\nvar pg = require(\u2018pg\u2019);<\/strong>
\n<\/code>
\n3. Provide connection string for the postgreSQL client, port generally is default one i.e. 5432:
\n
\nvar connectionString = \"postgres:\/\/userName:password@serverName\/ip:port\/nameOfDatabase\";<\/strong>
\n<\/code>
\n4. Instantiate the client for postgres database:
\n
\nvar pgClient = new pg.Client(connectionString);<\/strong>
\n<\/code>
\n5. Connect to database by using following command:
\n
\npgClient.connect();<\/strong>
\n<\/code>
\n6. Execute the query using following statement:
\n
\nvar query = pgClient.query(\"SELECT id from Customer where name = 'customername'\");<\/strong>
\n<\/code>
\n7. Get the result set using:
\n
\nquery.on(\"row\", function(row,result){
\nresult.addRow(row);
\n});<\/strong>
\n<\/code>
\n8. You can delete row(s) by using the following piece of code:
\n
\nquery.on(\"end\", function(result){
\nif(result.rows[0] === undefined){
\nreturn;
\n}
\nelse{
\nvar id = result.rows[0].id;
\nvar query = \"delete from CustomerAddress where customer_id = \" + id ;
\npgClient.query(query);
\n}
\npgClient.end();
\n});<\/strong>
\n<\/code>
\nSteps 1, 2, 3, 4, 5 and 6 are pretty straight forward.
\nPoint 6 and 7 need a bit of explanation:<\/p>\n

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

The end<\/strong> 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\u2019s connection using the end()<\/strong> method on the pgClient object.<\/p>\n

You might need to open the port for outside connections for making calls to database through port 5432<\/strong>(being default port for postgreSQL) if you are connecting to the database remotely.<\/p>\n","protected":false},"excerpt":{"rendered":"

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) […]<\/p>\n","protected":false},"author":186,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":349},"categories":[1818],"tags":[1561,4843,55,1177,942],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/19149"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/186"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=19149"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/19149\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=19149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=19149"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=19149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}