Superloopy Logo

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.

Date: 2013-06-16

Author: Stig Brautaset

Created: 2017-06-19 Mon 12:17

Validate