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, 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 doobie._
import 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
class AnalysisTestSpec extends Specification with doobie.specs2.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:68)
// [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:68)
// [info]
// [error] x C05 indepyear SMALLINT (int2) NULL →
// [error] Column is unused. Remove it from the SELECT statement. (analysisspec.scala:68)
// [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:68)
// [info]
// [info]
// [info]
// [info] Total for specification AnalysisTestSpec
// [info] Finished in 130 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._
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](
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:
// - 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:66)
// - 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:66)
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._
class AnalysisTestSuite extends FunSuite with doobie.munit.IOChecker {
override val colors = 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 transcator.
import _root_.weaver._
import doobie.weaver._
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) }
}