Query datasets with SQL in Postman

A view is a SQL query over your dataset’s data sources, which can include both data files (CSV, JSON, or spreadsheets) and live databases. When you add a data file, Postman makes it queryable as a table, so you can filter, combine, and reshape the data with SQL, as you can with databases. A view can also span multiple data sources, so you can join that file to a live database or to other files and return one combined result.

Your tests, scripts, and mocks read from a view, not from the file or database underneath it. You can reshape or replace a source without updating them, as long as the view still returns the fields they expect. You can also define several views over a single source to expose different subsets of rows and columns. This means you don’t need to duplicate or re-upload the data for each scenario.

Views use SQLite-compatible SQL syntax and functions, whether a source is a data file or a MySQL, Postgres, or SQL Server database.

Custom JDBC data sources always use the native SQL dialect of the connected database. SQLite syntax isn’t available and each view must target a single JDBC source.

The following examples show common ways to use views with different data sources.

About the example dataset

In the following examples, assume you have a dataset with the following data sources:

• A local CSV file named users with the following data:

  userId,firstName,lastName,email
  1,John,Doe,john.doe@example.com
  2,Jane,Smith,jane.smith@example.com
  3,Bob,Johnson,bob.johnson@example.com

• A MySQL database named orders with the following data:

  | orderId | userId | amount |
  |---------|--------|--------|
  | 101     | 1      | 50.00  |
  | 102     | 2      | 75.00  |
  | 103     | 3      | 25.00  |

In a view, reference each data source by its name, used as the table name. In these examples, the users file’s source is named source_users and the orders database’s source is named orders.

Select all data

You can create a view that selects all rows and columns.

SELECT * FROM source_users;

Filter rows

You can filter rows to return only the data your workflow needs.

SELECT email, firstName, lastName
FROM source_users
WHERE userId = '2';

Create new columns

You can create new columns using expressions and aliases in your query. This is useful when your tests or mock servers need values derived from existing data.

SQLite example:

SELECT firstName, lastName, firstName || ' ' || lastName AS fullName
FROM source_users;

MySQL example:

SELECT orderId, amount, CONCAT(orderId, ' ', amount) AS orderSummary
FROM orders;

Join multiple data sources

You can combine data from multiple data sources, such as a local CSV file and a MySQL table. This is useful when you want a consolidated view of data that’s stored in different places.

SELECT source_users.userId, source_users.firstName, source_users.lastName, orders.orderId, orders.amount
FROM source_users
JOIN orders ON source_users.userId = orders.userId;