Tests Are Great!

The last couple of iterations at work we’ve decided that increasing our test coverage is something we would like to prioritize. We’ve always had some tests that validate the database, but they require a developer to manually go in and add any new tables that should be checked to the test specification, along with the new table structure. Since these tests were first implemented we’ve gone through a database change, and are now supporting at a code-level two distinct different databases. Enter my idea: wouldn’t it be great if our tests could validate the two databases automatically regardless of if a developer remembers to add tests?

The Rabbit Hole

You may see where this is leading. Our tests currently use a cucumber framework to run through a specific feature file named after the table they check, and we read in a set of exclusions for each field we don’t want to validate. Now, a developer already has to add a definition file on the system at a code-level that includes all of the fields that are in the database due to the language we use, why can’t we just validate against these files? This seems like a quick way to catch errors on both sides. So I implement checks that go both directions: validate the database against the files on the filesystem, and validate the files against the database schema. This is fairly easy to cleanup the 200+ feature files into a single auto-updated piece of code that reduces clutter and makes the test runner much easier to understand.

Problems!

The first problem I hit with the tests was that we have tables in the database that we forgot to add descriptor files for. Not a problem at all, this is what we wanted to catch, we just add the files and keep going. The second issue is similar, and just as easy to resolve: having files that no longer have tables in the database due to cleanup and evolution of the database over time. Again, this is an easy fix. Just cleanup the erroneous files and continue. But then we hit a more difficult problem. The new database we’ve just converted to has a difference in schema than the old database. In fact, some of the tables that were converted don’t have indexes that they probably should have, and one even has missing a critical field completely missing. It just hasn’t been caught because the table is no longer in use. More and more small issues like this kept cropping up as I dug into getting these tests to function fully, and by the time I was done I had excluded basically all the tables that were already not being checked due to not having a cucumber feature file already.

Takeaway

Overall, I ended up having to shelve the new tests for now due to some incompatibilities with the way the old database is interacted with via SQL. The overall process however was a great way to dig into the actual discrepancies in our database schemas, and gives me a very clear direction on how to resolve the overall problems. Since it’s going to require more than just modifying the tests to get everything where I’d like it I’m going to do more digging into which tables are deprecated and no longer used in our software. Until then, the new tests will just have to wait, even if I did get them all passing.