Database (JDBC) Step - Database Test Automation

What is Database Step?

Database Step allows you to connect to databases via JDBC and execute SQL queries. You can verify whether UI operations are correctly reflected in the backend and prepare data before tests.

MySQL

PostgreSQL

Oracle

SQL Server

Data Validation

Did the UI action reflect in DB?

Test Data Setup

Prepare data before test

Cleanup

Clean up after test

When Should You Use It?

Do Use

* Created a record via UI, verify it exists in DB
* Prepare user/order data before test
* Test unique constraint (same email twice)
* Soft delete verification (is_deleted flag)
* Clean up created data after test
* Bulk insert for complex data scenarios

Don't Use

* If API is available and sufficient (use API)
* On production database (dangerous!)
* Situations testable only through UI
* NoSQL databases (MongoDB, etc.)

Connection Setup

Define your database connections in Project Settings -> Database Connections. You can then select and use these connections in your test steps.

JDBC Connection String Examples

MySQL

jdbc:mysql://hostname:3306/database_name

PostgreSQL

jdbc:postgresql://hostname:5432/database_name

Oracle

jdbc:oracle:thin:@hostname:1521:SID

SQL Server

jdbc:sqlserver://hostname:1433;databaseName=database_name

Security: Store database credentials as Secret type Global Parameters. They will be masked in logs and reports.

Real-World Scenario Examples

Scenario 1: User Registration Validation

Register via UI, verify in DB

Use Case: You filled out the registration form, "Registration Successful" message appeared. But was it actually written to the database?

1 UI: Fill out register form, submit

2 Assertion: "Registration Successful" message appears

3 Database Step:

SELECT id, email, created_at
FROM users
WHERE email = '{{test_email}}'

Result should return 1 row. Save the id value to user_id parameter.

Scenario 2: Pre-Test Data Preparation

Create an order to test the order detail page

Use Case: You want to test the "Order Detail" page but first you need an order. Creating an order via UI takes 15 steps, INSERT to DB takes 1 step!

INSERT INTO orders (user_id, total_amount, status, created_at)
VALUES ({{user_id}}, 299.99, 'pending', NOW());

-- Get the last inserted ID
SELECT LAST_INSERT_ID() as order_id;

Save the order_id, then navigate to {{base_url}}/orders/{{order_id}} page.

Scenario 3: Soft Delete Verification

Is the deleted record really soft deleted?

Use Case: You deleted an address from UI. But the system uses soft delete, so the record shouldn't be deleted, only is_deleted=1 should be set.

SELECT id, is_deleted, deleted_at
FROM addresses
WHERE id = {{address_id}}

Expected Result:

id: 456 | is_deleted: 1 | deleted_at: 2025-01-09 14:30:00

Scenario 4: Duplicate Email Check

There shouldn't be 2 records with the same email

Use Case: You're trying to register again with an already registered email. The system should throw an error.

1 Database: First make sure this email exists

SELECT COUNT(*) as count FROM users WHERE email = '{{test_email}}'
-- Result: count = 1 (already exists)

2 UI: Fill out register form with the same email

3 Assertion: "This email is already registered" error should appear

Scenario 5: Post-Test Cleanup

Clean up test data created during test

Use Case: Users and orders created during the test shouldn't stay in DB. Each test should start clean.

-- Delete test user's orders
DELETE FROM order_items WHERE order_id IN (
    SELECT id FROM orders WHERE user_id = {{test_user_id}}
);

DELETE FROM orders WHERE user_id = {{test_user_id}};

-- Delete test user
DELETE FROM users WHERE id = {{test_user_id}};

Tip: Add cleanup steps to the Teardown section of your Testcase. It runs whether the test PASSES or FAILS.

Scenario 6: Bulk Insert for Pagination Test

Create 100 products, test pagination

Use Case: You want to test product list pagination. But the test environment only has 5 products, you need 100.

-- Add 100 test products
INSERT INTO products (name, price, category_id, created_at)
SELECT
    CONCAT('Test Product ', n),
    ROUND(RAND() * 1000, 2),
    1,
    NOW()
FROM (
    SELECT a.N + b.N * 10 + 1 as n
    FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
          UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
         (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
          UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
) numbers
WHERE n <= 100;

Result Processing

Using Query Results

Data returned from a SELECT query is stored in JSON format and can be used in subsequent steps.

Example Query:

SELECT id, email, status FROM users WHERE id = 123

Returned Result (JSON):

[
  {
    "id": 123,
    "email": "user@test.com",
    "status": "active"
  }
]

Accessing Values

Expression	Description	Result
`{{db_result[0].id}}`	First row's id	123
`{{db_result[0].email}}`	First row's email	user@test.com
`{{db_result.length}}`	Total row count	1

Row Count Assertion

{{db_result.length}} == 1

Save to Global Parameter

{{db_result[0].id}} -> user_id

Frequently Asked Questions

Can I connect to a production database?

We strongly advise against it! Only use on test/staging environments. If you accidentally run DELETE, there's no going back. Even a read-only user can be risky.

I'm getting connection timeout, what should I do?

Make sure the database server is accessible from the Agent's IP. Check firewall rules. If VPN is required, the Agent needs to be connected to the VPN.

Can I use transactions?

Each Database Step opens and closes its own connection. For multi-step transactions, run all SQL in a single step or use a stored procedure.

Can I call stored procedures?

Yes! You can call stored procedures using CALL procedure_name(param1, param2) syntax.