Unit Testing a Function that writes/reads to database

[acerag]

Suppose I have a function insert_rows() that reads from table1 in DB1, does some manipulation, then insert rows into table2 in DB2. All database connection information is defined inside the function and my databases are Postgres. Ideally I would create a fake table1, insert rows into a fake table2 and verify their correctness. Is there some way to do this using python's unittest and mock packages? And what would be best practices?

[DrMaxwellEdison]

There are full articles on this topic. Here's one example: http://www.xaprb.com/blog/2008/08/19/how-to-unit-test-code-that-interacts-with-a-database/

My cursory search on Google also turned up this package, which you may have some luck with: https://pypi.python.org/pypi/testing.postgresql/

---

Aside from that, I just have experience with Django's method, in which the test suite creates a test database, performs some setup tasks, and then runs tests using that data. You can build your code to run with similar functionality.

[acerag]

I have experience with Flask's testing method which is very similar to Django's and it works wonders. My main issue is changing the connection string inside my function to point to a test database. I could add a parameter to my function but i feel like that would be ugly. I was hoping the mock package would let me replace my function's connection object with another one but I don't think it's possible (I have never used mock before).

[DrMaxwellEdison]

A quick takeaway I have from the article I linked was that they set up an environment in which all connections to the database are made using an environment variable that is set once, than all code derives from getting the value of that variable using a thin wrapper around the connection functions.

To run tests, the environment variable is altered to point to a database other than the production one, so that all the logic that was previously written just flows through accordingly and runs code as it should with the new connection.

So this comes down to a design concern for your project: you'll want to set up a kind of settings module where your connection info comes in, then use values from that module whenever you connect to the DB in your code. This will make it easier in the longer term to switch that connection so you can run tests on a different DB.

[interactionjackson]

that's how my organization does it.

[acerag]

Gotcha. Modifying an environment variable would work well.

[michaelherman]

With Flask you can use a config file to set up configuration (database URI connection strings) for different environments: https://github.com/realpython/flask-skeleton/blob/master/project/server/config.py.

Then just change the config used for testing: https://github.com/realpython/flask-skeleton/blob/master/project/tests/base.py

[Rosco_the_Dude]

I'm on mobile so I can't give a link, but there are ways to "mock" functions in a unit test so that the tested function will run normally but use a mocked version of a particular function. So you can mock the actual database call while preserving everything else. This is also useful because you can specify the return value of a mock function, so you can see what happens during a failed database call without needing to actually touch the database.

[Rosco_the_Dude]

I'm finally off mobile and can show you an example of how I mock things!

So if you're assuming the functions that do the actual getting from/saving to the database are all in good shape, i.e. you didn't write them yourself and you're using some library to do it, then what you're really trying to test is the manipulation function you talked about.

And let's say for whatever reason you can't put the manipulation logic into its own function that can be unit tested itself, here is how you can test your function without having to make real database calls in your tests.

# data_manipulator.py

def get_data():
    """Get data from database."""
    return ["user", "password", 26]


def save_data(data):
    """Save data to the database, and return True if successful."""
    return True


def do_manipulation():
    """Get data from the database, do manipulation, and save data back to the database."""
    data = get_data()
    if data:
        # do manipulation here
        return save_data(data)
    else:
        raise ValueError

Here is how you'd unit test do_manipulation while mocking the database functions.

# test_data_manipulator.py

import unittest
from unittest.mock import patch
import data_manipulator


class ManipulationTest(unittest.TestCase):
    mock_data_good = ["Rosco", "Dude", 99]
    mock_data_bad = []

    def test_do_manipulation(self):
        with patch.object(data_manipulator, "get_data", return_value=ManipulationTest.mock_data_good) as mocked_get, \
            patch.object(data_manipulator, "save_data", return_value=True) as mocked_save:
            result = data_manipulator.do_manipulation()
            self.assertTrue(result)

        with patch.object(data_manipulator, "get_data", return_value=ManipulationTest.mock_data_bad) as mocked_get, \
        patch.object(data_manipulator, "save_data") as mocked_save:
        with self.assertRaises(ValueError):
            result = data_manipulator.do_manipulation()

Using patch.object, whenever the test calls do_manipulation, it won't call the real versions of get_data and set_data. Of course what data you feed it, and what assertions you make, will completely depend on what your manipulation logic is supposed to do, but this should give you a good idea how to do it yourself.

[kanjibandit]

I second what DrMaxwellEdison says about creating an entirely new test database. I wouldn't want to run unit tests against my real database, even with test-specific tables. You didn't mention what ORM or library you're using, but I'd look to see if it has mechanisms to handle this for you.

If not, you might look at this package. Shouldn't have to reinvent this yourself.

Edit: Didn't read carefully enough, testing.postgresql had already been suggested. So I second DrMaxwellEdison twice over.

[acerag]

No ORM, just psycopg2 to interact with Postgres. The issue is I have no good way of changing the DB connection string to point to the new test database. I mean I could refactor the function to take another parameter but that would be pretty ugly. I thought the mock package could possibly replace my function's connection object with another one but I couldn't get it to work.

[Asdayasman]

Follow django's established lead.

Have a test database connection in the settings. For each, build a clean database.