Unit testing database access layer

Question

I know this question has been asked many times before, but I have some concrete code examples and I wanted to know whether it makes sense to unit test them:

class FooAPI(object):

    def create(self, prop1, prop2, prop3, prop4, prop5):
        sql = "INSERT INTO foo (prop1, prop2, prop3) VALUES (?, ?, ?)"
        self.connection.execute(sql, (prop1, prop2, prop3))

        foo_id = self.connection.insert_id()

        sql = "INSERT INTO foo_settings (foo_id, prop4, prop5) VALUES (?, ?, ?)"
        self.connection.execute(sql, (foo_id, prop4, prop5))

        return foo_id

    def update(self, foo_id, prop1, prop2, prop3, prop4, prop5):
        "Update code similar to above"

    def delete(self, foo_id):
        sql = "DELETE FROM foo WHERE foo_id = ?"
        self.connection.execute(sql, (foo_id,))

    def find(self, foo_id=None, prop1=None):
        "Find objects by ID or by prop1"

Does it make sense to unit-test the above code, and how would one go about this. There are two complicating factors here:

The database itself is not trivial and I currently have no easy way to create a database with all test data
The schema is evolving, meaning that it's impossible to create a test database once and for all.

score 0 · Answer 1 · answered Feb 18 '12 at 22:43

Depending on who you ask, it always makes sense to test code. That said, what you're doing here is pretty straight forward and might not need testing. However, I would argue that even if it's difficult to set up a test database, you will most likely need it later anyway, so you might as well do it now when you're not trying to do something complicated. Also, it's find that the schema is evolving. It's almost inevitable that there will be major database changes over time and you just have to roll with it.

The only good reason I can think of not to test is if you expect to totally change the database soon.

score 0 · Answer 2 · answered Feb 18 '12 at 22:49

0

Unit tests (i.e. without a database), not IMO. Integration tests generally I think it is a good idea.

The database itself is not trivial and I currently have no easy way to create a database with all test data

How do you maintain the schema? You need to get this right before you go forward. Can you go from a blank database to a one with at least the schema in a single automated step? I'm guessing not so Liquibase can help you if you don't have a good system to keep the db up to date.

Now the test data - Keep the data required small.

answered Feb 18 '12 at 22:49

Michael Lloyd Lee mlk

14,561
3
44
81

Yes, there is a simple way to initialize a blank database: we usr SQLAlchemy to maintain models. Test data is tougher since the schema changes periodically. – ipartola Feb 19 '12 at 03:02
How frequently does it change? http://stackoverflow.com/questions/1346037/changing-database-schemas-unit-tests – Michael Lloyd Lee mlk Feb 19 '12 at 12:16

Unit testing database access layer

2 Answers2