Performing database tests on SQL databases

Testing is one of those activities that if not exhaustive will not have its complete impact on your software development process. Oftentimes developers are only concerned about testing the application layer of the system (a.k.a the codebase) and ignore testing the data layer (the database) which is also as important as testing the code itself.

What is database testing? Database testing is mainly composed of constructing SQL queries to assert and validate different database operations, structures and attributes required by the application the database is to be used for. These may include validating the schema, testing CRUD operations and transactions to ensure that the database is properly set up to serve the application. For more on database testing and the strategies to go about performing it, you can check out our Introduction to database testing article.

In this tutorial, you will learn and demonstrate how to test a relational MySQL database and then proceed to set up a continuous integration pipeline to automate the testing process.

Prerequisites

To follow along, you will need a few things:

1. Basic knowledge of JavaScript
2. Node.js installed on your system (version >= 12)
3. A CircleCI account
4. A GitHub account
5. Basic knowledge of SQL.

With all these installed and set up, you can begin.

Getting a remote MySQL instance

When running database tests, you want to make sure you are not running these tests on a production database. Make sure the test database completely resembles the one in production.

For this tutorial, you will need a remote MySQL database to run tests against. Many cloud providers offer free MySQL databases that you can use and remove later on. You are free to get your MySQL database from any service you like, as long have you have the connection details.

For this tutorial, I am using a database from Cloud Clusters. As soon as you sign up, navigate to MySQL. The Express Plan for MySQL should work well enough for our project. Click Free Trial. Using the default settings, set up a free MySQL database. We will use the database details in the next section.

Notes:

• Create a table if the service you are using does not create a default table on instantiation.
• For this tutorial, you can use either the MySQL 5.7 or MySQL 8.0 version, and the latin, utf8, utf8mb4, and gbk character set. The only exception is the utf8mb4 character set in the MySQL 8.0 version, which throws an error.

You can get the final project code on GitHub. The MySQL instance used here is the MySQL 5.7 version. The database named circleci uses the utf8 character set.

Setting up the test project with Jest and the MySQL SDK

Once you have your remote MySQL set up, you can now begin setting up the test environment. Different codebases have different test runners for performing tests. In this tutorial, you will be using the Jest testing framework and MySQL Node.js SDK to perform the database tests, because we are using a JavaScript project.

Start a new Node.js project by creating a folder and moving into its root:

mkdir relational-db-testing
cd relational-db-testing

Then, initialize a Node.js project and scaffold a basic package.json file using this command:

npm init -y

Next, install jest as a development dependency:

npm install --save-dev jest

Then, install the mysql2 and faker package to connect to your MySQL instance and generate fake test data:

npm install mysql2 faker

Now that you have the project setup and all the packages in, time to start writing tests.

Data integrity with CRUD tests

For this tutorial, you will be running simple CRUD (Create-Read-Update-Delete) tests to validate the data integrity in your CRUD operations.

Create a new file users.test.js at the root of the project. Inside this file, you will be testing a users database table by adding users, reading added users, updating a user and deleting user data.

Add the following code to the file. It includes the test for testing the CREATE and READ operations.

const { createPool } = require("mysql2/promise");
const faker = require("faker");

describe("Database Tests", () => {
  let connection;

  beforeEach(async () => {
    let createTableSQL =
      "CREATE TABLE `users` ( `id` INT(2) NOT NULL AUTO_INCREMENT , `name` VARCHAR(100) NOT NULL , `email` VARCHAR(50) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;";

    connection = await createPool({
      host: "YOUR_DB_HOST",
      user: "YOUR_DB_USER",
      password: "YOUR_DB_PASSWORD",
      port: YOUR_DB_PORT,
      database: "YOUR_DB_NAME"
    });
    console.log("Connected to database");

    await connection.query(createTableSQL);
  });

  it("Test CREATE and READ", async () => {
    try {
      const total_test_users = 3;
      let insertQueries = [];

      for (let i = 0; i < total_test_users; i++) {
        let insertSQL = `INSERT INTO users (id, name, email) VALUES (NULL, '${faker.name.findName()}', '${faker.internet.email()}');`;

        insertQueries.push(connection.query(insertSQL));
      }

      await Promise.all(insertQueries);

      const [rows, fields] = await connection.query("SELECT * FROM users");

      expect(rows.length).toBe(total_test_users);
    } catch (error) {
      console.log(error);
      let dropTableSQL = "DROP TABLE IF EXISTS `users`";
      await connection.query(dropTableSQL);
      await connection.end();
    }
  }, 60000);

  afterEach(async () => {
    let dropTableSQL = "DROP TABLE IF EXISTS `users`";
    await connection.query(dropTableSQL);
    await connection.end();
  });
});

This test:

• Makes a connection to the database instance
• In the beforeEach function call, it creates a users table with the fields id, name, and email
• In the afterEach function call, the table is dropped and the connection is ended

Dropping the table ensures that each test in the suite has a new instance of the users table to work with to avoid tests using the same shared data. Remember to replace YOUR_DB_HOST, YOUR_DB_USER, YOUR_DB_PASSWORD, YOUR_DB_PORT, and YOUR_DB_NAME with your remote MySQL database host, user, password, port, and database name respectively.

In the Test CREATE and READ test case, the faker library is used to add 3 users to the users table. The table is then queried to ensure that it contains the exact number of users just added.

To run this test, update the test script in your package.json file:

....,
"scripts" : {
    "test" : "jest"
}

Now go to your terminal and run the test by running:

npm run test

The following output is displayed in your terminal once the test completes.

> jest

  console.log
    Connected to database

      at Object.<anonymous> (users.test.js:18:17)

 PASS  ./users.test.js (5.5 s)
  Database Tests
    ✓ Test CREATE and READ (4292 ms)

Test Suites: 1 passed, 1 total
Tests:       1 passed, 1 total
Snapshots:   0 total
Time:        5.554 s
Ran all test suites.

The tests for CREATE and READ passed, which means that these operations can be performed for our schema and database with the simple INSERT query. This can be extended further to check for cases like duplicate emails or usernames.

For the UPDATE and DELETE operations, go ahead and add the following test case just underneath the it block for the first test:

it("Test UPDATE and DELETE", async () => {
  try {
    let name = "Test user";
    let email = "test@user.com";
    let nameUpdate = "My Test User";

    let insertSQL = `INSERT INTO users (id, name, email) VALUES (NULL, '${name}', '${email}');`;

    await connection.query(insertSQL);

    //Run and test update
    let updateSQL = `UPDATE users SET name='${nameUpdate}' WHERE email='${email}'`;
    await connection.query(updateSQL);

    const [rows, fields] = await connection.query("SELECT * FROM users");
    expect(rows[0].name).toBe(nameUpdate);

    //Run and test delete
    let deleteSQL = `DELETE FROM users WHERE email='${email}'`;
    await connection.query(deleteSQL);

    const [allrows] = await connection.query("SELECT * FROM users");
    expect(allrows.length).toBe(0);
  } catch (error) {
    console.log(error);
    let dropTableSQL = "DROP TABLE IF EXISTS `users`";
    await connection.query(dropTableSQL);
    await connection.end();
  }
}, 60000);

In this test, the UPDATE operation is tested by creating a new user and updating the name field for that user. The table is then queried to check that the update persists.

Next, a DELETE operation is run against the user’s data using the user’s email. The table is once again queried to check that the user no longer exists.

Save this file and run the test command once again (npm run test). This time, you will have this output in your terminal:

> jest

  console.log
    Connected to database

      at Object.<anonymous> (users.test.js:18:17)

  console.log
    Connected to database

      at Object.<anonymous> (users.test.js:18:17)

 PASS  ./users.test.js (8.165 s)
  Database Tests
    ✓ Test CREATE and READ (3452 ms)
    ✓ Test UPDATE and DELETE (3205 ms)

Test Suites: 1 passed, 1 total
Tests:       2 passed, 2 total
Snapshots:   0 total
Time:        8.226 s
Ran all test suites.

Automating the testing process

Now that you have your tests running correctly, it is time to automate the testing process. First, you need to save all changes to the test file.

Then, make the current directory a local git repository and add a .gitignore file. Run these commands:

git init
wget https://raw.githubusercontent.com/github/gitignore/master/Node.gitignore && mv Node.gitignore .gitignore

The second command copies the contents of GitHub’s official .gitignore file for JavaScript projects hosted here.

Next, push the project to GitHub.

Go to the Projects page on the CircleCI dashboard to add the project.

Click Set Up Project to begin setting up the project. Click Skip this step on the modal that pops up. We will be manually adding our CircleCI config later in this tutorial.

On the set up page, click Use Existing Config to instruct CircleCI that you will add a configuration file manually instead of using the sample. Next, you are prompted to download a configuration file for the pipeline or start building.

Click Start Building. This build will fail because we have not set up our configuration file yet. That will be your next step.

To add your continuous integration pipeline script, go back to your project. Create a folder named .circleci at the root of the project folder, and add a file named config.yml to it. Inside config.yml, enter this code:

version: 2.1
jobs:
  build:
    working_directory: ~/repo
    docker:
      - image: circleci/node:12
    steps:
      - checkout
      - run:
          name: Update NPM
          command: "sudo npm install -g npm"
      - restore_cache:
          key: dependency-cache-{{ checksum "package-lock.json" }}
      - run:
          name: Install Dependencies
          command: npm install
      - save_cache:
          key: dependency-cache-{{ checksum "package-lock.json" }}
          paths:
            - ./node_modules
      - run:
          name: Run tests
          command: npm run test

This configuration pulls in the appropriate Node.js image and updates npm in it. Dependencies are then installed and cached to make subsequent builds faster. Finally, the tests are run by using the npm run test command.

Commit all changes to the project and push to your remote GitHub repository. This triggers the build pipeline, which should be successful.

Click build, then expand Run tests to review the test details.

Conclusion

In this tutorial, you have demonstrated how to test a relational database (MySQL) and how to automate the process using CircleCI. If testing is not a priority for your team, share this tutorial with them so they can learn for themselves how easy it is to set up. Data is at the core of almost every application you will develop. A fault, corruption, or breach of data can lead to users losing faith in the credibility of a product and cause the company to lose business. Give database testing top priority in your software development operations.

Happy coding!