k6 can directly stress test SQL database queries, bypassing application layers to isolate database performance.
Here’s a live example of a k6 script that hits a PostgreSQL database:
import http from 'k6/http';
import { check, sleep } from 'k6';
export const options = {
vus: 10, // Number of virtual users
duration: '30s', // Duration of the test
};
// PostgreSQL connection string format: postgresql://user:password@host:port/database
const DB_URL = 'postgresql://testuser:testpassword@localhost:5432/testdb';
export default function () {
// Example query: Select a user by ID
const query = 'SELECT id, username, email FROM users WHERE id = $1';
const params = [123]; // Parameter for the query
const res = http.post(
DB_URL,
JSON.stringify({
query: query,
params: params,
}),
{
headers: {
'Content-Type': 'application/json',
},
}
);
check(res, {
'status is 200': (r) => r.status === 200,
'response body is valid JSON': (r) => {
try {
JSON.parse(r.body);
return true;
} catch (e) {
return false;
}
},
});
sleep(1); // Pause between iterations
}
This script defines a basic load test with 10 virtual users running for 30 seconds. It connects to a PostgreSQL database using a provided connection string and executes a simple SELECT query. The http.post method is used because k6’s HTTP module is versatile and can be configured to talk to various backend services, including database endpoints that expose HTTP interfaces (like Hasura, PostgREST, or custom API gateways).
The core idea here is to simulate concurrent requests directly to the database, allowing you to measure its raw performance under load. This is crucial for identifying bottlenecks that are purely database-related, such as slow query execution, insufficient connection pooling, or disk I/O limitations.
To make this work, you’ll need a way for k6 to interact with your database over HTTP. Common approaches include:
- PostgREST: If you’re using PostgreSQL, PostgREST can instantly expose your database as a RESTful API. k6 can then make HTTP requests to these endpoints.
- Hasura: Similar to PostgREST, Hasura provides a GraphQL API over your PostgreSQL database, which k6 can query.
- Custom API Gateway: You might have a custom backend service that accepts database queries via HTTP and executes them.
- Database Proxies with HTTP Endpoints: Some database proxies or middleware might offer HTTP endpoints for query execution.
The http.post call in the k6 script sends a JSON payload containing the SQL query and its params. This is a common pattern for services that abstract database access. The check function verifies that the HTTP request was successful (status 200) and that the response is valid JSON.
You can extend this by:
- Varying Queries: Include multiple queries, different types of operations (INSERT, UPDATE, DELETE), and complex joins.
- Parameterization: Dynamically generate parameters for your queries to simulate realistic data access patterns.
- Transaction Simulation: Group multiple queries into a single HTTP request to test transaction performance.
- Error Handling: Introduce checks for specific database error codes or messages.
The sleep(1) ensures that virtual users don’t hammer the database too rapidly within a single iteration, mimicking more realistic user behavior where there are often pauses between actions.
The most surprising thing about using k6 for direct database load testing is how often the bottleneck isn’t the query execution itself, but rather the overhead of the API layer (if one is used) or the database’s connection management. You might find your database can execute a query incredibly fast when run manually, but under load via k6, performance plummets because the system is spending more time establishing connections or serializing/deserializing data than actually running the SQL.
The next challenge you’ll likely face is understanding how to interpret the results when using an abstraction layer like PostgREST or Hasura, as you’ll need to differentiate between the performance of the abstraction and the performance of the underlying database.