Confident Testing: Contract Testing Database Queries

OK. I know this can be a sensitive topic. After all is said and done, here we are in 2022 and you would think we shouldn’t have need to write SQL queries in a robust framework. We have Object Relational Mappers (ORMs) that will take care of our db connections and manage our queries. We shouldn’t have to care, right? Well, kind of.

When we connect to a database to get data and pass that data on in our applications, often we are convinced that we need at least an integration test, if not a full API test to test things are functioning correctly. Do we really need to?

The Answer? No. We don’t.

A New Perspective

When we test at this layer, we need to apply the principles of Continuous Confidence and Confident Testing. We need to start thinking about the changes we are making in the context of the frameworks and SDKs they are running in. We need to talk about our Code and our Application in a different light.

A New Definition: Application

When we talk about automated testing for our Application, we need to refine our perspective to really understand how Confident Testing should apply. First and foremost is the definition of our Application.

In software, we define an Application as the whole experience of the User in context of using our code. How the code interacts with subsystems and integrations and the user. This definition, however, is not useful in the context of testing. Applying this definition to automated testing encourages and leads us to look End-To-End testing tools like Selenium and Cypress to automate our test suite. Don’t fall for it. Redefine it.

Let’s define our Application as only the code we write and control.

When we work with that definition, automated testing becomes a LOT easier. A new paradigm begins to become easier. Unit Testing becomes the number one tool for testing.

Another Definition: Automated Testing

The definition of Automated Testing needs to evolve as well. We often test to make sure it “works”. We write test suites to be more “acceptance” based. We want to ensure that it behaves a certain way. When we move to Unit Testing, we start to realize that the behavior of the system is the sum of its parts.

Unit Testing tests the Sum of the Parts

Because we know that the sum of the parts are important, we can now start to realize how the principles of Confident Testing apply. Let’s review what they are:

1. Test what you changed
2. Test your Logic
3. Test your assumptions
4. Test your Contracts

These four principles will guide us to write tests that will help us discover bugs that we never knew were there and ultimately save us time. I won’t discuss all of the principles here, but the fourth we will get into in context of testing Database Queries.

Don’t Test Your Tools

OK. So let’s say we are using a SQL database to store our data in it. We write the following query:

select * from users

Do you think that would work? Do we really need to test that? If I write that query in the console, the only time it would fail is if there isn’t a users table in the database context I am in. That last part, however, is not controlled by this portion of the application but rather is controlled by the configuration of the application. Again, not much the application can control. So let’s just worry about the code we control.

The code we control is the query. If you think about it in a different way, it is the contract we are sending to the SQL database. If we send it valid SQL, it will perform just fine. After all, they have tested that before we released it. I don’t need to keep testing it over and over. If the database can’t select something, there is something far worse than our application that is the problem.

So what do we do? How do we test?

String Comparison Example

Yeah. You read that right. String comparison. You need to understand the query, the string, when you are using strings. You need to confirm the principles we talked about: confirm your logic!

But wait, you say. There is no logic there. Yes there is. It’s the table and what we are selecting. That is important. I would test like:

self.assertTrue(‘*’ in query)
self.assertTrue(‘Users’ in query)

Now, I know this is a simplified expression. That’s on purpose. I trust that we can figure out more complex parts of the query that will confirm that they are working properly. It’s ok that it is simple.

ORM Example

What about ORMs? That’s easy: Mock it! Mock the method calls and confirm they are called properly. So a basic SQLAlchemy Example:

Code:

results = db_session.query(Users).all()

So let’s assume the above the code we want to test. It is the same as the previous example, selecting all the users in the system. The Contract Tests that we care about are even simpler. The contract is HOW I call the sqlalchemy interfaces. So, after mocking the db_session I can confirm:

db_session.query.assert_called_with(Users)
db_session.query().all.assert_called_with()

Not once did I need a database. We are tested.

Caveat

Now. I am not saying, write tests, and never launch it and test it manually. I am saying the opposite. Build the code. When you know it is working, write the tests that CONFIRM and VALIDATE that the code will work the way it should. It’s that simple.

In conclusion, when you use the principles of Confident Testing with your database layer, you will find that you can test more efficiently and confidently. You will find that Unit Testing will take precedent over End-To-End testing, and your suite will no longer be based primarily on Selenium and Cypress testing.

Confident testing is all about testing for Confidence, not for results. Enjoy!