How to Use Database Queries in API Tests with Apidog (MySQL, MongoDB, Redis)

A green status code can lie. Your POST /orders endpoint returns 201 Created, the response body looks correct, and the HTTP assertion passes—but did the row reach the database with the expected status? Did inventory decrement? HTTP-only tests verify what the API reported, not what the system persisted. Query the database to close that gap.

Try Apidog today

Database queries let a scenario seed known data, call an API, and verify the resulting state in one workflow. Apidog supports this with Database Connections and the Database Operation processor, so you can run SQL or NoSQL commands before and after HTTP requests without external scripts. If you need the request-level foundation first, see how to write a test scenario with Apidog. For relational-database fundamentals, MDN’s server-side overview is a useful primer.

What database operations add to API tests

An API test that never checks persistence is a black-box test: it trusts the response. That is often enough for contract validation, but it misses bugs such as:

• A status field that was never updated.
• A foreign key that references the wrong record.
• A soft-delete endpoint that hard-deletes data.
• A successful response returned before a write fails or is rolled back.

Database steps add three practical capabilities:

1. Seed a deterministic starting state so tests do not depend on leftover records.
2. Assert against persisted state rather than relying only on the response body.
3. Extract database-generated values for later requests.

In Apidog, configure a reusable connection under Settings > Database Connections, then add a Database Operation as either:

• A Pre Processor, which runs before the HTTP request.
• A Post Processor, which runs after the HTTP request.

Create a connection once and reuse it across scenarios in the project.

Plan support: MySQL, SQL Server (2014+), PostgreSQL, and Oracle are available on the free plan. ClickHouse, MongoDB, and Redis require a paid plan. The MySQL examples below work on the free tier; the MongoDB and Redis examples require paid access.

Step 1: Create a database connection

Open Settings > Database Connections, then click + New.

Select your database type and provide:

• Host, such as db.staging.internal or 127.0.0.1
• Port, such as 3306 for MySQL
• Username
• Password
• Database Name, such as shop

If the database is behind a bastion host, expand SSH Tunnel and configure the jump host.

For MySQL, choose an SSL mode:

• Prefer: attempts SSL, then falls back if unavailable.
• Require
• Verify CA
• Verify Full

Use the strictest mode supported by your server, then click Save.

MySQL 8 authentication issue

MySQL 8 commonly uses caching_sha2_password. If your connection fails with an authentication error, switch the test user to mysql_native_password:

ALTER USER 'tester'@'%'
IDENTIFIED WITH mysql_native_password BY '...';

See the MySQL reference manual for details about authentication plugins.

Connection credentials are local

Database connection details are stored locally and are not synced to the cloud project. Each teammate must configure their own connection. For team workflows, see sharing database connection settings.

Step 2: Seed test data with a Pre Processor

Assume you are testing order creation. Before calling POST /api/orders, seed a known active customer.

In the request:

1. Open Pre Processors.
2. Hover Add Database Processor.
3. Choose Database operation.
4. Name the step seed customer.
5. Select the MySQL connection.
6. Add an insert statement.

Use environment variables with {{variable_name}} syntax:

INSERT INTO customers (id, email, status)
VALUES ({{customer_id}}, '{{customer_email}}', 'active')
ON DUPLICATE KEY UPDATE status = 'active';

This setup makes the test self-contained:

• The customer exists.
• The customer is active.
• The ID is known before the API request runs.
• The scenario does not depend on execution order or existing database contents.

Step 3: Verify persistence with a Post Processor

Now call the API that creates an order:

POST /api/orders
Content-Type: application/json

{
  "customer_id": {{customer_id}},
  "items": [{ "sku": "APRON-01", "qty": 2 }]
}

Capture the returned order ID from the response into order_id using a normal response assertion.

Then add a database check:

1. Open Post Processors.
   • In DESIGN Mode, use the Run tab.
   • In DEBUG Mode, use the Request tab.
2. Hover Add PostProcessor.
3. Select Database operation.
4. Name it verify order row.
5. Choose the database connection.
6. Run this query:

SELECT id, status, total_cents
FROM orders
WHERE id = {{order_id}};

Database query results are returned as an array of objects. Extract the status from the first result:

• Variable Name: db_order_status
• JSONPath Expression:

$[0].status

Click Send, then inspect the Console to view the raw database result and extracted variable.

Finally, add an assertion that db_order_status equals pending:

db_order_status == pending

Now a response that returns 201 Created but persists status = 'draft' will fail the scenario.

Step 4: Reuse a database value in a later request

Database extraction is also useful when an API does not return a server-generated value.

For example, creating an order might generate an internal fulfillment_ref that is stored in the orders table but omitted from the API response. Query it after order creation:

SELECT fulfillment_ref
FROM orders
WHERE id = {{order_id}};

Extract the value with:

• Variable Name: fulfillment_ref
• JSONPath Expression: $[0].fulfillment_ref

Use it in the next request:

GET /api/fulfillments/{{fulfillment_ref}}

This follows the same chaining pattern as HTTP response variables, except the source is the database. See passing data between test steps and API test orchestration and passing data.

MongoDB and Redis: NoSQL variants

MongoDB and Redis use the same Database Operation concept, but their configuration differs from SQL databases. Both require a paid plan. See the Apidog documentation for the complete field reference.

MongoDB

Add a Database Operation and select MongoDB.

Instead of SQL, select an Operation Type:

• Find
• Insert
• Update
• Delete
• Run Database Command

For CRUD operations, Collection Name is required. Enter JSON in Query Condition:

{ "_id": "65486728456e79993a150f1c" }

Apidog automatically converts a matching ID string to an ObjectId.

When you need BSON-specific types, these helpers are supported:

ISODate(...)
ObjectId(...)
NumberDecimal(...)
NumberLong(...)

Refer to the MongoDB manual for the stored-value mappings. You can configure the connection with either a full connection string or individual host and credential fields.

The SQL workflow documents JSONPath extraction through Extract Result To Variable. MongoDB and Redis documentation does not describe the same extraction mechanism. Use NoSQL operations for setup and state verification, and verify any extraction behavior in the Console before relying on it in a scenario.

Redis

A Redis connection requires:

• Host
• Port
• Password
• Database Index

Visual operations support GET, SET, and DELETE.

To verify a cached session:

1. Select GET.
2. Set Key to a value such as user:session:123.

For commands not covered by the dropdown, use Run Redis Command:

KEYS user:*

Use this to confirm that an endpoint wrote a cache entry or to clear a key before testing whether the endpoint repopulates it.

Advanced patterns and limits

Iterate through rows

When using a ForEach step, reference the current item with:

{{$.StepID.element.field}}

Replace StepID with the actual loop step number. This is useful when asserting one database row per iteration.

Branch based on a database value

Extract a database status, then route the scenario based on that value. Combine database reads with conditional logic in API test scenarios to take one path for paid records and another for pending records.

Route safely by environment

Create one database connection per environment, such as local and staging. Apidog selects the matching connection when you switch the active environment.

Keep SQL operations straightforward

The visual interface does not support complex operations such as stored procedures. Use direct statements such as:

SELECT ...
INSERT ...
UPDATE ...
DELETE ...

Oracle requires a local client

Oracle requires the Oracle Client to be installed on your machine before Apidog can connect.

Handle database credentials per environment

Never point tests at production accidentally.

Create separate connections for each environment:

• local
• staging
• Any other isolated test environment

Each connection has its own host, credentials, and database name.

When you switch the active environment using the top-right dropdown, Apidog routes database operations to the matching connection automatically:

• Select staging: the scenario queries staging.
• Select local: the same scenario queries your local database.

The SQL and test steps do not need to change.

Because credentials are stored locally rather than synced into the shared cloud project, production passwords remain outside the shared project configuration.

Run database-backed scenarios in CI with the Apidog CLI

After the scenario passes locally, run it in CI so pull requests validate persistence as well as HTTP behavior.

Install the CLI and authenticate:

npm install -g apidog-cli
apidog login --with-token <YOUR_ACCESS_TOKEN>

Run a scenario by scenario ID and environment ID:

apidog run --access-token $APIDOG_ACCESS_TOKEN -t <scenario_id> -e <env_id> -r cli

Options:

• -t: test scenario ID
• -e: environment ID
• -r: reporter

Supported reporters include:

cli
html
junit

Use multiple reporters by comma-separating them:

apidog run --access-token $APIDOG_ACCESS_TOKEN -t <scenario_id> -e <env_id> -r html,cli

Database connections are local, so your CI runner needs the exported configuration required to reach the target database.

For data-driven execution, see data-driven testing with the Apidog CLI. To run scenarios on a schedule, see scheduling API tests with Apidog.

Frequently asked questions

Which databases are free and which require a paid plan?

MySQL, SQL Server (2014+), PostgreSQL, and Oracle are available on the free plan. ClickHouse, MongoDB, and Redis require a paid plan.

Try the free database integrations first by downloading Apidog.

Can I use a database value in a later request?

Yes. Add a Post Processor with a Database Operation, run a SELECT, and use Extract Result To Variable.

For example:

SELECT fulfillment_ref
FROM orders
WHERE id = {{order_id}};

Extract with:

$[0].fulfillment_ref

Then reference it later:

{{fulfillment_ref}}

For response-based chaining, see passing data between test steps.

Do teammates automatically receive my database connections?

No. Connection credentials are local to each client and are not synced to the cloud. Every teammate configures their own connection.

Why does my MySQL 8 connection fail?

MySQL 8 defaults to caching_sha2_password, which can prevent a connection. Switch the user to mysql_native_password:

ALTER USER ... IDENTIFIED WITH mysql_native_password;

Then reconnect.

Can I run stored procedures?

Not through the visual interface. Use direct SELECT, INSERT, UPDATE, and DELETE statements for database test steps.

Wrap up

Database operations turn an API test from “the response looked right” into “the persisted data is correct.”

A practical workflow is:

1. Seed a known state with a Pre Processor.
2. Call the API.
3. Query the resulting row with a Post Processor.
4. Assert persisted values.
5. Extract server-generated database values for downstream requests.
6. Configure separate connections for local and staging environments.

Start with one request: point a MySQL connection at your development database, create an order, and add a Post Processor that reads the created row back from orders.

Download Apidog to try the free database integrations.