When writing unit tests, should I specify the query that will be performed for interacting with the database?

I can see both sides of this. On one hand, I want to make sure that the query that I specify is being performed. But I can be making the test more brittle due to the formatting of the query.

EDIT: Basically the code would be a mapper object that interacts with the db layer. But I am wondering how specific do I need to be in my interactions with a Db object.


As I want to mock any external dependencies to my objects when I am mocking my Database connection. Should I specify the query that is going to be performed.

For example:

testGettingUsers() {
    $mockDb = $this->getMockBuilder('My_DB_Connection')

         ->with('SELECT id, name FROM USERS WHERE name = foo')

Here I have specified my query as the parameter that I am going to call my DB connection with. Is that necessary?

  • What are you actually testing in the unit test? – Robert Harvey Jan 29 '13 at 16:43
  • Can you provide an example? – NikolaiDante Jan 29 '13 at 16:52

Each of your classes should have a specific responsibility (what it does) and it should only do that thing (a single responsibility) and it should do it well. Your testing should ensure that the object in question does what it is supposed to.

With regards to a mapper object we are concerned that it can properly map from a data source (we don't care what the underlying source is) to a given object (say a domain object). With this in mind it should be safe to assume the that actual query for your mapping is contained within methods on your mapper object.

public class CustomerMapper {
  private DatabaseObject _myDb;
  public Customer FindCustomerWithId(int id){
    return _myDb.Query("SELECT * FROM CUSTOMERS WHERE ID = " + id).FirstOrDefault();

In this case your unit test would simply test the FindCustomerWithId method and not concern itself with the given query.

public class CustomerMapperTests {
  public void test_finding_a_customer_by_id() {
     var systemUnderTest = new CustomerMapper();
     var customer = CustomerMapper.FindCustomerWithId(5);
     customer.ShoudlNotBeNull(); // Assertion

Now if we were to test the DatabaseObject class then it would make sense to test actual queries to ensure that said class handles them as expected.

public class DatabaseObjectTests {
  public void test_that_it_can_handle_a_count_query(){
    var systemUnderTest = new DatabaseObject();
    var numberOfCustomers = systemUnderTest.Query<int>("SELECT COUNT(*) FROM CUSTOMERS);

Edit To address the idea of mocking the database object during the CustomerMapper test I would not concern myself with queries or SQL there as well. Something along the lines of:

public void test_finding_a_customer(){
  var mock = new Mock<DatabaseObject>();
  mock.Setup(m => m.Query(It.IsAny<string>()).Returns(new Customer { Id = 5 });
  var systemUnderTest = new CustomerMapper(mock.Object);
  var customer = CustomerMapper.FindCustomerWithId(5);
  customer.ShoudlNotBeNull(); // Assertion
  • What about the db mock for the CustomerMapper test? – Schleis Jan 29 '13 at 17:51
  • Edited to address the idea of a mock for the database object. – Mike Jan 29 '13 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.