KlinesRepositorySpec.scala

class KlinesRepositorySpec extends UnitSpec with TimescaleSuite(Some("/database.sql")) {

  "KlinesRepository" should "return last klines" in {
    withTimescale { timescale =>
      val repository = new KlinesRepository(timescale.xa)

      val date = LocalDate.of(2021, 1, 1)
        .atStartOfDay()
        .atOffset(ZoneOffset.UTC)

      val kline = KLineRow.fromKline("BTCUSDT", Fixtures.kline(
        openTime = date.toInstant.toEpochMilli,
        closeTime = date.plusSeconds(60).toInstant.toEpochMilli - 1
      ))
      repository.insertKLines(Seq(kline)).unsafeRunSync()

      repository.klines("BTCUSDT", Interval.`1m`, date.minusSeconds(60), date)
        .compile.toVector
        .unsafeRunSync() shouldBe Seq(kline)

      repository.lastKlines("BTCUSDT", Interval.`1m`).unsafeRunSync() shouldBe Seq(kline)
    }
  }
}

TimescaleSuite.scala

import cats.effect.IO
import com.dimafeng.testcontainers.PostgreSQLContainer
import com.dimafeng.testcontainers.scalatest.TestContainerForAll
import doobie.Transactor
import org.testcontainers.utility.DockerImageName

import java.sql.DriverManager
import scala.io.Source

case class TimescaleContext(xa: Transactor[IO])

object TimescaleSuite {
  val TimescaleContainerDef: PostgreSQLContainer.Def = PostgreSQLContainer.Def(
    dockerImageName = DockerImageName.parse("timescale/timescaledb:latest-pg15")
      .asCompatibleSubstituteFor("postgres"),
  )
}

trait TimescaleSuite(bootstrapFilePath: Option[String] = None)
  extends TestContainerForAll { self: org.scalatest.Suite =>

  import TimescaleSuite.*

  override val containerDef: PostgreSQLContainer.Def = TimescaleContainerDef

  def withTimescale[C](f: TimescaleContext => C): C = {
    withContainers { c =>
      val conn = DriverManager.getConnection(c.jdbcUrl, c.username, c.password)

      // Running bootstrap sql
      bootstrapFilePath.foreach { file =>
        val source = Source.fromFile(getClass.getResource(file).getFile)
        val sqls   = source.getLines().mkString("\n").split(";\n")
        source.close()

        sqls.foreach(conn.prepareStatement(_).execute())
      }

      val transactor = Transactor.fromConnection[IO](conn)

      f(TimescaleContext(transactor))
    }
  }

}

UnitSpec.scala

import cats.effect.testing.scalatest.AsyncIOSpec
import org.scalatest.*
import org.scalatest.flatspec.{AnyFlatSpec, AsyncFlatSpec}
import org.scalatest.matchers.*

abstract class UnitSpec extends AnyFlatSpec, should.Matchers, OptionValues, Inside, Inspectors