Unit testing a database connection and general questions on database-dependent code and unit testing

If I have a method which establishes a database connection, how could this method be tested? Returning a bool in the event of a successful connection is one way, but is that the best way?

From a testability method, is it best to have the connection method as one method and the method to get data back a seperate method?

Also, how would I test methods which get back data from a database? I may do an assert against expected data but the actual data can change and still be the right resultset.

EDIT: For the last point, to check data, if it's supposed to be a list of cars, then I can check they are real car models. Or if they are a bunch of web servers, I can have a list of existant web servers on the system, return that from the code under test, and get the test result. If the results are different, the data is the issue but the query not?

THnaks

First, if you have involved a database, you are no longer unit testing. You have entered integration (for connection configuration) or functional testing land. And those are very different beasts.

The connection method should definitely be separate from data fetch. In fact, your connection should come from a factory so that you can pool it. As far as testing the connection, really all you can test is that your configuration is correct by making a connection to the DB. You shouldn't be trying to test your connection pool, as that should probably be a library someone else wrote (dbcp or c3p0). Furthermore, you probably can't test this, as your unit/integration/function tests should NEVER connect to a production level database.

As for testing that your data access code works. That's functional testing and involves a lot of framework and support. You need a separate testing DB, the ability to create the schema on the fly during testing, insert any static data into table, and return the database to a known clean state after each tests. Furthermore, this DB should be instantiated and run in such a way that 2 people can run the tests at once. Especially if you have more than 1 developer, plus an automated testing box.

Asserts should be against data that is either static data (list of states for example, that doesn't change often) or against data that is inserted during the test and removed afterwords so it doesn't interfere with other tests.

EDIT: As noted, there are frameworks to assist with this. DBUnit is fairly common.

You can grab ideas from here. I would go for mock objects when unit testing DB.

Otherwise, if application is huge and you are running long and complex unit tests, you can also virtualize your DB server and easily revert it to a saved snapshot to run again your tests on a known environment.

Using my Acolyte framework ( https://github.com/cchantep/acolyte ) you can mimick any JDBC supported DB, describing cases (how to handle each query/update executed) and which resultset/updatecount to returned in each case (describe fixtures as row list for queries, count for update).

Such connection can be directly used passing instance where JDBC is required, or registered with unique id in JDBC URL namespace jdbc:acolyte: to be available for code getting connection thanks to JDBC URL resolution.

Whatever way of creating connection, Acolyte keep each one isolated which is right for unit test (without having extra cleanup to do on a test DB).

As persistence cases can dispatched to different isolated connection, you no longer need a big-all-in-on-hard-to-manage db (or fixtures file): it can be easily split in various connection, e.g. one per persistence method/module.

My Acolyte framework is usable either in pure Java, or Scala.

If the goal is to test method functionality, not the database SP or SQL statement, then you may want to consider dependency injection in sense of data provider interface. In other words, your class uses an interface with methods returning data. The default implementation uses the database. The unit test implementation has several options:

• mocking (NMock, Moq, etc.), great way, I live mocking.
• in-memory database
• static database with static data

I don't like anything but first. As a general rule, programming to interfaces is always much more flexible.

For database connection establish testing: you could let the connection execute a very simple SQL as testing method. Some application servers have such configuration, following snippet is from JBoss DB configuration:

<!--  sql to call on an existing pooled connection when it is obtained from pool 
<check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>