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

Question

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

Also, as I write this none of the three upvotes are from you! No change there then! — Mitch Wheat
– Mitch Wheat, Commented Jan 8, 2011 at 1:03
I wll bare that in mind. I norally upvote helpful answers but the not the actual votedanswer. — GurdeepS
– GurdeepS, Commented Jan 8, 2011 at 1:27

rfeak · Accepted Answer · 2011-01-08 00:59:59Z

16

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.

edited Jan 8, 2011 at 0:59

answered Jan 8, 2011 at 0:51

rfeak

8,23431 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Schultz9999 Over a year ago

The first paragraph says it all.

Tal Joffe Over a year ago

I agree but something to pay attention to is that some functionality might differ between DB versions, configurations etc. this means that integration testing is not enough and you should strive to have another level of testing (E2E, Alpha testing etc.) on which you connect to a DB that is a clone of production DB and test against it. (edit - just noticed this is a very old thread but might be useful to new readers)

Community · Accepted Answer · 2017-05-23 12:07:11Z

2

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.

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Jan 8, 2011 at 0:49

LudoMC

7608 silver badges17 bronze badges

2 Comments

Mitch Wheat Over a year ago

"you can also virtualize your DB server and easily revert it to a saved snapshot to run again your tests on a known environment" - that is integration testing, NOT unit testing.

LudoMC Over a year ago

You're right. I perhaps expanded the subject a little too far away from the original question.

cchantep · Accepted Answer · 2018-11-28 21:13:14Z

1

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.

edited Nov 28, 2018 at 21:13

answered Mar 1, 2014 at 11:52

cchantep

9,1783 gold badges32 silver badges43 bronze badges

1 Comment

Bren Over a year ago

I think this is useful information. Even if not the right approach for the question asked. If I am working on a connection pool library or any other library involving connections I would want to unit test it and this would help.

Schultz9999 · Accepted Answer · 2011-01-08 01:18:42Z

0

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.

answered Jan 8, 2011 at 1:18

Schultz9999

8,9669 gold badges55 silver badges90 bronze badges

Comments

卢声远 Shengyuan Lu · Accepted Answer · 2011-01-08 02:27:13Z

0

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>

answered Jan 8, 2011 at 2:27

卢声远 Shengyuan Lu

32.1k23 gold badges90 silver badges134 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related