SQL and data requests

A data request runs SQL against a database or a data file and shows the rows in a table. It connects to PostgreSQL, MySQL and MariaDB servers, opens SQLite files, and loads CSV and JSON files into a temporary SQLite table so you can query them with SQL too. Use it to check what an API call actually wrote to the database, to look up test data, or, in the collection runner, to assert on the database state after a request.

Data requests show a brown SQL badge in the sidebar.

Run a query

1. In the API client, click New › Other Protocols › Data, or right-click a collection and choose Add Other Request › Data. Search Everything (⇧ ⌘ A) also has New Data Request.

2. Choose the source from the menu at the left: PostgreSQL, MySQL, SQLite, CSV or JSON.

3. Fill in the connection fields for that source (below).

4. Write your SQL in the editor, for example SELECT id, email FROM users ORDER BY id DESC LIMIT 20;.

5. Press ⌘ ↩ or click Run. The first run connects and loads the schema; the results appear below the editor.

123456

A new data request: source and connection on top, schema on the left, SQL and results on the right.

1. Source: PostgreSQL, MySQL, SQLite, CSV or JSON
2. Connection fields; for files, a path or https:// URL
3. Run (⌘↩)
4. Schema tree, loaded on the first run
5. SQL editor
6. Results

×

Connection fields

PostgreSQL and MySQL

Field	Default	What it is
host	127.0.0.1	The server’s host name or IP address. {{variables}} work.
port	Empty	Leave empty for the default: 5432 for PostgreSQL, 3306 for MySQL.
database	Empty	The database to connect to.
user	Empty	The user name.
password or {{vault:db}}	Empty	The password, masked. Use a vault reference.
TLS	TLS: prefer	TLS: disable never uses TLS. TLS: prefer tries TLS and falls back to an unencrypted connection if the server refuses. TLS: require fails the connection if TLS can’t be set up.

Supported sign-in methods:

Server	Authentication
PostgreSQL	Trust, cleartext password, MD5 and SCRAM-SHA-256.
MySQL and MariaDB	mysql_native_password, caching_sha2_password and sha256_password. Over a connection without TLS, Cloak Proxy encrypts the password with the server’s public key when the method needs it.

Connecting gives up after 10 seconds.

SQLite

Field	What it is
File path or https:// URL	A local .sqlite or .db file, or click Choose… to pick one. ~ is expanded and {{variables}} work.

A local file is opened for reading and writing, so INSERT, UPDATE and DELETE change it. If the file doesn’t exist, an empty database is created at that path. Type :memory: for a temporary in-memory database. A file at an http:// or https:// URL is downloaded to a temporary file and opened read-only; the copy is deleted when the connection closes.

CSV and JSON

Field	What it is
File path or https:// URL	A local file, a file:// URL, or an http:// or https:// URL to download. Click Choose… to pick a file. {{variables}} work.

Cloak Proxy reads the file into an in-memory SQLite database with one table, so you query it with SQLite’s SQL. The file itself is never changed.

• Table name: taken from the file name, lowercased, with anything that isn’t a letter or digit turned into _. Sales Q1 2024.csv becomes sales_q1_2024.
• CSV: the delimiter is detected from the first lines (comma, semicolon, tab or pipe), the first row is used as column names, and quoted fields, doubled quotes and line breaks inside quotes are handled. A column with no name becomes column_1, column_2 and so on.
• JSON: an array of objects, an object that wraps one such array (like { "data": [ … ] }), a single object, or newline-delimited JSON. The columns are every key that appears, in first-seen order. Nested objects and arrays are stored as JSON text.
• Column types: a column whose values are all whole numbers is INTEGER, all numbers is REAL, and anything else is TEXT, so WHERE price > 10 compares numbers.

Query a CSV file

SELECT region, SUM(amount) AS total
FROM sales_q1_2024
GROUP BY region
ORDER BY total DESC;

Browse the schema

After the first run, the left pane shows the source as a tree: database, schemas, tables and views, and columns with their types. Primary key columns have a key icon. Double-click any name to insert it into the SQL editor at the end of the query.

The connection stays open between runs, so later queries are fast. Click Disconnect (next to Run) to close it, for example after changing the connection fields; the next run connects again with the new settings.

Read the results

Each statement in the editor produces its own result. When there are several, a Result 1, Result 2 menu switches between them.

Part	What it shows
Summary line	Row count, rows affected (for INSERT, UPDATE and DELETE), how long the query took, and the command, such as SELECT 20 or UPDATE 3.
Column headers	Each column’s name and type.
Rows	Up to 2,000 rows. NULL is shown dimmed. Cells are selectable.
Copy CSV	Copies the whole result as CSV.
Copy JSON	Copies the whole result as a JSON array of objects.

Errors from the server appear in red above the results, with the server’s message.

Large numbers keep their exact digits, so NUMERIC and BIGINT values don’t lose precision.

Run data requests in the collection runner

The collection runner and cloakctl connect, run the SQL, and disconnect, turning the result into an HTTP-style response so tests can check it:

Response part	Success	Query error
Status	200 OK	400 Query Failed
Body	The rows of the last statement that returned rows, as a JSON array of objects keyed by column name. An empty array when no statement returned rows.	{ "error": "…" } with the database’s message.
x-sql-rows	The number of rows in the body.
x-sql-rows-affected	Rows affected, when the database reports it.
x-sql-command	The command tag, such as SELECT 20.

Values become JSON numbers, strings, booleans and null; binary values become base64 strings. A connection failure, such as a wrong password or an unreachable host, is reported as an error with no response.

Data requests have no Scripts tab of their own. Put tests in the collection’s or a folder’s post-response script, and check pm.info.requestName for tests meant for one request. A common pattern is an HTTP request that creates a record, followed by a data request that checks it:

Collection post-response script

if (pm.info.requestName === "Check user row") {
  pm.test("user was saved", () => {
    const rows = pm.response.json();
    pm.expect(rows).to.have.lengthOf(1);
    pm.expect(rows[0].email).to.eql("ada@example.com");
  });
}

{{variables}} set by earlier requests work in the SQL, for example WHERE id = {{userId}}. They’re pasted into the SQL as text, so only use variables whose values you control.

Point it at test databases

Data requests run any SQL you write, including DELETE and DROP, with the permissions of the user you sign in as. Use a test database or a read-only database user, never production credentials, and only query databases you’re allowed to access.

Database passwords

The connection fields, including the password field, are saved with the request in your workspace files. Store the password in the vault and type {{vault:db}} (or your entry’s name) in the field, so it stays in your Keychain and out of Git and exports. Prefer TLS: require for databases outside your Mac.

Related

Collection runnerCheck database state after API calls.

Variables and environmentsVault references for database passwords.

Datasets and data filesDrive runs from CSV and JSON test data.

Scripts and testsAssert on rows with the pm API.