If you read technical stuff on testing, you will have read at least five times “unit-tests should be isolated from the DB”, as it makes them faster, and easier to manage, and much less brittle.
What I wrote above is true, BUT, what if you need to test throughput, or feature stability, or data scalability? The unit tests won't help. In a current project, the most common source of failure of data not getting into the DB (outside of my control), the second most common source of failure is the DB integration breaking, due to an internal buffer running out of allowed RAM, cluster replication delay, or the entire thing just taking too long and being aborted, or commercial people wanting a different sort, etc etc. In pre-alpha, we had to iterate data-types several times, as had number overflow, and excessive volumes. All these issues would not be covered the unit-tests.

Research requirements

  • How can I exec a DB as a unit-test?
  • How can I define metrics that define success?
  • If I have an isolated “test DB”, which is the same volume as the live product, how do I ensure it is up-to-date with the new releases?

Some process notes on DB testing, which I reference in place of an intro paragraph 1 2 3 4 5 6. Assuming there is a build platform like Jenkins, having test tools in a different language to the main code isn't an issue. Simple unit tests can be built with temporary tables, and less specialised tools. The focus of that style of test is business logic testing, which you will note is not what this article is about. For testing business logic in databases 7.
For NoSQL? generically, the best tool I have been able to find is NoSQLunit. It lists support for a eight different NoSQL? datastores, including Elasticsearch, and MongoDB?. As with many of the tools on this article, it is written in Java, linking into JUnit; and is hosted on Maven. The authors don't seem to have heard of TAP. Some sample code 8 doesn't show much concentration on aggregates, but does have some support for them, by looking at the errors 9.
For Postgres based products, I like the look of pgTap (see notes in the solutions section). This has the ability to run storproc 10 11, which is an important point. It is implemented in Perl, and supports all basic test items (e.g. performance limiting 12 ). pgTap also supports making statements about triggers (the other item that is outside of most test and VCS tech trees). It would be an interesting but not immediate project to port to Mongo, Redis etc.
For Oracle based projects, there is PL/unit 13, as a more corporate project 14, it has different paperwork. This supports executing storeproc 15.

The database needs to have a known quality of performance for the following metrics:

  • In the below, substitute “aggregate” for “storproc”, “mapreduce” etc as needed for your platforms features. Referencing the feature on mature platforms, where data manipulation operations can be done, and return more useful information, than a plain read/ query.
  • can do an project-realistic insert inside an acceptable time (testing against having too many indices).
  • can do a project-realistic lookup, as a plain query, measuring time.
  • can do a project-realistic lookup, as a plain query, measuring RAM used (in DB server).
  • can do a project-realistic lookup, as a plain query, measuring RAM used (in scripting language).
  • can do a project-realistic lookup, as a plain query, with decomposition into smaller queries. Using smaller queries increases RAM requirements on the scripting language outside of the DB.
  • Can do a project-realistic aggregate, measuring time used.
  • Can do a project-realistic aggregate, measuring RAM used.
  • Test if the above read-side operations return the expected volume of data. This line item is to trap ms-excel, which sometimes lies about how much data it processes, on large documents.
  • As unit tests need to be repeatable, ensure any inserts are reverted.

The last point neatly segues into my third objective. To be able to run test repeatedly, and from a known starting point; I need to have management of datastore state. This is hard with most databases, when you have more than one connection; the lack of flexibility on that is to allow larger volumes of data in the data store. However DBunit would be a useful tool for a Java shop, and supports quite a few RDBMS 16 (I assume via JDBC). It stores data in XML, so large scale alterations are probably quite slow. It is quite well documented 17. It is unfortunate that this is a jUnit extension, for all the people who aren't paid for Java. DBunit only supports standard SQL types, taken from JDBC, so it is less flexible than desired. There is a DbUnit.NET for unfortunate people stuck with .net 2 (a MSFT framework from 2005). There is an alternative JDBDT, which seems to have more test features. It is also quite well documented, and can save test data as XML; but the demo code is really not focused on the “maintain db state” objective.

Practical solutions

As far as this article is research for me; this section is irrelevant; however to anyone-else without this, it lacks any conclusion.

  • Firstly, always separate the I/O of DB access from business logic. As my paid work is normally abit complex for ORM, so I normally manage my own Models. Models should not need logic branches, and shouldn't do any data translations. This means the DB can be swapped if needed, and can be mocked if needed. The bit of code needing the unit-testing most strongly is the business logic that sits on top of the models. Secondly each Model normally represent 1 Entity, so is wrapping one table or collection. Keeping each Entity definition separate reduces the tendency for logic branching.
  • I'm sure everyone knows this, but most DB support replication. All big complex reads-for-reporting should be done to a read-only slave/ secondary copy of the DB. One can also do read side bench-marking on the same read-slave, which should have same data as live.
  • If I have alot of time, I put the product schema into an SQLlite file, and a thousand rows in it. This makes is a small file that can be checked into a VCS, and I write unit tests against that Fixture. This reduces the amount of mock code that is needed, and I think is easier to manage. As the Fixture is a known value, it is possible to compare an object-under-test outcome to a literal value. This recipe is strong for business logic e.g. “commercial sorts”, and less use for performance, data volume, execution speed type of tests. The unit tests are written in the same test tool as the other unit-tests.
  • In previous contracts I created tools to extract dataschema from the live datastore, extract a sample 100,000 rows, anonymise it, and write to a test machine. The DB creation process took about two hours to execute.
  • For products wrapping Postgres, pgTap 18 seems a very useful tool. This needs to be integrated with a task scheduler like Jenkins 19. pgTap has a complete range of test primitives, like other test:: namespace modules tend to.