How do I test a method which interacts with a database?

Question

So I have a database class which has methods dedicated to specific actions. For example I have below method which accesses and writes to a table in the database:

def trackUserPerformance(self, verbform, verbsolution, erroneousUserInput, isVerbCorrect, date):

    with self as db:
        cursor = db.connection.cursor()

        cursor.execute(
            "INSERT INTO trackusersuccessfailure (verbform, verb, erroneousUserInput, state, date) VALUES "
            "(%s, %s, %s, %s, %s)", (verbform, verbsolution, erroneousUserInput, isVerbCorrect, date))

        self.connection.commit()

The method trackUserPerformance(...) is a method inside my database class. How do you for example rollback the query of the method trackUserPerformance(...) in tests? I personally like the idea of calling database methods like so:

db.specificMethod(...)

But it gets a little challenging when I need to test those method. My question is if my approach is even a good practice meaning to create methods that are responsible for specific database queries like the method above ore if this is not a good idea because of said challenge?

Answer 1

Is trackUserPerformance within the data access layer?

• If no, then you should consider adding the layer. A common—although not the only one—approach is to separate applications into following layers:

  1. presentation layer, which handles all the UI logic

  2. business layer, which contains the actual logic of the application

  3. data access layer which provides persistence.

• If yes, you should consider whether unit tests are the most appropriate here, or other types of tests should be used instead.

  1. If you rely on unit tests, then the actual database would be mocked, possibly by tampering with the db property.

  2. If you prefer system tests instead, i.e. tests where the actual database will be changed, then either use nested transactions (i.e. the test itself will start a parent transaction and revert it once the code is executed) or snapshots. It takes milliseconds to restore a snapshot, so it shouldn't affect too negatively the speed of the tests.

Answer 2

How do you for example rollback the query of the method trackUserPerformance(...) in tests?

From the code that is presented, most likely one will write one or more integration tests. As part of each test, it will have "Setup" and "Teardown" processing.

As part of the setup, one will ensure that the table where the records are being sent to is clean (has no records or at least does not contain the records from a previous test run). This is the setup. Then it will insert/update/delete the records in the table with the appropriate integration tests. Setup could also be creating a new blank database to start with if many different integration tests are being run.

When the tests are complete, the data that was inserted or updated is removed. This the teardown piece.

Both of these actions ensure that the integration tests give the same result every time they are executed.

As a best practice, I would separate out unit tests versus integration tests. Unit tests should run very fast, and one can run 1000s of them in just a few seconds. In addition, they don't have any dependencies. Integration tests will take longer as they are actually inserting records to the dependent database. Also bear in mind the integration tests may fail if the database is not available. So, they are more fragile than unit tests because they can fail when no changes have occurred to the code base.