search

typelevel / doobie

Functional JDBC layer for Scala.
MIT License
2.17k stars 357 forks source link

Extend the ConnectionPool Example to showcase using the transactor in a larger app #795

Open bmnn-mchl opened 6 years ago

bmnn-mchl commented 6 years ago

Thank you very much for creating doobie! Hope you don't mind me posting such a noob question, but I am just not sure if what I intend to do is a dump idea or the way to go? The connection pool example shows how to create an connection as a ressource

object HikariApp extends IOApp {
  // Resource yielding a transactor configured with a bounded connect EC and an unbounded
  // transaction EC. Everything will be closed and shut down cleanly after use.
  val transactor: Resource[IO, HikariTransactor[IO]] =
  for {
    ce <- ExecutionContexts.fixedThreadPool[IO](32) // our connect EC
    te <- ExecutionContexts.cachedThreadPool[IO]    // our transaction EC
    xa <- HikariTransactor.newHikariTransactor[IO](
      "org.h2.Driver",                        // driver classname
      "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",   // connect URL
      "sa",                                   // username
      "",                                     // password
      ce,                                     // await connection here
      te                                      // execute JDBC operations here
    )
  } yield xa

  def run(args: List[String]): IO[ExitCode] =
    transactor.use { xa: HikariTransactor[IO] =>
      // Construct and run your server here!
        sql"""
        create table if not exists numbers(id serial auto_increment primary key,
        value integer not null)
        """.update.run.transact(xa).unsafeRunSync()
      sql"insert into numbers (value) values(1)".update.run.transact(xa).unsafeRunSync()

/*
/Pass xa to other functions which need to access the database either synchronously or asynchronously like f(xa, ...), g(xa, ...)
*/

      println(sql"select value from numbers".query[Int].to[List].transact(xa).unsafeRunSync())
     ExitCode.Success.pure[IO]
    }
}

Is it correct to pass a reference to the transactor around like this? Are their any caveats that apply? Many thanks again, any help would be greatly appreciated!

Jetbo commented 5 years ago

I ran into this this week and found that you should only call transactor.use { xa => ... } once and pass around xa to the various services that need to make database calls. Otherwise if you call .use more than once, it'll start more than one connection pool.

prayagupa commented 5 years ago

@Jetbo do you mean use transactor.use only once in whole application lifecycle? I thought HikariTransactor.newHikariTransactor would give one of the available connection while using .use {}, and clean up the the connection afterwards not the pool, which does not seem the case.

But based on logging analysis it seem to create a pool everytime I use the HikariTransactor, my sample code here

02:16:57.243 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
02:16:57.248 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
02:16:57.249 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
02:16:57.249 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
02:16:57.249 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
02:16:57.249 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
02:16:57.249 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
02:16:57.249 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
02:16:57.249 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
02:16:57.249 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
02:16:57.251 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=<masked>}
02:16:57.251 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName.................none
02:16:57.251 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
02:16:57.251 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............none
02:16:57.251 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
02:16:57.251 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
02:16:57.251 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................jdbc:mysql://localhost:3306/updupd
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................10
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................10
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
02:16:57.252 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
02:16:57.253 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - schema..........................none
02:16:57.253 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
02:16:57.253 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
02:16:57.253 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - username........................"root"
02:16:57.253 [pool-1-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
02:16:57.253 [pool-1-thread-1] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
02:16:57.269 [pool-1-thread-1] DEBUG c.z.hikari.util.DriverDataSource - Loaded driver with class name com.mysql.cj.jdbc.Driver for jdbcUrl=jdbc:mysql://localhost:3306/updupd
Thu Nov 22 02:16:57 PST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
02:16:57.821 [pool-1-thread-1] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@c3c0da5
02:16:57.823 [pool-1-thread-1] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
02:16:57.859 [pool-2-thread-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Reset (autoCommit) on connection com.mysql.cj.jdbc.ConnectionImpl@c3c0da5
02:16:57.861 [scala-execution-context-global-15] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown initiated...
02:16:57.861 [scala-execution-context-global-15] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Before shutdown stats (total=1, active=0, idle=1, waiting=0)
02:16:57.862 [HikariPool-1 connection closer] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@c3c0da5: (connection evicted)
02:16:57.865 [scala-execution-context-global-15] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - After shutdown stats (total=0, active=0, idle=0, waiting=0)
02:16:57.865 [scala-execution-context-global-15] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown completed.
02:16:57.865 [main] INFO  cats.DoobieMySQLExamples$ - pure 41
02:16:57.892 [main] INFO  cats.DoobieMySQLExamples$ - making transaction: 
02:16:57.893 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-2 - configuration:
02:16:57.894 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=<masked>}
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName.................none
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............none
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................jdbc:mysql://localhost:3306/updupd
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
02:16:57.895 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................10
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................10
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-2"
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - schema..........................none
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - username........................"root"
02:16:57.896 [pool-3-thread-1] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
02:16:57.896 [pool-3-thread-1] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-2 - Starting...
02:16:57.896 [pool-3-thread-1] DEBUG c.z.hikari.util.DriverDataSource - Loaded driver with class name com.mysql.cj.jdbc.Driver for jdbcUrl=jdbc:mysql://localhost:3306/updupd
Thu Nov 22 02:16:57 PST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
02:16:57.922 [pool-3-thread-1] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-2 - Added connection com.mysql.cj.jdbc.ConnectionImpl@7ce8b5ee
02:16:57.922 [pool-3-thread-1] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-2 - Start completed.
02:16:58.011 [pool-4-thread-1] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Reset (autoCommit) on connection com.mysql.cj.jdbc.ConnectionImpl@7ce8b5ee
02:16:58.011 [scala-execution-context-global-15] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-2 - Shutdown initiated...
02:16:58.011 [scala-execution-context-global-15] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-2 - Before shutdown stats (total=1, active=0, idle=1, waiting=0)
02:16:58.011 [HikariPool-2 connection closer] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@7ce8b5ee: (connection evicted)
02:16:58.012 [scala-execution-context-global-15] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-2 - After shutdown stats (total=0, active=0, idle=0, waiting=0)
02:16:58.012 [scala-execution-context-global-15] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-2 - Shutdown completed.
select: List(San Bruno, warehouse-12a2fcf4-6, warehouse-13c5dc49-b, warehouse-cf9fef41-a, warehouse-046b49b5-a, San Bruno, warehouse-4f980583-4, warehouse-8ee5d0aa-7, San Bruno, warehouse-cc722fed-c, warehouse-9d45d3fd-c, San Bruno, San Bruno, San Bruno, San Bruno, San Bruno, San Bruno, San Bruno, San Bruno, San Bruno, San Bruno, San Bruno, San Bruno)
Thu Nov 22 02:16:58 PST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
result: List(Inventory(San Bruno,sku-008,10), Inventory(warehouse-12a2fcf4-6,sku-aedb6e1a-f,265839336), Inventory(warehouse-13c5dc49-b,sku-d6bdd2bb-8,-1112148965), Inventory(warehouse-cf9fef41-a,sku-9eceb867-1,317842136), Inventory(warehouse-046b49b5-a,sku-59276de3-d,943587267), Inventory(San Bruno,sku-008,10), Inventory(warehouse-4f980583-4,sku-980b04e8-e,1680251477), Inventory(warehouse-8ee5d0aa-7,sku-231714e0-9,711622611), Inventory(San Bruno,sku-008,10), Inventory(warehouse-cc722fed-c,sku-c2b692b0-c,276828292), Inventory(warehouse-9d45d3fd-c,sku-43310940-2,1430743049), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10), Inventory(San Bruno,sku-008,10))
Thu Nov 22 02:16:58 PST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
inserted data: Future(Success(24))

Process finished with exit code 0
droyop commented 5 years ago

Hi! I read your post some days ago and I promised myself I'd come back here at the end of my struggle and try to help.

Happy to accept suggestions. I'm fairly junior in this area.

In my (finally working) example I use the same IOApp pattern. I load the config into a config case class, and migrate the database (Though that's quite irrelevant for the matter)

object Server extends IOApp {

  //Load the configuration objects from the application.conf file
  private val conf: Config = Config.load().unsafeRunSync()

  //Migrate the database
  Flyway.configure.dataSource(conf.db.url, conf.db.user, conf.db.password).load.migrate

... and then I create the CP just like you did

  //Create the HikariCP as a resource
  private val transactor: Resource[IO, HikariTransactor[IO]] = for {
    ce <- ExecutionContexts.fixedThreadPool[IO](conf.db.threadSize)
    te <- ExecutionContexts.cachedThreadPool[IO]
    xa <- HikariTransactor.newHikariTransactor[IO](conf.db.driver,
      conf.db.url,
      conf.db.user,
      conf.db.password,
      ce,
      te)
  } yield xa

... The critical part. We use the resource, and define the transactor as implicit to be used by the repositories, wrapping everything.


 //Run the IOApp
  override def run(args: List[String]): IO[ExitCode] = {
    //We wrap everything with the resource and use the underlying transactor
    //implicitly
    transactor.use{implicit xa =>

      //Create an implicit instance of the repository (taking the implicit transactor)
      implicit val clientRepo: ClientRepository = new ClientRepository()

      //Create the Http4s server
      BlazeServerBuilder[IO]
          //Bind to the host:port
          .bindHttp(conf.srv.port, conf.srv.host)
          //The HttpApp defines the routes & behaviour (We can bind several)
          .withHttpApp(new ClientService().routes)
          .serve
          .compile
          .drain
          .as(ExitCode.Success)
    }
  }

The repository is fairly simple. It just takes the implicit transactor, and exposes a getClient method which return either the client or a not found error

class ClientRepository(implicit xa: HikariTransactor[IO]) {

  def getClient(id: Long): IO[Either[ClientNotFoundError.type, Client]] = {
    sql"SELECT id, name FROM client WHERE id = $id"
      .query[Client]
      .option
      .transact(xa)
      .map {
        case Some(client) => Right(client)
        case None => Left(ClientNotFoundError)
      }
  }

The service takes the implicit repository, and defines the routes, the logic and response handling.

In this case it just implements a get method for a client id. It gets the result from the repository, and maps it to either json or a 404 error.

class ClientService(implicit repository: ClientRepository) extends Http4sDsl[IO] {

  val routes = HttpRoutes.of[IO] {
    case GET -> Root / "clients" / LongVar(id) =>
      for {
        result <- repository.getClient(id)
        response <- buildResponse(result)
      } yield response
  }.orNotFound

  private def buildResponse(result: Either[ClientNotFoundError.type, Client]) = {
    result match {
      case Left(ClientNotFoundError) => NotFound()
      case Right(client) => Ok(client.asJson)
    }
  }
}

(just for completeness)

  case class Client(id: Option[Long], name: String)

  case object ClientNotFoundError

I have not tested this properly but based on the debug log everything seems to work really nice with the CP, and the server works like a charm and it's ridiculously lightweight.

Kudos: @jaspervz for the inspiration: https://github.com/jaspervz/todo-http4s-doobie You are the real MVP (I'll try to push the changes with the HikariCP for the newest doobie version)

wb14123 commented 3 years ago

I also suspect the life cycle of transactor is the whole application, otherwise every time we use it it will also generate multiple threads.

But what's the point to make it as a Resource if the life cycle of it is the whole application?

jatcwang commented 3 years ago

Yes that's right. You create the transactor once and use it for your whole application. When you application exits (e.g. ctrl-c) then the connection pool will be released cleanly.

See https://github.com/softwaremill/bootzooka/blob/master/backend/src/main/scala/com/softwaremill/bootzooka/Main.scala for example

wonk1132 commented 1 year ago

@droyop perhaps you could PR a doc change with your example in it? The docs are pretty misleading on this point.