Database Integration with Postman and xmysql

In backend automation and testing, we usually face this issue where we want to validate things in both API response and from the database, but doing so is quite a time taking and tiresome activity, a lot of manual effort is required in this process. In backend automation especially this becomes the bottleneck in many scenarios.

Postman?is a collaborative API development and testing tool which takes care of both manual and automated API testing, It is used for backend testing where we enter the end-point URL, send the request to the server, and receive the response back from the server.

The problem is that Postman uses HTTP Protocol to communicate, whereas DB will have DB protocol. So we need a Middleware to communicate from Postman to DB directly. So this is how it works.

Backend automation using Postman

Postman provides you the capability to easily create a correlation of different APIs and create an automation suite out of it and this will become very handy and helpful for QAs and even developers to quickly automate the regression and smoke suites out of it

Database Integration with Postman

Database integration is necessary for Postman because it enables you to test the functionality and performance of APIs that interact with a database. By integrating Postman with your database, you can verify that your APIs are able to retrieve, insert, update, and delete data as expected.

Tools for database connectivity

There are several tools that can be used for database connectivity in Postman, including:

Xmysql,?Apache Drill,?etc.

I am choosing?Xmysql?over other tools for database connectivity in Postman, the specific reason for choosing?Xmysql,?we can easily and quickly generate a REST API on top of our MySQL database, therefore Instead of querying a database directly, developers and testers can use Postman or any alternate tools to read and write to a database via a REST API.

Xmysql?is a Node.js tool that provides a simple and convenient way to connect Postman to a MySQL database. Xmysql generates REST APIs based on your MySQL database, allowing you to access and manipulate your data using simple HTTP requests. Xmysql provides a fast and efficient way to connect Postman to a MySQL database and perform operations such as retrieving, inserting, updating, and deleting data.

Pre-Requisite for Xmysql:

1. Node.js: Xmysql is built on Node.js, so you need to have?Node.js?installed on your system.
2. MySQL database: You need to have a MySQL database set up and running. You will also need to have access to the database and the necessary credentials, such as the username and password, to connect to it.
3. Postman: Xmysql is designed to be used with Postman, so you must install Postman on your system.

Getting Started ??

Steps:?Here are the steps to use Xmysql with Postman

Install Xmysql

• Open the command-line interface (CLI) and run the following command to install?Xmysql:

npm install -g xmysql        

• Connect to the MySQL database: You will be prompted to enter the database credentials, such as the hostname, port, username, password, and database name, respectively.

You need to connect to your MySQL database using the following?command:

xmysql -h host -o port -u mysqlUsername -p mysqlPassword -d databaseName        

• Open a web browser and navigate to?https://localhost:3000. You should see the xmysql interface, which allows you to browse your MySQL database and execute SQL queries.
• Open postman and create a new request. Set the request URL to?https://localhost:3000/api/db_table and set the request method.

That’s it! You can use Postman to make any SQL query to your MySQL database through xmysql.

Available APIs

xmysql provides a wide range of APIs to interact with your database, it provides a similar interface as any database client in correspondence of any HTTP protocol like,

• For selecting data from a specific table, you can simply write,

/api/tableName

• For updating data from a specific table, you can simply write,

/api/tableName

• Inserting data from a specific table, you can simply write,

/api/tableName

• Deleting data from a specific table, you can simply write,

/api/tableName/:id

Apart from the above, there are plenty of other APIs that xmysql provides, you can visit them?here

Let me know if this was helpful. If you ever need my help, you can write in the comments section.

Reference:

• https://documenter.getpostman.com/view/5922408/RznJmGfn#3ea22b15-8801-4817-a426-93878438702f
• https://github.com/o1lab/xmysql