<jack>

If it’s too hard you’re doing it wrong.

Improving SQL Tests with Doppelgänger and Canary Data

Posted at — Sep 26, 2020

Whether using an ORM or writing SQL directly applications that use a SQL database ultimately execute SELECT, UPDATE, and DELETE statements. These statements can appear to work correctly in development and testing but fail catastrophically in production.

Doppelgängers

For an example, let’s use the domain of a todo list. One task is to retrieve all todo items for a user. Below is an example test for a fetchTodosForUser function (in JS pseudo code).

user = Factory.createUser()
todo1 = Factory.createTodo({user: user, text: "Mow lawn"})
todo2 = Factory.createTodo({user: user, text: "Wash car"})

expected = [todo1, todo2]
actual = fetchTodosForUser(user)
assertEqual(expected, actual)

This looks like a reasonable test. But what if the implementation of fetchTodosForUser was the following:

function fetchTodosForUser(user) {
  return db.exec("SELECT * FROM todos")
}

This test could succeed even though fetchTodosForUser has a critical error. The SELECT does not have a WHERE clause that filters out other users’ todos. This would lead to a privacy violation in production. This error can be caught by introducing a doppelgänger record. A doppelgänger record is a record that shouldn’t affect your code, but might if your code has a bug. In this example, we can create another user and create a todo belonging to that user.

// doppelgänger records
otherUser = Factory.createUser()
Factory.createTodo({user: otherUser, text: "Pay bills"})

user = Factory.createUser()
todo1 = Factory.createTodo({user: user, text: "Mow lawn"})
todo2 = Factory.createTodo({user: user, text: "Wash car"})

expected = [todo1, todo2]
actual = fetchTodosForUser(user)
assertEqual(expected, actual) // Test now detects error

This test would catch the error.

Canaries

Doppelgänger data tries to break your code. But what about the other way around? What if your code breaks your data? For example, let’s look at a test for a deleteAllTodosForUser function.

user = Factory.createUser()
todo1 = Factory.createTodo({user: user, text: "Mow lawn"})
todo2 = Factory.createTodo({user: user, text: "Wash car"})

deleteAllTodosForUser(user)

expected = []
actual = fetchTodosForUser(user)
assertEqual(expected, actual)

This test has a similar omission. What if deleteAllTodosForUser was implemented as below:

function deleteAllTodosForUser(user) {
  return db.exec("DELETE FROM todos")
}

The first person in production deleted all their todos would delete the entire system! The solution is to include a canary record. A canary record should not be affected by the code under test. If the canary dies (is deleted) or is injured (is updated) then there is a problem. Let’s look at a better test with a canary record.

otherUser = Factory.createUser()
canary = Factory.createTodo({user: otherUser, text: "Pay bills"})

user = Factory.createUser()
todo1 = Factory.createTodo({user: user, text: "Mow lawn"})
todo2 = Factory.createTodo({user: user, text: "Wash car"})

deleteAllTodosForUser(user)

expected = []
actual = fetchTodosForUser(user)
assertEqual(expected, actual)

reloadedCanary = fetchTodoById(canary.id)
assertEqual(canary, reloadedCanary) // Test detects dead canary

Making It Easier

Doppelgängers and canaries are useful testing techniques, but they have downsides. Most importantly, they require the developer to have considered the potential error. Less significantly, it demands additional test code.

Doppelgängers are easy to introduce globally. The solution is to preload the test database before each test. If the data set is sufficiently broad this provides a significant amount of additional test coverage with no additional work per test.

After this the test data can also act as canary data by adding a hook that runs after each test and checks that no canary data has been changed or deleted.

Summary

Doppelgängers and canaries are very similar. They are test data that shouldn’t affect the execution of your code. The difference is intent. Doppelgängers are designed to detect erroneous reads while canaries are designed to detect erroneous writes.

While the examples given above are simple, these techniques can be extremely valuable to ensure privacy and security in multi-tenant or multi-user systems, ensuring complex reports give the correct answers, and ensuring that batch updates do not affect more than they should.

Special thanks to my friends at Hashrocket for first introducing me to the doppelgänger concept.