How (not) to approach persistence testing in Java and Groovy

Testing a Persistence Layer

The first thing we need to test is persisting User instances to a database.
We’ll create a data access object (DAO) class DataStore with methods for writing to and reading from the database.

We’ll begin with a feature method that tests storing a user object.
Don’t worry about how the handle and dataStore fields are initialized right now: we’re coming to that.
All you need to know at the moment is that handle is the direct connection to the database, and dataStore is the DAO we’re testing.

Handle handle
@Subject DataStore dataStore

def "can insert a user object"() {
  given:
  def clock = Clock.fixed(now(), UTC) 

  and:
  def user = new User("spock", clock.instant()) 

  when:
  dataStore.insert(user) 

  then:
  def iterator = handle.createQuery("select username, registered from user")
                       .iterator() 
  iterator.hasNext() 
  with(iterator.next()) {
    username == user.username
    registered.time == clock.instant().toEpochMilli()
  } 

  and:
  !iterator.hasNext() 
}

Because the test needs to ensure the registered timestamp is inserted to the database correctly, we’ll use a fixed clock to generate the timestamps.

Create a user object.

Invoke the insert method of our DAO passing the user.

Query the database directly.

Assert that we get a first row back.

Assert that the username and registered values on the first row correspond to the values on the user object.

Assert that no further rows were found.

The feature method is reasonably straightforward; however, one thing merits discussion. The test queries the database directly to verify the insert operation has worked.
It would likely result in more concise code if the test used another DAO method to read the database back again.
This feels wrong, though.
The DAO class is the subject of the test so if any of the assertions failed, it would not be possible to determine whether the problem lies in inserting the data or reading it back.
The least ambiguity in any potential failure arises if the test reads the database directly.

That’s not to say that reading the database is always the right thing to do.
The question, as always, is what behavior am I trying to test here?
In this case, the question we’re interested in is does the persistence layer work? Is the correct data being written to the database?
Given that, it’s appropriate to directly read the database.
A browser-based end-to-end test that fills in a form should almost certainly not then look directly in the database to see if the data was persisted correctly.
Even a test for a different aspect of the DAO might use the insertion and query methods if their behavior is adequately covered elsewhere.

Similarly, we will want to test that data is read from the database correctly.
In that case, it’s appropriate to insert data directly to the database because we’re interested in the translation between database rows and objects.

Let’s add that feature method to the specification:

def "can retrieve a list of user objects"() {
  given:
  def timestamp = LocalDateTime.of(1966, 9, 8, 20, 0).toInstant(UTC)
  ["kirk", "spock"].each {
    handle.createStatement("""insert into user (username, registered)
                              values (?, ?)""")
          .bind(0, it)
          .bind(1, timestamp)
          .execute()
  }

  when:
  def users = dataStore.findAllUsers()

  then:
  with(users.toList()) {
    username == ["kirk", "spock"]
    registered.every {
      it == timestamp
    }
  }
}

@Subject DataStore dataStore

def dbi = new DBI("jdbc:h2:mem:test")
Handle handle

def setup() {
  dbi.registerArgumentFactory(new TimeTypesArgumentFactory())
  dbi.registerMapper(new TimeTypesMapperFactory())

  handle = dbi.open()
  dataStore = handle.attach(DataStore)

  dataStore.createUserTable()
}

def cleanup() {
  handle.execute("drop table user if exists")
  handle.close()
}

@Subject DataStore dataStore

@Shared dbi = new DBI("jdbc:h2:mem:test") 
Handle handle

def setupSpec() { 
  dbi.registerArgumentFactory(new TimeTypesArgumentFactory())
  dbi.registerMapper(new TimeTypesMapperFactory())
}

def setup() {
  handle = dbi.open()
  dataStore = handle.attach(DataStore)

  dataStore.createUserTable()
}

def cleanup() {
  handle.execute("drop table user if exists")
  handle.close()
}

@Subject @Shared DataStore dataStore 

@Shared dbi = new DBI("jdbc:h2:mem:test")
@Shared Handle handle 

def setupSpec() {
  dbi.registerArgumentFactory(new TimeTypesArgumentFactory())
  dbi.registerMapper(new TimeTypesMapperFactory())

  handle = dbi.open() 
  dataStore = handle.attach(DataStore)
  dataStore.createUserTable()
}

def cleanupSpec() { 
  handle.execute("drop table user if exists")
  handle.close()
}

def cleanup() {
  handle.execute("delete from user") 
}

def "can insert a user object"() {
  given:
  def clock = Clock.fixed(now(), UTC)

  and:
  def user = new User("spock", clock.instant())

  expect:
  rowCount("user") == 0 

  when:
  dataStore.insert(user)

  then:
  def iterator = handle.createQuery("select username, registered from user")
                       .iterator()
  iterator.hasNext()
  with(iterator.next()) {
    username == user.username
    registered.time == clock.instant().toEpochMilli()
  }

  and:
  !iterator.hasNext()
}

private int rowCount(String table) {
  handle.createQuery("select count(*) from $table")
        .map(IntegerColumnMapper.PRIMITIVE)
        .first()
} 

Specifications and Inheritance

The lifecycle management that the specification is doing is probably not just applicable to tests for persisting users, but for similar tests that also need to integrate with the database.
So far we’ve made the User class persistent, but we need to do the same for the Message class.
We’ll add some methods to the DataStore DAO with a specification that tests reading from and writing to the database:

class MessagePersistenceSpec extends Specification {

  @Subject @Shared DataStore dataStore
  User kirk, spock

  @Shared dbi = new DBI("jdbc:h2:mem:test")
  @Shared Handle handle

  def setupSpec() {
    dbi.registerArgumentFactory(new TimeTypesArgumentFactory())
    dbi.registerMapper(new TimeTypesMapperFactory())

    handle = dbi.open()

    dataStore = handle.attach(DataStore)

    dataStore.createUserTable()
    dataStore.createMessageTable()
  }

  def cleanupSpec() {
    handle.execute("drop table message if exists")
    handle.execute("drop table user if exists")
    handle.close()
  }

  def setup() {
    kirk = new User("kirk")
    spock = new User("spock")
    [kirk, spock].each { dataStore.insert(it) }
  }

  def cleanup() {
    handle.execute("delete from message")
    handle.execute("delete from user")
  }

  def "can retrieve a list of messages posted by a user"() {
    given:
    insertMessage(kirk, "@khan KHAAANNN!")
    insertMessage(spock, "Fascinating!")
    insertMessage(spock, "@kirk That is illogical, Captain.")

    when:
    def posts = dataStore.postsBy(spock)

    then:
    with(posts) {
      size() == 2
      postedBy.every { it == spock }
    }
  }

  def "can insert a message"() {
    given:
    def clock = Clock.fixed(now(), UTC)
    def message = spock.post(
      "@bones I was merely stating a fact, Doctor.",
      clock.instant()
    )

    when:
    dataStore.insert(message)

    then:
    def iterator = handle.createQuery("""select u.username, m.text, m.posted_at
                                         from message m, user u
                                         where m.posted_by_id = u.id""")
                         .iterator()
    iterator.hasNext()
    with(iterator.next()) {
      text == message.text
      username == message.postedBy.username
      posted_at.time == clock.instant().toEpochMilli()
    }

    and:
    !iterator.hasNext()
  }

  private void insertMessage(User postedBy, String text) {
    handle.createStatement("""insert into message
                              (posted_by_id, text, posted_at)
                              select id, ?, ? from user where username = ?""")
          .bind(0, text)
          .bind(1, now())
          .bind(2, postedBy.username)
          .execute()
  }
}

This code is doing an awful lot of the same work as the test for user persistence.
It would make sense to extract a common superclass that can do some of the lifecycle management and provide some utility methods, such as the rowCount method we used earlier.

One of the advantages of the fact that @Shared fields and the setupSpec and cleanupSpec methods are nonstatic is that they can participate in inheritance hierarchies.
Let’s refactor and extract a superclass:

abstract class BasePersistenceSpec extends Specification {

  @Shared DataStore dataStore

  @Shared dbi = new DBI("jdbc:h2:mem:test")
  @Shared Handle handle

  def setupSpec() {
    dbi.registerArgumentFactory(new TimeTypesArgumentFactory())
    dbi.registerMapper(new TimeTypesMapperFactory())

    handle = dbi.open()
    dataStore = handle.attach(DataStore)
    dataStore.createUserTable()
  }

  def cleanupSpec() {
    handle.execute("drop table user if exists")
    handle.close()
  }

  def cleanup() {
    handle.execute("delete from user")
  }

  protected int rowCount(String table) {
    handle.createQuery("select count(*) from $table")
          .map(IntegerColumnMapper.PRIMITIVE)
          .first()
  }
}

Here, we’ve simply moved all the lifecycle methods and fields up from MessagePersistenceSpec.
The @Subject annotation is gone from the dataStore field because it’s no longer appropriate, and the rowCount method is now protected rather than private.
Otherwise, the code is unchanged.

We don’t need anything else for the UserPersistenceSpec class, but MessagePersistenceSpec has to manage the message table as well as the user table.

The feature methods remain unchanged but we can now remove the common parts of the lifecycle management code that are currently handled by the superclass:

class MessagePersistenceSpec extends BasePersistenceSpec {
  User kirk, spock
  
  def setupSpec() {
    dataStore.createMessageTable()
  }
  
  def cleanupSpec() {
    handle.execute("drop table message if exists")
  }
  
  def setup() {
    kirk = new User("kirk")
    spock = new User("spock")
    [kirk, spock].each { dataStore.insert(it) }
  }
  
  def cleanup() {
    handle.execute("delete from message")
  }

If you’re paying attention, you might notice something missing from the lifecycle methods in this derived class.
None of them are invoking the superclass method they override!
Because forgetting to do so will likely cause problems that can be difficult to debug and could be prone to copy-and-paste errors, Spock helps you by doing the right thing automatically.

If a specification’s superclass has any of the lifecycle management methods, they are automatically executed along with those of the specification itself.
It is not necessary to call super.setup() from a specification’s setup method, for example.

abstract class SuperSpec extends Specification {
  def setupSpec() {
    println "> super setupSpec"
  }

  def cleanupSpec() {
    println "> super cleanupSpec"
  }

  def setup() {
    println "--> super setup"
  }

  def cleanup() {
    println "--> super cleanup"
  }
}

class SubSpec extends SuperSpec {
  def setupSpec() {
    println "-> sub setupSpec"
  }

  def cleanupSpec() {
    println "-> sub cleanupSpec"
  }

  def setup() {
    println "---> sub setup"
  }

  def cleanup() {
    println "---> sub cleanup"
  }

  def "feature method 1"() {
    println "----> feature method 1"
    expect:
    2 * 2 == 4
  }

  def "feature method 2"() {
    println "----> feature method 2"
    expect:
    3 * 2 == 6
  }
}

> super setupSpec
-> sub setupSpec
--> super setup
---> sub setup
----> feature method 1
---> sub cleanup
--> super cleanup
--> super setup
---> sub setup
----> feature method 2
---> sub cleanup
--> super cleanup
-> sub cleanupSpec
> super cleanupSpec