Unit Testing

The YOLO-mode query checking feature demonstated in an earlier chapter is also available as a trait you can mix into your Specs2 or ScalaTest unit tests.

Setting Up

As with earlier chapters we set up a Transactor and YOLO mode. We will also use the doobie-specs2 and doobie-scalatest add-ons.

import doobie._
import doobie.implicits._
import cats._
import cats.data._
import cats.effect._
import cats.implicits._

// We need a ContextShift[IO] before we can construct a Transactor[IO]. The passed ExecutionContext
// is where nonblocking operations will be executed. For testing here we're using a synchronous EC.
implicit val cs = IO.contextShift(ExecutionContexts.synchronous)

// A transactor that gets connections from java.sql.DriverManager and executes blocking operations
// on an our synchronous EC. See the chapter on connection handling for more info.
val xa = Transactor.fromDriverManager[IO](
  "org.postgresql.Driver",     // driver classname
  "jdbc:postgresql:world",     // connect URL (driver-specific)
  "postgres",                  // user
  "",                          // password
  Blocker.liftExecutionContext(ExecutionContexts.synchronous) // just for testing
)

And again we are playing with the country table, given here for reference.

CREATE TABLE country (
  code        character(3)  NOT NULL,
  name        text          NOT NULL,
  population  integer       NOT NULL,
  gnp         numeric(10,2),
  indepyear   smallint
  -- more columns, but we won't use them here
)

So here are a few queries we would like to check. Note that we can only check values of type Query0 and Update0; we can’t check Process or ConnectionIO values, so a good practice is to define your queries in a DAO module and apply further operations at a higher level.

case class Country(code: Int, name: String, pop: Int, gnp: Double)

val trivial =
  sql"""
    select 42, 'foo'::varchar
  """.query[(Int, String)]

def biggerThan(minPop: Short) =
  sql"""
    select code, name, population, gnp, indepyear
    from country
    where population > $minPop
  """.query[Country]

def update(oldName: String, newName: String) =
  sql"""
    update country set name = $newName where name = $oldName
  """.update

The Specs2 Package

The doobie-specs2 add-on provides a mix-in trait that we can add to a Specification to allow for typechecking of queries, interpreted as a set of specifications.

Our unit test needs to extend AnalysisSpec and must define a Transactor[IO]. To construct a testcase for a query, pass it to the check method. Note that query arguments are never used, so they can be any values that typecheck.

import org.specs2.mutable.Specification

class AnalysisTestSpec extends Specification with doobie.specs2.IOChecker {

  val transactor = Transactor.fromDriverManager[IO](
    "org.postgresql.Driver", "jdbc:postgresql:world", "postgres", ""
  )

  check(trivial)
  check(biggerThan(0))
  check(update("", ""))

}

When we run the test we get output similar to what we saw in the previous chapter on checking queries, but each item is now a test. Note that doing this in the REPL is a little awkward; in real source you would get the source file and line number associated with each query.

import _root_.specs2.{ run => runTest }
import _root_.org.specs2.main.{ Arguments, Report }

// Run a test programmatically. Usually you would do this from sbt, bloop, etc.
runTest(new AnalysisTestSpec)(Arguments(report = Report(_color = Some(false))))
// [info] AnalysisTestSpec
// [info] 
// [info] + Query0[(Int, String)] defined at 13-Unit-Testing.md:51
// [info]   
// [info]     select 42, 'foo'::varchar
// [info]   
// [info]   + SQL Compiles and TypeChecks
// [info]   + C01 ?column? INTEGER (int4)    NULL?  →  Int
// [info]   + C02 varchar  VARCHAR (varchar) NULL?  →  String
// [info] + Query0[App.Country] defined at 13-Unit-Testing.md:57
// [info]   
// [info]     select code, name, population, gnp, indepyear
// [info]     from country
// [info]     where population > ?
// [info]   
// [info]   + SQL Compiles and TypeChecks
// [error]   x P01 Short  →  INTEGER (int4)
// [error]    Short is not coercible to INTEGER (int4) according to the JDBC
//    specification. Expected schema type was SMALLINT. (analysisspec.scala:62)
// [info] 
// [error]   x C01 code       CHAR     (bpchar)  NOT NULL  →  Int
// [error]    CHAR (bpchar) is ostensibly coercible to Int according to the JDBC
//    specification but is not a recommended target type. Expected schema
//    type was INTEGER. (analysisspec.scala:62)
// [info] 
// [info]   + C02 name       VARCHAR  (varchar) NOT NULL  →  String
// [info]   + C03 population INTEGER  (int4)    NOT NULL  →  Int
// [error]   x C04 gnp        NUMERIC  (numeric) NULL      →  Double
// [error]    NUMERIC (numeric) is ostensibly coercible to Double according to the
//    JDBC specification but is not a recommended target type. Expected
//    schema type was FLOAT or DOUBLE.
//    Reading a NULL value into Double will result in a runtime failure. Fix
//    this by making the schema type NOT NULL or by changing the Scala type
//    to Option[Double] (analysisspec.scala:62)
// [info] 
// [error]   x C05 indepyear  SMALLINT (int2)    NULL      →  
// [error]    Column is unused. Remove it from the SELECT statement. (analysisspec.scala:62)
// [info] 
// [info] + Update0 defined at 13-Unit-Testing.md:65
// [info]   
// [info]     update country set name = ? where name = ?
// [info]   
// [info]   + SQL Compiles and TypeChecks
// [info]   + P01 String  →  VARCHAR (varchar)
// [info]   + P02 String  →  VARCHAR (text)
// [info] 
// [info] 
// [info] Total for specification AnalysisTestSpec
// [info] Finished in 298 ms
// 16 examples, 4 failures, 0 error
// [info]

The ScalaTest Package

The doobie-scalatest add-on provides a mix-in trait that we can add to any Assertions implementation (like AnyFunSuite) much like the Specs2 package above.

import org.scalatest._

class AnalysisTestScalaCheck extends funsuite.AnyFunSuite with matchers.must.Matchers with doobie.scalatest.IOChecker {

  override val colors = doobie.util.Colors.None // just for docs

  val transactor = Transactor.fromDriverManager[IO](
    "org.postgresql.Driver", "jdbc:postgresql:world", "postgres", ""
  )

  test("trivial")    { check(trivial)        }
  test("biggerThan") { check(biggerThan(0))  }
  test("update")     { check(update("", "")) }

}

Details are shown for failing tests.

// Run a test programmatically. Usually you would do this from sbt, bloop, etc.
(new AnalysisTestScalaCheck).execute(color = false)
// Session$App$AnalysisTestScalaCheck:
// - trivial
// - biggerThan *** FAILED ***
//   Query0[App.Country] defined at 13-Unit-Testing.md:57
//     select code, name, population, gnp, indepyear
//     from country
//     where population > ?
//     ✓ SQL Compiles and TypeChecks
//     ✕ P01 Short  →  INTEGER (int4)
//       Short is not coercible to INTEGER (int4) according to the JDBC
//       specification. Expected schema type was SMALLINT.
//     ✕ C01 code       CHAR     (bpchar)  NOT NULL  →  Int
//       CHAR (bpchar) is ostensibly coercible to Int according to the JDBC
//       specification but is not a recommended target type. Expected
//       schema type was INTEGER.
//     ✓ C02 name       VARCHAR  (varchar) NOT NULL  →  String
//     ✓ C03 population INTEGER  (int4)    NOT NULL  →  Int
//     ✕ C04 gnp        NUMERIC  (numeric) NULL      →  Double
//       NUMERIC (numeric) is ostensibly coercible to Double according to
//       the JDBC specification but is not a recommended target type.
//       Expected schema type was FLOAT or DOUBLE.
//       Reading a NULL value into Double will result in a runtime failure.
//       Fix this by making the schema type NOT NULL or by changing the
//       Scala type to Option[Double]
//     ✕ C05 indepyear  SMALLINT (int2)    NULL      →  
//       Column is unused. Remove it from the SELECT statement. (Checker.scala:56)
// - update
The source code for this page can be found here.