K6 load tests — Part VII

Kavita Jadhav
3 min readNov 15, 2023

--

Connect to MySQL database

https://www.freepik.com

A sample load test is added in the earlier sections of the series. GitHub repository - https://github.com/KavitaJadhav/k6_learning.

Test data could be needed to run some of the API tests. Supported databases include MySQL, Postgres, SQLite3, and SQL server. To Establish a connection to the DB, update the Dockerfile to include an SQL extension:

RUN xk6 build --with github.com/grafana/xk6-sql@v0.2.1

You can use the latest version of xk6-sql. Rebuild docker image:

docker build . -t k6_learning --no-cache

Create or use existing MySQL database locally. I have configured below database:
username: root
password: password
schema: user_service
table: users

Create a new file db.js with below code snippet:

import sql from 'k6/x/sql';

export function getUserIds() {
const db = sql.open("mysql", 'root:password@tcp(127.0.0.1:3306)/user_service');
let results = sql.query(db, "select id from users;");
return results
}

Update test to consume method getUserIds:

import http from 'k6/http';
import {check} from 'k6';
import {setTags} from "./../../helper.js";
import * as db from './../../db.js';

export default function () {
setTags();

let user_ids = db.getUserIds()
console.log(user_ids)

let res = http.get('http://test.k6.io');
check(res, {
'success response': (result) => result.status === 200,
'body contains text': (result) => result.body.includes('Collection of simple web-pages suitable for load testing.')
})
};

Run tests with the command:

docker run -i -v $(pwd):/home/k6 k6_learning \
k6 run v1/test.k6.io/test.js \
--config v1/test.k6.io/load-test-config.json \
--out json=summary/test.json \
-e ENVIRONMENT='uat'

It will raise an error. The database is configured in localhost but tests are running in the docker container. This issue will not occur in higher environments.

time="2023-11-15T05:16:17Z" 
level=error msg="GoError: dial tcp 127.0.0.1:3306: connect: connection refused\n\tat reflect.methodValueCall (native)\n\tat getUserIds (file:///home/k6/db.js:6:32(13))\n\tat file:///home/k6/v1/test.k6.io/test.js:9:19(8)\n"
executor=shared-iterations
scenario=warmup
source=stacktrace

To fix the issue update the connection string and run tests. It will start printing user IDs in the logs:

const db = sql.open("mysql", 'root:password@tcp(docker.for.mac.localhost:3306)/user_service');
stdout logs

Increase iterations count from 1 to 10 in the load-test-config.json file. Add the below log statement in. the getUserIds method in the db.js file and run tests again.

console.log('Querying from database');

Multiple entries for the logged string will be available in the stdout file. The default function gets executed for each vu and iteration, resulting in multiple database connections.

K6 provides setup and teardown methods to configure common resources, data etc. To fix the above issue move getUserIds method call to the setup function and run tests.

import http from 'k6/http';
import {check} from 'k6';
import {setTags} from "./../../helper.js";
import * as db from './../../db.js';

let user_ids;

export function setup() {
return {
'userIds': db.getUserIds()
}
}

export default function (data) {
setTags();

user_ids = data['userIds']

let res = http.get('http://test.k6.io');
check(res, {
'success response': (result) => result.status === 200,
'body contains text': (result) => result.body.includes('Collection of simple web-pages suitable for load testing.')
})
};

In the stdout logs, a single entry for the logged string will be available.

As of now, a static database connection string is added in the db.js file. It can be parameterised using environment variables.

Next, integrate the Prometheus plugin to publish the test summary. Continued in Part VIII.

References - Load Testing SQL Databases with k6

Note: Subsequent parts of the series are available here.

Happy Testing!

--

--