Unit Testing
The YOLO-mode query checking feature demonstrated in an earlier chapter is also available as a trait you can mix into your Specs2, ScalaTest, MUnit or Weaver 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 org.typelevel.doobie._
import org.typelevel.doobie.implicits._
import cats._
import cats.data._
import cats.effect._
import cats.implicits._
// This is just for testing. Consider using cats.effect.IOApp instead of calling
// unsafe methods directly.
import cats.effect.unsafe.implicits.global
// 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](
driver = "org.postgresql.Driver", // JDBC driver classname
url = "jdbc:postgresql:world", // Connect URL - Driver specific
user = "postgres", // Database user name
password = "password", // Database password
logHandler = None // Don't setup logging for now. See Logging page for how to log events in detail
)
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]
val update: Update0 =
sql"""
update country set name = "new" where name = "old"
""".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
import org.typelevel.doobie.specs2.IOChecker
class AnalysisTestSpec extends Specification with IOChecker {
val transactor = Transactor.fromDriverManager[IO](
driver = "org.postgresql.Driver", url = "jdbc:postgresql:world", user = "postgres", password = "password", logHandler = None
)
check(trivial)
checkOutput(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[MdocApp.this.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 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:79)
// [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:79)
// [info]
// [error] x C05 indepyear SMALLINT (int2) NULL →
// [error] Column is unused. Remove it from the SELECT statement. (analysisspec.scala:79)
// [info]
// [info] + Update0 defined at 13-Unit-Testing.md:65
// [info]
// [info] update country set name = "new" where name = "old"
// [info]
// [error] x SQL Compiles and TypeChecks
// [error] ERROR: column "old" does not exist
// Position: 51 (analysisspec.scala:79)
// [info]
// [info]
// [info]
// [info] Total for specification AnalysisTestSpec
// [info] Finished in 346 ms
// 13 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._
import org.typelevel.doobie.scalatest.IOChecker
class AnalysisTestScalaCheck extends funsuite.AnyFunSuite with matchers.must.Matchers with IOChecker {
override val colors = org.typelevel.doobie.util.Colors.None // just for docs
val transactor = Transactor.fromDriverManager[IO](
driver = "org.postgresql.Driver", url = "jdbc:postgresql:world", user = "postgres", password = "password", logHandler = None
)
test("trivial") { check(trivial) }
test("biggerThan") { checkOutput(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)
// MdocSession$MdocApp$AnalysisTestScalaCheck$1:
// - trivial
// - biggerThan *** FAILED ***
// Query0[MdocApp.this.Country] defined at 13-Unit-Testing.md:57
// select code, name, population, gnp, indepyear
// from country
// where population > ?
// ✓ SQL Compiles and TypeChecks
// ✕ 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:72)
// - update *** FAILED ***
// Update0 defined at 13-Unit-Testing.md:65
// update country set name = "new" where name = "old"
// ✕ SQL Compiles and TypeChecks
// ERROR: column "old" does not exist
// Position: 51 (Checker.scala:72)
The MUnit Package
The doobie-munit add-on provides a mix-in trait that we can add to any Assertions implementation (like FunSuite) much like the ScalaTest package above.
import _root_.munit._
import org.typelevel.doobie.munit.IOChecker
class AnalysisTestSuite extends FunSuite with IOChecker {
override val colors = org.typelevel.doobie.util.Colors.None // just for docs
val transactor = Transactor.fromDriverManager[IO](
driver = "org.postgresql.Driver", url = "jdbc:postgresql:world", user = "postgres", password = "password", logHandler = None
)
test("trivial") { check(trivial) }
test("biggerThan") { checkOutput(biggerThan(0)) }
test("update") { check(update) }
}
The Weaver Package
The doobie-weaver add-on provides a mix-in trait what we can add to any effectful test Suite. The check function takes an implicit Transactor[F] parameter. Since Weaver has its own way to manage shared resources, it is convenient to use that to allocate the transactor.
import _root_.weaver._
import org.typelevel.doobie.weaver.IOChecker
object AnalysisTestSuite extends IOSuite with IOChecker {
override type Res = Transactor[IO]
override def sharedResource: Resource[IO,Res] =
Resource.pure(Transactor.fromDriverManager[IO](
driver = "org.postgresql.Driver", url = "jdbc:postgresql:world", user = "postgres", password = "password", logHandler = None
))
test("trivial") { implicit transactor => check(trivial) }
test("biggerThan") { implicit transactor => checkOutput(biggerThan(0)) }
test("update") { implicit transactor => check(update) }
}