I work with Oracle and MySQL on a daily basis, and one of the questions I’ve often met is: How do I test my database controllers? This post explores that question using Java, but the principles apply to any language.

Most of the time, you can mock your controllers away. For example, if you have code that retrieves a data set, performs some manipulation of that data, and then visualizes it, there are several tests to perform:

1. Did the data retrieve correctly? (i.e. was your query correct?)
2. Did the manipulation algorithm work correctly?
3. Did the visualization work correctly?

(2) is the one that most people test through unit tests. Hopefully you can test your manipulation methods by passing in a testing data set and evaluating the resulting dataset. If necessary, you can replace your controller with a mock/stub so that you’re not actually hitting the database.

(3) is the one that usually gets picked up by UI integration tests with frameworks like Selenium, Jasmine/Jest, etc. These tests can be fragile as UI changes and are usually very slow, as they require full-stack latency (POST your request, wait for the server to respond, inspect the DOM, rinse and repeat).

(1) is one for which I’ve seen the least number of tests (outside of integration tests), but they have some undesirable expenses: you have to have access to a database, and the left-over data might screw you (or your co-worker) up later.

What do these tests look like?

Usually these tests are something along the lines of:

SomeController.createUser(user);
User retrievedUser = SomeController.getUser(user.getUserId());
// Assumption: the User object has a compareTo() which is making 
// this test work for us.
Assert.assertEquals(user, retrievedUser, 
  "The retrieved user was not as expected!");

OK, that’s a pretty good test, but what about that user data? Did it get left in the database? You can add safety methods like

// Assuming that connection is a Test Class instance variable.
@AfterMethod
public void teardown() {
  connection.rollback();
}

to try to delete that object, but what if you’re working with some code that you can’t control/refactor and that code makes a commit? (This has definitely happened to me.)

Do we even care about left-over data?

If your tests are always run in a test database, then perhaps not.

• Are your tests safe to run in production?
• How about a staging environment?
• If you run tests in a staging environment, and you check for corrupted data in your staging environment, are those tests clean enough to not cause corruptions?

If you answered yes to all of those questions, then you can stop here. Congrats!

If you answered no to any of those questions, follow along with me. In this example, we’re going to deal with a UserController which adds a User entity to the database.

First, we need something that will accumulate userIds and then clear them out afterward.

public class TestDataMonitor {
  static HashSet<Long> userIds = new HashSet<Long>();
  public static void addUserId(long userId) {
    userIds.add(userId);
  }

  public static void deleteUsers() {
    // I assume that you're getting access to this controller through
    // construction, factory, injection, static calls, etc. This example
    // is with construction.
    UserController userController = new UserController();
    for (long userId : userIds) {
      userController.deleteUser(userId);
    }
  }
}

Next, we need to get our UserController to notice that users are being created.

For a given UserController, I created a TestUserController which looked like this:

public class TestUserController extends UserController {
  public long createUser(User user) {
    long userId = super.createUser(user);
    TestDataMonitor.addUserId(userId);
    return userId;
  }
}

You’ll need to use your TestUserController instead of UserController during testing; if you use injection, bind UserController to TestUserController. If you instantiate, just be sure to call TestUserController in your test classes (injection avoids accidentally using UserController by mistake).

Now, instead of that @AfterMethod as shown earlier, you can replace it with an @AfterMethod (or @AfterClass) of:

@AfterMethod
public void deleteCreatedUsers() {
  TestDataMonitor.deleteUsers();
}

Have multiple controllers in the same test? You can repeat this process pretty easily. I had a simple MySQL database for which

@@autocommit = 0

never worked. Because the entities were very simple, and because the controllers didn’t understand the concept of deleting data (why would we do that?), I was able to make my monitor class much more generic. It looked something like this:

public class TestDataMonitor {
  static HashMap<String, HashSet<Long>> dataToDelete = new HashMap<>();
  static HashMap<String, String> tableColumnMapping = new HashMap<>();

  static {
    tableColumnMapping.put(User.USER_TABLE_NAME, User.USER_ID_COLUMN);
    tableColumnMapping.put(Item.ITEM_TABLE_NAME, Item.ITEM_ID_COLUMN);
    dataToDelete.put(User.USER_TABLE_NAME, new HashSet<>());
    dataToDelete.put(Item.ITEM_TABLE_NAME, new HashSet<>());
  }

  /**
  * Note, we don't catch a NullPointerException here - if a test is using 
  * an unsupported tableName, we want to fail fast.
  */
  public static void addDataForDeletion(String tableName, long dataId) {
    dataToDelete.get(tableName).add(dataId);
  }

  public static void deleteData() throws SQLException {
    // I'm assuming you have a connection manager, injection, 
    // or something to get this connection.
    Connection connection = ConnectionManager.getConnection();
    for (String tableName : dataToDelete.keySet()) {
      String columnName = tableColumnMapping.get(tableName);
      for (long dataId : dataToDelete.get(tableName)) {
        String delete = "DELETE FROM " + tableName + 
          " WHERE " + columnName + " = " + dataId;
        PreparedStatement deleteStatement = 
          connection.prepareStatement(delete);
        deleteStatement.execute();
      }
      // So that multiple calls can be made throughout testing without 
      // unnecessary calls.
      dataToDelete.get(tableName).clear();
    }
  }
}

I still modified the UserController, but in this case it looks like:

public class TestUserController extends UserController {
  public long createUser(User user) {
    long userId = super.createUser(user);
    TestDataMonitor.addDataForDeletion(User.USER_TABLE_NAME, userId);
    return userId;
  }
}

So, why did I bother with all of that?

Remember those questions from above?

• Are your tests safe to run in production? I wouldn’t do this often, but so long as you’re capturing all of the rows with your controllers, you won’t leave anything extra behind. If you want to be extra safe, consider some full-table query comparisons (before and after tests run).
• How about a staging environment? Likewise, you’ll be less likely to destroy your colleague’s test data if you’re inserting your own test data and removing it at the end of your test. I’ve seen too many developers accidentally run tests against the wrong database to know that your tests shouldn’t care which database they’re connecting to.
• If you run tests in a staging environment, and you check for corrupted data in your staging environment, are those tests clean enough to not cause corruptions? You should definitely run your corruption checks after a test run. If they come back clean, then you don’t have any recognized corruptions! (You should keep your corruption checks up to date, but that’s a topic for a different post.)

In a recent refactoring project, I was able to add substantial testing code coverage to our controllers by adding this sort of safety net; without it, my tests would have added a significant amount of cruft to the database. I’ll still want to run these tests against a test-specific database (rather than production), but they will be safe to run in production if accidentally done.

If your tests aren’t safe, you’re not going to run them, and you’ll want to run your tests. Trust me.

How do you solve these problems?

Have a simpler solution? Please let me know!