How to Quickly and Efficiently Create Tests for MySQL

When developing MySQL database functionality, sometimes it is necessary to test stored procedures and functions. There are various utilities that allow you to do this, but in some situations, it is not possible to use them.

This may be due to the inability to install, limited access to servers, lack of time to configure and study them, and a number of other factors. However, it is often necessary to test the main functionality to make sure that after making the next changes, it works without errors.

In this article, I will tell you how to perform testing without installing any additional utilities.

Testing Process

First, let's describe a typical testing process:

• There is an object that needs to be tested (procedure, function, etc.).

• There is a set of input parameters that this object works with.

• Also, the database may contain the initial data necessary for the operation of the tested object (for example, data stored in the database table).

• The tested object performs some transformations, i.e., as a result of its work, something must be changed.

• Interpretation of test results is a comparison of expected data with received ones.

If we are talking about a procedure, it is most likely some data in the database that has changed. When talking about a function, it is usually the value that it returns. Consider the process of testing procedures on the example of a test database employees for the MySQL RDBMS.

The structure description and installation scripts can be found here.

Briefly, the installation commands look like this:

git clone https://github.com/datacharmer/test_db.git
cd test_db
mysql -t < employees.sql

We will only use these 3 tables:

Performing Insert

Let's first check how data is changed when we perform a manual insert by adding a new employee.

The data in the employee table is as follows:

USE employees;
SELECT * FROM employees e;

Since the table employees have no AUTO_INCREMENT on its primary key, we need to get the maximum value and increment it to get the next ID. We will store it in the session variable @emp_no:

SELECT MAX(emp_no) + 1 INTO @emp_no FROM employees e;

Now, perform insert:

INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
VALUES (@emp_no, CURDATE() - INTERVAL 30 YEAR, 'John', 'Doe', 'M', CURDATE());

We have stored the new ID in the session variable @emp_no, so we can reuse it in the next test cases. We also use it to check if the necessary data has been added:

SELECT * FROM employees e WHERE emp_no = @emp_no;

If the primary key had an AUTO_INCREMENT property, then we would not have to generate an ID, and we could get it in the following way:

SELECT LAST_INSERT_ID() INTO @emp_no;

Obviously, in this case, the ID would not need to be passed to the procedure and to be specified in the INSERT statement.

Creating a Procedure

Now, we create a procedure containing just one INSERT statement:

CREATE PROCEDURE employees.add_employee(IN p_emp_no int, IN p_birth_date date, IN p_first_name varchar(14), IN p_last_name varchar(16), IN p_gender enum ('M', 'F'), IN p_hire_date date)
BEGIN
  INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
  VALUES (p_emp_no, p_birth_date, p_first_name, p_last_name, p_gender, p_hire_date);
END

We can already call the procedure, but we need to remember some data before the call and perform some kind of check that will show that the procedure was executed correctly after the call.

Testing the Created Procedure

So, we want to create a test case for this procedure:

SELECT MAX(emp_no) + 1 INTO @emp_no FROM employees e;
CALL add_employee(@emp_no, CURDATE() - INTERVAL 30 YEAR, 'John', 'Doe', 'M', CURDATE())
SELECT IF(COUNT(1) = 1, 'OK', 'FAIL') AS test_result FROM employees e WHERE emp_no = @emp_no;

We pass some parameters as static values as some are generated using the CURDATE() function. But in some cases, we need to generate unique values every time we run the test, so the method of generating test values may be different.

The second SELECT statement returns either 'OK' or 'FAIL' depending on its check; the count or records with @emp_id should be equal to 1:

A Couple More Procedures

Let's add two more procedures for adding a new department and adding an employee to the department:

CREATE PROCEDURE employees.add_department(IN p_dept_no char(4), IN p_dept_name varchar(40))
BEGIN
  INSERT into departments (dept_no, dept_name)
  VALUES (p_dept_no, p_dept_name);
END

CREATE PROCEDURE employees.add_emp_to_dept(IN p_emp_no int, IN p_dept_no char(4), IN p_from_date date, IN p_to_date date)
BEGIN
  INSERT INTO dept_emp (emp_no, dept_no, from_date, to_date)
  VALUES (p_emp_no, p_dept_no, p_from_date, p_to_date);
END

Adding Tests for New Procedures

Now, we need to add two tests for these procedures. But before that, we would like to check what kind of data needs to be generated:

SELECT * FROM departments d;

As we can see, the department ID is in the format 'pNNN', so we have to use a complex construct to generate the next ID:

SELECT CONCAT('d', LPAD(MAX(SUBSTR(d.dept_no, 2, 10)) + 1, 3, 0))
INTO @dept_no
FROM departments d;

When using these expressions, you need to be careful. Here, I used an implicit type cast from number to string and back to reduce the number of functions used, but in some cases, this can lead to an error.

We call and test the procedure like this:

CALL add_department(@dept_no, 'Testing');
SELECT IF(COUNT(1) = 1, 'OK', 'FAIL') AS test_result FROM departments d WHERE d.dept_no = @dept_no;

In a similar way, we will check how to add an employee to the department:

SELECT * FROM dept_emp ORDER BY emp_no desc;

Since we already have IDs of created employees and departments stored in session variables @emp_no and @dept_no, we can use them to add the employee to the department:

CALL add_emp_to_dept(@emp_no, @dept_no, CURDATE(), CURDATE() + INTERVAL 1 YEAR);
SELECT IF(COUNT(1) = 1, 'OK', 'FAIL') AS test_result FROM dept_emp de WHERE de.emp_no = @emp_no AND de.dept_no = @dept_no;

We also use these session variables to perform test checks.

Testing a Procedure That Returns a Dataset

There is one more way to use procedures when there are no data changes, but the result of some SELECT statements is returned.

An example of such a procedure is one that returns information about all employees in a department. Let it be called get_employees:

CREATE PROCEDURE employees.get_employees(IN p_dept_no char(4))
BEGIN
  SELECT e.*
  FROM employees e
  INNER JOIN dept_emp de ON e.emp_no = de.emp_no
  WHERE de.dept_no = p_dept_no;
END

We call it:

CALL get_employees(@dept_no);

And the result is:

In this case, we don't have modified data, but we can check the number of rows returned.

And for this, we use the FOUND_ROWS function:

CALL get_employees(@dept_no);
SELECT IF(FOUND_ROWS() = 1, 'OK', 'FAIL') AS test_result FROM dept_emp de WHERE de.emp_no = @emp_no AND de.dept_no = @dept_no;

Let's Add Some Cleanup

To avoid clogging the database with test data, we need to delete them after all the tests have been completed:

DELETE FROM employees WHERE emp_no = @emp_no;
DELETE FROM departments WHERE dept_no = @dept_no;
DELETE FROM dept_emp WHERE dept_no = @dept_no;

Collecting and Displaying the Results of All Tests

Let's add some code and collect the results of all tests into a variable. This will help us to:

1. Visualize test results.

   
   

2. Run tests in a single script.

   
   

3. Run the test script from a command shell and parse the test results.

   
   

4. Set up integration with git, hooks, CI, etc.

Finally, here is our test script:

set @test_results = '';
set @pad = 80, @s = ' ';

set @test_name = 'Add employee';
SELECT MAX(emp_no) + 1 INTO @emp_no FROM employees e;
CALL add_employee(@emp_no, CURDATE() - INTERVAL 30 YEAR, 'John', 'Doe', 'M', CURDATE());
SELECT IF(COUNT(1) = 1, '[OK]', '[FAIL]') INTO @test_result FROM employees e WHERE emp_no = @emp_no;
set @test_results = CONCAT_WS('', @test_results, CONCAT_WS('', '[TEST]', LPAD(@test_name, @pad, @s), ' ', @test_result), '\n');

set @test_name = 'Add department';
SELECT CONCAT('p', LPAD(MAX(SUBSTR(d.dept_no, 2, 10)) + 1, 3, 0)) INTO @dept_no FROM departments d;
CALL add_department(@dept_no, CONCAT('Testing ', @dept_no));
SELECT IF(COUNT(1) = 1, '[OK]', '[FAIL]') INTO @test_result FROM departments d WHERE d.dept_no = @dept_no;
set @test_results = CONCAT_WS('', @test_results, CONCAT_WS('', '[TEST]', LPAD(@test_name, @pad, @s), ' ', @test_result), '\n');

set @test_name = 'Add employee to department';
CALL add_emp_to_dept(@emp_no, @dept_no, CURDATE(), CURDATE() + INTERVAL 1 YEAR);
SELECT IF(COUNT(1) = 1, '[OK]', '[FAIL]') INTO @test_result FROM dept_emp de WHERE de.emp_no = @emp_no AND de.dept_no = @dept_no;
set @test_results = CONCAT_WS('', @test_results, CONCAT_WS('', '[TEST]', LPAD(@test_name, @pad, @s), ' ', @test_result), '\n');

set @test_name = 'Get employees';
CALL get_employees(@dept_no);
SELECT IF(FOUND_ROWS() = 1, '[OK]', '[FAIL]') INTO @test_result;
set @test_results = CONCAT_WS('', @test_results, CONCAT_WS('', '[TEST]', LPAD(@test_name, @pad, @s), ' ', @test_result), '\n');

set @test_name = 'Cleanup';
DELETE FROM dept_emp WHERE dept_no = @dept_no;
DELETE FROM departments WHERE dept_no = @dept_no;
DELETE FROM employees WHERE emp_no = @emp_no;

SELECT IF(
  (SELECT COUNT(1) FROM employees e WHERE emp_no = @emp_no)
  + (SELECT COUNT(1) FROM departments WHERE dept_no = @dept_no)
  + (SELECT COUNT(1) FROM dept_emp WHERE dept_no = @dept_no)
  = 0, '[OK]', '[FAIL]') INTO @test_result;
;
set @test_results = CONCAT_WS('', @test_results, CONCAT_WS('', '[TEST]', LPAD(@test_name, @pad, @s), ' ', @test_result), '\n');

SELECT @test_results;

And its result:

As you can see from the picture, now we have the results of all tests collected together and we can display them, as well as process and analyze them.

The following points should be noted:

When calling the add_department procedure, we use CONCAT('Testing ', @dept_no) as the name of the department because this field must be unique. There is a more correct way to generate unique values, but I think this is a topic for the next discussion.

It is also necessary to ensure correct work with transactions; here, I intentionally skipped it to concentrate on the essence of testing.

Some repeating blocks can be placed in procedures or functions - if you wish and have permission for that.

But even in this form, it is not difficult to copy the block and create new tests from it.

I hope that the above information will be useful to everyone who is involved in the development and testing of databases.