A Database for Unit Testing


3 Dec 2007: Here are some notes of a Mayfly discussion at the Simple Design & Testing 2007 conference last weekend.

17 Feb 2007: Version 0.3 released. Adds ALTER TABLE, subselects, LIKE, the ability to dump the database as SQL, CASE, hex constants for BLOBs (x'hexdigits'), ALTER TABLE and more (see the NEWS file).

Also see our mailing lists and sourceforge news page.

Why Mayfly?

Although there are a vast number of free and non-free SQL implementations in Java, few of them are really good for unit testing. A database for unit testing should be very fast on small data sets and should not present performance or other obstacles to setting up databases, adding and removing tables, and destroying databases. A typical unit test run will want to create thousands of databases. With most SQL databases, this is not achievable, and one must resort to compromises such as keeping the tables around and only setting up data on each unit test run. Mayfly aims to make creating an in-memory SQL database as easy as creating any other in-memory data structure.

Some of what has been implemented so far:

  • The ability to very cheaply snapshot the database (both metadata and data) and restore from that point.
  • Your choice of JDBC or the Mayfly-specific Database class
  • The ability to dump the database to SQL (with the dump format being designed for unit testing operations like string compare on two dumps)
  • Inner and left joins.
  • WHERE clauses, many subselects
  • Integer, decimal and string data types. Auto-increment columns. ALTER TABLE.
  • Primary and foreign keys.

More background and discussion

Jim Kingdon on Programming (often about Mayfly)

Mayfly and Hibernate

Notes from talk at Simple Design & Testing 2007

See Martin Fowler's InMemoryDatabase.

One good SQL reference/tutorial site is SQLzoo.