Tests timestamp serialization for doobie-postgres

test-doobie-postgres-timestamp-serialization.sc

import $ivy.{
  `dev.zio::zio:1.0.17`,
  `dev.zio::zio-test:1.0.17`,
  `dev.zio::zio-interop-cats:13.0.0.1`,
  `org.tpolecat::doobie-core:1.0.0-RC1`,
  `org.tpolecat::doobie-postgres:1.0.0-RC1`,
  `org.typelevel::cats-core:2.8.0`
}

import cats.syntax.all._
import doobie.Transactor
import doobie.implicits._
import doobie.postgres.implicits._
import zio._
import zio.interop.catz._
import zio.interop.catz.implicits.rts
import zio.test._

import java.time._

object foo extends DefaultRunnableSpec {
  val xa = doobie.Transactor.fromDriverManager[Task](
    "org.postgresql.Driver", // driver classname
    "jdbc:postgresql://localhost:5432/tax_reportable_winnings", // connect URL (driver-specific)
    "postgres", // user
    "postgres" // password
  )

  val create = sql"CREATE TABLE IF NOT EXISTS t(ts timestamp not null)".update.run
  val createZDT = sql"CREATE TABLE IF NOT EXISTS t2(ts timestamp with time zone not null)".update.run

  def insert(i: Instant) = sql"INSERT INTO t (ts) VALUES ($i)".update.run
  def insertZDT(i: ZonedDateTime) = sql"INSERT INTO t2 (ts) VALUES ($i)".update.run


  val select = sql"SELECT ts FROM t".query[Instant].unique
  val selectZDT = sql"SELECT ts FROM t2".query[ZonedDateTime].unique

  val wipe = sql"TRUNCATE TABLE t".update.run
  val wipeZDT = sql"TRUNCATE TABLE t2".update.run

  val showTZ = sql"show TIMEZONE".query[String].unique


  def spec = suite("timezones")(
    testM("roundtrip - instant"){
      val now = Instant.parse("2022-01-01T00:00:00Z")
      showTZ.transact(xa).debug("timezone in DB: ") *>
      (create *> wipe *> insert(now) *> select).transact(xa)
        .map(res => assertTrue(now == res))
    },
    testM("roundtrip - ZDT") {
      val now = ZonedDateTime.parse("2022-01-01T00:00:00Z")
      showTZ.transact(xa).debug("timezone in DB: ") *>
        (createZDT *> wipeZDT *> insertZDT(now) *> selectZDT).transact(xa)
          .map(res => assertTrue(now == res))
    }
  )
}

Runtime.default.unsafeRun(foo.run.provideLayer(zio.test.environment.liveEnvironment >+> TestLogger.fromConsole))

testcontainer-version.sc

import $ivy.{
  `dev.zio::zio:1.0.17`,
  `dev.zio::zio-test:1.0.17`,
  `dev.zio::zio-interop-cats:13.0.0.1`,
  `org.tpolecat::doobie-core:1.0.0-RC1`,
  `org.tpolecat::doobie-postgres:1.0.0-RC1`,
  `org.typelevel::cats-core:2.8.0`,
  `com.dimafeng::testcontainers-scala-postgresql:0.40.10`
}

import cats.syntax.all._
import doobie.Transactor
import doobie.implicits._
import doobie.postgres.implicits._
import zio._
import zio.interop.catz._
import zio.interop.catz.implicits.rts
import zio.test._

import java.time._

object foo extends DefaultRunnableSpec {
  val container = com.dimafeng.testcontainers.PostgreSQLContainer(
    databaseName = "tax_reportable_winnings",
    username = "postgres",
    password = "postgres",
  )
  
  lazy val xa = doobie.Transactor.fromDriverManager[Task](
    "org.postgresql.Driver", // driver classname
    container.jdbcUrl, // connect URL (driver-specific)
    container.username, // user
    container.password // password
  )

  val create = sql"CREATE TABLE IF NOT EXISTS t(ts timestamp not null)".update.run
  val createZDT = sql"CREATE TABLE IF NOT EXISTS t2(ts timestamp with time zone not null)".update.run

  def insert(i: Instant) = sql"INSERT INTO t (ts) VALUES ($i)".update.run
  def insertZDT(i: ZonedDateTime) = sql"INSERT INTO t2 (ts) VALUES ($i)".update.run


  val select = sql"SELECT ts FROM t".query[Instant].unique
  val selectZDT = sql"SELECT ts FROM t2".query[ZonedDateTime].unique

  val wipe = sql"TRUNCATE TABLE t".update.run
  val wipeZDT = sql"TRUNCATE TABLE t2".update.run

  val showTZ = sql"show TIMEZONE".query[String].unique


  def spec = suite("timezones")(
    testM("roundtrip - instant"){
      val now = Instant.parse("2022-01-01T00:00:00Z")
      showTZ.transact(xa).debug("timezone in DB: ") *>
      (create *> wipe *> insert(now) *> select).transact(xa)
        .map(res => assertTrue(now == res))
    },
    testM("roundtrip - ZDT") {
      val now = ZonedDateTime.parse("2022-01-01T00:00:00Z")
      showTZ.transact(xa).debug("timezone in DB: ") *>
        (createZDT *> wipeZDT *> insertZDT(now) *> selectZDT).transact(xa)
          .map(res => assertTrue(now == res))
    }
  ) @@ TestAspect.beforeAll(ZIO.succeed(container.start())) @@ TestAspect.afterAll(ZIO.succeed(container.stop()))
}

Runtime.default.unsafeRun(foo.run.provideLayer(zio.test.environment.liveEnvironment >+> TestLogger.fromConsole))

for the first file start postgres in docker:

docker run -p 127.0.0.1:5432:5432 -d -e POSTGRES_PASSWORD=postgres -e POSTGRES_USER=postgres -e POSTGRES_DB=tax_reportable_winnings postgres