Database Connection

This chapter describes how to use queries built with LDBC to process connections to databases.

The following dependencies must be set up for the project

libraryDependencies ++= Seq(
  "io.github.takapi327" %% "ldbc-dsl" % "0.3.0-beta8",
  "com.mysql" % "mysql-connector-j" % "8.4.0"
)

If you have not yet read about how to build queries with LDBC, we recommend that you read the chapter Building Type-Safe Queries first.

The following code example assumes the following import

import com.mysql.cj.jdbc.MysqlDataSource

import cats.effect.IO
// This is just for testing. Consider using cats.effect.IOApp instead of calling
// unsafe methods directly.
import cats.effect.unsafe.implicits.global

import ldbc.sql.*
import ldbc.dsl.io.*
import ldbc.dsl.logging.ConsoleLogHandler
import ldbc.query.builder.TableQuery

Table definitions use the following

case class User(
  id: Long,
  name: String,
  age: Option[Int],
)

val table = Table[User]("user")(
  column("id", BIGINT, AUTO_INCREMENT, PRIMARY_KEY),
  column("name", VARCHAR(255)),
  column("age", INT.UNSIGNED.DEFAULT(None)),
)

val userQuery = TableQuery[User](table)

Using DataSource

LDBC uses JDBC's DataSource for database connections, and since LDBC does not provide an implementation for building this DataSource, it is necessary to use a library such as mysql or HikariCP. In this example, we will use mysqlDataSource to build the DataSource.

private val dataSource = new MysqlDataSource()
dataSource.setServerName("127.0.0.1")
dataSource.setPortNumber(3306)
dataSource.setDatabaseName("database name")
dataSource.setUser("user name")
dataSource.setPassword("password")

Log

LDBC can export execution and error logs of Database connections in any format using any logging library.

A logger using Cats Effect's Console is provided as standard, which can be used during development.

given LogHandler[IO] = ConsoleLogHandler[IO]

Customize

Use ldbc.dsl.logging.LogHandler to customize logs using any logging library.

The following is the standard implementation of logging: LDBC generates the following three types of events on database connections

• Success: Successful processing
• ProcessingFailure: Error in processing after data acquisition or before database connection
• ExecFailure: Error processing connection to database

Pattern matching is used to sort out what logs to write for each event.

def consoleLogger[F[_]: Console: Sync]: LogHandler[F] =
  case LogEvent.Success(sql, args) =>
    Console[F].println(
      s"""Successful Statement Execution:
         |  $sql
         |
         | arguments = [${ args.mkString(",") }]
         |""".stripMargin
    )
  case LogEvent.ProcessingFailure(sql, args, failure) =>
    Console[F].errorln(
      s"""Failed ResultSet Processing:
         |  $sql
         |
         | arguments = [${ args.mkString(",") }]
         |""".stripMargin
    ) >> Console[F].printStackTrace(failure)
  case LogEvent.ExecFailure(sql, args, failure) =>
    Console[F].errorln(
      s"""Failed Statement Execution:
         |  $sql
         |
         | arguments = [${ args.mkString(",") }]
         |""".stripMargin
    ) >> Console[F].printStackTrace(failure)

Query

Constructing a select statement allows the use of the toList/headOption/unsafe methods. These methods are used to determine the format of the data to be retrieved. If you do not specify any particular type, the column type specified in the select method will be returned as a Tuple.

toList

The toList method is used to retrieve a list of data as a result of executing a query. If you use the toList method to process the database and get zero data, an empty array will be returned.

val query1 = userQuery.selectAll.toList // List[(Long, String, Option[Int])]

Specifying a model in the toList method allows the data after acquisition to be converted to the specified model.

val query = userQuery.selectAll.toList[User] // User

The model type specified in the toList method must match the Tuple type specified in the select method or be type-convertible from the Tuple type to the specified model.

val query1 = userQuery.select(user => (user.name, user.age)).toList[User] // Compile error

case class Test(name: String, age: Option[Int])
val query2 = userQuery.select(user => (user.name, user.age)).toList[Test] // Test

headOption

If you want to get the first data as an Optional result of the query, use the headOption method. If the result of database processing using the headOption method is zero, none is returned.

Note that if you use the headOption method, only the first data will be returned, even if you execute a query that retrieves multiple data.

val query1 = userQuery.selectAll.headOption // Option[(Long, String, Option[Int])]
val query2 = userQuery.selectAll.headOption[User] // Option[User]

unsafe

When using the unsafe method, it is the same as the headOption method in that it returns only the first case of the retrieved data, but the data is returned as is, not as Optional. If the number of data returned is zero, an exception will be raised and appropriate exception handling is required.

It is named unsafe because it is likely to raise an exception at runtime.

val query1 = userQuery.selectAll.unsafe // (Long, String, Option[Int])
val query2 = userQuery.selectAll.unsafe[User] // User

Update

Constructing an insert/update/delete statement allows you to use the update method. The update method returns the number of write operations to the database.

val insert = userQuery.insert((1L, "name", None)).update // Int
val update = userQuery.update("name", "update name").update // Int
val delete = userQuery.delete.update // Int

In the case of an insert statement, you may want the values generated by AutoIncrement to be returned when inserting data. In this case, use the returning method instead of the update method to specify the columns to be returned.

val insert = userQuery.insert((1L, "name", None)).returning("id") // Long

The value specified in the returning method must be the name of a property that the model has. Also, if the specified property does not have the AutoIncrement attribute set on the table definition, an error will occur.

In MySQL, the only value that can be returned when inserting data is the AutoIncrement column, so the same specification applies to LDBC.

Perform database operations

Before making a database connection, commit timing, read/write-only, and other settings must be made.

Read Only

The readOnly method can be used to make the processing of a query to be executed read-only. The readOnly method can also be used with insert/update/delete statements, but it will result in an error at runtime because of the write operation.

val read = userQuery.selectAll.toList.readOnly(dataSource)

Auto Commit

The autoCommit method can be used to set the query processing to commit at each query execution.

val read = userQuery.insert((1L, "name", None)).update.autoCommit(dataSource)

Transaction

The transaction method can be used to combine multiple database connection operations into a single transaction.

The return value of the toList/headOption/unsafe/returning/update method is of type Kleisli[F, Connection[F], T]. Therefore, you can use map or flatMap to combine the process into one.

By using the transaction method on a single Kleisli[F, Connection[F], T], all database connection operations performed within will be combined into a single transaction.

(for
  result1 <- userQuery.insert((1L, "name", None)).returning("id")
  result2 <- userQuery.update("name", "update name").update
  ...
yield ...).transaction(dataSource)

Database Action

There is also a way to perform database processing using Database with connection information to the database.

There are two ways to construct a Database: using the DriverManager or generating one from a DataSource. The following is an example of constructing a Database with connection information to a database using a MySQL driver.

val db = Database.fromMySQLDriver[IO]("database name", "host", "port number", "user name", "password")

The advantages of using Database to perform database processing are as follows

• Simplifies DataSource construction (when using DriverManager)
• Eliminates the need to pass a DataSource for each query

The method using Database is merely a simplified method of passing a DataSource, so there is no difference in execution results between the two. The only difference is whether the processes are combined using flatMap or other methods and executed in a method chain, or whether the combined processes are executed using Database. Therefore, the user can choose the execution method of his/her choice.

Read Only

val user: Option[User] = db.readOnly(userQuery.selectAll.headOption[User]).unsafeRunSync()

Auto Commit

val result = db.autoCommit(userQuery.insert((1L, "name", None)).update).unsafeRunSync()

Transaction

db.transaction(for
  result1 <- userQuery.insert((1L, "name", None)).returning("id")
  result2 <- userQuery.update("name", "update name").update
  ...
yield ...).unsafeRunSync()

Database model

In LDBC, the Database model is also used for purposes other than holding database connection information. Another use is for SchemaSPY documentation generation, see here for information on SchemaSPY document generation.

If you have already generated a Database model for another use, you can use that model to build a Database with database connection information.

import ldbc.dsl.io.*

val database: Database = ???

val db = database.fromDriverManager()
// or
val db = database.fromDriverManager("user name", "password")

Use in method chain

The Database model can also be used in place of DataSource in TableQuery methods.

val read = userQuery.selectAll.toList.readOnly(db)
val commit = userQuery.insert((1L, "name", None)).update.autoCommit(db)
val transaction = (for
  result1 <- userQuery.insert((1L, "name", None)).returning("id")
  result2 <- userQuery.update("name", "update name").update
  ...
yield ...).transaction(db)

Using a HikariCP Connection Pool

ldbc-hikari provides a builder to build HikariConfig and HikariDataSource for building HikariCP connection pools.

libraryDependencies ++= Seq(
  "io.github.takapi327" %% "ldbc-hikari" % "0.3.0-beta8",
)

HikariConfigBuilder is a builder to build HikariConfig of HikariCP as the name suggests.

val hikariConfig: com.zaxxer.hikari.HikariConfig = HikariConfigBuilder.default.build()

The HikariConfigBuilder has a default and a from method. When default is used, the HikariConfig is constructed by retrieving the target values from the Config based on the LDBC specified path.

ldbc.hikari {
  jdbc_url = ...
  username = ...
  password = ...
}

If you want to specify a user-specific path, you must use the from method and pass the path you want to retrieve as an argument.

val hikariConfig: com.zaxxer.hikari.HikariConfig = HikariConfigBuilder.from("custom.path").build()

// custom.path {
//   jdbc_url = ...
//   username = ...
//   password = ...
// }

Please refer to official for details on what can be set in HikariCP.

The following is a list of keys that can be set for Config.

The HikariDataSourceBuilder allows you to build a HikariDataSource for HikariCP.

The HikariDataSource built by the builder is managed as a Resource since the connection pool is a lifetime managed object and needs to be shut down cleanly.

val dataSource: Resource[IO, HikariDataSource] = HikariDataSourceBuilder.default[IO].buildDataSource()

The HikariDataSource built via buildDataSource uses HikariConfig, which is built internally by retrieving settings from Config based on the LDBC specified path. This is equivalent to HikariConfig generated via default in HikariConfigBuilder.

If you want to use a user-specified HikariConfig, you can use buildFromConfig to build a HikariDataSource.

val hikariConfig = ???
val dataSource = HikariDataSourceBuilder.default[IO].buildFromConfig(hikariConfig)

A HikariDataSource built with HikariDataSourceBuilder is usually executed using IOApp.

object HikariApp extends IOApp:

  val dataSourceResource: Resource[IO, HikariDataSource] = HikariDataSourceBuilder.default[IO].buildDataSource()

  def run(args: List[String]): IO[ExitCode] =
    dataSourceResource.use { dataSource =>
       ...
    }

HikariDatabase

There is also a way to build a Database with HikariCP connection information.

The HikariDatabase is managed as a Resource like the HikariDataSource. Therefore, it is usually executed using IOApp.

object HikariApp extends IOApp:

  val hikariConfig = ???
  val databaseResource: Resource[F, Database[F]] = HikariDatabase.fromHikariConfig[IO](hikariConfig)

  def run(args: List[String]): IO[ExitCode] =
    databaseResource.use { database =>
       for
         result <- database.readOnly(...)
        yield ExitCode.Success
    }