Scala, Slick & PostgreSQL Unit Tests

When I first started writing DAO tests in Scala/Slick with PostgreSQL I would use a trait to create a new database for each individual test. I kept track of them in a list and dropped them in an afterAll() method. This worked well, except I found it too slow for more than a trivial number of tests. This post shows my current much faster approach.

I’m currently using a trait that creates a new database for each spec—named after the test class so multiple tests can run simultaneously—and drops this database after the tests have run. Here it is (import & package lines omitted for brevity):

trait PostgresSpec extends Suite with BeforeAndAfterAll {
  private val dbname = getClass.getSimpleName.toLowerCase
  private val driver = "org.postgresql.Driver"

  private val postgres = Database.forURL("jdbc:postgresql:postgres", driver = driver)
  postgres withSession {
    Q.updateNA(s"DROP DATABASE IF EXISTS $dbname").execute()
    Q.updateNA(s"CREATE DATABASE $dbname").execute()
  }

  override def afterAll() {
    postgres withSession Q.updateNA(s"DROP DATABASE $dbname").execute()
  }

  val database = Database.forURL(s"jdbc:postgresql:$dbname", driver = driver)
}

The above trait doesn’t mandate any particular testing style. (I’ve used FlatSpec in the below example, but you can use any of the other styles ScalaTest supports.) In your test class you have to use the BeforeAndAfter trait to create & drop the schema before and after each test. Here’s an example:

class PostgresUserDaoSpec extends FlatSpec with PostgresSpec with MustMatchers with BeforeAndAfter {

  import Schema._

  before {
    database withSession
      Schema.ddl.create
  }

  after {
    database withSession
      Schema.ddl.drop
  }

  trait Case {
    val dao = new PostgresUserDao with DatabaseProvider {
      val db = database
    }
    val user = User(UUID.randomUUID(), "foo@example.com", new DateTime)
    val user2 = User(UUID.randomUUID(), "bar@example.com", new DateTime)
  }

  "register" should "create a record in the user table" in new Case {
    dao.register(user)
    database withSession {
      Q.queryNA[Int]("select count(*) from public.user").first() must be(1)
    }
  }

  it should "allow multiple records" in new Case {
    dao.register(user)
    dao.register(user2) must be(true)
    database withSession {
      Q.queryNA[Int]("select count(*) from public.user").first() must be(2)
    }
  }

  // ... more tests ...
}

I would have preferred to run each test in a transaction and roll back afterwards, but I couldn’t figure out how to make Slick do that. So I went for the next best thing and dropped & re-created the tables for each test.

Posted 16 Jun 2013 in Scala, Development. Comment or Share it on Twitter.

Re-designing for readers was the previous article. The next article is Spray Routing Error Handling. You can browse articles by tag or view all articles in a chronological list.