Exploring FitNesse – Using Database Queries


In this series of blog posts I want to explore my choice to use the Java Persistence API in a test automation effort against a legacy (Oracle) database. For test automation I currently use FitNesse. Fitnesse offers many ways to access information from the database and for those who do not like to get their hands dirty writing Java (or other) fixtures, the Java Persistence API is definitely not the way to go.

Like I said, FitNesse offers many ways to access the database from the FitNesse page or from somewhere just below the FitNesse page, with an absolute minimal amount of (Java) programming. Below I list a couple of options. I never worked actively with any of these options, but I am familiar with them from reading through the documentation.

  • DbFit by Goijko Adzic. This seems to be a viable alternative to placing SQL queries in the Java layer. Adzic’ fixture set seems to be pretty complete and I recently learned that it is actually used in a data warehousing project in the Netherlands.
  • JdbcFixtures. This set of fixtures is limited and probably it can only be used for basic stuff.
  • A generic FitNesse fixture that is ready to use. The fixture mentioned was written by Anubhava Srivastava. It is perfectly fine to use code that someone else offers for use. But you’d have to know programming to check the code, adjust it to your needs or develop it further.
  • FitNesse QueryTable. This requires you to write the actual query in the Java fixture, so (a bit of) programming is involved. The table itself offers a nice way to return more than one row of data and check for certain outcomes.

Three of the options listed above allow for the tester to write the actual SQL statements in the FitNesse page. The tester does not have to know a lot about drivers and connections to write tests that fetch data from the database or even manipulate data that is in the database. The statements are executed ‘under water’ and the result is displayed in the FitNesse page. It is a nice way to quickly start using SQL queries and to keep all the logic in the same place, namely the FitNesse page.

I dismissed the use of SQL statements in FitNesse pages rather quickly after I started on the project. We had some pages in which SQL statements were used. In my opinion SQL statements were technical (implementation) details and I wanted to place all technical details in the layer that, according to me, they belong in – which is the programming (in my case Java) layer.

Besides that I thought that scattering many SQL statements across many FitNesse pages would seriously impact the maintainability of statements should the developers ever decide to change some of the tables. Actually there is a solution for the maintainability problem: build modular tests (building blocks) in FitNesse. If a query is used by many pages, build a separate FitNesse page for this query only, parametrize the page using variables and include (FitNesse: !include) it in the pages that use the query. I use the modular approach in FitNesse a lot and it works brilliantly. I will write more about this later.

A second objection to writing SQL statements in FitNesse pages is that it would probably take a while to find out which table is queried in which page, in what way. FitNesse has reasonable search capacities, but having to search for queries all the time is not ideal. The modular approach outlined above may fix this issue partly.

All in all I still had five major objections to placing SQL statements in FitNesse pages.

  1. Maintainability. This I explained above.
  2. Testability. I like to test my queries 🙂 Typos are quickly made and sometimes not that easy to find. Complex joins also invite mistakes.
  3. Handling of exceptions. Some queries may return nothing (no rows or null) or errors. FitNesse may not be the proper tool to handle these exceptions.
  4. Managing connections. Again, there are better ways to manage connections than in FitNesse pages.
  5. Handling of types. I am not sure if this is actually a problem. Fields in the database can be of many types (VARCHAR, FLOAT, DATE, TIMESTAMP, LONG, CHAR,  BLOB etc…). I think FitNesse ‘represents’ everything as text. I think this works out ok for non-text types, but I haven’t tested it. In Java, when fetching different types of data from the database, the type is something you carefully have to pay attention to.

So I decided to embed SQL statements in the Java layer (the Java fixtures) that is beneath FitNesse. However, here the maintainability problem remains. Sure, the testing of queries is made much easier by using jUnit. Connections can definitely be managed better in the Java layer. And Java has tools to handle exceptions programmatically. But you still run the risk of scattering SQL queries in all shapes and sizes all across the code.

This last observation eventually led me on the path to the Java Persistence API, about which I will write in a follow-up blog post.