Skip to content
Postman API Testing Tips

How To Use Postman To Get Data From Database Pgadmin

API Testing Blog

Accessing Your Database with pgAdmin

Before we dive into using Postman, let’s ensure you have the necessary setup:

  1. pgAdmin: Download and install pgAdmin (https://www.pgadmin.org/) - the popular graphical administration tool for PostgreSQL databases.
  2. PostgreSQL: To use pgAdmin, you must have PostgreSQL installed on your machine. If you don’t have it, install it from here: https://www.postgresql.org/download/
  3. Postman: Get it from https://www.postman.com/.

Understanding Your Database Structure

First, we need to understand the data we want to retrieve. Imagine a basic “users” table in pgAdmin, with columns for “id”, “username”, and “email”:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

Setting Up Your Postman Request

Now, let’s use Postman to access these users:

1. Creating a Database Connection in Postman

Although Postman can be used for API testing and not directly as a database access tool, there are two ways to achieve this:

  • Using a proxy:

    • First, ensure your PostgreSQL server allows external connections.
    • Identify your PostgreSQL host address, port (usually 5432), database name, username, and password.
    • Configure a proxy server like ngrok to expose your local PostgreSQL database to the internet.
    • In Postman, use the proxy server’s URL and credentials in your request.
    • This approach is helpful when your PostgreSQL instance isn’t directly accessible.

  • Using an intermediary API:

    • You can build a small API endpoint using a language like Python or Node.js that interacts with your PostgreSQL database.
    • This API would receive requests from Postman and execute database queries.
    • The results would then be sent back to Postman in a format it can understand, such as JSON.
    • This offers a more structured approach and allows for better control over the data returned.

2. Defining Your SQL Query

In Postman’s Body tab, use the following syntax to construct your SQL query:

{
  "query": "SELECT * FROM users"
}

Sending Your Request

  1. Request Type: Choose POST for this example, as we’re sending data (the SQL query) to the database.
  2. Headers: Include the Content-Type header with the value application/json to indicate that you are sending JSON data.
  3. Authorization: Depending on your preferred method, use your authentication details (like token, basic auth, etc.) in the Authorization header.
  4. Send Request: Click the “Send” button.

Interpreting the Response

Postman should display the response from your database server. You should see the retrieved user data in a format like JSON:

[
  {
    "id": 1,
    "username": "john.doe",
    "email": "john.doe@example.com"
  },
  {
    "id": 2,
    "username": "jane.smith",
    "email": "jane.smith@example.com"
  }
]

Example: Filtering Data

Let’s refine our query to retrieve specific users.

1. Modifying the SQL Query

Change your request body to:

{
  "query": "SELECT * FROM users WHERE username = 'john.doe'"
}

2. Resending the Request

Click Send again. This time, the response should only include data for the user with the username “john.doe”.

Conclusion: Expanding Your Postman Usage

Postman, in combination with a proxy server or an intermediary API, provides a convenient way to access your PostgreSQL database. You can:

  • Utilize different SQL queries: retrieve data based on various conditions, order, and sorting using WHERE, ORDER BY, LIMIT, and more.
  • Insert, update, or delete data: Use INSERT, UPDATE, and DELETE queries to manipulate your database records.
  • Work with complex database structures: Query across multiple tables using JOINs to retrieve related data.

Remember, the key is understanding how to structure your SQL queries and how to send them effectively through Postman.

API Testing Blog