Mock Database

MockObjects are a good thing, but simulating an entire database for testing purposes seems really complicated.

I've been writing code with MockObjects to stand in for various JDBC objects -- there's a MockConnection, a MockStatement, a MockResultSet, etc., etc. It's all well and good, except for the fact that my code currently passes upwards of 30 different SQL statements, and it's getting very cumbersome to do all this complex text parsing to figure out what kind of ResultSets the test code should be getting back.

In a theoretical sense, I suppose the problem is that although you only get one narrow interface to the database -- send it a SQL String, get back a ResultSet -- the database in fact represents a wealth of heterogeneous information. If you had all that information as objects, you'd break it up and make it all granular for easy testing/refactoring/what-have-you. But you don't have it all as objects, and so your tests get kind of crufty because the interface is way too narrow.

Have people had success writing MockObjects that take lots of different SQL statements? Is there a library out there that parses SQL into Java objects? Should I write one myself? Whew.

A GordianKnot approach: Don't use mere MockDatabases, create an entire TestDatabase. The TestDatabase only needs the fields of the tables that are being tested. Any extra fields or tables can be left out..

Perhaps you could make a different mock db for each query. Then you could ignore the query and simply return the result set you know it would have returned. Don't let the name trick you into thinking you need to build a database.

Right: If you are testing an object that issues two different SELECT statements, then the MockObject you use to test it need only determine which of the two is being issued, and return a predefined result for that select.

How can it do this?

Easy: Search the string for a table name that occurs in one of the SELECT statements and not the other.

How do you test the next object?

A: Create a new MockDatabase object just for it.

Objection: But then you'll be creating lots and lots of MockDatabase objects!

Response: Yes. That's exactly right. But each one is trivial. And soon you may realize that all your MockDatabase objects could inherit from a common base class so that you can leverage common functionality.

A MockDatabase should not need to actually interpret the SQL statements. Tests should be deterministic. Therefore a MockDatabase can be pre-loaded with a sequence of results to be returned from the calls made on it by the tested object. To test that the correct tables are being queried by the SQL statements generated by the tested object, use regular expressions or something equally simple.

--NatPryce

From: JavaUnit

I found a great way of creating static test data to mimic foreign databases which might be too dynamic for your testing taste: TinySQL (http://www.jepstone.net/tinySQL/) is a relatively full-featured (but admittedly slow) JDBC driver for ASCII and DBF files.

Most of my database software outputs XML, so comparing results to what is expected is fairly easy. All I need is a quick utility method to read the entire contents of a text file into a String variable.

Hope this helps,

-- AlexCruise

It's very easy to test database access objects using a database that supports an in-memory, in-process mode, like HypersonicSql, AxionDatabase (http://axion.tigris.org/) or McKoiDb? (http://mckoi.com/database/) / Daffodil DB (http://www.daffodildb.com). Then you've got something much closer to the real thing, without the bother of MockObjects.

I'm curious - for those of you out there who are using MockObjects to test JDBC code, do you typically have at least one unit test which uses a "real" database connection as a sanity check? Or do you keep live connections out of the unit testing altogether, letting this type of testing fall under the umbrella of "functional testing"? While I like the speed and focus of MockObjects for database testing, certain things about it make me nervous, e.g. SQL statements are not validated. --GregMerrill

I'm currently getting big into database mock objects with VbUnitThree. I'm currently ignoring the issue of testing the real database objects. But I know this is a mistake; I'll have to come back and write unit tests for them, just like any other class. -- JeffGrigg

OK, it bit me today: My unit tests didn't test the SQL, and so didn't catch a stray column. But that's OK, because the end-to-end functional tests (done in the same unit testing framework, only less often), caught the problem. No blood; no foul. (Yes, I still need to write unit tests for the SQL. I will, I will... ;-) -- jtg

Is it just me or is it really difficult to write a decent MockObject to handle SQL? Part of the problem is that the database is in fact the proxy for a million complicated cases -- if you owned them as objects, you can decompose them into easily testable components, but they're not, so you can't.

I'm finding I'm writing one MockStatement? that has to handle every kind of SQL that my program calls, and it's pretty damn complicated. I'm tripping over myself a lot. What suggestions do people have when you're writing a MockDatabase?

I think you might be biting off more than you can chew. In my view, MockObjects, like utility scripts, are primarily intended to be useful now. A MockObject library might be useful in the long-run, but will take more time to develop than is typically available on a project. Also, the MockObject is for testing the thing that uses the RealObject, not the RealObject itself. Instead of writing a generic MockObject to handle any SQL query or statement, just write a dummy object that has the necessary interface, and just enough state to fake it (e.g. with a predetermined/scripted response). For testing the SQL itself, I don't see anyway to get around it but to actually run the SQL and test the results. But at least, with MockObjects, you'll minimize how much of your test code depends on a live database. You can run the live query once to test it, and the mock query several times to test whatever depends on the query. That's how I use MockObjects anyway.

Right, I'm using them for that, too. But I'd like to test the actual object that pulls results out of the DB, and I'd like to do that without hitting the database every time I run JavaUnit. But, yeah, maybe it's too much. I don't seem to be the only person whose eyes are bigger than his stomach, though: Someone else is describing a similar problem on EasyMock.

I have a similar problem: I'm writing an API layer in Java over a database, and am currently writing the unit tests for this API. I'm new to such rigorous unit testing (don't think less of me;-), so am not entirely clear on what my MockDatabase should do.

Supposing you want to test a method that inserts a record into a table. To test that it works, you want to insert the record, and then see if it's there. I guess in the MockDatabase, you just check that the INSERT statement being passed in is correct. There is also an exception case where the record to be inserted has the same primary key as an existing record. How is that tested? Should the unit test merely test that the method knows what to do when a "unique constraint violated" SQLException comes rolling back at it, and leave the verification that the DB picks it up to the functional testing?

I have been toying with MockObjects and JdbcSpy driver. This is not TestFirst, but the JdbcSpy driver intercepts the sql, then creates MockObjects. So if you have a currently working LegacySystem and need to generate MockObjects for it, this works pretty well. -- Joe

I've figured out that to test a method that inserts a record into a table, you do NOT want to fetch the record to see that it's there. You only want to see that it was inserted. That means mocking the lower-level object that does the actual insert. Checking that a value exists in the table after inserting also tests the functionality of the database, which is way outside the scope of the unit test for this method. --ChristofferHammarstrom

See also: DbUnit, MockObjectsAndIntegrationTesting?, SqlFlaws