Testing Doobie Interpreter Using Testcontainers

Question

I'm trying to test some SQL statements developed with doobie using Testcontainers libraries. In detail, I'm using the following dependencies:

"org.testcontainers" % "testcontainers" % "1.17.3" % Test,
"org.testcontainers" % "postgresql"     % "1.17.3" % Test

My domain model is straightforward:

final case class Company(id: Int, name: String, description: String)

I create the table on PostgreSQL using an init script:

CREATE TABLE companies (
  id serial NOT NULL,
  name text NOT NULL,
  description text
);

ALTER TABLE companies
ADD CONSTRAINT pk_companies PRIMARY KEY (id);

I'm trying to develop an integration test that verifies the insertion of a new Company. I'm using scalatest:

"org.scalatest" %% "scalatest"                     % scalaTestVersion           % Test,
"org.typelevel"  %% "cats-effect-testing-scalatest" % scalaTestCatsEffectVersion % Test

The code of the test is the following:

class CompaniesSpec extends AsyncFreeSpec with AsyncIOSpec with BeforeAndAfter with Matchers {

  private val transactor: Resource[IO, Transactor[IO]] = for {
    postgres <- makePostgres
    ce       <- ExecutionContexts.fixedThreadPool[IO](1)
    xa <- HikariTransactor.newHikariTransactor[IO](
      "org.postgresql.Driver",
      postgres.getJdbcUrl,
      postgres.getUsername,
      postgres.getPassword,
      ce
    )
  } yield xa

  private def makePostgres =
    Resource.make(IO {
      val container: PostgreSQLContainer[Nothing] =
        new PostgreSQLContainer().withInitScript("sql/companies.sql")
      container.start()
      container
    })(c => IO(c.stop()))

  "Companies algebra " - {
    "should create a new Company" in {
      (for {
        companyId    <- transactor.use { xa =>
          sql"INSERT INTO companies (name, description) VALUES ('My Company', 'My Company Description')"
            .update
            .withUniqueGeneratedKeys[Int]("id")
            .transact(xa)
        }
        maybeCompany <- transactor.use { xa =>
          sql"SELECT * FROM companies WHERE id = $companyId"
            .query[Company]
            .option
            .transact(xa)
        }
      } yield maybeCompany)
        .asserting { company =>
          company shouldBe defined
          company.get.name shouldBe "My Company"
          company.get.description shouldBe "My Company Description"
        }
    }
  }
}

However, the test's assertions fail with the following error, which indicates that the code is not inserting anything into the database.

None was not defined
ScalaTestFailureLocation: com.rockthejvm.board.playground.algebras.CompaniesSpec at (CompaniesSpec.scala:68)

I really can't understand why the test is failing. Can anyone help me?

Answer 1

makePostgres is a Resource which you compose with the HikariTransactor into the transaction Resource. That means that every time you call transaction.use{ ... } a new container is started and a new hikari connection pool is started. When the code in use finishes both are stopped.

Because you execute both queries in a different transactor.use{ ... } block they both run in a different container and on a different connection pool.

Instead you should make sure you use the same resources throughout your test.

transactor.use { xa =>
  for {
    companyId    <-
      sql"INSERT INTO companies (name, description) VALUES ('My Company', 'My Company Description')"
        .update
        .withUniqueGeneratedKeys[Int]("id")
        .transact(xa)
    maybeCompany <- 
      sql"SELECT * FROM companies WHERE id = $companyId"
        .query[Company]
        .option
        .transact(xa)
  } yield maybeCompany
}
.asserting { company =>
  company shouldBe defined
  company.get.name shouldBe "My Company"
  company.get.description shouldBe "My Company Description"
}