Accessing a SQL database from your JavaScript Test

A common task in GUI tests is to automate entering some data into forms. To properly test and verify such scenarios, it is often necessary to interact with the database backend from the test.

As an example, let's take the evaluation form on our website and assume we'd like to write a test for this. So whenever a visitor fills out the evaluation form, among other things the entered information is put into an SQL database.

Let's assume the following tables in the database backing the evaluation request form.

CREATE TABLE evaluation_requests (
   id CHAR(36),
   insertTime timestamp DEFAULT CURRENT_TIMESTAMP,
   name VARCHAR(255),
   email VARCHAR(255),
   company VARCHAR(255),
   countryCode CHAR(2),
   productId SMALLINT,
   PRIMARY KEY (id)
);
CREATE TABLE countries (
   id CHAR(2),
   name VARCHAR(255)
);
CREATE TABLE products (
   id SMALLINT,
   name VARCHAR(255)
);

And let us take the following scenario as the test we want to automate:

Feature: Creating Evaluation Requests

# ...

    Scenario: An entry is added to database
        Given the evaluation form is opened
               When I fill out the evaluation form
| Max Mustermann | max@example.com | Sample Ltd. | Germany | Squish for Qt |
               And I submit the evaluation form
        Then the above information should be in the database

# ...

So filling out and submitting the form with some non-special values shall trigger a new row being added to the evaluation_requests table. For verifying this as part of the implementation of the the above information should be in the database step, we want to look into the MySQL database.

The values we expect to show up in the database are taken from the Scenario. To have them accessible in the last step, we have to preserve them in the previous step; by putting them into the context's userData space.

When("I fill out the evaluation form", function(context) {
    var data = context.table[0];
    context.userData.formValues = data;
    // ...
    // UI actions for filling out the form
}

In the last step's implementation, we start by establishing a connection to our SQL database.

var dbConnection = SQL.connect( { Driver: "MySQL",
   Host: "www-test",
   Port: 3306,
   Database: "evaldb-04",
   UserName: "evals",
   Password: "3vals" } );

The SQL query is based on the above table definitions. Having the tests running in an isolated test environment means we can safely assume nothing else than our own tests are writing to the database. Therefore, we can care about the most recent entry only (ORDER BY insertTime DESC LIMIT 1).

var sql = "SELECT e.name, email, company, c.name as country, p.name as product \
    FROM evaluation_requests e \
    LEFT JOIN countries c \
    ON e.countryCode = c.id \
    LEFT JOIN products p \
    ON e.productId = p.id \
    ORDER BY insertTime DESC \
    LIMIT 1";

The query() method executes the SQL statement, and provides the result set, pointing to the first row.

var result = dbConnection.query(sql);

In case of an empty result set, the result.isValid attribute is set to false. We can access the values of the current row using the value() function. Using numeric indices is possible as well as using the more readable column names.

if (!result.isValid) {
    test.fail("Entry not found.");
} else {
    test.compare(result.value("name"), context.userData.formValues[0]);
    test.compare(result.value("email"), context.userData.formValues[1]);
    test.compare(result.value("company"), context.userData.formValues[2]);
    test.compare(result.value("country"), context.userData.formValues[3]);
    test.compare(result.value("product"), context.userData.formValues[4]);
}

Finally we finish the database access by closing the connection.
dbConnection.close();
So we end up with:

var dbConnection = SQL.connect({
    Driver: "MySQL",
    Host: "www-test",
    Port: 3306,
    Database: "evaldb-04",
    UserName: "evals",
    Password: "3vals" });
var sql = "SELECT e.name, email, company, c.name as country, \
    p.name as product \
    FROM evaluation_requests e \
    LEFT JOIN countries c \
    ON e.countryCode = c.id \
    LEFT JOIN products p \
    ON e.productId = p.id \
    ORDER BY insertTime DESC \
    LIMIT 1";
var result = dbConnection.query(sql);
if (!result.isValid) {
    test.fail("Entry not found.");
} else {
    test.compare(result.value("name"), context.userData.formValues[0]);
    test.compare(result.value("email"), context.userData.formValues[1]);
    test.compare(result.value("company"), context.userData.formValues[2]);
    test.compare(result.value("country"), context.userData.formValues[3]);
    test.compare(result.value("product"), context.userData.formValues[4]);
}
dbConnection.close();

In case you care about more than a single row, result.toNext() moves forward to the next row. Having this combined with result.isValid allows you to easily traverse through a result set:

result = connection.query(sql);
while (result.isValid) {
    doSomethingWith(row);
    result.next();
}