Database query and validation using Postman

After only few days of using Postman, once we were done with exploring the basic Postman features, felt a need to query the application database. We were exposing Rest endpoints for many modules of our enterprise application and validating data persistence after a POST call was a necessity. Other API testing tools (SoapUI/JMeter) we were using had this capability and we were expecting this in Postman as well. After much googling realized there is no direct way to do database query in Postman.

So we thought of creating a Restful service to perform the database query and calling that service from our Postman collection. We wrote a simple node.js program to run as a Restful service in an express server. We were anyway using node.js to run our collection using Newman - so no additional requirement of installing new software/managing a server to host the service using this approach. Given below the steps to achieve our objective:

Step 1: Install node.js if not already installed.

[Download relevant installer from https://nodejs.org/en/download/ and install]

Step 2:

Install 'express', 'mysql' and 'body-parser' using below commands from command prompt:

npm install express

npm install mysql

npm install body-parser

Step 3:

Save the below program in a directory naming it "connect_mysql.js":

var express = require('express');
var app = express();
const bodyParser = require('body-parser');
var mysql = require('mysql');

app.use(bodyParser.urlencoded({ extended: true }));

app.post('/sendQuery', function (req, res) {
   console.log('Host:', req.body.host)
   console.log('User:', req.body.user)
   console.log('Password:', req.body.password)
   console.log('Query:', req.body.query)
 
   var connection = mysql.createConnection({
   host     : req.body.host,
   user     : req.body.user,
       password : req.body.password,
       database : req.body.database
});

connection.query(req.body.query, function(err, rows, fields) {
   if (!err){
   console.log('Result Set: ', rows);
   res.send(rows)
   }
   
   else
     console.log('Error while performing Query.');
}); 

})

var server = app.listen(8081, function () {
  var host = server.address().address
  var port = server.address().port
  console.log(host)
  console.log("Example app listening at port %s", port)
})

Step 4:

Run the program to start the service from command prompt:

node connect_mysql.js

[We created a batch file to launch Postman and start this service together]

Step 5:

In Postman add a POST request to http://localhost://8081/sendQuery with "x-www-form-urlencoded" payload as shown below:

Send the POST request.

This is how the response looks like:

And we can validate the result writing tests parsing the response body like below:

Hope this will help some of you to perform database validation from Postman/Newman. Thank you.