This is the full developer documentation for ldbc.
# Start of ldbc documentation
# Integration of HikariCP with ldbc
## Introduction
HikariCP is one of the most widely used database connection pool libraries in Java.
When combined with ldbc, it enables efficient database connection management.
## Adding Dependencies
First, add the necessary dependencies to build.sbt:
```scala
libraryDependencies ++= Seq(
"com.mysql" % "mysql-connector-j" % "8.4.0",
"com.zaxxer" % "HikariCP" % "6.2.1"
)
```
## Basic HikariCP Configuration
HikariCP has many configuration options. Here are the main configuration items:
```scala
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
val config = new HikariConfig()
config.setJdbcUrl("jdbc:mysql://localhost:3306/database")
config.setUsername("username")
config.setPassword("password")
// Basic connection pool settings
config.setMaximumPoolSize(10) // Maximum pool size
config.setMinimumIdle(5) // Minimum idle connections
config.setConnectionTimeout(30000) // Connection timeout (milliseconds)
config.setIdleTimeout(600000) // Idle timeout (milliseconds)
val dataSource = new HikariDataSource(config)
```
## Integration Example with ldbc
Here's a basic example of combining HikariCP with ldbc:
```scala
import cats.effect.*
import com.zaxxer.hikari.HikariDataSource
import ldbc.dsl.*
import ldbc.dsl.codec.Codec
import jdbc.connector.*
import ldbc.connector.Connector
// Data model definition
case class User(id: Int, name: String, email: String)
object User:
given Codec[User] = Codec.derived[User]
object HikariExample extends IOApp.Simple:
def run: IO[Unit] =
// HikariCP configuration
val ds = new HikariDataSource()
ds.setJdbcUrl("jdbc:mysql://localhost:3306/mydb")
ds.setUsername("user")
ds.setPassword("password")
// DataSource and Connector setup
val program = for
hikari <- Resource.fromAutoCloseable(IO(ds))
execution <- ExecutionContexts.fixedThreadPool[IO](10)
datasource = MySQLDataSource.fromDataSource[IO](hikari, execution)
yield Connector.fromDataSource(datasource)
// Query execution
program.use { connector =>
for
users <- sql"SELECT * FROM users".query[User].to[List].readOnly(connector)
_ <- IO.println(s"Found users: $users")
yield ()
}
```
## Advanced Configuration Examples
HikariCP offers various settings for performance tuning:
```scala
val config = new HikariConfig()
// Basic settings
config.setPoolName("MyPool") // Set pool name
config.setAutoCommit(false) // Disable auto-commit
// Performance settings
config.setMaxLifetime(1800000) // Maximum connection lifetime (30 minutes)
config.setValidationTimeout(5000) // Connection validation timeout
config.setLeakDetectionThreshold(60000) // Connection leak detection threshold
// MySQL-specific settings
config.addDataSourceProperty("cachePrepStmts", "true")
config.addDataSourceProperty("prepStmtCacheSize", "250")
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048")
```
## Summary
Combining HikariCP with ldbc provides the following benefits:
- Efficient connection pooling
- Simplified transaction management
- Type-safe query execution
- Pure functional programming using Cats Effect
With proper configuration and error handling, you can achieve stable, high-performance database access.
# Integration of Http4s and ldbc
This guide explains how to build a web application by combining [Http4s](https://http4s.org/) and ldbc.
## Introduction
Http4s is a pure functional HTTP server and client library for Scala. By combining it with ldbc,
you can build web applications with type-safe database access.
## Adding Dependencies
First, you need to add the following dependencies to your build.sbt:
```scala
libraryDependencies ++= Seq(
"org.http4s" %% "http4s-dsl" % "0.23.30",
"org.http4s" %% "http4s-ember-server" % "0.23.30",
"org.http4s" %% "http4s-circe" % "0.23.30",
"io.circe" %% "circe-generic" % "0.14.10"
)
```
## Basic Usage
### 1. Table Definition
First, create models and table definitions corresponding to your database tables:
```scala
// Model definition
case class City(
id: Int,
name: String,
countryCode: String,
district: String,
population: Int
)
// JSON encoder definition
object City:
given Encoder[City] = Encoder.derived[City]
// Table definition
class CityTable extends Table[City]("city"):
// Set column naming convention (optional)
given Naming = Naming.PASCAL
// Column definitions
def id: Column[Int] = int("ID").unsigned.autoIncrement.primaryKey
def name: Column[String] = char(35)
def countryCode: Column[String] = char(3).unique
def district: Column[String] = char(20)
def population: Column[Int] = int()
// Mapping definition
override def * : Column[City] =
(id *: name *: countryCode *: district *: population).to[City]
val cityTable = TableQuery[CityTable]
```
### 2. Database Connection Configuration
Configure the database connection:
```scala
private def datasource =
MySQLDataSource
.build[IO]("127.0.0.1", 13306, "ldbc")
.setPassword("password")
.setDatabase("world")
.setSSL(SSL.Trusted)
```
Main options available in connection configuration:
- Hostname
- Port number
- Database name
- Username
- Password
- SSL settings (Trusted, Verify, etc.)
### 3. HTTP Route Definition
Define Http4s routes and incorporate ldbc queries:
```scala
private def routes(connector: Connector[IO]): HttpRoutes[IO] = HttpRoutes.of[IO] {
case GET -> Root / "cities" =>
for
cities <- cityTable.selectAll.query.to[List].readOnly(connector)
result <- Ok(cities.asJson)
yield result
}
```
### 4. Server Startup
Finally, start the Http4s server:
```scala
object Main extends ResourceApp.Forever:
override def run(args: List[String]): Resource[IO, Unit] =
// Create Connector
val connector = Connector.fromDataSource(datasource)
EmberServerBuilder
.default[IO]
.withHttpApp(routes(connector).orNotFound)
.build
.void
```
## Advanced Examples
### Adding Custom Queries
Example of implementing searches with specific conditions or complex queries:
```scala
private def routes(connector: Connector[IO]): HttpRoutes[IO] = HttpRoutes.of[IO] {
case GET -> Root / "cities" / "search" / name =>
for
cities <- cityTable.filter(_.name === name).query.to[List].readOnly(connector)
result <- Ok(cities.asJson)
yield result
case GET -> Root / "cities" / "population" / IntVar(minPopulation) =>
for
cities <- cityTable.filter(_.population >= minPopulation).query.to[List].readOnly(connector)
result <- Ok(cities.asJson)
yield result
}
```
## Error Handling
Example of properly handling database errors:
```scala
private def handleDatabaseError[A](action: IO[A]): IO[Response[IO]] =
action.attempt.flatMap {
case Right(value) => Ok(value.asJson)
case Left(error) =>
InternalServerError(s"Database error: ${error.getMessage}")
}
private def routes(connector: Connector[IO]): HttpRoutes[IO] = HttpRoutes.of[IO] {
case GET -> Root / "cities" =>
handleDatabaseError {
cityTable.selectAll.query.to[List].readOnly(connector)
}
}
```
## Summary
Combining Http4s and ldbc offers the following advantages:
- Type-safe database access
- Benefits of pure functional programming
- Flexible routing and error handling
In actual application development, you can combine these basic patterns to implement more complex functionality.
# Observability with OpenTelemetry
This page explains how to achieve observability through database query tracing, metrics collection, and logging by integrating ldbc with OpenTelemetry.
## What is OpenTelemetry?
[OpenTelemetry](https://opentelemetry.io/) is an open-source framework for implementing application observability. It provides features such as distributed tracing, metrics collection, and logging, enabling an integrated approach to monitoring and analyzing application performance and behavior.
By integrating with OpenTelemetry, ldbc can benefit from:
- Tracking database query execution time and processing
- Measuring query performance
- Error detection and diagnosis
- System-wide visualization and monitoring
## Required Dependencies
To use OpenTelemetry with ldbc, you need to add the following dependencies to your project:
```scala
libraryDependencies ++= Seq(
// otel4s library (Scala wrapper for OpenTelemetry)
"org.typelevel" %% "otel4s-oteljava" % "0.11.2",
// OpenTelemetry exporter (for data transmission)
"io.opentelemetry" % "opentelemetry-exporter-otlp" % "1.48.0" % Runtime,
// Auto-configuration functionality (configuration via environment variables and system properties)
"io.opentelemetry" % "opentelemetry-sdk-extension-autoconfigure" % "1.48.0" % Runtime,
)
```
## OpenTelemetry Configuration in ldbc
ldbc provides a `setTracer` method in ConnectionProvider where you can configure the OpenTelemetry Tracer.
Basic configuration steps are as follows:
1. Initialize OpenTelemetry
2. Obtain a TracerProvider
3. Configure it in the ConnectionProvider
Here's a basic configuration example:
```scala
import cats.effect.*
import io.opentelemetry.api.GlobalOpenTelemetry
import org.typelevel.otel4s.oteljava.OtelJava
import ldbc.connector.*
import ldbc.dsl.*
// Service name (trace identifier)
val serviceName = "my-ldbc-app"
// Resource configuration
val resource: Resource[IO, Connector[IO]] =
for
// Create otel4s instance from GlobalOpenTelemetry
otel <- Resource
.eval(IO.delay(GlobalOpenTelemetry.get))
.evalMap(OtelJava.forAsync[IO])
// Get TracerProvider with service name
tracer <- Resource.eval(otel.tracerProvider.get(serviceName))
// Configure database connection and set TracerProvider
datasource = MySQLDataSource
.build[IO]("localhost", 3306, "user")
.setPassword("password")
.setDatabase("database")
.setSSL(SSL.Trusted)
.setTracer(tracer) // Set OpenTelemetry Tracer
yield Connector.fromDataSource(datasource)
// Execute query using the resource
val program = resource.use { connector =>
sql"SELECT * FROM users".query[String].to[List].readOnly(connector)
}
```
## Configuration via Environment Variables
OpenTelemetry can be configured using system properties or environment variables. You can customize OpenTelemetry behavior by setting the following system properties when starting your application:
```shell
java -Dotel.java.global-autoconfigure.enabled=true \
-Dotel.service.name=ldbc-app \
-Dotel.traces.exporter=otlp \
-Dotel.metrics.exporter=none \
-Dotel.exporter.otlp.endpoint=http://localhost:4317 \
-jar your-application.jar
```
For SBT execution, you can configure it as follows:
```scala
javaOptions ++= Seq(
"-Dotel.java.global-autoconfigure.enabled=true",
"-Dotel.service.name=ldbc-app",
"-Dotel.traces.exporter=otlp",
"-Dotel.metrics.exporter=none"
)
```
## Practical Example: ldbc Application with OpenTelemetry
Here's a complete example of setting up an observability environment using Docker Compose with Jaeger and Prometheus, and sending trace data from an ldbc application.
### Project Structure
Assume the following project structure:
```
otel-example/
├── src/
│ └── main/
│ └── scala/
│ └── Main.scala
├── database/
│ └── xxx.sql
├── dependencies/
│ ├── jaeger/
│ │ └── jaeger-ui.json
│ ├── prometheus/
│ │ └── prometheus.yml
│ └── grafana/
│ └── datasources.yaml
└── docker-compose.yaml
```
### Main.scala
Here's the main application code:
```scala
import cats.effect.*
import io.opentelemetry.api.GlobalOpenTelemetry
import org.typelevel.otel4s.oteljava.OtelJava
import ldbc.connector.*
import ldbc.dsl.*
object Main extends IOApp.Simple:
private val serviceName = "ldbc-otel-example"
private val base = MySQLDataSource
.build[IO]("127.0.0.1", 13307, "ldbc")
.setPassword("password")
.setDatabase("world")
.setSSL(SSL.Trusted)
private def setupTracing: Resource[IO, Connector[IO]] =
for
otel <- Resource
.eval(IO.delay(GlobalOpenTelemetry.get))
.evalMap(OtelJava.forAsync[IO])
tracer <- Resource.eval(otel.tracerProvider.get(serviceName))
datasource <- Resource.eval(IO.delay(base.setTracer(tracer)))
yield Connector.fromDataSource(datasource)
override def run: IO[Unit] =
setupTracing.use { connector =>
sql"SELECT name FROM city".query[String].to[List].readOnly(connector).flatMap { cities =>
IO.println(cities)
}
}
```
### docker-compose.yaml
Use the following Docker Compose configuration for the observability environment:
```yaml
services:
database:
image: mysql:8.0.41
container_name: ldbc-otel-example
ports:
- 13307:3306
networks:
- static-network
volumes:
- ./database:/docker-entrypoint-initdb.d
- ./database/my.cnf:/etc/database/conf.d/my.cn
environment:
MYSQL_USER: 'ldbc'
MYSQL_PASSWORD: 'password'
MYSQL_ROOT_PASSWORD: 'root'
TZ: 'Asia/Tokyo'
healthcheck:
test: [ "CMD", "mysqladmin", "ping", "-h", "localhost" ]
timeout: 20s
retries: 10
prometheus:
image: prom/prometheus:latest
volumes:
- "./dependencies/prometheus/prometheus.yml:/etc/prometheus/prometheus.yml"
ports:
- "9090:9090"
networks:
- static-network
jaeger:
image: jaegertracing/all-in-one:latest
volumes:
- "./dependencies/jaeger/jaeger-ui.json:/etc/jaeger/jaeger-ui.json"
command: --query.ui-config /etc/jaeger/jaeger-ui.json
environment:
- METRICS_STORAGE_TYPE=prometheus
- PROMETHEUS_SERVER_URL=http://prometheus:9090
- COLLECTOR_OTLP_ENABLED=true
ports:
- "16685:16685" # GRPC
- "16686:16686" # UI
- "4317:4317" # OTLP gRPC receiver
- "4318:4318" # OTLP http receiver
networks:
- static-network
grafana:
image: grafana/grafana-oss
restart: unless-stopped
volumes:
- "./dependencies/grafana/datasources.yaml:/etc/grafana/provisioning/datasources/datasources.yaml"
ports:
- "3000:3000"
networks:
- static-network
depends_on:
jaeger:
condition: service_started
networks:
static-network:
name: static-network
```
### Execution Steps
1. First, start the observability environment with Docker Compose:
```bash
cd otel-example
docker-compose up -d
```
2. Then, run the application with the following Java options:
```bash
sbt -Dotel.java.global-autoconfigure.enabled=true \
-Dotel.service.name=ldbc-otel-example \
-Dotel.metrics.exporter=none \
run
```
3. After running the application, access the Jaeger UI ([http://localhost:16686](http://localhost:16686)) to view traces:
You can also check traces in the Grafana UI ([http://localhost:3000](http://localhost:3000)):
## Trace Details
ldbc's OpenTelemetry integration records the following information in traces:
- **Query execution time**: Time taken for database queries
- **SQL statements**: Content of executed SQL queries
- **Query parameters**: Parameter values passed to queries (safely recorded)
- **Connection information**: Server and database name connection details
- **Error information**: Detailed information when errors occur
## Trace Customization Options
ldbc's OpenTelemetry integration allows for additional customization such as:
- **Sampling rate configuration**: Trace only a portion of queries instead of all
- **Attribute filtering**: Exclude sensitive information from traces
- **Custom trace exporters**: Send to tracing systems other than Jaeger
These settings can be configured through OpenTelemetry configuration files or system properties.
## Summary
Combining ldbc with OpenTelemetry enables detailed visualization and monitoring of database operations. Using the methods introduced in this guide makes it easier to identify application performance issues, discover bottlenecks, and perform troubleshooting.
In production environments, you may need to adjust settings according to security requirements and environment specifics. We recommend referring to the official OpenTelemetry documentation to find the optimal configuration.
# Examples
This section shows several examples using ldbc.
## Table of Contents
@:navigationTree {
entries = [ { target = "/en/examples", depth = 2 } ]
}
# ldbc (Lepus Database Connectivity)
Please note that **ldbc** is pre-1.0 software and is still under active development. Newer versions may no longer be binary compatible with earlier versions.
ldbc is a library for building pure functional JDBC layers by [Cats Effect 3](https://typelevel.org/cats-effect/) and [Scala 3](https://github.com/scala/scala3).
ldbc is a [Typelevel](http://typelevel.org/) project. It embraces pure, unconventional, functional programming as described in Scala's [Code of Conduct](http://scala-lang.org/conduct.html) and is meant to provide a safe and friendly environment for teaching, learning, and contributing.
## Introduction
Most of our application development involves the use of databases.
One way to access databases in Scala is to use JDBC, and there are several libraries in Scala that wrap this JDBC.
- Functional DSLs (Slick, quill, zio-sql)
- SQL string interpolators (Anorm, doobie)
ldbc is also a library that wraps JDBC, combining aspects of both approaches. It's a Scala 3 library that provides a type-safe and refactorable SQL interface, capable of expressing SQL expressions on MySQL databases.
Unlike other libraries, ldbc also offers its own connector built in Scala.
Scala currently supports multiple platforms: JVM, JS, and Native.
However, libraries using JDBC can only run in JVM environments.
Therefore, ldbc is being developed to provide a connector written in Scala that supports the MySQL protocol, allowing it to work across different platforms.
By using ldbc, you can access databases across platforms while leveraging Scala's type safety and the benefits of functional programming.
### Target Audience
This documentation is intended for developers who use ldbc, a library for database access using the Scala programming language.
ldbc is designed for those interested in typed pure functional programming. If you're not a Cats user or are unfamiliar with functional I/O and monadic Cats Effect, you may need to proceed at a slower pace.
That said, if you find yourself confused or frustrated by this documentation or the ldbc API, please open an issue and ask for help. Both the library and documentation are young and rapidly evolving, so some lack of clarity is inevitable. This document will therefore be continuously updated to address issues and omissions.
## Quick Start
The current version is **0.4.0**, compatible with **Scala 3.3.6**.
```scala
libraryDependencies ++= Seq(
// Start with this one
"io.github.takapi327" %% "ldbc-dsl" % "0.4.0",
// Choose the connector you want to use
"io.github.takapi327" %% "jdbc-connector" % "0.4.0", // Java connector (supported platform: JVM)
"io.github.takapi327" %% "ldbc-connector" % "0.4.0", // Scala connector (supported platforms: JVM, JS, Native)
// And add these as needed
"io.github.takapi327" %% "ldbc-query-builder" % "0.4.0", // Type-safe query building
"io.github.takapi327" %% "ldbc-schema" % "0.4.0", // Database schema construction
)
```
## TODO
- JSON data type support
- SET data type support
- Geometry data type support
- CHECK constraint support
- Support for databases other than MySQL
- ZIO module support
- Test kit
- etc...
# Migration Notes (from 0.3.x to 0.4.x)
## Packages
**Removed Packages**
| Module / Platform | JVM | Scala Native | Scala.js |
|----------------------|:---:|:------------:|:--------:|
| `ldbc-schemaSpy` | ✅ | ❌ | ❌ |
**Packages renewed as different features**
| Module / Platform | JVM | Scala Native | Scala.js | Scaladoc |
|----------------------|:---:|:------------:|:--------:|-----------------------------------------------------------------------------------------------------------------------------------------------------------|
| `ldbc-core` | ✅ | ✅ | ✅ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-core_3) |
**All Packages**
| Module / Platform | JVM | Scala Native | Scala.js | Scaladoc |
|----------------------|:---:|:------------:|:--------:|-----------------------------------------------------------------------------------------------------------------------------------------------------------|
| `ldbc-sql` | ✅ | ✅ | ✅ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-sql_3) |
| `ldbc-core` | ✅ | ✅ | ✅ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-core_3) |
| `ldbc-connector` | ✅ | ✅ | ✅ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-connector_3) |
| `jdbc-connector` | ✅ | ❌ | ❌ | [](https://javadoc.io/doc/io.github.takapi327/jdbc-connector_3) |
| `ldbc-dsl` | ✅ | ✅ | ✅ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-dsl_3) |
| `ldbc-statement` | ✅ | ✅ | ✅ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-statement_3) |
| `ldbc-query-builder` | ✅ | ✅ | ✅ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-query-builder_3) |
| `ldbc-schema` | ✅ | ✅ | ✅ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-schema_3) |
| `ldbc-codegen` | ✅ | ✅ | ✅ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-codegen_3) |
| `ldbc-hikari` | ✅ | ❌ | ❌ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-hikari_3) |
| `ldbc-plugin` | ✅ | ❌ | ❌ | [](https://javadoc.io/doc/io.github.takapi327/ldbc-plugin_2.12_1.0) |
## 🎯 Major Changes
### 1. Built-in Connection Pooling
Starting from version 0.4.0, ldbc-connector includes a high-performance connection pooling feature. This enables efficient connection management without using external libraries like HikariCP.
**Benefits:**
- Optimized for Cats Effect's fiber-based concurrency model
- Circuit breaker protection during failures
- Dynamic pool sizing
- Detailed metrics tracking
### 2. API Changes
#### Migration from ConnectionProvider to MySQLDataSource and Connector Usage
ConnectionProvider is now deprecated and replaced with the new MySQLDataSource and Connector APIs.
**Old API (0.3.x):**
```scala
import ldbc.connector.*
// Using ConnectionProvider
val provider = ConnectionProvider
.default[IO]("localhost", 3306, "root")
.setPassword("password")
.setDatabase("test")
// Direct usage
provider.use { connection =>
// SQL execution
}
```
**New API (0.4.x):**
```scala
import ldbc.connector.*
import ldbc.core.*
import ldbc.dsl.*
// Using MySQLDataSource
val dataSource = MySQLDataSource
.build[IO]("localhost", 3306, "root")
.setPassword("password")
.setDatabase("test")
// Create Connector and execute DBIO
val connector = Connector.fromDataSource(dataSource)
// Execute SQL queries
val result = sql"SELECT * FROM users"
.query[User]
.to[List]
.readOnly(connector)
// Or create Connector from Connection
dataSource.getConnection.use { connection =>
val connector = Connector.fromConnection(connection)
// Execute DBIO
sql"INSERT INTO users (name) VALUES ($name)"
.update
.commit(connector)
}
// Connection pooling
val pooledDataSource = MySQLDataSource.pooling[IO](
MySQLConfig.default
.setHost("localhost")
.setPort(3306)
.setUser("root")
.setPassword("password")
.setDatabase("test")
.setMinConnections(5)
.setMaxConnections(20)
)
pooledDataSource.use { pool =>
val connector = Connector.fromDataSource(pool)
// Execute DBIO
sql"SELECT * FROM users WHERE id = $id"
.query[User]
.option
.readOnly(connector)
}
```
### 3. Configuration Changes
#### ldbc-connector Configuration
**Old Method (0.3.x):**
```scala
val provider = ConnectionProvider
.default[IO]("localhost", 3306, "root")
.setPassword("password")
.setDatabase("test")
.setSSL(SSL.Trusted)
.addSocketOption(SocketOption.receiveBufferSize(4096))
.setReadTimeout(30.seconds)
```
**New Method (0.4.x):**
```scala
val dataSource = MySQLDataSource
.build[IO]("localhost", 3306, "root")
.setPassword("password")
.setDatabase("test")
.setSSL(SSL.Trusted)
.addSocketOption(SocketOption.receiveBufferSize(4096))
.setReadTimeout(30.seconds)
.setDebug(true)
.setAllowPublicKeyRetrieval(true)
```
#### jdbc-connector Configuration
**Old Method (0.3.x):**
```scala
import jdbc.connector.*
val dataSource = new com.mysql.cj.jdbc.MysqlDataSource()
// Manual configuration
val provider = ConnectionProvider
.fromDataSource[IO](dataSource, ec)
```
**New Method (0.4.x):**
```scala
import jdbc.connector.*
// Create Connector from DataSource
val ds = new com.mysql.cj.jdbc.MysqlDataSource()
ds.setServerName("localhost")
ds.setPortNumber(3306)
ds.setDatabaseName("test")
ds.setUser("root")
ds.setPassword("password")
val connector = Connector.fromDataSource[IO](ds, ExecutionContexts.synchronous)
// Create Connector from DriverManager
val connector = Connector.fromDriverManager[IO].apply(
driver = "com.mysql.cj.jdbc.Driver",
url = "jdbc:mysql://localhost:3306/test",
user = "root",
password = "password",
logHandler = None
)
// Via MySQLDataSource (ldbc-connector)
val dataSource = MySQLDataSource
.fromDataSource[IO](ds, ExecutionContexts.synchronous)
```
### 4. Connection Pooling Usage
#### Basic Usage
```scala
import ldbc.connector.*
import ldbc.core.*
import scala.concurrent.duration.*
val config = MySQLConfig.default
.setHost("localhost")
.setPort(3306)
.setUser("myuser")
.setPassword("mypassword")
.setDatabase("mydb")
// Pool configuration
.setMinConnections(5) // Minimum connections
.setMaxConnections(20) // Maximum connections
.setConnectionTimeout(30.seconds) // Connection timeout
.setIdleTimeout(10.minutes) // Idle timeout
.setMaxLifetime(30.minutes) // Maximum lifetime
MySQLDataSource.pooling[IO](config).use { pool =>
// Create and use Connector
val connector = Connector.fromDataSource(pool)
// Execute SQL queries
sql"SELECT COUNT(*) FROM users"
.query[Long]
.unique
.readOnly(connector)
}
```
#### Pool with Metrics
```scala
import ldbc.connector.pool.*
val metricsResource = for {
tracker <- Resource.eval(PoolMetricsTracker.inMemory[IO])
pool <- MySQLDataSource.pooling[IO](
config,
metricsTracker = Some(tracker)
)
} yield (pool, tracker)
metricsResource.use { case (pool, tracker) =>
for {
_ <- pool.getConnection.use(_.execute("SELECT 1"))
metrics <- tracker.getMetrics
_ <- IO.println(s"""
|Pool Metrics:
| Total connections: ${metrics.totalCreated}
| Active: ${metrics.activeConnections}
| Idle: ${metrics.idleConnections}
""".stripMargin)
} yield ()
}
```
#### Before/After Hooks
```scala
case class RequestContext(requestId: String)
val poolWithHooks = MySQLDataSource.poolingWithBeforeAfter[IO, RequestContext](
config = config,
before = Some { conn =>
for {
id <- IO.randomUUID.map(_.toString)
_ <- conn.execute(s"SET @request_id = '$id'")
} yield RequestContext(id)
},
after = Some { (ctx, conn) =>
IO.println(s"Request ${ctx.requestId} completed")
}
)
```
### 5. Migration Considerations
#### Scala Native Limitations
@:callout(warning)
**Important**: Scala Native 0.4.x only supports single-threaded execution. Therefore, using connection pooling with Scala Native is not recommended. Instead, create a new connection for each operation:
```scala
// Recommended usage for Scala Native
val dataSource = MySQLDataSource
.build[IO]("localhost", 3306, "user")
.setPassword("password")
.setDatabase("mydb")
// Don't use pooling
val connector = Connector.fromDataSource(dataSource)
// Execute DBIO
sql"SELECT * FROM products WHERE price > $minPrice"
.query[Product]
.to[List]
.readOnly(connector)
```
@:@
### 6. Breaking Changes
The following APIs have been removed or changed:
1. **ConnectionProvider**: Deprecated and replaced with `MySQLDataSource` (will be removed in 0.5.x)
2. **Provider trait**: Deprecated and replaced with `DataSource` trait
3. **ldbc.sql.Provider**: Removed
4. **Direct connection usage**: Must now use the new `Connector` API
### 7. DBIO Execution Pattern Changes
The DBIO execution method has been changed to be clearer and more flexible.
**Old Method (0.3.x):**
```scala
provider.use { connection =>
(for
result1 <- sql"SELECT 1".query[Int].to[List]
result2 <- sql"SELECT 2".query[Int].to[Option]
yield (result1, result2)).readOnly(connection)
}
```
**New Method (0.4.x):**
```scala
val connector = Connector.fromDataSource(dataSource)
// Various execution modes
sql"SELECT * FROM users".query[User].to[List].readOnly(connector) // Read-only
sql"INSERT INTO users ...".update.commit(connector) // With commit
sql"UPDATE users ...".update.transaction(connector) // Transaction
sql"DELETE FROM users ...".update.rollback(connector) // Rollback
// Combining multiple queries
(for
users <- sql"SELECT * FROM users".query[User].to[List]
count <- sql"SELECT COUNT(*) FROM users".query[Long].unique
yield (users, count)).readOnly(connector)
```
### 8. New Features
#### CircuitBreaker
The connection pool includes a built-in CircuitBreaker for protection during database failures:
- Automatically stops connection attempts after consecutive failures
- Gradual recovery through exponential backoff
- Protects both the application and database
#### Adaptive Pool Sizing
Dynamically adjusts pool size based on load:
```scala
val config = MySQLConfig.default
// ... other configuration
.setAdaptiveSizing(true)
.setAdaptiveInterval(1.minute)
```
#### Leak Detection
Detects connection leaks in development:
```scala
val config = MySQLConfig.default
// ... other configuration
.setLeakDetectionThreshold(2.minutes)
```
#### Streaming Query Support
ldbc supports efficient streaming queries using `fs2.Stream`. This allows handling large amounts of data with controlled memory usage.
**Basic Usage:**
```scala
import fs2.Stream
import ldbc.dsl.*
// Streaming with default fetchSize (1)
val stream: Stream[DBIO, String] =
sql"SELECT name FROM city"
.query[String]
.stream
// Streaming with specified fetchSize
val streamWithFetchSize: Stream[DBIO, City] =
sql"SELECT * FROM city"
.query[City]
.stream(fetchSize = 100)
```
**Practical Examples:**
```scala
// Efficiently process large datasets
val processLargeCities: IO[List[String]] =
sql"SELECT name, population FROM city"
.query[(String, Int)]
.stream(1000) // Fetch 1000 rows at a time
.filter(_._2 > 1000000) // Cities with > 1M population
.map(_._1) // Extract city names
.take(50) // Take first 50
.compile.toList
.readOnly(connector)
// Aggregation processing
val calculateTotal: IO[BigDecimal] =
sql"SELECT amount FROM transactions WHERE year = 2024"
.query[BigDecimal]
.stream(5000) // Process 5000 rows at a time
.filter(_ > 100) // Transactions > 100
.fold(BigDecimal(0))(_ + _) // Calculate total
.compile.lastOrError
.transaction(connector)
```
**MySQL Optimization Settings:**
```scala
// Enable server-side cursors for better memory efficiency
val datasource = MySQLDataSource
.build[IO](host, port, user)
.setPassword(password)
.setDatabase(database)
.setUseCursorFetch(true) // Enable true streaming
```
**Streaming Benefits:**
- **Memory Efficiency**: Constant memory usage even with large datasets
- **Early Processing**: Process data while receiving it
- **Cancellable**: Stop processing based on conditions
- **Rich fs2 Operations**: Use functional operations like `filter`, `map`, `take`, `fold`
## Summary
Migrating to 0.4.x provides the following benefits:
1. **Performance Improvements**: Efficient connection management through built-in pooling
2. **More Intuitive API**: Simplified configuration with builder pattern
3. **Advanced Features**: CircuitBreaker, adaptive sizing, metrics tracking
4. **Reduced External Dependencies**: No need for HikariCP
The migration work mainly involves API updates, and since functional backward compatibility is maintained, gradual migration is possible.
# Q: How do I add processing before and after the connection is established?
## A: To add processing before and after connection establishment, use `withBeforeAfter`.
You can use the `withBeforeAfter` method when creating a Connection to invoke processes both before and after a connection is established. For example, as shown below, by passing arbitrary processes to `before` and `after`, you can output logs before and after connection establishment.
The second argument of `withBeforeAfter` specifies the type of the Before process result that will be passed to After.
```scala 3
import ldbc.connector.*
def before: Connection[IO] => IO[Unit] = _ => IO.println("Connecting to...")
def after: (Unit, Connection[IO]) => IO[Unit] = (_, _) => IO.println("Connection Closed")
val provider =
ConnectionProvider
...
.withBeforeAfter(before, after)
```
@:callout(warning)
This feature is only available when using `ldbc-connector`.
```scala 3
libraryDependencies += "io.github.takapi327" %% "ldbc-connector" % "0.4.0"
```
@:@
## References
- [Using ldbc connector](/en/tutorial/Connection.md#using-the-ldbc-connector)
# Q: How do I use my own type?
## A: To use your own custom type, first define an Encoder and Decoder for that type. This allows you to bind values to the database and extract values from query results as custom types. For example, you can define a Status type representing user status as follows:
```scala 3
// Definition of custom Status type
enum Status(val active: Boolean, val label: String):
case Active extends Status(true, "Active")
case Inactive extends Status(false, "Inactive")
// Define an Encoder to bind Status as a Boolean
given Encoder[Status] = Encoder[Boolean].contramap(_.active)
// Define a Decoder to convert from Boolean to Status
given Decoder[Status] = Decoder[Boolean].map {
case true => Status.Active
case false => Status.Inactive
}
// Example of using Codec to integrate Encoder and Decoder
given Codec[Status] =
Codec[Boolean].imap(b => if b then Status.Active else Status.Inactive)(_.active)
```
In the sample above, the Status type is actually converted to a Boolean value and used in database INSERTs and query result decoding. This maintains type safety in database interactions and allows for easy integration of custom logic.
Additionally, when creating a new type by combining multiple types, you can compose Encoders and Decoders as follows:
```scala 3
// Example of combining two values and converting from tuple to custom type
case class CustomStatus(code: Int, label: String)
given Encoder[CustomStatus] = (Encoder[Int] *: Encoder[String]).to[CustomStatus]
given Decoder[CustomStatus] = (Decoder[Int] *: Decoder[String]).to[CustomStatus]
// or
given Codec[CustomStatus] = (Codec[Int] *: Codec[String]).to[CustomStatus]
```
By defining Encoders, Decoders, and Codecs for your custom types, you can naturally handle custom types in data operations through ldbc.
## References
- [Custom Data Types](/en/tutorial/Custom-Data-Type.md)
- [How to use Codec](/en/tutorial/Custom-Data-Type.md#codec)
# Q: How do I use nested models?
## A: In ldbc, you can map multiple columns to nested models.
For example, here's how to give a User model a nested Name model, which maps to separate columns (e.g., first_name, last_name) in the database:
```scala 3
// Definition of nested model
case class User(id: Long, name: User.Name, email: String)
object User:
case class Name(firstName: String, lastName: String)
// Table definition example
class UserTable extends Table[User]("user"):
def id: Column[Long] = bigint().autoIncrement.primaryKey
def firstName: Column[String] = varchar(255)
def lastName: Column[String] = varchar(255)
def email: Column[String] = varchar(255)
// Convert (firstName *: lastName) to User.Name, and map id, name, and email to User
override def * : Column[User] =
(id *: (firstName *: lastName).to[User.Name] *: email).to[User]
// Usage example:
TableQuery[UserTable].selectAll.query.to[List].foreach(println)
// Records retrieved from UserTable's select are automatically converted to User.Name(firstName, lastName)
```
With this definition, the database columns `first_name` and `last_name` correspond to `firstName` and `lastName` in User.Name, allowing you to use them as a nested model.
## References
- [Schema Definition Details](/en/tutorial/Schema.md)
- [Custom Data Types](/en/tutorial/Custom-Data-Type.md)
# Q: How to use the Enum type?
## A: EEnum types are automatically encoded/decoded using `Codec.derivedEnum`.
Enum types are automatically encoded/decoded using `Codec.derivedEnum`. The following example defines an Enum type named `Color` and encodes/decodes it using `Codec.derivedEnum`.
The Enum type treats the value of the Enum as it is as a string. For example, `Color.Red` is treated as `“Red”`. This can be used with MySQL's Enum type.
Example:
```scala 3
import ldbc.codec.*
enum Color:
case Red, Blue, Yellow
object Color:
given Codec[Color] = Codec.derivedEnum[Color]
val query = sql"SELECT 'Red'".query[Color].to[Option]
```
If you want to encode/decode an Enum using the values of the fields it contains instead of strings, you need to extend `Codec` to implement it. The following example defines an Enum type called `Color` and extends `Codec` to encode/decode it.
```scala 3
import ldbc.codec.*
enum Color(val colorCode: String):
case Red extends Color("FF0000")
case Blue extends Color("0000FF")
case Yellow extends Color("FFFF00")
object Color:
given Codec[Color] = Codec[String].eimap { str =>
Color.values.find(_.colorCode == str) match
case Some(color) => Right(color)
case None => Left(s"Invalid color code: $str")
} (_.colorCode)
val query = sql"SELECT 'FF0000'".query[Color].to[Option]
```
## References
- [Custom Data Types](/en/tutorial/Custom-Data-Type.md)
- [ENUM Type and Special Data Types](/en/tutorial/Schema.md#enum-type-and-special-data-types)
# Q: How to change column names in Query Builder?
## A: In Query Builder, there are mainly two methods available for changing column names in model definitions.
### A: 1. Using annotations
You can specify column names used in queries by adding the `@Column` annotation to model fields.
For example, if you want to treat the `name` field of the User model as `full_name`, define it as follows:
```scala 3
case class User(
id: Int,
@Column("full_name") name: String,
email: String
) derives Table
val query = TableQuery[User].select(user => user.id *: user.name *: user.email)
// When generating the query, the name field is treated as "full_name"
println(query.statement)
// Output example: "SELECT `id`, `full_name`, `email` FROM user"
```
### A: 2. Using the alias feature of Query Builder
Another method is provided to specify an alias for columns during query construction without modifying the model definition.
The example below shows how to change the column name during retrieval using the `alias` function or a custom mapping function:
```scala 3
import ldbc.dsl.codec.Codec
import ldbc.query.builder.*
case class User(id: Int, name: String, email: String) derives Table
object User:
given Codec[User] = Codec.derived[User]
val userTable = TableQuery[User]
// Build a query and specify aliases in the select clause
val queryWithAlias = userTable
.select(user => user.id *: user.name.as("full_name") *: user.email)
println(queryWithAlias.statement)
// Output example: "SELECT `id`, `name` AS `full_name`, email FROM user"
```
As shown above, you can change the format and display of column names in Query Builder by using annotations at model definition time or by specifying aliases during query construction.
## References
- [How to use Query Builder](/en/tutorial/Query-Builder.md)
- [Schema Definition Details](/en/tutorial/Schema.md)
# Q: How to change the format of column names using the schema?
## A: In schema definitions, the format of column names can be changed by modifying the implicit instance of Naming.
For example, the default is camel case, but using `Naming.PASCAL` converts all column names to Pascal case.
The sample code below demonstrates setting `given Naming = Naming.PASCAL` within a table definition, which is automatically applied to column names.
```scala 3
// Schema definition example (changing column name format)
case class User(id: Long, name: String, age: Option[Int])
class UserTable extends Table[User]("user"):
// Set Naming to change column names to Pascal case
given Naming = Naming.PASCAL
def id: Column[Long] = bigint() // Automatically becomes "Id"
def name: Column[String] = varchar(255) // Automatically becomes "Name"
def age: Column[Option[Int]] = int().defaultNull // Automatically becomes "Age"
override def * : Column[User] = (id *: name *: age).to[User]
// Usage example: The changed column names are applied in select statements
val userTable: TableQuery[UserTable] = TableQuery[UserTable]
val select = userTable.selectAll
println(select.statement)
// Output example: "SELECT `Id`, `Name`, `Age` FROM user"
```
This method allows you to change column names in bulk, enabling consistent schema definitions aligned with your project's naming conventions.
## References
- [Schema Definition Details](/en/tutorial/Schema.md)
- [Custom Data Types](/en/tutorial/Custom-Data-Type.md)
# Q: How to define complex queries with plain queries?
### A: You can define IN clauses by using the `in` function.
For example, you can define an IN clause that embeds values from a list into an SQL statement as follows:
```scala
// Sample: Generating an IN clause
val ids = NonEmptyList.of(1, 2, 3)
val inClause = in(sql"user.id", ids)
// The generated SQL will be "(user.id IN (?, ?, ?))"
```
### A: Use the `and` function to combine multiple SQL conditions with AND.
In the example below, multiple conditions are concatenated with AND to build a single WHERE clause.
```scala
// Sample: Generating AND conditions
val cond1: SQL = sql"user.age > ?" // Example: age filter
val cond2: SQL = sql"user.status = ?" // Example: status filter
val andClause = and(NonEmptyList.of(cond1, cond2))
// The generated SQL will be "((user.age > ?) AND (user.status = ?))"
```
### A: Use the `or` function to combine multiple conditions with OR.
In the example below, multiple conditions are concatenated with OR to generate a flexible WHERE clause.
```scala
// Sample: Generating OR conditions
val condA: SQL = sql"user.country = ?"
val condB: SQL = sql"user.region = ?"
val orClause = or(NonEmptyList.of(condA, condB))
// The generated SQL will be "((user.country = ?) OR (user.region = ?))"
```
### A: The `whereAnd` and `whereOr` functions are useful when dynamically building WHERE clauses.
Using these, you can automatically generate WHERE clauses only when conditions exist.
```scala
// Sample: Generating dynamic WHERE clauses
val conditions: NonEmptyList[SQL] = NonEmptyList.of(sql"user.age > ?", sql"user.status = ?")
val whereClause = whereAnd(conditions)
// The generated SQL will be "WHERE (user.age > ?) AND (user.status = ?)"
```
### A: The `comma` and `parentheses` functions are helpful in complex queries for concatenating multiple columns or conditions.
These functions allow you to properly separate and group list-format SQL elements.
```scala
// Sample: Concatenating columns and grouping
val colList = comma(NonEmptyList.of(sql"user.id", sql"user.name", sql"user.email"))
val grouped = parentheses(colList)
// The generated SQL will be "(user.id, user.name, user.email)"
```
## References
- [Parameterized Queries](/en/tutorial/Parameterized-Queries.md)
# Q: How to handle multiple databases (multi-tenant environment)?
## A: When dealing with multiple databases, create separate ConnectionProviders for each database.
When handling multiple databases, create separate `ConnectionProvider` instances for each database. For example, you can create different providers for different databases as shown below, and switch between providers as needed.
```scala 3
val datasource1 = MySQLDataSource
.build[IO]("host", 3306, "user")
.setPassword("password")
.setDatabase("database1")
val datasource2 = MySQLDataSource
.build[IO]("host", 3306, "user")
.setPassword("password")
.setDatabase("database2")
// Switch between datasources as needed
val program1 = datasource1.getConnection.use { conn => /* operations on database1 */ }
val program2 = datasource2.getConnection.use { conn => /* operations on database2 */ }
```
# Q: How to perform DDL with schema?
## A: To generate and execute DDL from a schema, use the `schema` method of TableQuery.
The `schema` method is a convenient mechanism that automatically generates DDL statements such as CREATE, DROP, TRUNCATE, etc. from table definitions.
The following sample code demonstrates how to generate DDL for `UserTable` and execute table creation, data deletion, and table deletion in sequence.
```scala 3
// ...existing code...
// Example: DDL operations for UserTable
// Assuming UserTable is defined
val userSchema = TableQuery[UserTable].schema
// Examples of generating DDL statements
val createDDL = userSchema.createIfNotExists.statements
val dropDDL = userSchema.dropIfExists.statements
val truncateDDL = userSchema.truncate.statements
// If you want to check the generated DDL statements
createDDL.foreach(println) // CREATE TABLE statement will be output
dropDDL.foreach(println) // DROP TABLE statement will be output
truncateDDL.foreach(println) // TRUNCATE TABLE statement will be output
// Example of executing DDL operations
DBIO.sequence(
userSchema.createIfNotExists,
userSchema.truncate,
userSchema.dropIfExists
)
.commit(connector)
```
The code above implements operations that create tables while checking for their existence, reset table data as needed, and then delete the tables themselves.
## References
- [Schema Definition Details](/en/tutorial/Schema.md)
- [Database Operations](/en/tutorial/Database-Operations.md)
# Q: How to use connection pool with Scala connector?
## A: ldbc-connector now provides built-in connection pooling support!
Starting from version 0.4.0, ldbc-connector includes comprehensive connection pooling functionality. The pooling system is designed specifically for Cats Effect's fiber-based concurrency model, offering high performance and excellent resource efficiency.
## Quick Start
Here's how to create and use a pooled connection with ldbc-connector:
```scala 3
import cats.effect.*
import ldbc.connector.*
import scala.concurrent.duration.*
object ConnectionPoolExample extends IOApp.Simple:
val run =
// Configure your connection pool
val poolConfig = MySQLConfig.default
.setHost("localhost")
.setPort(3306)
.setUser("root")
.setPassword("password")
.setDatabase("testdb")
// Pool-specific settings
.setMinConnections(5) // Keep at least 5 connections ready
.setMaxConnections(20) // Maximum 20 connections
.setConnectionTimeout(30.seconds) // Wait up to 30 seconds for a connection
// Create the pooled data source
MySQLDataSource.pooling[IO](poolConfig).use { pool =>
// Use connections from the pool
pool.getConnection.use { connection =>
for
stmt <- connection.createStatement()
rs <- stmt.executeQuery("SELECT 'Hello from pooled connection!'")
_ <- rs.next()
result <- rs.getString(1)
_ <- IO.println(result)
yield ()
}
}
```
## Configuring Pool Settings
ldbc-connector offers extensive configuration options for fine-tuning your connection pool:
```scala 3
val advancedConfig = MySQLConfig.default
.setHost("localhost")
.setPort(3306)
.setUser("myapp")
.setPassword("secret")
.setDatabase("production")
// Pool Size Management
.setMinConnections(10) // Minimum idle connections
.setMaxConnections(50) // Maximum total connections
// Timeout Configuration
.setConnectionTimeout(30.seconds) // Max wait for connection
.setIdleTimeout(10.minutes) // Remove idle connections after
.setMaxLifetime(30.minutes) // Replace connections after
.setValidationTimeout(5.seconds) // Connection validation timeout
// Health Checks
.setKeepaliveTime(2.minutes) // Validate idle connections every
.setConnectionTestQuery("SELECT 1") // Custom validation query (optional)
// Advanced Features
.setLeakDetectionThreshold(2.minutes) // Warn about leaked connections
.setAdaptiveSizing(true) // Dynamic pool sizing
.setAdaptiveInterval(1.minute) // Check pool size every
```
## Using with Resource Safety
The pooled data source is managed as a `Resource`, ensuring proper cleanup:
```scala 3
import cats.effect.*
import cats.implicits.*
import ldbc.connector.*
def processUsers[F[_]: Async: Network: Console](
pool: PooledDataSource[F]
): F[List[String]] =
pool.getConnection.use { conn =>
conn.prepareStatement("SELECT name FROM users").use { stmt =>
stmt.executeQuery().flatMap { rs =>
// Safely iterate through results
LazyList.unfold(())(_ =>
rs.next().map(hasNext =>
if hasNext then Some((rs.getString("name"), ())) else None
).toOption.flatten
).compile.toList
}
}
}
// Usage
val result = MySQLDataSource.pooling[IO](config).use { pool =>
processUsers[IO](pool)
}
```
## Monitoring Pool Health
Track your pool's performance with built-in metrics:
```scala 3
import ldbc.connector.pool.*
val monitoredPool = for
tracker <- Resource.eval(PoolMetricsTracker.inMemory[IO])
pool <- MySQLDataSource.pooling[IO](
config,
metricsTracker = Some(tracker)
)
yield (pool, tracker)
monitoredPool.use { (pool, tracker) =>
for
// Use the pool
_ <- pool.getConnection.use(conn => /* your queries */ IO.unit)
// Check metrics
metrics <- tracker.getMetrics
_ <- IO.println(s"""
|Pool Metrics:
| Total connections created: ${metrics.totalCreated}
| Active connections: ${metrics.activeConnections}
| Idle connections: ${metrics.idleConnections}
| Waiting requests: ${metrics.waitingRequests}
| Total acquisitions: ${metrics.totalAcquisitions}
| Average wait time: ${metrics.averageAcquisitionTime}ms
""".stripMargin)
yield ()
}
```
## Connection Lifecycle Hooks
Add custom behavior when connections are acquired or released:
```scala 3
case class RequestContext(requestId: String, userId: String)
val poolWithHooks = MySQLDataSource.poolingWithBeforeAfter[IO, RequestContext](
config = config,
before = Some { conn =>
// Set session variables or prepare connection
val context = RequestContext("req-123", "user-456")
conn.createStatement()
.flatMap(_.executeUpdate(s"SET @request_id = '${context.requestId}'"))
.as(context)
},
after = Some { (context, conn) =>
// Log or cleanup after connection use
IO.println(s"Connection released for request: ${context.requestId}")
}
)
```
## Key Features
### Built-in Circuit Breaker
Protects against database failures by failing fast when the database is down:
- Automatically opens after 5 consecutive failures
- Waits 30 seconds before attempting reconnection
- Uses exponential backoff for repeated failures
### Fiber-Optimized
Designed for Cats Effect's lightweight fibers:
- Minimal memory overhead (~150 bytes per fiber vs 1-2MB per thread)
- Non-blocking connection acquisition
- Excellent performance under high concurrency
### Comprehensive Validation
- Automatic connection health checks
- Configurable validation queries
- Idle connection keepalive
- Connection leak detection
## Migration from JDBC/HikariCP
If you're migrating from HikariCP, here's a comparison:
```scala 3
// HikariCP configuration
val hikariConfig = new HikariConfig()
hikariConfig.setJdbcUrl("jdbc:mysql://localhost:3306/testdb")
hikariConfig.setUsername("root")
hikariConfig.setPassword("password")
hikariConfig.setMaximumPoolSize(20)
hikariConfig.setMinimumIdle(5)
hikariConfig.setConnectionTimeout(30000)
// Equivalent ldbc-connector configuration
val ldbcConfig = MySQLConfig.default
.setHost("localhost")
.setPort(3306)
.setDatabase("testdb")
.setUser("root")
.setPassword("password")
.setMaxConnections(20)
.setMinConnections(5)
.setConnectionTimeout(30.seconds)
```
## Best Practices
1. **Start with defaults**: The default configuration works well for most applications
2. **Monitor your pool**: Use metrics tracking to understand your actual usage patterns
3. **Set appropriate timeouts**: Configure based on your application's SLA requirements
4. **Enable leak detection**: In development/staging to catch connection leaks early
5. **Use lifecycle hooks**: For request tracing or session configuration
## References
- [Connection Pooling](/en/tutorial/Connection.md#connection-pooling)
- [Connection Pooling Architecture](/en/reference/Pooling.md)
- [Performance Benchmarks](/en/reference/Pooling.md#benchmark-results)
# Q: How to use with ZIO?
## A: For use with ZIO, use `zio-interop-cats`.
```scala
libraryDependencies += "dev.zio" %% "zio-interop-cats" % ""
```
The following is sample code for using ldbc with ZIO.
```scala 3 mdoc
import java.util.UUID
import cats.effect.std.UUIDGen
import fs2.hashing.Hashing
import fs2.io.net.Network
import zio.*
import zio.interop.catz.*
object Main extends ZIOAppDefault:
given cats.effect.std.Console[Task] = cats.effect.std.Console.make[Task]
given UUIDGen[Task] with
override def randomUUID: Task[UUID] = ZIO.attempt(UUID.randomUUID())
given Hashing[Task] = Hashing.forSync[Task]
given Network[Task] = Network.forAsync[Task]
private def datasource =
MySQLDataSource
.build[Task]("127.0.0.1", 13306, "ldbc")
.setPassword("password")
.setDatabase("world")
.setSSL(SSL.Trusted)
private val connector = Connector.fromDataSource(datasource)
override def run =
sql"SELECT Name FROM city"
.query[String]
.to[List]
.readOnly(connector)
.flatMap { cities =>
Console.printLine(cities)
}
```
### パフォーマンス
Performance results from the Cats Effect to ZIO conversion are shown below.
@:image(/img/connector/Select_effect.svg) {
alt = "Select Benchmark for Effect System"
}
# Q: How to handle the `Implicit search problem too large.` error?
```shell
[error] |Implicit search problem too large.
[error] |an implicit search was terminated with failure after trying 100000 expressions.
[error] |The root candidate for the search was:
[error] |
[error] | given instance given_Decoder_P in object Decoder for ldbc.dsl.codec.Decoder[City]}
```
# A: When `Implicit search problem too large.` occurs, you can solve it by increasing the recursion depth using the `-Ximplicit-search-limit` option.
When the `Implicit search problem too large.` error occurs, you can often solve the problem by increasing the search limit compilation option. You can set any value for the number indicating the recursion depth.
```scala 3
scalacOptions += "-Ximplicit-search-limit:100000"
```
However, expanding the limit through this option may lead to increased compilation time. In that case, you can also solve it by manually constructing the required type as shown below:
```scala 3
given Decoder[City] = Decoder.derived[City]
// Or given Decoder[City] = (Decoder[Int] *: Decoder[String] *: Decoder[Int] *: ....).to[City]
given Encoder[City] = Encoder.derived[City]
// Or given Encoder[City] = (Encoder[Int] *: Encoder[String] *: Encoder[Int] *: ....).to[City]
```
Alternatively, you can solve it by using `Codec` to combine `Encoder` and `Decoder`.
```scala 3
given Codec[City] = Codec.derived[City]
// Or given Codec[City] = (Codec[Int] *: Codec[String] *: Codec[Int] *: ....).to[City]
```
## References
- [Custom Data Type](/en/tutorial/Custom-Data-Type.md)
# Q: Is there a function to limit the number of concurrent queries?
## A: Currently there is no built-in concurrency limit feature, but you can implement it using Cats Effect's Semaphore.
You can implement a function to limit concurrent executions using Cats Effect's `Semaphore`. For example, by defining a `limitedConcurrency` function as follows, you can execute queries with a limited number of concurrent executions.
```scala 3
import cats.effect.*
import cats.effect.syntax.all.*
import ldbc.dsl.*
def limitedConcurrency[A](program: DBIO[A], connector: Connector[IO], maxConcurrent: Int): IO[A] =
Semaphore[IO](maxConcurrent).flatMap { sem =>
sem.permit.use(_ => program.readOnly(connector))
}
```
# Q: Is there a way to stream query results asynchronously?
## A: Streaming API is supported.
Streaming processing can be done using the `stream` method. This returns an Fs2 `Stream`.
```scala 3
val stream: fs2.Stream[DBIO, String] = sql"SELECT * FROM `table`".query[String].stream
```
In MySQL, the behavior of streaming changes significantly depending on the `UseCursorFetch` setting:
- **UseCursorFetch=true**: True streaming processing using server-side cursors
- **UseCursorFetch=false**: Limited streaming processing (with memory constraints)
For handling large datasets, we recommend setting `UseCursorFetch=true`.
## Reference
- [Selecting Data - Efficient Processing of Large Data with Streaming](/en/tutorial/Selecting-Data.md#efficient-processing-of-large-data-with-streaming)
# Q: What is ldbc?
## A: ldbc is an OSS library that enables type-safe database access, query building, and schema definition.
ldbc leverages Scala's power to prevent errors at compile time while allowing intuitive database operations.
For example, let's start with how to build a schema. Here's an example of schema (table) definition:
```scala 3
// Schema definition example
case class User(id: Long, name: String, email: String)
class UserTable extends Table[User]("user"):
def id: Column[Long] = bigint().autoIncrement.primaryKey
def name: Column[String] = varchar(255)
def email: Column[String] = varchar(255)
override def * : Column[User] = (id *: name *: email).to[User]
```
In ldbc, after defining such a schema, you can abstract tables using TableQuery.
```scala
// UserTable abstraction using TableQuery
val userTable: TableQuery[UserTable] = TableQuery[UserTable]
// This allows you to use the schema with QueryBuilder API
```
Next, here's an example of how to use the query builder with the schema definition above. We'll show how to insert and retrieve data using TableQuery based on the schema.
### Query Builder Example Using Schema
```scala 3
// Using UserTable and TableQuery defined in the schema
val userTable: TableQuery[UserTable] = TableQuery[UserTable]
// Data insertion using schema
val schemaInsert: DBIO[Int] =
(userTable += User(1, "Charlie", "charlie@example.com")).update
// Data retrieval using schema (mapping to User)
val schemaSelect = userTable.selectAll.query.to[List]
// Execution example
for
_ <- schemaInsert.commit(connector)
users <- schemaSelect.readOnly(connector)
yield users.foreach(println)
```
You can also perform data operations using plain queries directly. For example, here's how to insert and retrieve data using plain SQL:
```scala 3
// Data insertion using plain query
val plainInsert: DBIO[Int] =
sql"INSERT INTO user (id, name, email) VALUES (2, 'Dave', 'dave@example.com')".update
// Data retrieval using plain query (mapping to User)
val plainSelect: DBIO[List[User]] =
sql"SELECT id, name, email FROM user".query[User].to[List]
// Execution example
for
_ <- plainInsert.commit(connector)
users <- plainSelect.readOnly(connector)
yield users.foreach(println)
```
As shown above, ldbc is an attractive library that enables intuitive data operations from schema construction to query building using those schemas through its simple yet powerful API.
## References
- [How to Use Query Builder](/en/tutorial/Query-Builder.md)
- [Schema Definition Details](/en/tutorial/Schema.md)
# Q: What is the difference between a Java connector and a Scala connector?
## A: While both Java connector (jdbc-connector) and Scala connector (ldbc-connector) provide database connectivity, they differ in the following aspects:
### A: Java Connector (jdbc-connector)
The Java connector uses traditional JDBC API to connect to databases.
- Depends on JDBC drivers (e.g., `mysql-connector-j` for MySQL) and requires low-level configuration.
- Connection establishment, query execution, and result retrieval are implemented using traditional procedural APIs.
```scala
import com.mysql.cj.jdbc.MysqlDataSource
import cats.effect.IO
import ldbc.dsl.DBIO
// Example of datasource configuration and JDBC connector usage
val ds = new com.mysql.cj.jdbc.MysqlDataSource()
ds.setServerName("127.0.0.1")
ds.setPortNumber(13306)
ds.setDatabaseName("world")
ds.setUser("ldbc")
ds.setPassword("password")
val connector = Connector.fromDataSource[IO](ds, ExecutionContexts.synchronous)
DBIO.pure(()).commit(connector)
```
### A: Scala Connector (ldbc-connector)
The Scala connector manages database connections leveraging type safety and functional programming.
- Uses Cats Effect's `Resource` and `IO` for safe connection acquisition and release.
- Enables intuitive data manipulation when combined with DSL and query builders.
- Additionally, ldbc-connector works not only on JVM but also on non-JVM platforms like Scala.js and Scala Native.
This makes database connectivity easily achievable in cross-platform development environments.
```scala
import cats.effect.IO
import ldbc.connector.*
import ldbc.dsl.DBIO
// Example of creating a connection using ldbc-connector (JVM, Scala.js, Scala Native compatible)
val datasource = MySQLDataSource
.build[IO]("127.0.0.1", 3306, "ldbc")
.setPassword("password")
.setDatabase("ldbc")
.setSSL(SSL.Trusted)
val connector = Connector.fromDataSource(datasource)
DBIO.pure(()).commit(connector)
```
### A: Key Differences
- **API Design Philosophy**:
While Java connector uses traditional procedural JDBC API as is, Scala connector is based on functional programming, achieving type-safe and declarative connection management.
- **Error Handling and Resource Management**:
Scala connector uses Cats Effect's `Resource` and `IO` for safe connection acquisition and release, enabling concise error handling.
- **Integration**:
Scala connector seamlessly integrates with ldbc DSL and query builders, providing a unified type-safe API from query definition to execution.
## References
- [Connection](/en/tutorial/Connection.md)
- [What is ldbc?](/en/qa/What-is-ldbc.md)
# Q: Which dependencies should I set?
## A: To use ldbc, you need to set the following dependencies according to your needs.
- Plain DSL
- Query Builder
- Schema Definition and Model Mapping
**Connector**
To perform database connection processing using ldbc, you need to set one of the following dependencies.
**jdbc-connector**
If you want to use the traditional connector written in Java, set the following dependencies:
```scala 3
libraryDependencies ++= Seq(
"io.github.takapi327" %% "jdbc-connector" % "0.4.0",
"com.mysql" % "mysql-connector-j" % "8.4.0"
)
```
**ldbc-connector**
If you want to use the new connector written in Scala, set the following dependencies:
```scala 3
libraryDependencies ++= Seq(
"io.github.takapi327" %% "ldbc-connector" % "0.4.0"
)
```
ldbc-connector works not only on JVM but also on JS and Native platforms.
To use ldbc with Scala.js or Scala Native, set the dependencies as follows:
```scala 3
libraryDependencies ++= Seq(
"com.example" %%% "ldbc-connector" % "0.4.0"
)
```
### Plain DSL
To use the plain DSL, set the following dependencies:
```scala 3
libraryDependencies ++= Seq(
"io.github.takapi327" %% "ldbc-dsl" % "0.4.0"
)
```
Plain DSL is a method to write simple SQL statements as they are. For example, you can execute queries using SQL literals directly.
```scala
import ldbc.dsl.*
val plainResult = sql"SELECT name FROM user"
.query[String]
.to[List]
.readOnly(connector)
// plainResult is returned as List[String]
```
### Query Builder
To use the query builder, set the following dependencies:
```scala 3
libraryDependencies ++= Seq(
"io.github.takapi327" %% "ldbc-query-builder" % "0.4.0"
)
```
Query builder is a method to construct queries with type-safe API. In the following example, we define a `User` model and build a SELECT statement using `TableQuery`.
```scala 3
import ldbc.dsl.codec.Codec
import ldbc.query.builder.*
case class User(id: Int, name: String, email: String) derives Table
object User:
given Codec[User] = Codec.derived[User]
val userQuery = TableQuery[User]
.select(user => user.id *: user.name *: user.email)
.where(_.email === "alice@example.com")
// userQuery.statement is generated as "SELECT id, name, email FROM user WHERE email = ?"
```
### Schema Definition and Model Mapping
To use schema definition and model mapping, set the following dependencies:
```scala 3
libraryDependencies ++= Seq(
"io.github.takapi327" %% "ldbc-schema" % "0.4.0"
)
```
Schema definition and model mapping enable one-to-one mapping between table definitions and Scala models. Here's an example of defining a `User` table:
```scala 3
import ldbc.schema.*
case class User(id: Long, name: String, email: String)
class UserTable extends Table[User]("user"):
def id: Column[Long] = bigint().autoIncrement.primaryKey
def name: Column[String] = varchar(255)
def email: Column[String] = varchar(255)
override def * : Column[User] = (id *: name *: email).to[User]
val userQuery = TableQuery[UserTable]
.select(user => user.id *: user.name *: user.email)
.where(_.email === "alice@example.com")
// userQuery.statement is generated as "SELECT id, name, email FROM user WHERE email = ?"
```
## References
- [How to use Query Builder](/en/tutorial/Query-Builder.md)
- [Schema Definition Details](/en/tutorial/Schema.md)
- [Plain DSL Usage Examples](/en/tutorial/Selecting-Data.md)
- [Database Connection](/en/tutorial/Connection.md)
- [Parameterized Queries](/en/tutorial/Parameterized-Queries.md)
# QA
This section contains questions and answers about ldbc.
## Table of Contents
@:navigationTree {
entries = [ { target = "/en/qa", depth = 2 } ]
}
# Connector
This chapter describes database connections using ldbc's own MySQL connector.
To connect to MySQL databases in Scala, you need to use JDBC. JDBC is a standard Java API that can also be used in Scala.
Since JDBC is implemented in Java, when using it in Scala, it can only operate in the JVM environment.
In recent years, the Scala ecosystem has seen active development of plugins to enable operation in environments like JS and Native.
Scala continues to evolve from a language that only operates in JVM environments using Java assets to one that can operate in multi-platform environments.
However, JDBC is a standard Java API and does not support operation in Scala's multi-platform environments.
Therefore, even if you create a Scala application to run in JS, Native, etc., you cannot connect to databases like MySQL because JDBC cannot be used.
The Typelevel Project includes a Scala library for [PostgreSQL](https://www.postgresql.org/) called [Skunk](https://github.com/typelevel/skunk).
This project does not use JDBC and achieves PostgreSQL connections using pure Scala. As a result, you can connect to PostgreSQL regardless of whether you're in JVM, JS, or Native environments when using Skunk.
The ldbc connector is a project being developed to enable MySQL connections regardless of whether you're in JVM, JS, or Native environments, inspired by Skunk.
The ldbc connector is the lowest layer API.
We plan to provide higher-layer APIs using this connector in the future. We also plan to maintain compatibility with existing higher-layer APIs.
To use it, you need to set up the following dependency in your project:
**JVM**
```scala 3
libraryDependencies += "io.github.takapi327" %% "ldbc-connector" % "0.4.0"
```
**JS/Native**
```scala 3
libraryDependencies += "io.github.takapi327" %%% "ldbc-connector" % "0.4.0"
```
**Supported Versions**
The current version supports the following versions of MySQL:
- MySQL 5.7.x
- MySQL 8.x
The main support is for MySQL 8.x. MySQL 5.7.x is sub-supported. Therefore, caution is required when operating with MySQL 5.7.x.
In the future, support for MySQL 5.7.x is planned to be discontinued.
## Connection
To connect to MySQL using the ldbc connector, use `MySQLDataSource`.
```scala 3
import cats.effect.IO
import ldbc.connector.MySQLDataSource
val datasource = MySQLDataSource
.build[IO](
host = "127.0.0.1",
port = 3306,
user = "root",
)
```
Below is a list of properties that can be set when constructing a `Connection`.
| Property | Details | Required |
|---------------------------|------------------------------------------------------------------------------|----------|
| `host` | `Database host information` | ✅ |
| `port` | `Database port information` | ✅ |
| `user` | `Database user information` | ✅ |
| `password` | `Database password information (default: None)` | ❌ |
| `database` | `Database name information (default: None)` | ❌ |
| `debug` | `Whether to display debug information (default: false)` | ❌ |
| `ssl` | `SSL settings (default: SSL.None)` | ❌ |
| `socketOptions` | `Specify socket options for TCP/UDP sockets (default: defaultSocketOptions)` | ❌ |
| `readTimeout` | `Specify timeout duration (default: Duration.Inf)` | ❌ |
| `allowPublicKeyRetrieval` | `Whether to retrieve public key (default: false)` | ❌ |
| `logHandler` | `Log output settings` | ❌ |
| `before` | `Processing to be executed after connection is established` | ❌ |
| `after` | `Processing to be executed before disconnecting` | ❌ |
| `tracer` | `Tracer settings for metrics output (default: Tracer.noop)` | ❌ |
Create a `Connector` from `MySQLDataSource` to perform database operations. The `Connector` is an interface that translates the `DBIO` monad into actual database operations.
```scala 3
import ldbc.connector.*
// Connectorを作成
val connector = Connector.fromDataSource(datasource)
// DBIOを実行
sql"SELECT 1".query[Int].unsafe.readOnly(connector)
```
Additionally, if you need to use the low-level Connection API, you can use `getConnection` to obtain a `Resource`:
```scala 3
datasource.getConnection.use { conn =>
// Connection APIを直接使用
}
```
### Authentication
In MySQL, authentication occurs when the client connects to the MySQL server and sends user information during the LoginRequest phase. The server then searches for the sent user in the `mysql.user` table and determines which authentication plugin to use. After determining the authentication plugin, the server calls the plugin to start user authentication and sends the result to the client. Thus, authentication in MySQL is pluggable (various types of plugins can be attached and detached).
The authentication plugins supported by MySQL are listed on the [official page](https://dev.mysql.com/doc/refman/8.0/en/authentication-plugins.html).
ldbc currently supports the following authentication plugins:
- Native Pluggable Authentication
- SHA-256 Pluggable Authentication
- SHA-2 Pluggable Authentication Cache
*Note: Native Pluggable Authentication and SHA-256 Pluggable Authentication are deprecated plugins from MySQL 8.x. It is recommended to use the SHA-2 Pluggable Authentication Cache unless there is a specific reason.*
You do not need to be aware of authentication plugins in the ldbc application code. Users should create users with the desired authentication plugin on the MySQL database and use those users to attempt connections to MySQL in the ldbc application code.
ldbc internally determines the authentication plugin and connects to MySQL using the appropriate authentication plugin.
## Execution
The following table is assumed to be used in subsequent processes.
```sql
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT NULL
);
```
### Statement
`Statement` is an API for executing SQL without dynamic parameters.
*Note: Since `Statement` does not use dynamic parameters, there is a risk of SQL injection depending on how it is used. Therefore, it is recommended to use `PreparedStatement` when using dynamic parameters.*
Use the `createStatement` method of `Connection` to construct a `Statement`.
#### Read Query
To execute read-only SQL, use the `executeQuery` method.
The values returned from the MySQL server as a result of executing the query are stored in `ResultSet` and returned as the return value.
```scala 3 3
datasource.getConnection.use { conn =>
for
statement <- conn.createStatement()
result <- statement.executeQuery("SELECT * FROM users")
yield
// Processing using ResultSet
}
```
#### Write Query
To execute SQL for writing, use the `executeUpdate` method.
The values returned from the MySQL server as a result of executing the query are the number of affected rows returned as the return value.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.createStatement()
result <- statement.executeUpdate("INSERT INTO users (name, age) VALUES ('Alice', 20)")
yield
}
```
#### Retrieve AUTO_INCREMENT Value
To retrieve the AUTO_INCREMENT value after executing a query using `Statement`, use the `getGeneratedKeys` method.
The values returned from the MySQL server as a result of executing the query are the values generated by AUTO_INCREMENT returned as the return value.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.createStatement()
_ <- statement.executeUpdate("INSERT INTO users (name, age) VALUES ('Alice', 20)", Statement.RETURN_GENERATED_KEYS)
generatedKeys <- statement.getGeneratedKeys()
yield
}
```
### Client/Server PreparedStatement
ldbc provides `PreparedStatement` divided into `Client PreparedStatement` and `Server PreparedStatement`.
`Client PreparedStatement` is an API for constructing SQL with dynamic parameters on the application and sending it to the MySQL server.
Therefore, the query sending method to the MySQL server is the same as `Statement`.
This API corresponds to JDBC's `PreparedStatement`.
For a safer way to construct queries within the MySQL server, use `Server PreparedStatement`.
`Server PreparedStatement` is an API for preparing queries to be executed within the MySQL server in advance and setting parameters on the application for execution.
With `Server PreparedStatement`, the query sending and parameter sending are separated, allowing query reuse.
When using `Server PreparedStatement`, prepare the query in advance on the MySQL server. The MySQL server uses memory to store it, but query reuse is possible, leading to performance improvements.
However, the prepared query continues to use memory until it is released, posing a risk of memory leaks.
When using `Server PreparedStatement`, use the `close` method to properly release the query.
#### Client PreparedStatement
Use the `clientPreparedStatement` method of `Connection` to construct a `Client PreparedStatement`.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.clientPreparedStatement("SELECT * FROM users WHERE id = ?")
...
yield ...
}
```
#### Server PreparedStatement
Use the `serverPreparedStatement` method of `Connection` to construct a `Server PreparedStatement`.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.serverPreparedStatement("SELECT * FROM users WHERE id = ?")
...
yield ...
}
```
#### Read Query
To execute read-only SQL, use the `executeQuery` method.
The values returned from the MySQL server as a result of executing the query are stored in `ResultSet` and returned as the return value.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.clientPreparedStatement("SELECT * FROM users WHERE id = ?") // or conn.serverPreparedStatement("SELECT * FROM users WHERE id = ?")
_ <- statement.setLong(1, 1)
result <- statement.executeQuery()
yield
// Processing using ResultSet
}
```
When using dynamic parameters, use the `setXXX` method to set the parameters.
The `setXXX` method can also use the `Option` type. If `None` is passed, NULL is set as the parameter.
The `setXXX` method specifies the parameter index and the parameter value.
```scala 3
statement.setLong(1, 1)
```
The following methods are currently supported.
| Method | Type | Remarks |
|-----------------|---------------------------------------|-----------------------------------------------------|
| `setNull` | | Sets NULL as the parameter |
| `setBoolean` | `Boolean/Option[Boolean]` | |
| `setByte` | `Byte/Option[Byte]` | |
| `setShort` | `Short/Option[Short]` | |
| `setInt` | `Int/Option[Int]` | |
| `setLong` | `Long/Option[Long]` | |
| `setBigInt` | `BigInt/Option[BigInt]` | |
| `setFloat` | `Float/Option[Float]` | |
| `setDouble` | `Double/Option[Double]` | |
| `setBigDecimal` | `BigDecimal/Option[BigDecimal]` | |
| `setString` | `String/Option[String]` | |
| `setBytes` | `Array[Byte]/Option[Array[Byte]]` | |
| `setDate` | `LocalDate/Option[LocalDate]` | Directly handles `java.time` instead of `java.sql`. |
| `setTime` | `LocalTime/Option[LocalTime]` | Directly handles `java.time` instead of `java.sql`. |
| `setTimestamp` | `LocalDateTime/Option[LocalDateTime]` | Directly handles `java.time` instead of `java.sql`. |
| `setYear` | `Year/Option[Year]` | Directly handles `java.time` instead of `java.sql`. |
#### Write Query
To execute SQL for writing, use the `executeUpdate` method.
The values returned from the MySQL server as a result of executing the query are the number of affected rows returned as the return value.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.clientPreparedStatement("INSERT INTO users (name, age) VALUES (?, ?)") // or conn.serverPreparedStatement("INSERT INTO users (name, age) VALUES (?, ?)")
_ <- statement.setString(1, "Alice")
_ <- statement.setInt(2, 20)
result <- statement.executeUpdate()
yield result
}
```
#### Retrieve AUTO_INCREMENT Value
To retrieve the AUTO_INCREMENT value after executing a query, use the `getGeneratedKeys` method.
The values returned from the MySQL server as a result of executing the query are the values generated by AUTO_INCREMENT returned as the return value.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.clientPreparedStatement("INSERT INTO users (name, age) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS) // or conn.serverPreparedStatement("INSERT INTO users (name, age) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS)
_ <- statement.setString(1, "Alice")
_ <- statement.setInt(2, 20)
_ <- statement.executeUpdate()
getGeneratedKeys <- statement.getGeneratedKeys()
yield getGeneratedKeys
}
```
### ResultSet
`ResultSet` is an API for storing the values returned from the MySQL server after executing a query.
To retrieve records obtained by executing SQL from `ResultSet`, you can use the `next` method and `getXXX` methods similar to JDBC, or use the ldbc-specific `decode` method.
#### next/getXXX
The `next` method returns `true` if the next record exists, and `false` if the next record does not exist.
The `getXXX` method is an API for retrieving values from records.
The `getXXX` method can specify the column index or the column name to retrieve.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.clientPreparedStatement("SELECT `id`, `name`, `age` FROM users WHERE id = ?")
_ <- statement.setLong(1, 1)
result <- statement.executeQuery()
yield
val builder = List.newBuilder[(Long, String, Int)]
while resultSet.next() do
val id = resultSet.getLong(1)
val name = resultSet.getString("name")
val age = resultSet.getInt(3)
builder += (id, name, age)
builder.result()
}
```
## Transaction
To execute transactions using `Connection`, combine the `setAutoCommit` method, `commit` method, and `rollback` method.
First, use the `setAutoCommit` method to disable automatic transaction commit.
```scala 3
conn.setAutoCommit(false)
```
After performing some processing, use the `commit` method to commit the transaction.
```scala 3
for
statement <- conn.clientPreparedStatement("INSERT INTO users (name, age) VALUES (?, ?)")
_ <- statement.setString(1, "Alice")
_ <- statement.setInt(2, 20)
_ <- conn.setAutoCommit(false)
result <- statement.executeUpdate()
_ <- conn.commit()
yield
```
Alternatively, use the `rollback` method to roll back the transaction.
```scala 3
for
statement <- conn.clientPreparedStatement("INSERT INTO users (name, age) VALUES (?, ?)")
_ <- statement.setString(1, "Alice")
_ <- statement.setInt(2, 20)
_ <- conn.setAutoCommit(false)
result <- statement.executeUpdate()
_ <- conn.rollback()
yield
```
When the `setAutoCommit` method is used to disable automatic transaction commit, the connection is automatically rolled back when the Resource is released.
### Transaction Isolation Level
ldbc allows you to set the transaction isolation level.
The transaction isolation level is set using the `setTransactionIsolation` method.
MySQL supports the following transaction isolation levels:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
For more information on MySQL transaction isolation levels, refer to the [official documentation](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html).
```scala 3
import ldbc.connector.Connection.TransactionIsolationLevel
conn.setTransactionIsolation(TransactionIsolationLevel.REPEATABLE_READ)
```
To retrieve the currently set transaction isolation level, use the `getTransactionIsolation` method.
```scala 3
for
isolationLevel <- conn.getTransactionIsolation()
yield
```
### Savepoints
For more advanced transaction management, you can use the "Savepoint" feature. This allows you to mark specific points during database operations, enabling you to roll back to those points if something goes wrong. This is particularly useful for complex database operations or when you need to set safe points during long transactions.
**Features:**
- Flexible Transaction Management: Use savepoints to create "checkpoints" at any point within a transaction. If needed, you can roll back to that point, saving time and improving efficiency.
- Error Recovery: Instead of redoing everything from the beginning when an error occurs, you can roll back to the last safe savepoint, saving time and improving efficiency.
- Advanced Control: By setting multiple savepoints, you can achieve more precise transaction control. Developers can easily implement more complex logic and error handling.
By leveraging this feature, your application can achieve more robust and reliable database operations.
**Setting Savepoints**
To set a savepoint, use the `setSavepoint` method. You can specify the name of the savepoint with this method.
If you do not specify a name for the savepoint, a default name generated by UUID will be set.
You can retrieve the name of the set savepoint using the `getSavepointName` method.
*Note: In MySQL, auto-commit is enabled by default. To use savepoints, you need to disable auto-commit. Otherwise, all operations will be committed each time, and you will not be able to roll back transactions using savepoints.*
```scala 3
for
_ <- conn.setAutoCommit(false)
savepoint <- conn.setSavepoint("savepoint1")
yield savepoint.getSavepointName
```
**Rolling Back to Savepoints**
To roll back a part of a transaction using a savepoint, pass the savepoint to the `rollback` method.
After partially rolling back using a savepoint, if you commit the entire transaction, the transaction after that savepoint will not be committed.
```scala 3
for
_ <- conn.setAutoCommit(false)
savepoint <- conn.setSavepoint("savepoint1")
_ <- conn.rollback(savepoint)
_ <- conn.commit()
yield
```
**Releasing Savepoints**
To release a savepoint, pass the savepoint to the `releaseSavepoint` method.
After releasing a savepoint, if you commit the entire transaction, the transaction after that savepoint will be committed.
```scala 3
for
_ <- conn.setAutoCommit(false)
savepoint <- conn.setSavepoint("savepoint1")
_ <- conn.releaseSavepoint(savepoint)
_ <- conn.commit()
yield
```
## Utility Commands
MySQL has several utility commands. ([Reference](https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_command_phase_utility.html))
ldbc provides APIs to use these commands.
| Command | Purpose | Supported |
|------------------------|-----------------------------------------------------------------------------|-----------|
| `COM_QUIT` | `Informs the server that the client is requesting to close the connection.` | ✅ |
| `COM_INIT_DB` | `Changes the default schema of the connection.` | ✅ |
| `COM_STATISTICS` | `Retrieves internal status string in a readable format.` | ✅ |
| `COM_DEBUG` | `Dumps debug information to the server's standard output.` | ❌ |
| `COM_PING` | `Checks if the server is alive.` | ✅ |
| `COM_CHANGE_USER` | `Changes the user of the current connection.` | ✅ |
| `COM_RESET_CONNECTION` | `Resets the session state.` | ✅ |
| `COM_SET_OPTION` | `Sets options for the current connection.` | ✅ |
### COM QUIT
`COM_QUIT` is a command to inform the server that the client is requesting to close the connection.
In ldbc, you can close the connection using the `close` method of `Connection`.
When you use the `close` method, the connection is closed, and you cannot use the connection in subsequent processing.
*Note: `Connection` uses `Resource` for resource management. Therefore, you do not need to use the `close` method to release resources.*
```scala 3
datasource.getConnection.use { conn =>
conn.close()
}
```
### COM INIT DB
`COM_INIT_DB` is a command to change the default schema of the connection.
In ldbc, you can change the default schema using the `setSchema` method of `Connection`.
```scala 3
datasource.getConnection.use { conn =>
conn.setSchema("test")
}
```
### COM STATISTICS
`COM_STATISTICS` is a command to retrieve internal status string in a readable format.
In ldbc, you can retrieve the internal status string using the `getStatistics` method of `Connection`.
```scala 3
datasource.getConnection.use { conn =>
conn.getStatistics
}
```
The status that can be retrieved is as follows:
- `uptime` : Time since the server started
- `threads` : Number of currently connected clients
- `questions` : Number of queries since the server started
- `slowQueries` : Number of slow queries
- `opens` : Number of table opens since the server started
- `flushTables` : Number of table flushes since the server started
- `openTables` : Number of currently open tables
- `queriesPerSecondAvg` : Average number of queries per second
### COM PING
`COM_PING` is a command to check if the server is alive.
In ldbc, you can check if the server is alive using the `isValid` method of `Connection`.
If the server is alive, it returns `true`, and if it is not alive, it returns `false`.
```scala 3
datasource.getConnection.use { conn =>
conn.isValid
}
```
### COM CHANGE USER
`COM_CHANGE_USER` is a command to change the user of the current connection.
It also resets the following connection states:
- User variables
- Temporary tables
- Prepared statements
- etc...
In ldbc, you can change the user using the `changeUser` method of `Connection`.
```scala 3
datasource.getConnection.use { conn =>
conn.changeUser("root", "password")
}
```
### COM RESET CONNECTION
`COM_RESET_CONNECTION` is a command to reset the session state.
`COM_RESET_CONNECTION` is a lighter version of `COM_CHANGE_USER`, with almost the same functionality to clean up the session state, but with the following features:
- Does not re-authenticate (thus avoiding extra client/server exchanges).
- Does not close the connection.
In ldbc, you can reset the session state using the `resetServerState` method of `Connection`.
```scala 3
datasource.getConnection.use { conn =>
conn.resetServerState
}
```
### COM SET OPTION
`COM_SET_OPTION` is a command to set options for the current connection.
In ldbc, you can set options using the `enableMultiQueries` method and `disableMultiQueries` method of `Connection`.
Using the `enableMultiQueries` method allows you to execute multiple queries at once.
Using the `disableMultiQueries` method prevents you from executing multiple queries at once.
*Note: This can only be used for batch processing with Insert, Update, and Delete statements. If used with Select statements, only the result of the first query will be returned.*
```scala 3
datasource.getConnection.use { conn =>
conn.enableMultiQueries *> conn.disableMultiQueries
}
```
## Batch Commands
ldbc allows you to execute multiple queries at once using batch commands.
By using batch commands, you can execute multiple queries at once, reducing the number of network round trips.
To use batch commands, use the `addBatch` method of `Statement` or `PreparedStatement` to add queries, and use the `executeBatch` method to execute the queries.
```scala 3 3
datasource.getConnection.use { conn =>
for
statement <- conn.createStatement()
_ <- statement.addBatch("INSERT INTO users (name, age) VALUES ('Alice', 20)")
_ <- statement.addBatch("INSERT INTO users (name, age) VALUES ('Bob', 30)")
result <- statement.executeBatch()
yield result
}
```
In the above example, you can add the data of `Alice` and `Bob` at once.
The executed query will be as follows:
```sql
INSERT INTO users (name, age) VALUES ('Alice', 20);INSERT INTO users (name, age) VALUES ('Bob', 30);
```
The return value after executing the batch command is an array of the number of affected rows for each executed query.
In the above example, the data of `Alice` is added as 1 row, and the data of `Bob` is also added as 1 row, so the return value will be `List(1, 1)`.
After executing the batch command, the queries added with the `addBatch` method so far are cleared.
To manually clear the queries, use the `clearBatch` method to clear them.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.createStatement()
_ <- statement.addBatch("INSERT INTO users (name, age) VALUES ('Alice', 20)")
_ <- statement.clearBatch()
_ <- statement.addBatch("INSERT INTO users (name, age) VALUES ('Bob', 30)")
_ <- statement.executeBatch()
yield
}
```
In the above example, the data of `Alice` is not added, but the data of `Bob` is added.
### Difference Between Statement and PreparedStatement
There may be differences in the queries executed by batch commands when using `Statement` and `PreparedStatement`.
When executing an INSERT statement with batch commands using `Statement`, multiple queries are executed at once.
However, when executing an INSERT statement with batch commands using `PreparedStatement`, one query is executed.
For example, when executing the following query with batch commands, multiple queries are executed at once because `Statement` is used.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.createStatement()
_ <- statement.addBatch("INSERT INTO users (name, age) VALUES ('Alice', 20)")
_ <- statement.addBatch("INSERT INTO users (name, age) VALUES ('Bob', 30)")
result <- statement.executeBatch()
yield result
}
// Executed query
// INSERT INTO users (name, age) VALUES ('Alice', 20);INSERT INTO users (name, age) VALUES ('Bob', 30);
```
However, when executing the following query with batch commands, one query is executed because `PreparedStatement` is used.
```scala 3
datasource.getConnection.use { conn =>
for
statement <- conn.clientPreparedStatement("INSERT INTO users (name, age) VALUES (?, ?)")
_ <- statement.setString(1, "Alice")
_ <- statement.setInt(2, 20)
_ <- statement.addBatch()
_ <- statement.setString(1, "Bob")
_ <- statement.setInt(2, 30)
_ <- statement.addBatch()
result <- statement.executeBatch()
yield result
}
// Executed query
// INSERT INTO users (name, age) VALUES ('Alice', 20), ('Bob', 30);
```
This is because when using `PreparedStatement`, you can set multiple parameters in one query by using the `addBatch` method after setting the query parameters.
## Executing Stored Procedures
ldbc provides an API for executing stored procedures.
To execute a stored procedure, use the `prepareCall` method of `Connection` to construct a `CallableStatement`.
*Note: The stored procedure used is from the [official](https://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-statements-callable.html) documentation.*
```sql
CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam INT)
BEGIN
DECLARE z INT;
SET z = inOutParam + 1;
SET inOutParam = z;
SELECT inputParam;
SELECT CONCAT('zyxw', inputParam);
END
```
To execute the above stored procedure, it will be as follows.
```scala 3
datasource.getConnection.use { conn =>
for
callableStatement <- conn.prepareCall("CALL demoSp(?, ?)")
_ <- callableStatement.setString(1, "abcdefg")
_ <- callableStatement.setInt(2, 1)
hasResult <- callableStatement.execute()
values <- Monad[IO].whileM[List, Option[String]](callableStatement.getMoreResults()) {
for
resultSet <- callableStatement.getResultSet().flatMap {
case Some(rs) => IO.pure(rs)
case None => IO.raiseError(new Exception("No result set"))
}
yield resultSet.getString(1)
}
yield values // List(Some("abcdefg"), Some("zyxwabcdefg"))
}
```
To retrieve the value of an output parameter (a parameter specified as OUT or INOUT when creating the stored procedure), in JDBC, you need to specify the parameter before executing the statement using various `registerOutputParameter()` methods of the CallableStatement interface. However, in ldbc, you only need to set the parameter using the `setXXX` method, and the parameter will be set when executing the query.
However, you can also specify the parameter using the `registerOutputParameter()` method in ldbc.
```scala 3
datasource.getConnection.use { conn =>
for
callableStatement <- conn.prepareCall("CALL demoSp(?, ?)")
_ <- callableStatement.setString(1, "abcdefg")
_ <- callableStatement.setInt(2, 1)
_ <- callableStatement.registerOutParameter(2, ldbc.connector.data.Types.INTEGER)
hasResult <- callableStatement.execute()
value <- callableStatement.getInt(2)
yield value // 2
}
```
*Note: When specifying an Out parameter with `registerOutParameter`, if the same index value is not used to set the parameter with the `setXXX` method, the value will be set to `Null` on the server.*
# Performance
This document provides a detailed analysis of the performance characteristics between ldbc and jdbc, helping you understand when and why to choose ldbc based on technical considerations.
## Executive Summary
Benchmark results show that ldbc demonstrates approximately 1.8-2.1x higher throughput compared to jdbc. This advantage stems from Cats Effect's Fiber-based concurrency model and non-blocking I/O implementation. ldbc particularly excels in high-concurrency environments with superior scalability.
### Key Findings
1. **Performance**: ldbc is ~2x faster than jdbc (8-thread environment)
2. **Scalability**: ldbc achieves near-linear scaling with increased thread count
3. **Resource Efficiency**: Significantly reduced memory usage (Fiber: 500 bytes vs OS Thread: 1MB)
4. **Latency**: Maintains stable response times even under high load
## Benchmark Environment
### Hardware and Software Environment
- **JDK**: Amazon Corretto 21.0.6
- **JVM**: OpenJDK 64-Bit Server VM (21.0.6+7-LTS)
- **Memory**: 4GB (Heap size: -Xms4G -Xmx4G)
- **GC**: G1GC (-XX:+UseG1GC -XX:MaxGCPauseMillis=200)
- **MySQL**: Version 8.4.0 (Docker environment)
- Port: 13306
- User: ldbc
- Password: password
### JMH Benchmark Configuration
```scala
@BenchmarkMode(Array(Mode.Throughput)) // Throughput measurement
@OutputTimeUnit(TimeUnit.SECONDS) // Output in ops/s
@State(Scope.Benchmark) // Benchmark scope
@Fork(value = 1) // Fork count: 1
@Warmup(iterations = 5) // Warmup: 5 iterations
@Measurement(iterations = 10) // Measurement: 10 iterations
@Threads(1) // Thread count: varies 1-16
```
### Test Conditions
- **Connection Method**: No connection pooling (single connection reused)
- **Query Types**:
- Statement: Dynamic SQL execution
- PreparedStatement: Parameterized queries
- **Data Sizes**: 500, 1000, 1500, 2000 rows
- **Target Table**: 16 columns (various data types)
- Numeric types: Long, Short, Int, Float, Double, BigDecimal
- String types: String (2 types)
- Date/Time types: LocalDate, LocalTime, LocalDateTime (2 types)
- Boolean type: Boolean
### ldbc-specific Configuration
```scala
MySQLDataSource
.build[IO]("127.0.0.1", 13306, "ldbc")
.setPassword("password")
.setDatabase("benchmark")
.setSSL(SSL.Trusted)
// Default settings (no optimization)
.setUseServerPrepStmts(false)
.setUseCursorFetch(false)
```
### jdbc-specific Configuration
```scala
val ds = new MysqlDataSource()
ds.setServerName("127.0.0.1")
ds.setPortNumber(13306)
ds.setDatabaseName("benchmark")
ds.setUser("ldbc")
ds.setPassword("password")
ds.setUseSSL(true)
// Fixed-size thread pool
val executorService = Executors.newFixedThreadPool(
Math.max(4, Runtime.getRuntime.availableProcessors())
)
```
## Performance Comparison by Thread Count
### Single Thread Environment
@:image(/img/connector/Select_Thread1.svg) {
alt = "Select Benchmark (1 Thread)"
}
In a single-threaded environment, the performance difference between ldbc and jdbc is relatively small as concurrency advantages are not utilized.
**Performance Ratio (ldbc/jdbc)**:
- 500 rows: 1.43x
- 1000 rows: 1.52x
- 1500 rows: 1.48x
- 2000 rows: 1.51x
### 2 Thread Environment
@:image(/img/connector/Select_Thread2.svg) {
alt = "Select Benchmark (2 Threads)"
}
Starting with 2 threads, ldbc's advantages become more apparent.
**Performance Ratio (ldbc/jdbc)**:
- 500 rows: 1.83x
- 1000 rows: 1.48x
- 1500 rows: 1.66x
- 2000 rows: 1.75x
### 4 Thread Environment
@:image(/img/connector/Select_Thread4.svg) {
alt = "Select Benchmark (4 Threads)"
}
At 4 threads, ldbc's scalability becomes pronounced.
**Performance Ratio (ldbc/jdbc)**:
- 500 rows: 1.89x
- 1000 rows: 1.82x
- 1500 rows: 1.87x
- 2000 rows: 1.93x
### 8 Thread Environment
@:image(/img/connector/Select_Thread8.svg) {
alt = "Select Benchmark (8 Threads)"
}
At 8 threads, ldbc shows its highest performance advantage.
**Performance Ratio (ldbc/jdbc)**:
- 500 rows: 1.76x
- 1000 rows: 2.01x
- 1500 rows: 1.92x
- 2000 rows: 2.09x
### 16 Thread Environment
@:image(/img/connector/Select_Thread16.svg) {
alt = "Select Benchmark (16 Threads)"
}
Even at 16 threads, ldbc maintains stable high performance.
**Performance Ratio (ldbc/jdbc)**:
- 500 rows: 1.95x
- 1000 rows: 2.03x
- 1500 rows: 1.98x
- 2000 rows: 2.12x
## Technical Analysis
### Cats Effect Performance Characteristics
Cats Effect 3 is optimized for long-lived backend network applications:
#### Optimization Targets
- **Physical threads**: Environments with 8+ threads
- **Processing type**: Network socket I/O-focused processing
- **Runtime duration**: Applications running for hours or more
#### Performance Metrics
- **`flatMap` operation**:
- Older Intel CPUs: ~7 nanoseconds
- Modern AMD CPUs: ~3 nanoseconds
- **Bottlenecks**: Scheduling and I/O rather than computation
#### Userspace Scheduler
1. **Work-stealing algorithm**: Designed for throughput over pure responsiveness
2. **Fine-grained preemption**: More flexible task switching than Kotlin coroutines
3. **Stack usage**: Constant memory usage through coroutine interpreter
#### Comparison with Other Runtimes
- **Project Loom (Virtual Threads)**: Cats Effect currently outperforms
- **Threadless suspension**: Supports safe resource management and asynchronous interruption
### Concurrency Model Differences
#### ldbc (Cats Effect 3)
ldbc adopts Cats Effect 3's Fiber-based concurrency model:
```scala
// Non-blocking I/O operations
for {
statement <- connection.prepareStatement(sql)
_ <- statement.setInt(1, id)
resultSet <- statement.executeQuery()
result <- resultSet.decode[User]
} yield result
```
**Characteristics**:
- **Fibers (Green Threads)**: Lightweight user-space threads
- Memory usage: ~300-500 bytes per Fiber
- Context switching: Completes in user space (no kernel calls required)
- CPU cache efficiency: High cache hit rate due to thread affinity
- **Work-Stealing Thread Pool**:
- Per-CPU-core work queues (avoiding global contention)
- Dynamic load balancing
- Automatic yield insertion to prevent CPU starvation
#### jdbc (Traditional Thread Model)
jdbc uses traditional OS threads with blocking I/O:
```scala
// Blocking I/O operations
Sync[F].blocking {
val statement = connection.prepareStatement(sql)
statement.setInt(1, id)
val resultSet = statement.executeQuery()
// Thread blocks here
}
```
**Characteristics**:
- **OS Threads**: Native threads
- Memory usage: ~1MB per thread
- Context switching: Requires kernel calls
- Fixed-size thread pool
### Network I/O Implementation
#### ldbc - Non-blocking Socket
```scala
// Non-blocking reads using fs2 Socket
socket.read(8192).flatMap { chunk =>
// Efficient chunk-based processing
processChunk(chunk)
}
```
- **Zero-copy optimization**: Efficient buffer management using BitVector
- **Streaming**: Efficient processing of large result sets
- **Timeout control**: Fine-grained timeout configuration
#### jdbc - Blocking Socket
```scala
// Traditional blocking I/O
val bytes = inputStream.read(buffer)
// Thread blocks until I/O completes
```
- **Buffering**: Entire result set loaded into memory
- **Thread blocking**: Thread unavailable during I/O wait
### Memory Efficiency and GC Pressure
#### ldbc Memory Management
1. **Pre-allocated buffers**: Reusable buffers for result rows
2. **Streaming processing**: On-demand data fetching
3. **Immutable data structures**: Efficient memory usage through structural sharing
#### jdbc Memory Management
1. **Bulk loading**: Entire result set held in memory
2. **Intermediate objects**: Boxing/unboxing overhead
3. **GC pressure**: Frequent GC due to temporary objects
## Usage Recommendations by Scenario
### When to Choose ldbc
1. **High-Concurrency Applications**
- Web applications (high traffic)
- Microservices
- Real-time data processing
2. **Resource-Constrained Environments**
- Container environments (Kubernetes, etc.)
- Serverless environments
- Memory-limited environments
3. **Scalability Focus**
- Expected future load increases
- Need for elastic scaling
- Cloud-native applications
4. **Functional Programming**
- Pure functional architecture
- Type safety emphasis
- Composability focus
### When to Choose jdbc
1. **Legacy System Integration**
- Existing jdbc codebase
- Third-party library dependencies
- High migration costs
2. **Simple CRUD Operations**
- Low concurrency
- Batch processing
- Administrative tools
3. **Special jdbc Features**
- Vendor-specific extensions
- Special driver requirements
## Performance Tuning
### Cats Effect Best Practices
#### 1. Understanding Your Workload
- **I/O-bound tasks**: Cats Effect excels with non-blocking I/O
- **CPU-bound tasks**: Consider delegation to dedicated thread pools
- **Measurement importance**: Measure performance in your specific application context
#### 2. Optimizing IO Operations
```scala
// Leverage fine-grained IO composition
val optimized = for {
data <- fetchData() // Non-blocking I/O
_ <- IO.cede // Explicit cooperative yield
processed <- processData(data) // CPU-intensive processing
_ <- saveResult(processed) // Non-blocking I/O
} yield processed
// Execute CPU-bound tasks on dedicated pool
val cpuBound = IO.blocking {
// Heavy computation
}.evalOn(cpuBoundExecutor)
```
#### 3. Resource Management
```scala
// Safe resource management with Resource
val dataSource = Resource.make(
createDataSource() // Acquire
)(_.close()) // Release
// Guaranteed resource cleanup
dataSource.use { ds =>
// Process with datasource
}
```
### ldbc Optimization Settings
```scala
val datasource = MySQLDataSource
.build[IO]("localhost", 3306, "user")
.setPassword("password")
.setDatabase("db")
// Performance settings
.setUseServerPrepStmts(true) // Server-side prepared statements
.setUseCursorFetch(true) // Cursor-based fetching
.setFetchSize(1000) // Fetch size
.setSocketOptions(List(
SocketOption.noDelay(true), // TCP_NODELAY
SocketOption.keepAlive(true) // Keep-alive
))
.setReadTimeout(30.seconds) // Read timeout
```
### Thread Pool Configuration
```scala
// Cats Effect 3 runtime configuration
object Main extends IOApp {
override def computeWorkerThreadCount: Int =
math.max(4, Runtime.getRuntime.availableProcessors())
override def run(args: List[String]): IO[ExitCode] = {
// Application logic
}
}
```
## Conclusion
ldbc is an excellent choice particularly when these conditions are met:
1. **High Concurrency**: Need to handle many concurrent connections
2. **Scalability**: Require flexible scaling based on load
3. **Resource Efficiency**: Need to minimize memory usage
4. **Type Safety**: Value compile-time type checking
5. **Functional Programming**: Adopting pure functional architecture
As benchmark results demonstrate, ldbc achieves approximately 2x the throughput of jdbc in environments with 8+ threads, while maintaining excellent scalability with minimal performance degradation at higher thread counts.
### Benefits from Cats Effect Optimization
The Cats Effect 3 runtime that ldbc leverages is optimized for:
- **Long-running applications**: Shows its true value in applications running for hours or more
- **Network I/O-centric**: Perfect for non-blocking I/O operations like database access
- **Multi-core utilization**: Best performance in environments with 8+ threads
These characteristics perfectly align with ldbc's use case as a database access library, making ldbc a powerful choice for modern cloud-native applications and high-traffic web services.
**Key Principle**: "Performance is always relative to your target use-case and assumptions" - we recommend measuring in your actual application context.
# Connection Pooling
## Overview
ldbc-connector is a library designed for Cats Effect that provides high-performance and safe database connection pooling. Unlike traditional JVM thread-based pooling (such as HikariCP), it is designed to fully utilize Cats Effect's fiber-based concurrency model.
@:callout(warning)
**Limitations with Scala Native 0.4.x**
The current Scala Native 0.4.x only supports single-threaded execution. Since the connection pooling functionality in ldbc-connector is designed with multi-threading in mind, when using it with Scala Native 0.4.x, the following issues may occur:
- Concurrent connection management does not work correctly
- Background tasks (HouseKeeper, AdaptivePoolSizer, etc.) may not execute as expected
- Concurrent connections are effectively limited to 1
- Deadlocks or unexpected behavior may occur
Scala Native 0.5.x is planned to support multi-threading, but until then, using connection pooling with Scala Native is not recommended. Instead, we recommend creating and using a new connection for each database operation.
@:@
## Architecture Overview
The ldbc-connector pooling system consists of the following main components:
### 1. PooledDataSource
The core component of the pool that manages the entire connection lifecycle.
Main responsibilities:
- Coordinating connection acquisition and release
- Managing pool size
- Collecting metrics
- Coordinating background tasks
### 2. ConcurrentBag
A high-performance concurrent data structure inspired by HikariCP's ConcurrentBag, but optimized for Cats Effect fibers rather than JVM threads.
Features:
- Lock-free operations
- Direct handoff between fibers
- Efficient wait queue management
- Atomic state management using `Ref[F]`
### 3. CircuitBreaker
A critical component for preventing the "Thundering Herd" problem when the database is down.
## CircuitBreaker Details
### State Transition Diagram
### Purpose of CircuitBreaker
The CircuitBreaker pattern is implemented to solve the following problems:
1. **Thundering Herd Problem Prevention**
- Prevents situations where a large number of clients attempt to reconnect simultaneously after the database becomes temporarily unavailable
- This avoids further increasing the load on an already struggling database
2. **Fail Fast**
- When the database is unresponsive, new connection attempts fail immediately
- This prevents clients from waiting for long timeouts
3. **Gradual Recovery**
- Carefully tests whether the service has recovered through the Half-Open state
- Exponential backoff gradually increases retry intervals for repeated failures
### Implementation Details
```scala
trait CircuitBreaker[F[_]]:
def protect[A](action: F[A]): F[A]
def state: F[CircuitBreaker.State]
def reset: F[Unit]
```
Configuration parameters:
- `maxFailures`: Number of failures before transitioning to Open state (default: 5)
- `resetTimeout`: Time before transitioning from Open to Half-Open state (default: 60 seconds)
- `exponentialBackoffFactor`: Timeout increase factor on failure (default: 2.0)
- `maxResetTimeout`: Maximum reset timeout (default: 5 minutes)
### Operation Flow
1. **Closed State**
- All requests are processed normally
- Failures are counted, and the state transitions to Open when threshold is reached
2. **Open State**
- All requests fail immediately (fail fast)
- After reset timeout elapses, attempts to transition to Half-Open state
3. **Half-Open State**
- Allows a single test request
- On success: Returns to Closed state
- On failure: Returns to Open state with exponentially increased timeout
## JVM Threads vs Cats Effect Fibers
### Differences and Characteristics of Concurrency Models
#### JVM Thread Characteristics (HikariCP, etc.)
**Advantages:**
- **OS-level support**: Preemptive scheduling
- **Existing ecosystem**: Many libraries and tools support
- **Debug tools**: Mature profiling and monitoring tools
- **Simple execution model**: Easy-to-understand execution flow
**Limitations:**
- **Memory usage**: 1-2MB per thread
- **Context switching**: High cost at kernel level
- **Scalability**: Practical limit of thousands of threads
- **Blocking**: Threads actually block
#### Cats Effect Fiber Characteristics (ldbc-connector)
**Advantages:**
- **Memory efficiency**: ~150 bytes per fiber
- **Lightweight context switching**: User-space switching
- **High scalability**: Millions of fibers possible
- **Semantic blocking**: Worker threads are released
**Limitations:**
- **Cooperative scheduling**: Requires explicit yield points
- **Learning curve**: Requires functional programming knowledge
- **Ecosystem**: Limited supporting libraries
- **Debug complexity**: Difficult to trace asynchronous execution flow
### Impact on Pooling Design
The characteristics of each concurrency model lead to different pooling implementation approaches:
#### Thread-based Pool (HikariCP style)
- **Pool size**: Requires careful configuration due to OS resource constraints
- **Wait strategy**: Blocking wait, utilizing dedicated thread pools
- **Use cases**: CPU-intensive tasks, integration with legacy systems
- **Operations**: Easy management with existing monitoring tools
#### Fiber-based Pool (ldbc-connector)
- **Pool size**: Larger pool sizes possible
- **Wait strategy**: Non-blocking wait, efficient resource sharing
- **Use cases**: I/O-intensive tasks, environments requiring high concurrency
- **Operations**: Requires Cats Effect-compatible monitoring and management
## Comparison with HikariCP
### Similarities
- Performance-focused design
- Lock-free data structures like ConcurrentBag
- Connection management using proxy pattern
- Automatic pool size adjustment
### Differences
| Feature | HikariCP | ldbc-connector |
|---------------------|---------------------------|----------------------------|
| Concurrency Model | JVM Threads | Cats Effect Fibers |
| Blocking Handling | Blocks threads | Semantic blocking |
| Scalability | Limited by thread count | Virtually unlimited fibers |
| CircuitBreaker | Requires external library | Built-in |
| Error Handling | Exception-based | Functional |
| Resource Management | try-with-resources | Resource |
### Usage Scenarios and Selection Criteria
#### When ldbc-connector is suitable:
1. **High Concurrency Environments**
- Thousands of concurrent connection requests
- Microservice architectures
- Reactive applications
2. **I/O-Bound Workloads**
- Long-running queries
- Simultaneous access to multiple databases
- Asynchronous processing pipelines
3. **Cats Effect Ecosystem**
- Already using Cats Effect
- Adopting functional programming approach
- Type safety-focused environments
#### When thread-based pools like HikariCP are suitable:
1. **Integration with Existing Systems**
- Legacy applications
- Traditional frameworks like Spring Framework
- Compatibility with JDBC-compliant tools
2. **Operational Considerations**
- Utilizing existing monitoring and management tools
- Leveraging existing team knowledge
- Proven, stable implementation
3. **Simple Concurrency Requirements**
- Moderate concurrent connection counts
- Predictable workloads
- CPU-intensive processing
## Background Tasks
ldbc-connector runs multiple background tasks to maintain pool health:
### HouseKeeper
- Removes expired connections
- Handles idle timeout
- Maintains minimum connection count
### AdaptivePoolSizer
- Dynamically adjusts pool size based on utilization metrics
- Scales up and down based on load
- Stabilization through cooldown periods
### KeepaliveExecutor
- Periodically validates idle connections
- Maintains connections and prevents timeouts
## Configuration Examples
### Basic Usage
```scala 3
import cats.effect.IO
import ldbc.connector.*
import scala.concurrent.duration.*
// Pool configuration
val config = MySQLConfig.default
.setHost("localhost")
.setPort(3306)
.setUser("myuser")
.setPassword("mypassword")
.setDatabase("mydb")
// Pool size settings
.setMinConnections(5) // Minimum connections (default: 5)
.setMaxConnections(20) // Maximum connections (default: 10)
// Timeout settings
.setConnectionTimeout(30.seconds) // Connection acquisition timeout (default: 30s)
.setIdleTimeout(10.minutes) // Idle timeout (default: 10min)
.setMaxLifetime(30.minutes) // Maximum lifetime (default: 30min)
.setValidationTimeout(5.seconds) // Validation timeout (default: 5s)
// Validation & Health checks
.setAliveBypassWindow(500.millis) // Skip validation if used recently (default: 500ms)
.setKeepaliveTime(2.minutes) // Idle validation interval (default: 2min)
.setConnectionTestQuery("SELECT 1") // Custom test query (optional)
// Leak detection
.setLeakDetectionThreshold(2.minutes) // Connection leak detection (default: none)
// Maintenance
.setMaintenanceInterval(30.seconds) // Background cleanup interval (default: 30s)
// Adaptive sizing
.setAdaptiveSizing(true) // Dynamic pool size adjustment (default: false)
.setAdaptiveInterval(1.minute) // Adaptive sizing check interval (default: 1min)
// Create pooled datasource
val poolResource = MySQLDataSource.pooling[IO](config)
// Use the pool
poolResource.use { pool =>
pool.getConnection.use { conn =>
// Use connection
for
stmt <- conn.createStatement()
rs <- stmt.executeQuery("SELECT 1")
_ <- rs.next()
result <- rs.getInt(1)
yield result
}
}
```
### Pool with Metrics Tracking
```scala 3
import ldbc.connector.pool.*
val metricsResource = for
tracker <- Resource.eval(PoolMetricsTracker.inMemory[IO])
pool <- MySQLDataSource.pooling[IO](
config,
metricsTracker = Some(tracker)
)
yield (pool, tracker)
metricsResource.use { case (pool, tracker) =>
// Use pool and monitor metrics
for
_ <- pool.getConnection.use(conn => /* use connection */ IO.unit)
metrics <- tracker.getMetrics
_ <- IO.println(s"Total acquisitions: ${metrics.totalAcquisitions}")
_ <- IO.println(s"Average acquisition time: ${metrics.acquisitionTime}")
yield ()
}
```
### Pool with Lifecycle Hooks
```scala 3
case class SessionContext(userId: String, startTime: Long)
val beforeHook: Connection[IO] => IO[SessionContext] = conn =>
for
_ <- conn.createStatement().flatMap(_.executeUpdate("SET SESSION sql_mode = 'STRICT_ALL_TABLES'"))
startTime = System.currentTimeMillis
yield SessionContext("user123", startTime)
val afterHook: (SessionContext, Connection[IO]) => IO[Unit] = (ctx, conn) =>
IO.println(s"Connection used by ${ctx.userId} for ${System.currentTimeMillis - ctx.startTime}ms")
val poolWithHooks = MySQLDataSource.poolingWithBeforeAfter[IO, SessionContext](
config = config,
before = Some(beforeHook),
after = Some(afterHook)
)
```
### CircuitBreaker Configuration
CircuitBreaker is configured automatically internally, with the following default values in the current implementation:
- `maxFailures`: 5 (threshold for transitioning to Open state)
- `resetTimeout`: 30 seconds (time before transitioning to Half-Open state)
- `exponentialBackoffFactor`: 2.0 (backoff factor)
- `maxResetTimeout`: 5 minutes (maximum reset timeout)
## Benchmark Results
The following shows benchmark results comparing the performance of ldbc-connector and HikariCP with different thread counts. The benchmark measures concurrent performance in executing SELECT statements.
### Test Environment
- Benchmark content: Concurrent execution of SELECT statements
- Test targets: ldbc-connector vs HikariCP
- Thread counts: 1, 2, 4, 8, 16
### Result Graphs
#### Thread Count: 1
#### Thread Count: 2
#### Thread Count: 4
#### Thread Count: 8
#### Thread Count: 16
### Analysis of Benchmark Results
The following trends can be observed from these benchmark results:
1. **Low Concurrency Environment (1-2 threads)**
- Performance of both implementations shows relatively close values
- The difference in overhead is small for simple workloads
2. **Medium Concurrency Environment (4-8 threads)**
- As concurrency increases, the characteristics of each implementation begin to emerge
- The impact of the fiber-based lightweight concurrency model can be observed
3. **High Concurrency Environment (16 threads)**
- The behavioral differences between the two implementations become clear under high concurrency
- Resource efficiency and scalability characteristics become prominent
### Performance Characteristics Discussion
The benchmark results reflect the inherent characteristics of each approach:
**ldbc-connector (Fiber-based)**
- Efficient resource utilization through lightweight concurrency primitives
- CPU usage optimization through semantic blocking
- Scalability under high concurrency
**HikariCP (Thread-based)**
- Stable performance from mature implementation
- Fairness through OS-level scheduling
- High compatibility with existing JVM toolchains
### Recommendations by Use Case
Recommendations based on benchmark results for different use cases:
1. **When low to moderate concurrency is required**
- Both implementations provide sufficient performance
- Choose based on existing infrastructure and team experience
2. **When high concurrency is required**
- Consider application characteristics (I/O-centric vs CPU-centric)
- Also consider operational requirements (monitoring, debugging, troubleshooting)
3. **For dynamic workloads**
- Consider utilizing adaptive pool sizing
- CircuitBreaker behavior during failures is also an important selection criterion
It's important to note that benchmark results are measurements under specific conditions, and actual application performance is influenced by many factors including workload characteristics, database configuration, and network environment. Testing with actual workloads is recommended before production deployment.
## Summary
The ldbc-connector pooling system is an implementation that leverages Cats Effect's concurrency model. By incorporating the CircuitBreaker pattern, it enhances resilience during database failures.
Fiber-based and thread-based approaches each have their strengths and weaknesses. It's important to make appropriate choices by comprehensively evaluating application requirements, existing infrastructure, team skill sets, and operational considerations.
ldbc-connector is a choice that can maximize its characteristics particularly in environments requiring high concurrency or when adopting the Cats Effect ecosystem.
# Reference
This section describes the core abstractions and mechanical details of ldbc.
## Table of Contents
@:navigationTree {
entries = [ { target = "/en/reference", depth = 2 } ]
}
# Connection
We've completed the [Setup](/en/tutorial/Setup.md) in the previous page. In this page, we'll learn in detail how to safely connect to databases.
In ldbc, the concept of "connection" plays a central role in managing database connections. A connection provides resources to establish a connection to a database, execute queries, and safely close the connection. This connection management is performed safely using the Resource type from cats-effect.
## Types of Connectors
ldbc supports two types of connection methods.
**jdbc connector** - Connection method using standard JDBC drivers
- Can utilize existing JDBC drivers as they are
- Familiar to developers who are used to JDBC
- Easy integration with other JDBC-based tools
**ldbc connector** - Dedicated connector optimized by ldbc
- Performance optimized for MySQL protocol
- High functional extensibility
- More configuration options available
Let's take a closer look at each connection method.
## Using the JDBC Connector
The JDBC connector establishes connections using standard JDBC drivers. It's recommended when you want to leverage existing JDBC knowledge or prioritize compatibility with other JDBC-based tools.
### Adding Dependencies
First, add the necessary dependencies. When using the jdbc connector, you need to add the MySQL connector as well.
```scala
//> dep "io.github.takapi327::jdbc-connector:0.4.0"
//> dep "com.mysql":"mysql-connector-j":"8.4.0"
```
### Connection using DataSource
The most common method is using a `DataSource`. This allows advanced features such as connection pooling.
```scala
// Required imports
import cats.effect.IO
import jdbc.connector.*
// Set up MySQL data source
val ds = new com.mysql.cj.jdbc.MysqlDataSource()
ds.setServerName("127.0.0.1")
ds.setPortNumber(13306)
ds.setDatabaseName("world")
ds.setUser("ldbc")
ds.setPassword("password")
// Create a datasource
val datasource = MySQLDataSource
.fromDataSource[IO](ds, ExecutionContexts.synchronous)
// Use the connection
val program = datasource.getConnection.use { connection =>
connection.execute("SELECT 1")
}
```
### Connection using DriverManager
You can also connect using the `DriverManager`. This is convenient for simple applications or script execution.
```scala
// Required imports
import cats.effect.IO
import jdbc.connector.*
// Create a provider from DriverManager
val datasource = MySQLDataSource
.fromDriverManager[IO]
.apply(
"com.mysql.cj.jdbc.Driver",
"jdbc:mysql://127.0.0.1:13306/world",
"ldbc",
"password",
None // Log handler is optional
)
// Use the connection
val program = datasource.getConnection.use { connection =>
connection.execute("SELECT 1")
}
```
### Using Existing Connection
If you already have an established `java.sql.Connection` object, you can wrap and use it:
```scala
// Existing java.sql.Connection
val jdbcConnection: java.sql.Connection = ???
// Convert to ldbc connection
val datasource = MySQLDataSource.fromConnection[IO](jdbcConnection)
// Use the connection
val program = datasource.getConnection.use { connection =>
connection.execute("SELECT 1")
}
```
## Using the ldbc Connector
The ldbc connector is an optimized connector developed by ldbc, offering more configuration options and flexibility.
### Adding Dependencies
First, add the necessary dependency.
```scala
//> dep "io.github.takapi327::ldbc-connector:0.4.0"
```
### Connection with Basic Configuration
Let's start with the simplest configuration:
```scala
import cats.effect.IO
import ldbc.connector.*
// Create a provider with basic configuration
val datasource = MySQLDataSource
.build[IO]("localhost", 3306, "ldbc")
.setPassword("password")
.setDatabase("world")
// Use the connection
val program = datasource.getConnection.use { connection =>
connection.execute("SELECT 1")
}
```
### Connection with SSL Configuration
You can add SSL configuration to establish a secure connection:
※ Note that Trusted accepts all certificates. This is a setting for development environments.
```scala
import cats.effect.IO
import ldbc.connector.*
val datasource = MySQLDataSource
.default[IO]("localhost", 3306, "ldbc", "password", "world")
.setSSL(SSL.Trusted) // Enable SSL connection
// Use the connection
val program = datasource.getConnection.use { connection =>
connection.execute("SELECT 1")
}
```
ldbc supports all TLS modes provided by fs2. Below is a list of available SSL modes:
| Mode | Platform | Details |
|--------------------------------|-----------------|--------------------------------------------------------------------------------------------------------------------------------------------|
| `SSL.None` | `JVM/JS/Native` | `ldbc will not request SSL. This is the default.` |
| `SSL.Trusted` | `JVM/JS/Native` | `Connect via SSL and trust all certificates.` `Use this if you're running with a self-signed certificate, for instance.` |
| `SSL.System` | `JVM/JS/Native` | `Connect via SSL and use the system default SSLContext to verify certificates.` `Use this if you're running with a CA-signed certificate.` |
| `SSL.fromSSLContext(…)` | `JVM` | `Connect via SSL using an existing SSLContext.` |
| `SSL.fromKeyStoreFile(…)` | `JVM` | `Connect via SSL using a specified keystore file.` |
| `SSL.fromKeyStoreResource(…)` | `JVM` | `Connect via SSL using a specified keystore classpath resource.` |
| `SSL.fromKeyStore(…)` | `JVM` | `Connect via SSL using an existing Keystore.` |
| `SSL.fromSecureContext(...)` | `JS` | `Connect via SSL using an existing SecureContext.` |
| `SSL.fromS2nConfig(...)` | `Native` | `Connect via SSL using an existing S2nConfig.` |
### Connection with Advanced Configuration
You can leverage many more configuration options:
```scala
import scala.concurrent.duration.*
import cats.effect.IO
import fs2.io.net.SocketOption
import ldbc.connector.*
val datasource = MySQLDataSource
.build[IO]("localhost", 3306, "ldbc")
.setPassword("password")
.setDatabase("world")
.setDebug(true)
.setSSL(SSL.None)
.addSocketOption(SocketOption.receiveBufferSize(4096))
.setReadTimeout(30.seconds)
.setAllowPublicKeyRetrieval(true)
.setLogHandler(customLogHandler) // Set custom log handler
// Use the connection
val program = datasource.getConnection.use { connection =>
connection.execute("SELECT 1")
}
```
### Adding Before/After Processing
If you want to execute specific processing after establishing a connection or before disconnecting, you can use the `withBefore` and `withAfter` methods:
```scala
import cats.effect.IO
import ldbc.connector.*
val datasource = MySQLDataSource
.build[IO]("localhost", 3306, "ldbc")
.setPassword("password")
.setDatabase("world")
.withBefore { connection =>
// Processing executed after connection establishment
connection.execute("SET time_zone = '+09:00'")
}
.withAfter { (result, connection) =>
// Processing executed before disconnection
connection.execute("RESET time_zone")
}
// Use the connection
val program = datasource.getConnection.use { connection =>
connection.execute("SELECT NOW()")
}
```
## List of Configurable Parameters
The following parameters can be configured with the ldbc connector:
| Property | Details | Required |
|---------------------------|-----------------------------------------------------------------------|----|
| `host` | `Database host information` | ✅ |
| `port` | `Database port information` | ✅ |
| `user` | `Database user information` | ✅ |
| `password` | `Database password information (default: None)` | ❌ |
| `database` | `Database name information (default: None)` | ❌ |
| `debug` | `Whether to display debug information (default: false)` | ❌ |
| `ssl` | `SSL configuration (default: SSL.None)` | ❌ |
| `socketOptions` | `Specify socket options for TCP/UDP sockets (default: defaultSocketOptions)` | ❌ |
| `readTimeout` | `Specify timeout duration (default: Duration.Inf)` | ❌ |
| `allowPublicKeyRetrieval` | `Whether to retrieve public key (default: false)` | ❌ |
| `logHandler` | `Log output configuration` | ❌ |
| `before` | `Processing to execute after connection establishment` | ❌ |
| `after` | `Processing to execute before disconnecting` | ❌ |
| `tracer` | `Tracer configuration for metrics output (default: Tracer.noop)` | ❌ |
## Resource Management and Connection Usage
ldbc manages connection lifecycles using cats-effect's Resource. You can use connections in the following two ways:
### use Method
The `use` method is convenient for simple usage:
```scala
val result = datasource.getConnection.use { connection =>
// Processing using the connection
connection.execute("SELECT * FROM users")
}
```
### getConnection Method
For more detailed resource management, use the `getConnection` method:
```scala 3
val program = for
result <- provider.getConnection().use { connection =>
// Processing using the connection
connection.execute("SELECT * FROM users")
}
// Other processing...
yield result
```
Using these methods, you can perform database operations while safely managing the opening/closing of connections.
## Connection Pooling
Starting from version 0.4.0, ldbc-connector provides built-in connection pooling capabilities. Connection pooling is essential for production applications as it significantly improves performance by reusing existing database connections instead of creating new ones for each request.
@:callout(warning)
**Limitations with Scala Native 0.4.x**
The current Scala Native 0.4.x only supports single-threaded execution. Since the connection pooling functionality in ldbc-connector is designed with multi-threading in mind, when using it with Scala Native 0.4.x, connection pooling may not work correctly.
Scala Native 0.5.x is planned to support multi-threading, but until then, using connection pooling with Scala Native is not recommended. Instead, we recommend creating and using a new connection for each database operation.
@:@
### Why Use Connection Pooling?
Creating a new database connection is an expensive operation that involves:
- Network round trips for TCP handshake
- MySQL authentication protocol exchange
- SSL/TLS negotiation (if enabled)
- Server resource allocation
Connection pooling eliminates this overhead by maintaining a pool of reusable connections, resulting in:
- **Improved Performance**: Connections are reused, eliminating connection establishment overhead
- **Better Resource Management**: Limits the number of concurrent connections to the database
- **Enhanced Reliability**: Built-in health checks ensure only healthy connections are used
- **Automatic Recovery**: Failed connections are automatically replaced
### Creating a Connection Pool
To create a pooled data source with ldbc connector:
```scala
import cats.effect.IO
import ldbc.connector.*
import scala.concurrent.duration.*
// Basic pool configuration
val poolConfig = MySQLConfig.default
.setHost("localhost")
.setPort(3306)
.setUser("myuser")
.setPassword("mypassword")
.setDatabase("mydb")
// Pool-specific settings
.setMinConnections(5) // Keep at least 5 connections ready
.setMaxConnections(20) // Maximum 20 connections
.setConnectionTimeout(30.seconds) // Wait up to 30 seconds for a connection
// Create the pooled data source
MySQLDataSource.pooling[IO](poolConfig).use { pool =>
// Use connections from the pool
pool.getConnection.use { connection =>
connection.execute("SELECT 1")
}
}
```
### Pool Configuration Options
The ldbc connection pool offers extensive configuration options:
#### Pool Size Settings
- **minConnections**: Minimum number of idle connections to maintain (default: 5)
- **maxConnections**: Maximum total connections allowed (default: 20)
#### Timeout Configuration
- **connectionTimeout**: Maximum time to wait for a connection from the pool (default: 30 seconds)
- **idleTimeout**: Time before idle connections are closed (default: 10 minutes)
- **maxLifetime**: Maximum lifetime of a connection before replacement (default: 30 minutes)
- **validationTimeout**: Timeout for connection validation queries (default: 5 seconds)
#### Health and Validation
- **keepaliveTime**: Interval for validating idle connections (optional)
- **connectionTestQuery**: Custom query for connection validation (optional, defaults to using MySQL's isValid)
- **aliveBypassWindow**: Skip validation for recently used connections (default: 500ms)
#### Advanced Features
- **leakDetectionThreshold**: Warn about connections not returned to pool (optional)
- **adaptiveSizing**: Enable dynamic pool sizing based on load (default: true)
- **adaptiveInterval**: How often to check and adjust pool size (default: 30 seconds)
#### Logging Configuration
- **logPoolState**: Enable periodic logging of pool state (default: false)
- **poolStateLogInterval**: Interval for pool state logging (default: 30 seconds)
- **poolName**: Pool identification name for logging (default: "ldbc-pool")
### Example with Advanced Configuration
```scala
import cats.effect.IO
import ldbc.connector.*
import scala.concurrent.duration.*
val advancedConfig = MySQLConfig.default
.setHost("production-db.example.com")
.setPort(3306)
.setUser("app_user")
.setPassword("secure_password")
.setDatabase("production_db")
// Pool Size Management
.setMinConnections(10) // Keep 10 connections ready
.setMaxConnections(50) // Scale up to 50 connections
// Timeout Configuration
.setConnectionTimeout(30.seconds) // Max wait for connection
.setIdleTimeout(10.minutes) // Remove idle connections after
.setMaxLifetime(30.minutes) // Replace connections after
.setValidationTimeout(5.seconds) // Connection validation timeout
// Health Checks
.setKeepaliveTime(2.minutes) // Validate idle connections every 2 minutes
.setConnectionTestQuery("SELECT 1") // Custom validation query
// Advanced Features
.setLeakDetectionThreshold(2.minutes) // Warn about leaked connections
.setAdaptiveSizing(true) // Enable dynamic pool sizing
.setAdaptiveInterval(1.minute) // Check pool size every minute
// Logging Configuration
.setLogPoolState(true) // Enable pool state logging
.setPoolStateLogInterval(1.minute) // Log pool state every minute
.setPoolName("production-pool") // Set pool name
// Create and use the pool
MySQLDataSource.pooling[IO](advancedConfig).use { pool =>
// Your application code
}
```
### Connection Lifecycle Hooks with Pooling
You can add custom logic that executes when connections are acquired from or returned to the pool:
```scala
case class RequestContext(requestId: String, userId: String)
// Define hooks
val beforeHook: Connection[IO] => IO[RequestContext] = conn =>
for {
context <- IO(RequestContext("req-123", "user-456"))
_ <- conn.createStatement()
.flatMap(_.executeUpdate(s"SET @request_id = '${context.requestId}'"))
} yield context
val afterHook: (RequestContext, Connection[IO]) => IO[Unit] = (context, conn) =>
IO.println(s"Connection released for request: ${context.requestId}")
// Create pool with hooks
MySQLDataSource.poolingWithBeforeAfter[IO, RequestContext](
config = poolConfig,
before = Some(beforeHook),
after = Some(afterHook)
).use { pool =>
pool.getConnection.use { conn =>
// Connection has session variables set
conn.execute("SELECT @request_id")
}
}
```
### Monitoring Pool Health
Track your pool's performance with built-in metrics:
```scala
import ldbc.connector.pool.*
// Create pool with metrics tracking
val monitoredPool = for {
tracker <- Resource.eval(PoolMetricsTracker.inMemory[IO])
pool <- MySQLDataSource.pooling[IO](
config,
metricsTracker = Some(tracker)
)
} yield (pool, tracker)
monitoredPool.use { (pool, tracker) =>
for {
// Use the pool
_ <- pool.getConnection.use { conn =>
conn.execute("SELECT * FROM users")
}
// Check metrics
metrics <- tracker.getMetrics
_ <- IO.println(s"""
|Pool Metrics:
| Total connections created: ${metrics.totalCreated}
| Active connections: ${metrics.activeConnections}
| Idle connections: ${metrics.idleConnections}
| Waiting requests: ${metrics.waitingRequests}
| Average wait time: ${metrics.averageAcquisitionTime}ms
""".stripMargin)
} yield ()
}
```
### Enabling Pool State Logging
ldbc-connector provides detailed pool state logging influenced by HikariCP. This allows you to visualize pool behavior and diagnose performance issues:
```scala
import cats.effect.IO
import ldbc.connector.*
import scala.concurrent.duration.*
// Configuration with pool logging enabled
val loggedPoolConfig = MySQLConfig.default
.setHost("localhost")
.setPort(3306)
.setUser("myuser")
.setPassword("mypassword")
.setDatabase("mydb")
.setMinConnections(5)
.setMaxConnections(20)
// Logging configuration
.setLogPoolState(true) // Enable pool state logging
.setPoolStateLogInterval(30.seconds) // Log every 30 seconds
.setPoolName("app-pool") // Name to identify pool in logs
// Create the pool
MySQLDataSource.pooling[IO](loggedPoolConfig).use { pool =>
// Use the pool - logs like the following will be output every 30 seconds:
// [INFO] app-pool - Stats (total=5, active=2, idle=3, waiting=0)
pool.getConnection.use { conn =>
conn.execute("SELECT * FROM users")
}
}
```
When pool logging is enabled, the following information is recorded in the logs:
```
// Periodic pool state logs
[INFO] app-pool - Stats (total=10, active=3, idle=7, waiting=0)
// Connection creation failure
[ERROR] Failed to create connection to localhost:3306 (database: mydb): Connection refused
// Connection acquisition timeout (with detailed diagnostics)
[ERROR] Connection acquisition timeout after 30 seconds (host: localhost:3306, db: mydb, pool: 20/20, active: 20, idle: 0, waiting: 5)
// Connection validation failure
[WARN] Connection conn-123 failed validation, removing from pool
// Connection leak detection
[WARN] Possible connection leak detected: Connection conn-456 has been in use for longer than 2 minutes
```
### Pool Architecture Features
The ldbc connection pool includes several advanced features:
#### Circuit Breaker Protection
Prevents connection storms during database failures:
- Automatically opens after consecutive failures
- Uses exponential backoff for retry attempts
- Protects both your application and database
#### Lock-Free Design
Uses a ConcurrentBag data structure for high-performance:
- Minimal contention under high concurrency
- Thread-local optimization for connection reuse
- Excellent scalability characteristics
#### Adaptive Pool Sizing
Dynamically adjusts pool size based on load:
- Grows the pool during high demand
- Shrinks during low usage periods
- Prevents resource waste
#### Detailed Pool Logging
Comprehensive logging system influenced by HikariCP:
- **Pool State Logs**: Periodically outputs connection counts, active/idle connections, and wait queue size
- **Connection Lifecycle Logs**: Records detailed information during connection creation, validation, and removal
- **Error Diagnostics**: Outputs detailed pool state on connection acquisition timeout
- **Leak Detection Logs**: Warns about connections in use beyond configured threshold
### Best Practices
1. **Start with Conservative Settings**: Begin with default values and adjust based on monitoring
2. **Monitor Pool Metrics**: Use metrics to understand your actual usage patterns
3. **Set Appropriate Timeouts**: Balance between user experience and resource protection
4. **Enable Leak Detection in Development**: Catch connection leaks early
5. **Use Connection Test Queries Sparingly**: They add overhead; rely on MySQL's isValid when possible
6. **Enable Pool Logging in Production**: For troubleshooting and performance analysis
7. **Consider Your Workload**:
- High-throughput applications: larger pool sizes
- Bursty workloads: enable adaptive sizing
- Long-running queries: increase connection timeout
- Debugging and troubleshooting: enable pool logging to quickly identify issues
### Migration from Non-Pooled Connections
Migrating to pooled connections is straightforward:
```scala
// Before: Direct connection
val dataSource = MySQLDataSource
.build[IO]("localhost", 3306, "user")
.setPassword("password")
.setDatabase("mydb")
// After: Pooled connection
val dataSource = MySQLDataSource.pooling[IO](
MySQLConfig.default
.setHost("localhost")
.setPort(3306)
.setUser("user")
.setPassword("password")
.setDatabase("mydb")
.setMinConnections(5)
.setMaxConnections(20)
)
```
The API remains the same - you still use `getConnection` to obtain connections. The pool handles all the complexity behind the scenes.
For more detailed information about the connection pooling architecture and implementation details, see the [Connection Pooling Architecture](/en/reference/Pooling.md) reference documentation.
# Custom Data Types
Now that we've learned how to configure [Logging](/en/tutorial/Logging.md), let's look at how to support your own data types in ldbc to write more expressive code. This page explains how to perform database operations using not only basic types but also domain-specific types.
In real applications, you often want to use domain-specific types rather than just simple basic types. For example, you might want to define custom types like `Status` or `Currency` and map them to database basic types (strings or integers). ldbc provides mechanisms to do this easily.
This chapter explains how to use custom types or unsupported types in table definitions built with ldbc.
Let's add a new column to the table definition created during setup.
```sql
ALTER TABLE user ADD COLUMN status BOOLEAN NOT NULL DEFAULT TRUE;
```
## Encoder
In ldbc, values passed to statements are represented by `Encoder`. `Encoder` is a trait for representing values to be bound to statements.
By implementing `Encoder`, you can represent values passed to statements with custom types.
### Basic Usage
Let's add a `Status` to represent the user's status in the user information. Here's an example using an `enum`:
```scala 3
enum Status(val done: Boolean, val name: String):
case Active extends Status(false, "Active")
case InActive extends Status(true, "InActive")
```
In the code example below, we define an `Encoder` for a custom type. The `contramap` method is used to specify how to convert from the custom type to a basic type (in this case, `Boolean`):
```scala 3
given Encoder[Status] = Encoder[Boolean].contramap(_.done)
```
This allows you to bind custom types to statements. Here's a specific example:
```scala 3
val program1: DBIO[Int] =
sql"INSERT INTO user (name, email, status) VALUES (${ "user 1" }, ${ "user@example.com" }, ${ Status.Active })".update
```
### Composite Type Encoder
Encoders can also create new types by composing multiple types. You can compose types using the `*:` operator:
```scala 3
val encoder: Encoder[(Int, String)] = Encoder[Int] *: Encoder[String]
```
Composed types can also be converted to arbitrary classes. In the example below, the `to` method is used to convert from a tuple to a case class:
```scala 3
case class Status(code: Int, name: String)
given Encoder[Status] = (Encoder[Int] *: Encoder[String]).to[Status]
```
In this case, the fields of the `Status` class must correspond in order. That is, `code` corresponds to the `Int` encoder, and `name` corresponds to the `String` encoder.
## Decoder
In addition to parameters, ldbc also provides `Decoder` for retrieving custom types from execution results.
By implementing `Decoder`, you can retrieve custom types from statement execution results.
### Basic Usage
The code example below shows how to use `Decoder` to convert a `Boolean` value to a `Status` type:
```scala 3
given Decoder[Status] = Decoder[Boolean].map {
case true => Status.InActive
case false => Status.Active
}
```
This allows you to directly retrieve `Status` type values from query results:
```scala 3
val program2: DBIO[(String, String, Status)] =
sql"SELECT name, email, status FROM user WHERE id = 1".query[(String, String, Status)].unsafe
```
### Composite Type Decoder
Decoders can also create new types by composing multiple types. Use the `*:` operator to compose types:
```scala 3
val decoder: Decoder[(Int, String)] = Decoder[Int] *: Decoder[String]
```
Composed types can also be converted to arbitrary classes:
```scala 3
case class Status(code: Int, name: String)
given Decoder[Status] = (Decoder[Int] *: Decoder[String]).to[Status]
```
This definition allows two columns (integer and string) retrieved from the database to be automatically converted to instances of the `Status` class.
## Codec
By using `Codec`, which combines `Encoder` and `Decoder`, you can use custom types for both values passed to statements and statement execution results. This reduces code duplication and achieves consistent type conversions.
### Basic Usage
The code example below shows how to use `Codec` to integrate the `Encoder` and `Decoder` from earlier:
```scala 3
given Codec[Status] = Codec[Boolean].imap(_.done)(Status(_))
```
### Composite Type Codec
Codecs can also create new types by composing multiple types:
```scala 3
val codec: Codec[(Int, String)] = Codec[Int] *: Codec[String]
```
Composed types can also be converted to arbitrary classes:
```scala 3
case class Status(code: Int, name: String)
given Codec[Status] = (Codec[Int] *: Codec[String]).to[Status]
```
### Retrieving Encoder and Decoder Individually
Since Codec is a combination of `Encoder` and `Decoder`, you can also get the conversion process for each type individually:
```scala 3
val encoder: Encoder[Status] = Codec[Status].asEncoder
val decoder: Decoder[Status] = Codec[Status].asDecoder
```
## Converting Complex Object Structures
Since `Codec`, `Encoder`, and `Decoder` can each be composed, complex object structures can be created by combining multiple types.
This allows users to convert retrieved records into nested hierarchical data:
```scala 3
case class City(id: Int, name: String, countryCode: String)
case class Country(code: String, name: String)
case class CityWithCountry(city: City, country: Country)
// Example of retrieving joined city and country information
val program3: DBIO[List[CityWithCountry]] =
sql"""
SELECT c.id, c.name, c.country_code, co.code, co.name
FROM city c
JOIN country co ON c.country_code = co.code
""".query[CityWithCountry].list
```
In the example above, `CityWithCountry` objects are automatically constructed from the query results. ldbc resolves types at compile time and generates appropriate encoders and decoders.
## Handling Large Objects
Since Codec, along with `Encoder` and `Decoder`, is implicitly resolved, users do not usually need to explicitly specify these types.
However, if there are many properties in a model, the implicit resolution process might become too complex and fail:
```shell
[error] |Implicit search problem too large.
[error] |an implicit search was terminated with failure after trying 100000 expressions.
[error] |The root candidate for the search was:
[error] |
[error] | given instance given_Decoder_P in object Decoder for ldbc.dsl.codec.Decoder[City]}
```
In such cases, one of the following solutions is effective:
1. Increase the search limit in compile options:
```scala
scalacOptions += "-Ximplicit-search-limit:100000"
```
However, this method may increase compilation time.
2. Manually construct explicit type conversions:
```scala 3
// Explicitly build Decoder
given Decoder[City] = (
Decoder[Int] *:
Decoder[String] *:
Decoder[String]
).to[City]
// Explicitly build Encoder
given Encoder[City] = (
Encoder[Int] *:
Encoder[String] *:
Encoder[String]
).to[City]
```
3. Use `Codec` to define both at once:
```scala 3
given Codec[City] = (
Codec[Int] *:
Codec[String] *:
Codec[String]
).to[City]
```
## Practical Application Examples
Below is an example of code using domain-specific types as a more practical example:
```scala 3
// Value representing an email address
opaque type Email = String
object Email:
def apply(value: String): Email = value
def unapply(email: Email): String = email
// User ID
opaque type UserId = Long
object UserId:
def apply(value: Long): UserId = value
def unapply(userId: UserId): Long = userId
// User class
case class User(id: UserId, name: String, email: Email, status: Status)
object User:
// Codec for User ID
given Codec[UserId] = Codec[Long].imap(UserId.apply)(_.value)
// Codec for Email
given Codec[Email] = Codec[String].imap(Email.apply)(Email.unapply)
// Now you can retrieve and update users in a type-safe manner
val getUser: DBIO[Option[User]] =
sql"SELECT id, name, email, status FROM user WHERE id = ${UserId(1)}".query[User].option
val updateEmail: DBIO[Int] =
sql"UPDATE user SET email = ${Email("new@example.com")} WHERE id = ${UserId(1)}".update
```
## Next Steps
Now you know how to use custom data types with ldbc. By defining your own types, you can write more expressive and type-safe code. You can also accurately represent domain concepts in code, reducing the occurrence of bugs.
Next, let's move on to [Query Builder](/en/tutorial/Query-Builder.md) to learn how to build type-safe queries without writing SQL directly.
# Database Operations
In [Updating Data](/en/tutorial/Updating-Data.md), you learned how to insert, update, and delete data. This page explains more advanced database operations such as transaction management and commit timing.
Transaction management is important for safely performing database operations. ldbc provides abstractions for transaction management that adhere to the principles of functional programming.
This section explains database operations.
Before connecting to a database, you need to configure settings such as commit timing and read/write modes.
## Read-Only
To start a read-only transaction, use the `readOnly` method.
Using the `readOnly` method allows you to set query processing to read-only mode. You can use the `readOnly` method with `insert/update/delete` statements as well, but they will result in errors at execution time since they perform write operations.
```scala
val read = sql"SELECT 1".query[Int].to[Option].readOnly(connection)
```
## Writing
To write to the database, use the `commit` method.
Using the `commit` method configures your query processing to commit after each query execution.
```scala
val write = sql"INSERT INTO `table`(`c1`, `c2`) VALUES ('column 1', 'column 2')".update.commit(connection)
```
## Transactions
To start a transaction, use the `transaction` method.
Using the `transaction` method allows you to group multiple database connection operations into a single transaction.
With ldbc, you'll organize database connection processes in the form of `DBIO[A]`. DBIO is a monad, so you can use for-comprehension to combine smaller programs into a larger program.
```scala 3
val program: DBIO[(List[Int], Option[Int], Int)] =
for
result1 <- sql"SELECT 1".query[Int].to[List]
result2 <- sql"SELECT 2".query[Int].to[Option]
result3 <- sql"SELECT 3".query[Int].unsafe
yield (result1, result2, result3)
```
Once you have combined operations into a single `DBIO` program, you can process them in a single transaction using the `transaction` method.
```scala
val transaction = program.transaction(connection)
```
## Next Steps
You've now learned all the basic uses of ldbc. You've acquired the knowledge necessary for everyday database operations, including database connections, query execution, reading and writing data, and transaction management.
From here, we'll move on to more advanced topics. Let's start with [Error Handling](/en/tutorial/Error-Handling.md) and learn how to properly handle exceptions that may occur in database operations.
# Error Handling
In [Database Operations](/en/tutorial/Database-Operations.md), we learned about basic transaction management. However, in real applications, errors can occur for various reasons, such as database connection issues, SQL syntax errors, unique constraint violations, and more. This page explains how to properly handle errors in ldbc.
## Basics of Error Handling
ldbc follows functional programming principles and integrates with Cats Effect's error handling capabilities. The `DBIO` monad has a `MonadThrow` instance, providing a standard functional interface for raising and handling exceptions.
There are three main error handling methods you'll primarily use:
- `raiseError`: Raise an exception - converts a `Throwable` to a `DBIO[A]`
- `handleErrorWith`: Handle an exception - transforms a `DBIO[A]` to another `DBIO[A]` with an error handling function
- `attempt`: Capture an exception - transforms a `DBIO[A]` to a `DBIO[Either[Throwable, A]]`
## Types of Exceptions in Database Operations
Here are the main types of exceptions you might encounter when using ldbc:
1. **Connection Errors**: Occur when unable to connect to the database server (e.g., `ConnectException`)
2. **SQL Exceptions**: Errors during SQL execution (e.g., `SQLException`)
- Syntax errors
- Unique key constraint violations
- Foreign key constraint violations
- Timeouts
3. **Type Conversion Errors**: When database results can't be converted to the expected type (e.g., `ldbc.dsl.exception.DecodeFailureException`)
## Basic Usage of Error Handling
### Raising Exceptions (raiseError)
To explicitly raise an exception under specific conditions, use `raiseError`:
```scala
import cats.syntax.all.*
import ldbc.dsl.*
// Example of raising an error under specific conditions
def validateUserId(id: Int): DBIO[Int] =
if id <= 0 then
DBIO.raiseError[Int](new IllegalArgumentException("ID must be a positive value"))
else
DBIO.pure(id)
// Usage example
val program: DBIO[String] = for
id <- validateUserId(0)
result <- sql"SELECT name FROM users WHERE id = $id".query[String].unsafe
yield result
// In this example, an error occurs because id is 0, and subsequent SQL isn't executed
```
### Handling Errors (handleErrorWith)
To handle errors that occur, use the `handleErrorWith` method:
```scala
import ldbc.dsl.*
import java.sql.SQLException
// Error handling example
val findUserById: DBIO[String] = for
userId <- DBIO.pure(123)
result <- sql"SELECT name FROM users WHERE id = $userId".query[String].unsafe.handleErrorWith {
case e: SQLException if e.getMessage.contains("table 'users' doesn't exist") =>
// Fallback when table doesn't exist
DBIO.pure("User table has not been created yet")
case e: SQLException =>
// Handling other SQL errors
DBIO.pure(s"Database error: ${e.getMessage}")
case e: Throwable =>
// Handling other errors
DBIO.pure(s"Unexpected error: ${e.getMessage}")
}
yield result
```
### Capturing Exceptions (attempt)
To capture errors as an `Either` type, use the `attempt` method:
```scala
import cats.syntax.all.*
import ldbc.dsl.*
// Example of capturing exceptions using attempt
val safeOperation: DBIO[String] = {
val riskyOperation = sql"SELECT * FROM potentially_missing_table".query[String].unsafe
riskyOperation.attempt.flatMap {
case Right(result) =>
DBIO.pure(s"Operation successful: $result")
case Left(error) =>
DBIO.pure(s"An error occurred: ${error.getMessage}")
}
}
```
## Practical Error Handling Patterns
Here are some error handling patterns that are useful in real applications.
### Implementing Retry Functionality
An example of automatically retrying on temporary database connection errors:
```scala
import scala.concurrent.duration.*
import cats.effect.{IO, Sync}
import cats.effect.syntax.all.*
import cats.syntax.all.*
import ldbc.dsl.*
// Retry implementation example - combining IO and DBIO
def retryWithBackoff[F[_]: Sync, A](
dbioOperation: DBIO[A],
connection: Connection[F],
maxRetries: Int = 3,
initialDelay: FiniteDuration = 100.millis,
maxDelay: FiniteDuration = 2.seconds
): F[A] =
def retryLoop(remainingRetries: Int, delay: FiniteDuration): F[A] =
// Convert DBIO to F type (e.g., IO) and execute
dbioOperation.run(connection).handleErrorWith { error =>
if remainingRetries > 0 && isTransientError(error) then
// For temporary errors, delay and retry
val nextDelay = (delay * 2).min(maxDelay)
Sync[F].sleep(delay) >> retryLoop(remainingRetries - 1, nextDelay)
else
// If max retries exceeded or permanent error, rethrow
Sync[F].raiseError[A](error)
}
retryLoop(maxRetries, initialDelay)
// Example with concrete IO type
def retryDatabaseOperation[A](
operation: DBIO[A],
connection: Connection[IO],
maxRetries: Int = 3
): IO[A] =
retryWithBackoff(operation, connection, maxRetries)
// Helper method to determine if an error is transient
def isTransientError(error: Throwable): Boolean =
error match
case e: SQLException if e.getSQLState == "40001" => true // For deadlocks
case e: SQLException if e.getSQLState == "08006" => true // For connection loss
case e: Exception if e.getMessage.contains("connection reset") => true
case _ => false
// Usage example
val query = sql"SELECT * FROM users".query[User].unsafe
val result = retryDatabaseOperation(query, myConnection)
```
### Error Handling with Custom Error Types and EitherT
An example of defining application-specific error types for more detailed error handling:
```scala
import cats.data.EitherT
import cats.syntax.all.*
import ldbc.dsl.*
// Application-specific error types
sealed trait AppDatabaseError
case class UserNotFoundError(id: Int) extends AppDatabaseError
case class DuplicateUserError(email: String) extends AppDatabaseError
case class DatabaseConnectionError(cause: Throwable) extends AppDatabaseError
case class UnexpectedDatabaseError(message: String, cause: Throwable) extends AppDatabaseError
// User model
case class User(id: Int, name: String, email: String)
// Example of error handling with EitherT
def findUserById(id: Int): EitherT[DBIO, AppDatabaseError, User] =
val query = sql"SELECT id, name, email FROM users WHERE id = $id".query[User].to[Option]
EitherT(
query.attempt.map {
case Right(user) => user.toRight(UserNotFoundError(id))
case Left(e: SQLException) if e.getMessage.contains("Connection refused") =>
Left(DatabaseConnectionError(e))
case Left(e) =>
Left(UnexpectedDatabaseError(e.getMessage, e))
}
)
// Usage example
val program = for
user <- findUserById(123)
// Other operations...
yield user
// Processing the result
val result: DBIO[Either[AppDatabaseError, User]] = program.value
// Final processing
val finalResult: DBIO[String] = result.flatMap {
case Right(user) => DBIO.pure(s"User found: ${user.name}")
case Left(UserNotFoundError(id)) => DBIO.pure(s"User with ID ${id} does not exist")
case Left(DatabaseConnectionError(_)) => DBIO.pure("A database connection error occurred")
case Left(error) => DBIO.pure(s"Error: $error")
}
```
### Combining Transactions with Error Handling
Example of error handling within a transaction:
```scala
import cats.effect.IO
import cats.syntax.all.*
import ldbc.dsl.*
import java.sql.Connection
// Error handling within transactions
def transferMoney(fromAccount: Int, toAccount: Int, amount: BigDecimal): DBIO[String] =
val operation = for
// Check balance of source account
balance <- sql"SELECT balance FROM accounts WHERE id = $fromAccount".query[BigDecimal].unsafe
_ <- if balance < amount then
DBIO.raiseError[Unit](new IllegalStateException(s"Insufficient account balance: $balance < $amount"))
else
DBIO.pure(())
// Withdraw from source account
_ <- sql"UPDATE accounts SET balance = balance - $amount WHERE id = $fromAccount".update.unsafe
// Deposit to target account
_ <- sql"UPDATE accounts SET balance = balance + $amount WHERE id = $toAccount".update.unsafe
// Create transaction record
_ <- sql"""INSERT INTO transactions (from_account, to_account, amount, timestamp)
VALUES ($fromAccount, $toAccount, $amount, NOW())""".update.unsafe
yield "Money transfer completed"
// Wrap as transaction (automatically rolled back on error)
operation.handleErrorWith { error =>
DBIO.pure(s"Transfer error: ${error.getMessage}")
}
// Usage example
val fromAccount: Int = ???
val toAccount: Int = ???
val amount: BigDecimal = ???
// Create Connector
val connector = Connector.fromDataSource(datasource)
transferMoney(fromAccount, toAccount, amount).transaction(connector)
```
## Summary
Error handling is an important aspect of robust database applications. In ldbc, you can handle errors explicitly based on functional programming principles. The main points are:
- Use `raiseError` to raise exceptions
- Use `handleErrorWith` to handle exceptions
- Use `attempt` to capture exceptions as `Either`
- Define appropriate error types for application-specific error handling
- Combine transactions with error handling to maintain data integrity
By utilizing these techniques, you can implement database operations that are resilient to unexpected errors and easy to maintain.
## Next Steps
Now that you understand error handling, move on to [Logging](/en/tutorial/Logging.md) to learn how to log query executions and errors. Logging is important for debugging and monitoring.
# logging
You learned how to handle errors in [Error Handling](/en/tutorial/Error-Handling.md). This page describes how to set up logging and log query execution and errors.
Logging is an important part of understanding application behavior and diagnosing problems. ldbc provides a customizable logging system that can be integrated with any logging library.
ldbc can export execution and error logs of database connections in any format using any logging library.
By default, a logger using Cats Effect's Console is provided, which can be used during development.
To customize logging using an arbitrary logging library, use `ldbc.sql.logging.LogHandler`.
The following is the standard implementation of logging. ldbc generates the following three types of events on database connection.
```scala 3
def console[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)
```
The created LogHandler can be passed as an argument to `setLogHandler` when creating a Provider to use any log output method.
```scala 3
import ldbc.connector.*
val datasource =
MySQLDataSource
.build[IO]("127.0.0.1", 3306, "ldbc")
.setPassword("password")
.setDatabase("ldbc")
.setSSL(SSL.Trusted)
.setLogHandler(console[IO])
```
## Next Steps
Now you know how to set up logging in ldbc. Setting up proper logging will make it easier to monitor application behavior and diagnose problems.
Next, go to [Custom Data Types](/en/tutorial/Custom-Data-Type.md) to learn how to use your own data types in ldbc.
# Parameterized Queries
In the [Simple Program](/en/tutorial/Simple-Program.md) tutorial, we learned how to execute basic queries. In real applications, you'll often execute queries based on user input or variable values. On this page, you'll learn how to safely handle parameters.
In ldbc, we strongly recommend using parameterized queries to prevent SQL injection attacks. Parameterized queries allow you to separate SQL code from data, enabling safer database access.
## Parameter Basics
In ldbc, there are two main ways to embed parameters in SQL statements:
1. **Dynamic parameters** - Used as regular parameters, processed by `PreparedStatement` to prevent SQL injection attacks
2. **Static parameters** - Directly embedded as part of the SQL statement (e.g., table names, column names, etc.)
## Adding Dynamic Parameters
First, let's create a query without parameters.
```scala
sql"SELECT name, email FROM user".query[(String, String)].to[List]
```
Next, let's incorporate the query into a method and add a parameter to select only data matching the user-specified `id`. We insert the `id` argument into the SQL statement as `$id`, just like string interpolation.
```scala
val id = 1
sql"SELECT name, email FROM user WHERE id = $id".query[(String, String)].to[List]
```
When we execute the query using a connection, it works without issues.
```scala
// Create Connector
val connector = Connector.fromDataSource(datasource)
sql"SELECT name, email FROM user WHERE id = $id"
.query[(String, String)]
.to[List]
.readOnly(connector)
```
What's happening here? It looks like we're just dropping string literals into an SQL string, but we're actually building a `PreparedStatement`, and the `id` value is ultimately set by a call to `setInt`. This protects our application from SQL injection attacks.
You can use parameters of various types:
```scala
val id: Int = 1
val name: String = "Alice"
val active: Boolean = true
val createdAt: LocalDateTime = LocalDateTime.now()
sql"INSERT INTO user (id, name, active, created_at) VALUES ($id, $name, $active, $createdAt)"
```
In ldbc, appropriate encoders are provided for each type, safely converting Scala/Java values to SQL values.
## Multiple Parameters
Multiple parameters can be used in the same way.
```scala
val id = 1
val email = "alice@example.com"
// Create Connector
val connector = Connector.fromDataSource(datasource)
sql"SELECT name, email FROM user WHERE id = $id AND email > $email"
.query[(String, String)]
.to[List]
.readOnly(connector)
```
## Combining Queries
When building large queries, you can combine multiple SQL fragments.
```scala
val baseQuery = sql"SELECT name, email FROM user"
val whereClause = sql"WHERE id > $id"
val orderClause = sql"ORDER BY name ASC"
val query = baseQuery ++ whereClause ++ orderClause
```
## SQL Helper Functions
ldbc provides many helper functions for easily constructing complex SQL clauses.
### Handling IN Clauses
A common challenge in SQL is using a series of values in an IN clause. In ldbc, this can be easily implemented using the `in` function.
```scala
val ids = NonEmptyList.of(1, 2, 3)
// Create Connector
val connector = Connector.fromDataSource(datasource)
(sql"SELECT name, email FROM user WHERE " ++ in("id", ids))
.query[(String, String)]
.to[List]
.readOnly(connector)
```
This is equivalent to the following SQL:
```sql
SELECT name, email FROM user WHERE (id IN (?, ?, ?))
```
Note that `ids` must be a `NonEmptyList` because an IN clause cannot be empty.
### Other Helper Functions
ldbc provides many other convenient functions:
#### Generating VALUES Clauses
```scala
val users = NonEmptyList.of(
(1, "Alice", "alice@example.com"),
(2, "Bob", "bob@example.com")
)
(sql"INSERT INTO user (id, name, email) " ++ values(users))
```
#### WHERE Clause Conditions
You can easily construct AND and OR conditions:
```scala
val activeFilter = sql"active = true"
val nameFilter = sql"name LIKE ${"A%"}"
val emailFilter = sql"email IS NOT NULL"
// WHERE (active = true) AND (name LIKE 'A%') AND (email IS NOT NULL)
val query1 = sql"SELECT * FROM user " ++ whereAnd(activeFilter, nameFilter, emailFilter)
// WHERE (active = true) OR (name LIKE 'A%')
val query2 = sql"SELECT * FROM user " ++ whereOr(activeFilter, nameFilter)
```
#### Generating SET Clauses
You can easily generate SET clauses for UPDATE statements:
```scala
val name = "New Name"
val email = "new@example.com"
val updateValues = set(
sql"name = $name",
sql"email = $email",
sql"updated_at = NOW()"
)
sql"UPDATE user " ++ updateValues ++ sql" WHERE id = 1"
```
#### Generating ORDER BY Clauses
```scala
val query = sql"SELECT * FROM user " ++ orderBy(sql"name ASC", sql"created_at DESC")
```
### Optional Conditions
When conditions are optional (may not exist), you can use functions with the `Opt` suffix:
```scala
val nameOpt: Option[String] = Some("Alice")
val emailOpt: Option[String] = None
val nameFilter = nameOpt.map(name => sql"name = $name")
val emailFilter = emailOpt.map(email => sql"email = $email")
// Since nameFilter is Some(...) and emailFilter is None, the WHERE clause will only contain "name = ?"
val query = sql"SELECT * FROM user " ++ whereAndOpt(nameFilter, emailFilter)
```
## Static Parameters
Sometimes you may want to parameterize structural parts of the SQL statement, such as column names or table names. In such cases, you can use "static parameters" which directly embed values into the SQL statement.
While dynamic parameters (regular `$value`) are processed by `PreparedStatement` and replaced with `?` in the query string, static parameters are directly embedded as strings.
To use static parameters, use the `sc` function:
```scala
val column = "name"
val table = "user"
// Treating as a dynamic parameter would result in "SELECT ? FROM user"
// sql"SELECT $column FROM user".query[String].to[List]
// Treating as a static parameter results in "SELECT name FROM user"
sql"SELECT ${sc(column)} FROM ${sc(table)}".query[String].to[List]
```
In this example, the generated SQL is `SELECT name FROM user`.
> **Warning**: `sc(...)` does not escape the passed string, so passing unvalidated data such as user input directly poses a risk of SQL injection attacks. Use static parameters only from safe parts of your application (constants, configurations, etc.).
Common use cases for static parameters:
```scala
// Dynamic sort order
val sortColumn = "created_at"
val sortDirection = "DESC"
sql"SELECT * FROM user ORDER BY ${sc(sortColumn)} ${sc(sortDirection)}"
// Dynamic table selection
val schema = "public"
val table = "user"
sql"SELECT * FROM ${sc(schema)}.${sc(table)}"
```
## Next Steps
Now you understand how to use parameterized queries. With the ability to handle parameters, you can build more complex and practical database queries.
Next, proceed to [Selecting Data](/en/tutorial/Selecting-Data.md) to learn how to retrieve data in various formats.
# Query Builder
We've learned how to add custom types to ldbc in the [Custom Data Type](/en/tutorial/Custom-Data-Type.md) section. In this page, we'll explain how to build queries in a type-safe way without writing SQL directly.
The Query Builder is a feature that allows you to construct database queries in a more type-safe manner than SQL string interpolation. This helps detect more errors at compile time and prevents mistakes related to query structure.
In this chapter, we'll explain methods for building type-safe queries.
## Prerequisites
You need to set up the following dependency in your project:
```scala
//> using dep "io.github.takapi327::ldbc-query-builder:0.4.0"
```
## Basic Usage
In ldbc, we use case classes to represent tables and build queries. Let's start with a simple table definition:
```scala 3
import ldbc.dsl.codec.Codec
import ldbc.query.builder.*
// Table definition
case class User(id: Int, name: String, email: String) derives Table
object User:
gicen Codec[User] = Codec.derived[User]
```
The `Table` trait is automatically derived using the `derives` keyword. This allows the class properties to be treated as database columns.
To execute queries against the defined table, use `TableQuery`:
```scala
// Build query against the table
val query = TableQuery[User]
.select(user => user.id *: user.name *: user.email)
.where(_.email === "alice@example.com")
```
In the code above:
- `TableQuery[User]` - Creates a query for the `User` table
- `select(...)` - Specifies which columns to retrieve
- `*:` - An operator for combining multiple columns
- `where(...)` - Specifies the query condition
## Customizing Table Definitions
### Changing Column Names
If a property name differs from the database column name, you can specify it using the `@Column` annotation:
```scala 3
case class User(
id: Int,
@Column("full_name") name: String, // name property maps to full_name column
email: String
) derives Table
```
### Changing Table Names
By default, the class name is used as the table name, but you can explicitly specify a table name using `Table.derived`:
```scala 3
case class User(id: Int, name: String, email: String)
object User:
given Table[User] = Table.derived("users") // Specify "users" as the table name
```
## Basic Query Operations
### SELECT
#### Basic SELECT
To retrieve specific columns only:
```scala
val select = TableQuery[User].select(_.id)
// SELECT id FROM user
```
To retrieve multiple columns, use the `*:` operator:
```scala
val select = TableQuery[User].select(user => user.id *: user.name)
// SELECT id, name FROM user
```
To retrieve all columns:
```scala
val select = TableQuery[User].selectAll
// SELECT id, name, email FROM user
```
#### Aggregate Functions
How to use aggregate functions (e.g., count):
```scala
val select = TableQuery[User].select(_.id.count)
// SELECT COUNT(id) FROM user
```
### WHERE Conditions
To add conditions to a query, use the `where` method:
```scala
val where = TableQuery[User].selectAll.where(_.email === "alice@example.com")
// SELECT id, name, email FROM user WHERE email = ?
```
List of comparison operators available in the `where` method:
| Operator | SQL Statement | Description |
|----------------------------------------|---------------------------------------|--------------------------------------------------|
| `===` | `column = ?` | Equal to |
| `>=` | `column >= ?` | Greater than or equal to |
| `>` | `column > ?` | Greater than |
| `<=` | `column <= ?` | Less than or equal to |
| `<` | `column < ?` | Less than |
| `<>` | `column <> ?` | Not equal to |
| `!==` | `column != ?` | Not equal to (alternative syntax) |
| `IS ("TRUE"/"FALSE"/"UNKNOWN"/"NULL")` | `column IS {TRUE/FALSE/UNKNOWN/NULL}` | Check if equals specified value |
| `<=>` | `column <=> ?` | NULL-safe equal operator (can compare with NULL) |
| `IN (value, value, ...)` | `column IN (?, ?, ...)` | Check if matches any of the specified values |
| `BETWEEN (start, end)` | `column BETWEEN ? AND ?` | Check if within specified range |
| `LIKE (value)` | `column LIKE ?` | Pattern matching |
| `LIKE_ESCAPE (like, escape)` | `column LIKE ? ESCAPE ?` | Pattern matching with escape character |
| `REGEXP (value)` | `column REGEXP ?` | Regular expression |
| `<<` (value) | `column << ?` | Bit left shift |
| `>>` (value) | `column >> ?` | Bit right shift |
| `DIV (cond, result)` | `column DIV ? = ?` | Integer division |
| `MOD (cond, result)` | `column MOD ? = ?` | Modulo |
| `^ (value)` | `column ^ ?` | Bit XOR |
| `~ (value)` | `~column = ?` | Bit NOT |
Example of combining conditions:
```scala
val complexWhere = TableQuery[User]
.selectAll
.where(user => user.email === "alice@example.com" && user.id > 5)
// SELECT id, name, email FROM user WHERE email = ? AND id > ?
```
### GROUP BY and HAVING
To group data, use the `groupBy` method:
```scala
val select = TableQuery[User]
.select(user => user.id.count *: user.name)
.groupBy(_.name)
// SELECT COUNT(id), name FROM user GROUP BY name
```
You can use `having` to set conditions on grouped data:
```scala
val select = TableQuery[User]
.select(user => user.id.count *: user.name)
.groupBy(_.name)
.having(_._1 > 1)
// SELECT COUNT(id), name FROM user GROUP BY name HAVING COUNT(id) > ?
```
### ORDER BY
To specify result order, use the `orderBy` method:
```scala
val select = TableQuery[User]
.select(user => user.id *: user.name)
.orderBy(_.id)
// SELECT id, name FROM user ORDER BY id
```
To specify ascending or descending order:
```scala
// Ascending order
val selectAsc = TableQuery[User]
.select(user => user.id *: user.name)
.orderBy(_.id.asc)
// SELECT id, name FROM user ORDER BY id ASC
// Descending order
val selectDesc = TableQuery[User]
.select(user => user.id *: user.name)
.orderBy(_.id.desc)
// SELECT id, name FROM user ORDER BY id DESC
// Sorting by multiple columns
val selectMultiple = TableQuery[User]
.select(user => user.id *: user.name)
.orderBy(user => user.name.asc *: user.id.desc)
// SELECT id, name FROM user ORDER BY name ASC, id DESC
```
### LIMIT and OFFSET
Use the `limit` method to limit the number of rows and the `offset` method to specify the number of rows to skip:
```scala
val select = TableQuery[User]
.select(user => user.id *: user.name)
.limit(10) // Get a maximum of 10 rows
.offset(5) // Skip the first 5 rows
// SELECT id, name FROM user LIMIT ? OFFSET ?
```
## Table Joins
There are several ways to join multiple tables. First, let's show example table definitions:
```scala 3
// Table definitions
case class User(id: Int, name: String, email: String) derives Table
case class Product(id: Int, name: String, price: BigDecimal) derives Table
case class Order(
id: Int,
userId: Int,
productId: Int,
orderDate: LocalDateTime,
quantity: Int
) derives Table
// Generate TableQuery
val userTable = TableQuery[User]
val productTable = TableQuery[Product]
val orderTable = TableQuery[Order]
```
### Inner Join
Retrieves only the matching rows from both tables:
```scala
val join = userTable
.join(orderTable)
.on((user, order) => user.id === order.userId)
.select((user, order) => user.name *: order.quantity)
// SELECT user.`name`, order.`quantity` FROM user JOIN order ON user.id = order.user_id
```
### Left Join
Retrieves all rows from the left table and matching rows from the right table. If no match, the right columns will be NULL:
```scala
val leftJoin = userTable
.leftJoin(orderTable)
.on((user, order) => user.id === order.userId)
.select((user, order) => user.name *: order.quantity)
// SELECT user.`name`, order.`quantity` FROM user LEFT JOIN order ON user.id = order.user_id
// The return type is (String, Option[Int])
// since data from the order table might be NULL
```
### Right Join
Retrieves all rows from the right table and matching rows from the left table. If no match, the left columns will be NULL:
```scala
val rightJoin = orderTable
.rightJoin(userTable)
.on((order, user) => order.userId === user.id)
.select((order, user) => order.quantity *: user.name)
// SELECT order.`quantity`, user.`name` FROM order RIGHT JOIN user ON order.user_id = user.id
// The return type is (Option[Int], String)
// since data from the order table might be NULL
```
### Joining Multiple Tables
It's possible to join three or more tables:
```scala
val multiJoin = productTable
.join(orderTable).on((product, order) => product.id === order.productId)
.rightJoin(userTable).on((_, order, user) => order.userId === user.id)
.select((product, order, user) => product.name *: order.quantity *: user.name)
// SELECT
// product.`name`,
// order.`quantity`,
// user.`name`
// FROM product
// JOIN order ON product.id = order.product_id
// RIGHT JOIN user ON order.user_id = user.id
// The return type is (Option[String], Option[Int], String)
// because rightJoin is used, data from product and order tables might be NULL
```
## INSERT Statement
There are several methods to insert new records:
### Using the `insert` Method
Directly specify a tuple of values:
```scala
val insert = TableQuery[User].insert((1, "Alice", "alice@example.com"))
// INSERT INTO user (`id`, `name`, `email`) VALUES(?, ?, ?)
// Insert multiple records at once
val multiInsert = TableQuery[User].insert(
(1, "Alice", "alice@example.com"),
(2, "Bob", "bob@example.com")
)
// INSERT INTO user (`id`, `name`, `email`) VALUES(?, ?, ?), (?, ?, ?)
```
### Using the `insertInto` Method
Useful when you want to insert values only into specific columns (e.g., when using AUTO INCREMENT):
```scala
val insert = TableQuery[User]
.insertInto(user => user.name *: user.email)
.values(("Alice", "alice@example.com"))
// INSERT INTO user (`name`, `email`) VALUES(?, ?)
// Insert multiple records at once
val multiInsert = TableQuery[User]
.insertInto(user => user.name *: user.email)
.values(List(
("Alice", "alice@example.com"),
("Bob", "bob@example.com")
))
// INSERT INTO user (`name`, `email`) VALUES(?, ?), (?, ?)
```
### Using Model Objects (with `+=` and `++=` Operators)
```scala
// Insert one record
val insert = TableQuery[User] += User(1, "Alice", "alice@example.com")
// INSERT INTO user (`id`, `name`, `email`) VALUES(?, ?, ?)
// Insert multiple records
val multiInsert = TableQuery[User] ++= List(
User(1, "Alice", "alice@example.com"),
User(2, "Bob", "bob@example.com")
)
// INSERT INTO user (`id`, `name`, `email`) VALUES(?, ?, ?), (?, ?, ?)
```
### INSERT Using SELECT Results
You can also insert results from a SELECT query into another table:
```scala
val insertSelect = TableQuery[User]
.insertInto(user => user.id *: user.name *: user.email)
.select(
TableQuery[User]
.select(user => user.id *: user.name *: user.email)
.where(_.id > 10)
)
// INSERT INTO user (`id`, `name`, `email`)
// SELECT id, name, email FROM user WHERE id > ?
```
### ON DUPLICATE KEY UPDATE
Using the ON DUPLICATE KEY UPDATE clause to update existing rows when a unique or primary key is duplicated:
```scala
val insert = TableQuery[User]
.insert((9, "Alice", "alice@example.com"))
.onDuplicateKeyUpdate(v => v.name *: v.email)
// INSERT INTO user (`id`, `name`, `email`) VALUES(?, ?, ?)
// AS new_user ON DUPLICATE KEY UPDATE `name` = new_user.`name`, `email` = new_user.`email`
// When updating only specific columns
val insertWithSpecificUpdate = TableQuery[User]
.insert((9, "Alice", "alice@example.com"))
.onDuplicateKeyUpdate(_.name, "UpdatedName")
// INSERT INTO user (`id`, `name`, `email`) VALUES(?, ?, ?)
// AS new_user ON DUPLICATE KEY UPDATE `name` = ?
```
## UPDATE Statement
To update existing records:
### Updating a Single Column
```scala
val update = TableQuery[User].update(_.name)("UpdatedName")
// UPDATE user SET name = ?
```
### Updating Multiple Columns
```scala
val update = TableQuery[User]
.update(u => u.name *: u.email)(("UpdatedName", "updated-email@example.com"))
// UPDATE user SET name = ?, email = ?
```
### Conditional Updates (Skip Updating Specific Columns Based on Conditions)
```scala
val shouldUpdate = true // or false
val update = TableQuery[User]
.update(_.name)("UpdatedName")
.set(_.email, "updated-email@example.com", shouldUpdate)
// If shouldUpdate is true: UPDATE user SET name = ?, email = ?
// If shouldUpdate is false: UPDATE user SET name = ?
```
### Update Using Model Objects
```scala
val update = TableQuery[User].update(User(1, "UpdatedName", "updated-email@example.com"))
// UPDATE user SET id = ?, name = ?, email = ?
```
### UPDATE with WHERE Conditions
```scala
val update = TableQuery[User]
.update(_.name, "UpdatedName")
.where(_.id === 1)
// UPDATE user SET name = ? WHERE id = ?
// Adding AND condition
val updateWithMultipleConditions = TableQuery[User]
.update(_.name, "UpdatedName")
.where(_.id === 1)
.and(_.email === "alice@example.com")
// UPDATE user SET name = ? WHERE id = ? AND email = ?
// Adding OR condition
val updateWithOrCondition = TableQuery[User]
.update(_.name, "UpdatedName")
.where(_.id === 1)
.or(_.id === 2)
// UPDATE user SET name = ? WHERE id = ? OR id = ?
```
## DELETE Statement
To delete records:
### Basic DELETE
```scala
val delete = TableQuery[User].delete
// DELETE FROM user
```
### DELETE with WHERE Conditions
```scala
val delete = TableQuery[User]
.delete
.where(_.id === 1)
// DELETE FROM user WHERE id = ?
// Adding AND/OR conditions
val deleteWithMultipleConditions = TableQuery[User]
.delete
.where(_.id === 1)
.and(_.email === "alice@example.com")
// DELETE FROM user WHERE id = ? AND email = ?
```
### DELETE with LIMIT
To delete only a specific number of records:
```scala
val delete = TableQuery[User]
.delete
.where(_.id > 10)
.limit(5)
// DELETE FROM user WHERE id > ? LIMIT ?
```
## Advanced Query Examples
### Subqueries
Example using a subquery:
```scala
val subQuery = TableQuery[Order]
.select(order => order.userId)
.where(_.quantity > 10)
val mainQuery = TableQuery[User]
.select(user => user.name)
.where(_.id IN subQuery)
// SELECT name FROM user WHERE id IN (SELECT user_id FROM order WHERE quantity > ?)
```
### Complex Joins and Conditions
```scala
val complexQuery = userTable
.join(orderTable).on((user, order) => user.id === order.userId)
.join(productTable).on((_, order, product) => order.productId === product.id)
.select((user, order, product) => user.name *: product.name *: order.quantity)
.where { case ((user, order, product)) =>
(user.name LIKE "A%") && (product.price > 100)
}
.orderBy { case ((_, _, product)) => product.price.desc }
.limit(10)
// SELECT
// user.`name`,
// product.`name`,
// order.`quantity`
// FROM user
// JOIN order ON user.id = order.user_id
// JOIN product ON order.product_id = product.id
// WHERE user.name LIKE ? AND product.price > ?
// ORDER BY product.price DESC
// LIMIT ?
```
### Queries with Conditional Branching
When you want to change the query based on runtime conditions:
```scala
val nameOption: Option[String] = Some("Alice") // or None
val minIdOption: Option[Int] = Some(5) // or None
val query = TableQuery[User]
.selectAll
.whereOpt(user => nameOption.map(name => user.name === name))
.andOpt(user => minIdOption.map(minId => user.id >= minId))
// If both nameOption and minIdOption are Some:
// SELECT id, name, email FROM user WHERE name = ? AND id >= ?
// If nameOption is None and minIdOption is Some:
// SELECT id, name, email FROM user WHERE id >= ?
// If both are None:
// SELECT id, name, email FROM user
```
## Executing Queries
Execute the constructed queries as follows:
```scala 3
import ldbc.dsl.*
// Create Connector
val connector = Connector.fromDataSource(datasource)
(for
// Execute a SELECT query
users <- TableQuery[User].selectAll.where(_.id > 5).query.to[List] // Get results as a List
// Get a single result
user <- TableQuery[User].selectAll.where(_.id === 1).query.to[Option]
// Execute an update query
_ <- TableQuery[User].update(_.name)("NewName").where(_.id === 1).update
yield ???).transaction(connector)
```
## Next Steps
Now you know how to build type-safe queries using the Query Builder. This approach allows you to detect more errors at compile time than writing SQL directly, enabling you to write safer code.
Next, proceed to [Schema](/en/tutorial/Schema.md) to learn how to define database schemas using Scala code.
# Schema Code Generation
In [Schema](/en/tutorial/Schema.md), we learned how to define schemas using Scala code. However, when working with existing databases, manually defining schemas can be time-consuming and prone to errors. This page explains how to automatically generate Scala code from existing SQL files.
Code generation is a powerful tool for automating repetitive tasks and reducing human errors. ldbc provides functionality to automatically generate model classes and table definitions from SQL files.
## SBT Plugin Setup
### Adding the Plugin
You need to set up the following dependency in your project. Add it to `project/plugins.sbt`.
```scala
addSbtPlugin("io.github.takapi327" % "ldbc-plugin" % "0.4.0")
```
### Enabling the Plugin
Enable the plugin for your project in the `build.sbt` file.
```sbt
lazy val root = (project in file("."))
.enablePlugins(Ldbc)
```
## Basic Usage
### Specifying SQL Files
Configure the SQL files to be parsed. You can specify a single file or multiple files.
```sbt
// Specifying a single SQL file
Compile / parseFiles := List(
baseDirectory.value / "sql" / "schema.sql"
)
// Specifying multiple SQL files
Compile / parseFiles := List(
baseDirectory.value / "sql" / "users.sql",
baseDirectory.value / "sql" / "products.sql"
)
```
### Specifying Directories
To target all SQL files in a specific directory, use `parseDirectories`.
```sbt
// Specify by directory
Compile / parseDirectories := List(
baseDirectory.value / "sql"
)
```
### Generated Code
After configuration, code will be automatically generated when you compile with sbt.
```shell
sbt compile
```
The generated files are stored in the `target/scala-X.X/src_managed/main` directory.
### Manual Generation
If you want to force code generation without using cache, use the following command:
```shell
sbt generateBySchema
```
## SQL File Format Requirements
SQL files must include the following elements:
### Database Definition
At the beginning of the file, always include a Create or Use statement for the database. This determines the package name and table ownership in the generated code.
```sql
-- Method 1: Creating a database
CREATE DATABASE `my_app`;
-- Or Method 2: Using an existing database
USE `my_app`;
```
### Table Definitions
After the database definition, include table definitions.
```sql
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) NOT NULL UNIQUE,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```
Complete SQL file example:
```sql
CREATE DATABASE `my_app`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) NOT NULL UNIQUE,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`price` DECIMAL(10, 2) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```
## Detailed Configuration Options
The ldbc plugin offers the following configuration keys to customize code generation.
### Configuration Key List
| Key | Default Value | Details |
|----------------------|-----------------|--------------------------------------------------------|
| `parseFiles` | `List.empty` | List of SQL files to parse |
| `parseDirectories` | `List.empty` | Directory-based specification of SQL files to parse |
| `excludeFiles` | `List.empty` | List of filenames to exclude from parsing |
| `customYamlFiles` | `List.empty` | List of YAML files for type customization |
| `classNameFormat` | `Format.PASCAL` | Format for generated class names (PASCAL, CAMEL, SNAKE)|
| `propertyNameFormat` | `Format.CAMEL` | Format for generated property names (PASCAL, CAMEL, SNAKE)|
| `ldbcPackage` | `ldbc.generated`| Package name for generated files |
### Example: Detailed Configuration
```sbt
Compile / parseFiles := List(
baseDirectory.value / "sql" / "schema.sql"
)
Compile / parseDirectories := List(
baseDirectory.value / "sql" / "tables"
)
Compile / excludeFiles := List(
"temp_tables.sql", "test_data.sql"
)
Compile / classNameFormat := PASCAL // PascalCase (MyClass)
Compile / propertyNameFormat := CAMEL // camelCase (myProperty)
Compile / ldbcPackage := "com.example.db"
```
## Example of Generated Code
For example, with an SQL file like:
```sql
CREATE DATABASE `shop`;
CREATE TABLE `products` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`price` DECIMAL(10, 2) NOT NULL,
`description` TEXT,
`category_id` INT NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```
The following Scala code would be generated:
```scala
package com.example.db
import ldbc.schema.*
import java.time.LocalDateTime
// Model class
case class Product(
id: Long,
name: String,
price: BigDecimal,
description: Option[String],
categoryId: Int,
createdAt: LocalDateTime
)
// Table definition and query builder
object Product {
val table = TableQuery[ProductTable]
class ProductTable extends Table[Product]("products"):
def id: Column[Long] = column[Long]("id", BIGINT, AUTO_INCREMENT, PRIMARY_KEY)
def name: Column[String] = column[String]("name", VARCHAR(255), NOT_NULL)
def price: Column[BigDecimal] = column[BigDecimal]("price", DECIMAL(10, 2), NOT_NULL)
def description: Column[Option[String]] = column[Option[String]]("description", TEXT)
def categoryId: Column[Int] = column[Int]("category_id", INT, NOT_NULL)
def createdAt: Column[LocalDateTime] = column[LocalDateTime]("created_at", TIMESTAMP, NOT_NULL, DEFAULT_CURRENT_TIMESTAMP)
override def * : Column[Product] = (id *: name *: price *: description *: categoryId *: createdAt).to[Product]
}
```
## Customizing Types
If you want to change the types in the automatically generated code to your own types, you can customize them using YAML files.
### YAML File Configuration
First, create a YAML file for customization.
```yaml
# custom_types.yml
database:
name: 'shop'
tables:
- name: 'products'
columns:
- name: 'category_id'
type: 'ProductCategory'
object:
extends:
- 'com.example.ProductTypeMapping'
```
Then, add this YAML file to your project configuration.
```sbt
Compile / customYamlFiles := List(
baseDirectory.value / "config" / "custom_types.yml"
)
```
### Custom Type Implementation
Next, implement the custom type conversion referenced in the YAML file.
```scala
// com/example/ProductTypeMapping.scala
package com.example
import ldbc.dsl.Codec
sealed trait ProductCategory {
def id: Int
}
object ProductCategory {
case object Electronics extends ProductCategory { val id = 1 }
case object Books extends ProductCategory { val id = 2 }
case object Clothing extends ProductCategory { val id = 3 }
def fromId(id: Int): ProductCategory = id match {
case 1 => Electronics
case 2 => Books
case 3 => Clothing
case _ => throw new IllegalArgumentException(s"Unknown category ID: $id")
}
}
trait ProductTypeMapping {
given Codec[ProductCategory] = Codec[Int].imap(ProductCategory.fromId)(_.id)
}
```
### Generated Code After Customization
With the above configuration, code like the following would be generated:
```scala
package ldbc.generated.shop
import ldbc.schema.*
import java.time.LocalDateTime
import com.example.ProductCategory
case class Product(
id: Long,
name: String,
price: BigDecimal,
description: Option[String],
categoryId: ProductCategory, // Changed to custom type
createdAt: LocalDateTime
)
object Product extends com.example.ProductTypeMapping {
val table = TableQuery[ProductTable]
class ProductTable extends Table[Product]("products"):
def id: Column[Long] = column[Long]("id", BIGINT, AUTO_INCREMENT, PRIMARY_KEY)
def name: Column[String] = column[String]("name", VARCHAR(255), NOT_NULL)
def price: Column[BigDecimal] = column[BigDecimal]("price", DECIMAL(10, 2), NOT_NULL)
def description: Column[Option[String]] = column[Option[String]]("description", TEXT)
def categoryId: Column[Int] = column[Int]("category_id", INT, NOT_NULL) // Actual column type doesn't change
def createdAt: Column[LocalDateTime] = column[LocalDateTime]("created_at", TIMESTAMP, NOT_NULL, DEFAULT_CURRENT_TIMESTAMP)
override def * : Column[Product] = (id *: name *: price *: description *: categoryId *: createdAt).to[Product]
}
```
## Detailed YAML Customization Syntax
In the customization YAML file, the following configurations are possible:
```yaml
database:
name: '{database_name}'
tables:
- name: '{table_name}'
columns: # Optional
- name: '{column_name}'
type: '{desired_Scala_type}'
class: # Optional
extends:
- '{package_path_of_trait_to_extend_the_model_class}'
object: # Optional
extends:
- '{package_path_of_trait_to_extend_the_object}'
```
### Example: Adding Traits to a Model Class
```yaml
database:
name: 'shop'
tables:
- name: 'products'
class:
extends:
- 'com.example.JsonSerializable'
- 'com.example.Validatable'
```
### Example: Customizing Multiple Tables and Columns
```yaml
database:
name: 'shop'
tables:
- name: 'products'
columns:
- name: 'price'
type: 'Money'
object:
extends:
- 'com.example.MoneyTypeMapping'
- name: 'orders'
columns:
- name: 'status'
type: 'OrderStatus'
object:
extends:
- 'com.example.OrderStatusMapping'
```
## Using Generated Code
Generated code can be used like any other ldbc code.
```scala
import ldbc.dsl.*
import ldbc.generated.shop.Product
val datasource = MySQLDataSource(...)
// Referencing table queries
val products = Product.table
// Executing queries
val allProducts = datasource.getConnection.use { conn =>
products.filter(_.price > 100).all.run(conn)
}
```
## Best Practices for Code Generation
### 1. Clear SQL File Structure
- Group related tables in the same file
- Always include database definition at the beginning of each file
- Add appropriate comments to explain SQL
### 2. Consistent Naming Conventions
- Use consistent naming conventions for tables and columns in SQL
- Explicitly configure naming rules for the generated Scala code
### 3. Smart Use of Custom Types
- Use custom types for domain-specific concepts
- Leverage custom types to encapsulate complex business logic
### 4. Automate Regeneration
Consider integrating into CI/CD pipelines for regular schema updates.
## Troubleshooting
### When Code Is Not Generated
- Verify SQL file paths are correct
- Ensure database definition is at the beginning of the SQL files
- Check for SQL syntax errors
### When Type Conversion Errors Occur
- Verify custom YAML configurations are correct
- Ensure referenced packages and classes exist in the classpath
- Check that implicit type conversions (given/using) are properly defined
### When Generated Code Has Issues
- Don't manually modify; instead, fix the SQL or YAML files and regenerate
- Check for unsupported SQL features or special types
## Tutorial Completion
Congratulations! You've completed all sections of the ldbc tutorial. Now you have the basic skills and knowledge to develop database applications using ldbc.
Throughout this journey, you've learned:
- Basic usage and setup of ldbc
- Database connections and query execution
- Reading and writing data with type-safe mapping
- Transaction management and error handling
- Advanced features (logging, custom data types, query builders)
- Schema definition and code generation
Use this knowledge to build type-safe and efficient database applications. For more information and updates, refer to the official documentation and GitHub repository.
Happy coding with ldbc!
# Schema
You've learned how to build type-safe queries with the [Query Builder](/en/tutorial/Query-Builder.md). This page explains how to define database schemas in Scala code and map tables to models.
Schema definitions are a critical element that clearly defines the boundary between your application and the database. ldbc provides functionality to define schemas in Scala code, leveraging the powerful type system to represent database structures.
This chapter explains how to handle database schemas in Scala code, particularly how to manually write schemas, which is useful when starting to write an application without an existing database. If you already have schemas in your database, you can skip this work by using the Code Generator.
## Preparation
You need to set up the following dependency in your project:
```scala
//> using dep "io.github.takapi327::ldbc-schema:0.4.0"
```
The following code examples assume these imports:
```scala 3
import ldbc.schema.*
```
## Basic Table Definition
In ldbc, you create table definitions by extending the `Table` class. This allows you to associate Scala models (such as case classes) with database tables.
### Basic Table Definition
```scala 3
// Model definition
case class User(
id: Long,
name: String,
age: Option[Int] // Use Option for columns that allow NULL
)
// Table definition
class UserTable extends Table[User]("user"): // "user" is the table name
// Column definitions
def id: Column[Long] = column[Long]("id")
def name: Column[String] = column[String]("name")
def age: Column[Option[Int]] = column[Option[Int]]("age")
// Mapping with the model
override def * : Column[User] = (id *: name *: age).to[User]
```
In the example above:
- `Table[User]` indicates that this table is associated with the User model
- `"user"` is the table name in the database
- Each column is defined with the `column` method
- The `*` method defines how to map all columns to the model
### Table Definition with Data Types
You can specify MySQL data types and attributes for columns:
```scala 3
class UserTable extends Table[User]("user"):
// Column definitions with data types and attributes
def id: Column[Long] = column[Long]("id", BIGINT, AUTO_INCREMENT, PRIMARY_KEY)
def name: Column[String] = column[String]("name", VARCHAR(255))
def age: Column[Option[Int]] = column[Option[Int]]("age", INT)
override def * : Column[User] = (id *: name *: age).to[User]
```
## Using Dedicated Column Definition Methods
ldbc also provides specialized column definition methods for each data type. Because the variable name is used as the column name, you can write code more simply.
```scala 3
class UserTable extends Table[User]("user"):
def id: Column[Long] = bigint().autoIncrement.primaryKey
def name: Column[String] = varchar(255)
def age: Column[Option[Int]] = int().defaultNull
override def * : Column[User] = (id *: name *: age).to[User]
```
Using dedicated column definition methods allows you to set attributes appropriate for that data type, enabling you to write more type-safe code.
### Explicitly Specifying Column Names
If you want to explicitly specify column names:
```scala 3
class UserTable extends Table[User]("user"):
def id: Column[Long] = bigint("user_id").autoIncrement.primaryKey
def name: Column[String] = varchar("user_name", 255)
def age: Column[Option[Int]] = int("user_age").defaultNull
override def * : Column[User] = (id *: name *: age).to[User]
```
### Setting Column Naming Conventions
You can change the naming convention for columns using `Naming`:
```scala 3
class UserTable extends Table[User]("user"):
// Convert column names to Pascal case (e.g., userId → UserId)
given Naming = Naming.PASCAL
def userId: Column[Long] = bigint().autoIncrement.primaryKey
def userName: Column[String] = varchar(255)
def userAge: Column[Option[Int]] = int().defaultNull
override def * : Column[User] = (userId *: userName *: userAge).to[User]
```
Available naming conventions:
- `Naming.SNAKE` (default): Snake case (e.g., user_id)
- `Naming.CAMEL`: Camel case (e.g., userId)
- `Naming.PASCAL`: Pascal case (e.g., UserId)
## Numeric Column Definitions
For numeric columns, the following operations are possible:
### Integer Types
```scala 3
def id: Column[Long] = bigint().autoIncrement.primaryKey
def count: Column[Int] = int().unsigned.default(0) // Set as unsigned, default value 0
def smallValue: Column[Short] = smallint().unsigned
```
### Decimal Types
```scala 3
def price: Column[BigDecimal] = decimal(10, 2) // 10 digits in total, 2 decimal places
def rating: Column[Double] = double(5) // Double-precision floating point
def score: Column[Float] = float(4) // Single-precision floating point
```
## String Column Definitions
For string columns, the following operations are possible:
```scala 3
def name: Column[String] = varchar(255) // Variable-length string (max 255 characters)
def code: Column[String] = char(5) // Fixed-length string (5 characters)
def description: Column[String] = text() // Text type
def content: Column[String] = longtext() // Long text type
// Specifying character set
def japaneseText: Column[String] = text().charset(Character.utf8mb4)
// Specifying collation
def sortableText: Column[String] = varchar(255)
.charset(Character.utf8mb4)
.collate(Collate.utf8mb4_unicode_ci)
```
## Binary Column Definitions
Defining columns for binary data:
```scala 3
def data: Column[Array[Byte]] = binary(255) // Fixed-length binary
def flexData: Column[Array[Byte]] = varbinary(1000) // Variable-length binary
def largeData: Column[Array[Byte]] = blob() // Binary Large Object
```
## Date and Time Column Definitions
Defining columns for dates and times:
```scala 3
def birthDate: Column[LocalDate] = date() // Date only
def createdAt: Column[LocalDateTime] = datetime() // Date and time
def updatedAt: Column[LocalDateTime] = timestamp()
.defaultCurrentTimestamp(onUpdate = true) // Auto-update on creation and modification
def startTime: Column[LocalTime] = time() // Time only
def fiscalYear: Column[Int] = year() // Year only
```
## ENUM Type and Special Data Types
Example of using ENUM type:
```scala 3
// ENUM definition
enum UserStatus:
case Active, Inactive, Suspended
// Using ENUM in table definition
class UserTable extends Table[User]("user"):
// ...
def status: Column[UserStatus] = `enum`[UserStatus]("status")
```
Other special data types:
```scala 3
def isActive: Column[Boolean] = boolean() // BOOLEAN type
def uniqueId: Column[BigInt] = serial() // SERIAL type (auto-increment BIGINT UNSIGNED)
```
## Setting Default Values
How to set default values for columns:
```scala 3
def score: Column[Int] = int().default(100) // Fixed value
def updatedAt: Column[LocalDateTime] = timestamp()
.defaultCurrentTimestamp() // Current timestamp
def createdDate: Column[LocalDate] = date()
.defaultCurrentDate // Current date
def nullableField: Column[Option[String]] = varchar(255)
.defaultNull // NULL value
```
## Primary Keys, Foreign Keys, and Indexes
### Single-Column Primary Key
```scala 3
def id: Column[Long] = bigint().autoIncrement.primaryKey
```
### Composite Primary Key Definition
```scala 3
class OrderItemTable extends Table[OrderItem]("order_item"):
def orderId: Column[Int] = int()
def itemId: Column[Int] = int()
def quantity: Column[Int] = int().default(1)
// Composite primary key definition
override def keys = List(
PRIMARY_KEY(orderId *: itemId)
)
override def * : Column[OrderItem] = (orderId *: itemId *: quantity).to[OrderItem]
```
### Index Definition
```scala 3
class UserTable extends Table[User]("user"):
// ...column definitions...
// Index definitions
override def keys = List(
INDEX_KEY("idx_user_name", name), // Named index
UNIQUE_KEY("idx_user_email", email) // Unique index
)
```
You can also specify index types:
```scala 3
override def keys = List(
INDEX_KEY(
Some("idx_name"),
Some(Index.Type.BTREE), // Can specify BTREE or HASH index type
None,
name
)
)
```
### Foreign Key Definition
To define foreign keys, first create a TableQuery for the referenced table:
```scala 3
// Referenced table
val userTable = TableQuery[UserTable]
// Referencing table
class ProfileTable extends Table[Profile]("profile"):
def id: Column[Long] = bigint().autoIncrement.primaryKey
def userId: Column[Long] = bigint()
// ...other columns...
// Foreign key definition
def fkUser = FOREIGN_KEY(
"fk_profile_user", // Foreign key name
userId, // Referencing column
REFERENCE(userTable)(_.id) // Referenced table and column
.onDelete(Reference.ReferenceOption.CASCADE) // Action on delete
.onUpdate(Reference.ReferenceOption.RESTRICT) // Action on update
)
override def keys = List(
PRIMARY_KEY(id),
fkUser // Add foreign key
)
```
Reference constraint options (`ReferenceOption`):
- `RESTRICT`: Prevents changes to parent record as long as child records exist
- `CASCADE`: Changes child records along with parent record changes
- `SET_NULL`: Sets relevant columns in child records to NULL when parent record changes
- `NO_ACTION`: Delays constraint checking (basically the same as RESTRICT)
- `SET_DEFAULT`: Sets relevant columns in child records to default values when parent record changes
## Setting Constraints
If you want to define constraints with specific naming conventions, you can use `CONSTRAINT`:
```scala 3
override def keys = List(
CONSTRAINT(
"pk_user", // Constraint name
PRIMARY_KEY(id) // Constraint type
),
CONSTRAINT(
"fk_user_department",
FOREIGN_KEY(departmentId, REFERENCE(departmentTable)(_.id))
)
)
```
## Complex Mapping with Models
### Mapping Nested Models
```scala 3
case class User(
id: Long,
name: UserName, // Nested type
contact: Contact // Nested type
)
case class UserName(first: String, last: String)
case class Contact(email: String, phone: Option[String])
class UserTable extends Table[User]("user"):
def id: Column[Long] = bigint().autoIncrement.primaryKey
def firstName: Column[String] = varchar(50)
def lastName: Column[String] = varchar(50)
def email: Column[String] = varchar(100)
def phone: Column[Option[String]] = varchar(20).defaultNull
// Nested value mapping
def userName: Column[UserName] = (firstName *: lastName).to[UserName]
def contact: Column[Contact] = (email *: phone).to[Contact]
override def * : Column[User] = (id *: userName *: contact).to[User]
```
This configuration will generate the following SQL:
```sql
CREATE TABLE `user` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`firstName` VARCHAR(50) NOT NULL,
`lastName` VARCHAR(50) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`phone` VARCHAR(20) NULL,
PRIMARY KEY (`id`)
)
```
## Schema Generation and DDL Execution
Generate DDL (Data Definition Language) from table definitions and create schemas in the database.
### Generating TableQuery
```scala 3
val users = TableQuery[UserTable]
val profiles = TableQuery[ProfileTable]
val orders = TableQuery[OrderTable]
```
### Generating and Executing Schema
```scala 3
import ldbc.dsl.*
// Combining schemas
val schema = users.schema ++ profiles.schema ++ orders.schema
// Apply schema using database connection
datasource.getConnection.use { conn =>
DBIO.sequence(
// Create tables (only if they don't exist)
schema.createIfNotExists,
// Other operations such as data insertion...
).commit(connector)
}
```
### DDL Operations
```scala 3
val userSchema = users.schema
// Various DDL operations
userSchema.create // Create table
userSchema.createIfNotExists // Create table only if it doesn't exist
userSchema.drop // Drop table
userSchema.dropIfExists // Drop table only if it exists
userSchema.truncate // Delete all data in the table
```
### Checking DDL Statements
How to check the actual SQL that will be executed:
```scala 3
// Check creation query
userSchema.create.statements.foreach(println)
// Check conditional creation query
userSchema.createIfNotExists.statements.foreach(println)
// Check drop query
userSchema.drop.statements.foreach(println)
// Check conditional drop query
userSchema.dropIfExists.statements.foreach(println)
// Check truncate query
userSchema.truncate.statements.foreach(println)
```
## Setting Column Attributes
Various attributes can be set for columns:
```scala 3
def id: Column[Long] = bigint()
.autoIncrement // Auto increment
.primaryKey // Primary key
.comment("User ID") // Comment
def email: Column[String] = varchar(255)
.unique // Unique constraint
.comment("Email address")
def status: Column[String] = varchar(20)
.charset(Character.utf8mb4) // Character set
.collate(Collate.utf8mb4_unicode_ci) // Collation
def hiddenField: Column[String] = varchar(100)
.invisible // Invisible attribute (not retrieved with SELECT *)
def formatField: Column[String] = varchar(100)
.setAttributes(COLUMN_FORMAT.DYNAMIC[String]) // Column storage format
def storageField: Column[Array[Byte]] = blob()
.setAttributes(STORAGE.DISK[Array[Byte]]) // Storage type
```
## Summary
Using the schema module of ldbc allows you to define safe and expressive database schemas by leveraging Scala's type system.
Key features:
- Strong type safety: Detect schema issues at compile time
- Rich data type support: Supports all MySQL data types
- Flexible model mapping: Handles everything from simple case classes to complex nested models
- DDL generation: Generate SQL directly from table definitions
- Extensibility: Supports custom data types and mapping functions
## Next Steps
Now you understand how to define schemas in Scala code. Manually defining schemas allows you to closely integrate your application and database structures.
Next, proceed to [Schema Code Generation](/en/tutorial/Schema-Code-Generation.md) to learn how to automatically generate schema code from existing SQL files.
# Selecting Data
Now that we've learned about [Parameterized Queries](/en/tutorial/Parameterized-Queries.md), let's look at how to retrieve data in various formats. This page explains how to efficiently fetch data using SELECT queries and map them to Scala types.
One of ldbc's most powerful features is its ability to easily map database results to Scala types. It can handle various data formats, from simple primitive types to complex case classes.
*Note: In this tutorial, we use a `Connector` to execute database operations. Create it as follows:*
```scala
import ldbc.connector.*
// Create Connector
val connector = Connector.fromDataSource(datasource)
```
## Basic Data Retrieval Workflow
The basic flow for retrieving data with ldbc is as follows:
1. Create an SQL query with the `sql` interpolator
2. Specify the result type with `.query[T]`
3. Convert results to a collection with `.to[Collection]` (optional)
4. Execute the query using `.readOnly(connector)`/`.commit(connector)`/`.transaction(connector)`, etc.
5. Process the results
Let's look at this flow along with the type transformations in code.
## Reading Rows into Collections
In our first query, let's look at an example that retrieves some usernames into a list and outputs them. We'll show how the types change at each step:
```scala
sql"SELECT name FROM user"
.query[String] // Query[String]
.to[List] // DBIO[List[String]]
.readOnly(connector) // IO[List[String]]
.unsafeRunSync() // List[String]
.foreach(println) // Unit
```
Let's explain this code in detail:
- `sql"SELECT name FROM user"` - Defines the SQL query.
- `.query[String]` - Maps each row result to a `String` type. This generates a `Query[String]` type.
- `.to[List]` - Aggregates the results into a `List`. This generates a `DBIO[List[String]]` type. This method can be used with any collection type that implements `FactoryCompat` (such as `List`, `Vector`, `Set`, etc.). Similar methods are:
- `.unsafe` which returns a single value, raising an exception if there is not exactly one row returned.
- `.option` which returns an Option, raising an exception if there is more than one row returned.
- `.nel` which returns an NonEmptyList, raising an exception if there are no rows returned.
- `.readOnly(connector)` - Executes the query using the connection in read-only mode. The return value is `IO[List[String]]`.
- `.unsafeRunSync()` - Executes the IO monad to get the actual result (`List[String]`).
- `.foreach(println)` - Outputs each element of the result.
## Multiple Column Queries
Of course, you can select multiple columns and map them to tuples:
```scala
sql"SELECT name, email FROM user"
.query[(String, String)] // Query[(String, String)]
.to[List] // DBIO[List[(String, String)]]
.readOnly(connector) // IO[List[(String, String)]]
.unsafeRunSync() // List[(String, String)]
.foreach { case (name, email) => println(s"Name: $name, Email: $email") }
```
In multiple column queries, it's important that the order of selected columns matches the order of type parameters in the tuple. In the above example, `name` corresponds to the 1st column (tuple's `_1`), and `email` to the 2nd column (tuple's `_2`).
## Mapping to Case Classes
While tuples are convenient, we recommend using case classes to improve code readability. ldbc can automatically map query results to case classes:
```scala
// Case class representing user information
case class User(id: Long, name: String, email: String)
// Query execution and mapping
sql"SELECT id, name, email FROM user"
.query[User] // Query[User]
.to[List] // DBIO[List[User]]
.readOnly(connector) // IO[List[User]]
.unsafeRunSync() // List[User]
.foreach(user => println(s"ID: ${user.id}, Name: ${user.name}, Email: ${user.email}"))
```
### How Mapping Works
The following diagram shows how SQL query results are mapped to the User model:
The mapping process is performed as follows based on this diagram:
1. **SQL Execution**: Create a query with `sql"SELECT id, name, email FROM user"` string interpolation and specify the result type with `.query[User]`
2. **ResultSet**: The result set returned from the database (values stored sequentially starting from column number 1)
3. **Decoder Resolution**: At compile time, compose basic Decoders like `Decoder[Long]` and `Decoder[String]` to build `Decoder[User]`
4. **Mapping Process**: At runtime, convert each column value to the appropriate type with the decode method and finally generate a User instance
## Joining Multiple Tables and Nested Case Classes
When retrieving data from multiple tables using `JOIN`, you can map to nested case class structures. In the following example, we join the `city` and `country` tables and map the result to a `CityWithCountry` class:
```scala
// Case class representing a city
case class City(id: Long, name: String)
// Case class representing a country
case class Country(code: String, name: String, region: String)
// Case class combining city and country information
case class CityWithCountry(city: City, country: Country)
// Executing a join query
sql"""
SELECT
city.id,
city.name,
country.code,
country.name,
country.region
FROM city
JOIN country ON city.country_code = country.code
"""
.query[CityWithCountry] // Query[CityWithCountry]
.to[List] // DBIO[List[CityWithCountry]]
.readOnly(connector) // IO[List[CityWithCountry]]
.unsafeRunSync() // List[CityWithCountry]
.foreach(cityWithCountry => println(
s"City: ${cityWithCountry.city.name}, Country: ${cityWithCountry.country.name}"
))
```
### How Complex Mapping Works
The following diagram shows how JOIN results are mapped to nested case classes (CityWithCountry):
As can be seen from this diagram:
1. **SQL Execution**: Execute a SQL query with JOIN clause and specify the result type with `.query[CityWithCountry]`
2. **ResultSet**: Result set with 5 columns (city.id, city.name, country.code, country.name, country.region)
3. **Decoder Construction**:
- Decoder for City: Compose `Decoder[Long]` and `Decoder[String]` to create `Decoder[City]`
- Decoder for Country: Compose three `Decoder[String]`s to create `Decoder[Country]`
- Finally compose both to build `Decoder[CityWithCountry]`
4. **Mapping Process**:
- Generate City object from columns 1,2
- Generate Country object from columns 3,4,5
- Combine both to create CityWithCountry instance
## Using Tuples for Join Queries
Instead of nested case classes, you can also use tuples to retrieve data from multiple tables:
```scala
case class City(id: Long, name: String)
case class Country(code: String, name: String, region: String)
sql"""
SELECT
city.id,
city.name,
country.code,
country.name,
country.region
FROM city
JOIN country ON city.country_code = country.code
"""
.query[(City, Country)] // Query[(City, Country)]
.to[List] // DBIO[List[(City, Country)]]
.readOnly(connector) // IO[List[(City, Country)]]
.unsafeRunSync() // List[(City, Country)]
.foreach { case (city, country) =>
println(s"City: ${city.name}, Country: ${country.name}")
}
```
## Getting a Single Result (Option Type)
If you want to get a single result or an optional result (0 or 1 record) instead of a list, you can use `.to[Option]`:
```scala
case class User(id: Long, name: String, email: String)
// Searching for a single user by ID
sql"SELECT id, name, email FROM user WHERE id = ${userId}"
.query[User] // Query[User]
.to[Option] // DBIO[Option[User]]
.readOnly(connector) // IO[Option[User]]
.unsafeRunSync() // Option[User]
.foreach(user => println(s"Found user: ${user.name}"))
```
If no result is found, `None` is returned, and if one is found, `Some(User(...))` is returned.
## Choosing Query Execution Methods
ldbc provides different query execution methods depending on the purpose:
- `.readOnly(connector)` - Used for read-only operations (such as SELECT statements)
- `.commit(connector)` - Executes write operations in auto-commit mode
- `.rollback(connector)` - Executes write operations and always rolls back (for testing)
- `.transaction(connector)` - Executes operations within a transaction and commits only on success
```scala
// Example of a read-only operation
sql"SELECT * FROM users"
.query[User]
.to[List]
.readOnly(connector)
// Example of a write operation (auto-commit)
sql"UPDATE users SET name = ${newName} WHERE id = ${userId}"
.update
.commit(connector)
// Multiple operations in a transaction
(for {
userId <- sql"INSERT INTO users (name, email) VALUES (${name}, ${email})".returning[Long]
_ <- sql"INSERT INTO user_roles (user_id, role_id) VALUES (${userId}, ${roleId})".update
} yield userId).transaction(connector)
```
## Combining Collection Operations with Queries
By applying Scala collection operations to retrieved data, you can concisely describe more complex data processing:
```scala
// Example of grouping users
sql"SELECT id, name, department FROM employees"
.query[(Long, String, String)] // ID, name, department
.to[List]
.readOnly(connector)
.unsafeRunSync()
.groupBy(_._3) // Group by department
.map { case (department, employees) =>
(department, employees.map(_._2)) // Map to department name and list of employee names
}
.foreach { case (department, names) =>
println(s"Department: $department, Employees: ${names.mkString(", ")}")
}
```
## Efficient Processing of Large Data with Streaming
When processing large amounts of data, loading all data into memory at once can cause memory exhaustion. ldbc allows you to process data efficiently using **streaming**.
### Basic Usage of Streaming
Streaming allows you to fetch and process data incrementally, significantly reducing memory usage:
```scala
import fs2.Stream
import cats.effect.*
// Basic streaming
val cityStream: Stream[DBIO, String] =
sql"SELECT name FROM city"
.query[String]
.stream // Stream[DBIO, String]
// Create Connector
val connector = Connector.fromDataSource(datasource)
// Fetch and process only the first 5 records
val firstFiveCities: IO[List[String]] =
cityStream
.take(5) // Only the first 5 records
.compile.toList // Convert Stream to List
.readOnly(connector) // IO[List[String]]
```
### Specifying Fetch Size
You can control the number of rows fetched at once using the `stream(fetchSize: Int)` method:
```scala
// Fetch 10 rows at a time
val efficientStream: Stream[DBIO, String] =
sql"SELECT name FROM city"
.query[String]
.stream(10) // fetchSize = 10
// Efficiently process large data
val processLargeData: IO[Int] =
sql"SELECT id, name, population FROM city"
.query[(Long, String, Int)]
.stream(100) // Fetch 100 rows at a time
.filter(_._3 > 1000000) // Cities with population over 1 million
.map(_._2) // Extract only city names
.compile.toList
.readOnly(connector)
.map(_.size)
```
### Practical Data Processing with Streaming
Since streaming returns Fs2 `Stream`, you can use rich functional operations:
```scala
// Process large user data incrementally
// Create Connector
val connector = Connector.fromDataSource(datasource)
val processUsers: IO[Unit] =
sql"SELECT id, name, email, created_at FROM users"
.query[(Long, String, String, java.time.LocalDateTime)]
.stream(50) // Fetch 50 rows at a time
.filter(_._4.isAfter(lastWeek)) // Users created since last week
.map { case (id, name, email, _) =>
s"New user: $name ($email)"
}
.evalMap(IO.println) // Output results sequentially
.compile.drain // Execute the stream
.readOnly(connector)
```
### Optimizing Behavior with UseCursorFetch
In MySQL, the efficiency of streaming changes significantly depending on the `UseCursorFetch` setting:
```scala
// UseCursorFetch=true (recommended) - True streaming
val efficientProvider = MySQLDataSource
.build[IO](host, port, user)
.setPassword(password)
.setDatabase(database)
.setUseCursorFetch(true) // Enable server-side cursors
.setSSL(SSL.None)
// UseCursorFetch=false (default) - Limited streaming
val standardProvider = MySQLDataSource
.build[IO](host, port, user)
.setPassword(password)
.setDatabase(database)
.setSSL(SSL.None)
```
**When UseCursorFetch=true:**
- Uses server-side cursors to fetch data incrementally as needed
- Significantly reduces memory usage (safe even with millions of rows)
- Enables true streaming processing
**When UseCursorFetch=false:**
- Loads all results into memory when executing the query
- Fast for small datasets but risky for large data
- Limited effectiveness of streaming
### Example of Large Data Processing
Here's an example of safely processing one million rows:
```scala
// Efficient large data processing
val datasource = MySQLDataSource
.build[IO](host, port, user)
.setPassword(password)
.setDatabase(database)
.setUseCursorFetch(true) // Important: Enable server-side cursors
// Create Connector
val connector = Connector.fromDataSource(datasource)
val processMillionRecords: IO[Long] =
sql"SELECT id, amount FROM transactions WHERE year = 2024"
.query[(Long, BigDecimal)]
.stream(1000) // Process 1000 rows at a time
.filter(_._2 > 100) // Transactions over 100 yen only
.map(_._2) // Extract amounts only
.fold(BigDecimal(0))(_ + _) // Calculate sum
.compile.lastOrError // Get final result
.readOnly(connector)
```
### Benefits of Streaming
1. **Memory Efficiency**: Keep memory usage constant even with large data
2. **Early Processing**: Process data while receiving it simultaneously
3. **Interruptible**: Stop processing midway based on conditions
4. **Functional Operations**: Rich operations like `filter`, `map`, `take`
```scala
// Example of early termination based on conditions
// Create Connector
val connector = Connector.fromDataSource(datasource)
val findFirstLargeCity: IO[Option[String]] =
sql"SELECT name, population FROM city ORDER BY population DESC"
.query[(String, Int)]
.stream(10)
.find(_._2 > 5000000) // First city with population over 5 million
.map(_.map(_._1)) // Extract only city name
.compile.last
.readOnly(connector)
```
## How Mapping Works in Detail
### What is a Decoder?
In ldbc, a `Decoder` is a crucial component responsible for converting from `ResultSet` to Scala types. Decoders have the following characteristics:
1. **Type Safety**: Verify type consistency at compile time
2. **Composable**: Create Decoders for complex structures by combining smaller Decoders
3. **Auto-derivation**: Often generated automatically without explicit definitions
### Basic Decoder Operations
```scala
// Basic type Decoders (implicitly provided)
val longDecoder: Decoder[Long] = Decoder.long
val stringDecoder: Decoder[String] = Decoder.string
// Composing multiple Decoders
val tupleDecoder: Decoder[(Long, String)] =
longDecoder *: stringDecoder
// Converting to case class
case class User(id: Long, name: String)
val userDecoder: Decoder[User] = tupleDecoder.to[User]
```
### Reading by Column Number
When Decoders read values from a `ResultSet`, they use column numbers (starting from 1):
```scala
// How decode(columnIndex, resultSet) method works
decoder.decode(1, resultSet) // Read the 1st column
decoder.decode(2, resultSet) // Read the 2nd column
```
### Error Handling
During the decoding process, the following types of errors may occur:
- **Type mismatch**: SQL type and Scala type are incompatible
- **NULL values**: Mapping to types that don't allow NULL
- **Column count mismatch**: Expected column count differs from actual
These errors are represented as `Either[Decoder.Error, A]` and appropriate error messages are provided at runtime.
## Summary
ldbc provides features for retrieving data from databases in a type-safe and intuitive manner. In this tutorial, we covered:
- The basic workflow for data retrieval
- Single-column and multi-column queries
- Mapping to case classes and **how the internal mechanism works**
- **Type-safe conversion processing with Decoders**
- Joining multiple tables and nested data structures
- Getting single and multiple results
- **Efficient processing of large data with streaming**
- Various execution methods
By understanding how mapping works through Decoder composition, you can safely handle even more complex data structures. Additionally, by leveraging streaming capabilities, you can process large amounts of data efficiently in terms of memory usage. When dealing with large datasets, consider setting `UseCursorFetch=true`.
## Next Steps
Now that you understand how to retrieve data from databases in various formats, you've seen how type-safe mapping allows you to map database results directly to Scala data structures.
Next, let's move on to [Updating Data](/en/tutorial/Updating-Data.md) to learn how to insert, update, and delete data.
# Setup
Welcome to the first step in getting started with ldbc! This page explains how to prepare your development environment and database.
## Requirements
- JDK 21 or higher
- Scala 3
- Docker (for database environment)
- [Scala CLI](https://scala-cli.virtuslab.org/) (recommended)
## Database Setup
First, start a MySQL database using Docker. Create the following docker-compose.yml file:
```yaml
services:
mysql:
image: mysql:8.4.0
container_name: ldbc
ports:
- 13306:3306
volumes:
- ./database:/docker-entrypoint-initdb.d
healthcheck:
test: [ "CMD", "mysqladmin", "ping", "-h", "localhost" ]
timeout: 20s
retries: 10
```
Next, create the following SQL file in the `database` directory and set up the user and initial data:
```sql
-- user.sql
CREATE USER 'ldbc'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO ldbc;
```
```sql
-- schema.sql
CREATE DATABASE IF NOT EXISTS sandbox_db;
USE sandbox_db;
-- Create tables
CREATE TABLE IF NOT EXISTS `user` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS `product` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(100) NOT NULL,
`price` DECIMAL(10, 2) NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS `order` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`user_id` INT NOT NULL,
`product_id` INT NOT NULL,
`order_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`quantity` INT NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES `user` (id),
FOREIGN KEY (product_id) REFERENCES `product` (id)
);
-- Insert initial data
INSERT INTO user (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');
INSERT INTO product (name, price) VALUES
('Laptop', 999.99),
('Mouse', 19.99),
('Keyboard', 49.99),
('Monitor', 199.99);
INSERT INTO `order` (user_id, product_id, quantity) VALUES
(1, 1, 1), -- Alice ordered 1 Laptop
(1, 2, 2), -- Alice ordered 2 Mice
(2, 3, 1), -- Bob ordered 1 Keyboard
(3, 4, 1); -- Charlie ordered 1 Monitor
```
Start the database using Docker Compose:
```bash
docker compose up -d
```
## Setting up a Scala Project
This tutorial uses [Scala CLI](https://scala-cli.virtuslab.org/) to get started easily. If you haven't installed it yet, you can do so with the following command:
```bash
# For macOS
brew install Virtuslab/scala-cli/scala-cli
# For other OS, please refer to Scala CLI's official website
```
### Your First ldbc Project
Create a new directory and set up your first ldbc project:
```bash
mkdir ldbc-tutorial
cd ldbc-tutorial
touch FirstSteps.scala
```
Add the following code to `FirstSteps.scala`:
```scala
//> using scala "3.3.6"
//> using dep "io.github.takapi327::ldbc-dsl:0.4.0"
//> using dep "io.github.takapi327::ldbc-connector:0.4.0"
import cats.effect.*
import cats.syntax.all.*
import ldbc.connector.*
import ldbc.dsl.*
object FirstSteps extends IOApp.Simple:
// A program that returns a simple constant
val simpleProgram: DBIO[Int] = DBIO.pure(0)
// Database connection configuration
val datasource = MySQLDataSource
.build[IO]("127.0.0.1", 13306, "ldbc")
.setPassword("password")
.setDatabase("sandbox_db")
.setSSL(SSL.Trusted)
// Create Connector
val connector = Connector.fromDataSource(datasource)
def run: IO[Unit] =
// Execute the program
simpleProgram.readOnly(connector).flatMap { result =>
IO.println(s"Value retrieved from database: $result")
}
```
Run the program using Scala CLI:
```bash
scala-cli FirstSteps.scala
```
If you see "Value retrieved from database: 42", you've succeeded! This doesn't actually query the database yet, but it confirms that you have the basic ldbc structure and connection setup working.
## Automatic Setup Script (Optional)
We also provide a Scala CLI script that automatically handles all the setup:
```bash
scala-cli https://github.com/takapi327/ldbc/tree/master/docs/src/main/scala/00-Setup.scala --dependency io.github.takapi327::ldbc-dsl:0.4.0 --dependency io.github.takapi327::ldbc-connector:0.4.0
```
## Next Steps
You're now ready to use ldbc! Proceed to [Connection](/en/tutorial/Connection.md) to learn more about database connections.
# Simple Programs
Now that we've learned how to configure [connections](/en/tutorial/Connection.md), let's finally run some queries against the database. This page explains how to create basic database programs using ldbc.
In ldbc, we use the `DBIO` monad to represent a series of database operations. This powerful abstraction allows you to combine multiple queries and perform database operations while enjoying the benefits of pure functional programming.
The `DBIO` monad represents operations that will be executed using an actual database connection. This delays the execution of queries until an actual connection is established, allowing for efficient resource management.
Note: The programs used on this page assume the environment built in the setup.
## First Program: Retrieving a Simple Value
In our first program, we'll learn how to connect to the database and retrieve a simple calculation result.
First, we use the `sql string interpolator` to create a query to the database. This feature allows us to write SQL queries safely and concisely.
```scala 3
// Create an SQL query and specify the return type
val program: DBIO[Option[Int]] = sql"SELECT 2".query[Int].to[Option]
```
The above code does the following:
1. `sql"SELECT 2"`: Creates an SQL query using the SQL string interpolator
2. `.query[Int]`: Specifies that we expect a result of type `Int`
3. `.to[Option]`: Specifies that we want to get the result as an `Option[Int]` type (will be `None` if no result exists)
The `to` method allows you to specify how to retrieve results. The main methods and their return types are as follows:
| Method | Return Type | Notes |
|--------------|----------------------|-------------------------------------------------------------------------------------|
| `to[List]` | `F[List[A]]` | Retrieves all results as a list |
| `to[Option]` | `F[Option[A]]` | Expects 0 or 1 result, errors if more |
| `unsafe` | `F[A]` | Expects exactly 1 result, errors otherwise |
| `option` | `F[Option[A]]` | Expects exactly one line of results; if more than one line exists, an error occurs. |
| `nel` | `F[NonEmptyList[A]]` | Expecting multiple results precisely; an error occurs if there are zero results. |
Next, we execute this program. We create a Connector, run the query, and display the results:
```scala 3
// Create Connector and execute the program
val connector = Connector.fromDataSource(datasource)
program.readOnly(connector).map(println(_)).unsafeRunSync()
```
In the above code:
1. `val connector = Connector.fromDataSource(datasource)`: Creates a Connector from the datasource
2. `program.readOnly(connector)`: Executes the created query in read-only mode using the Connector
3. `.map(println(_))`: Displays the result to standard output
4. `.unsafeRunSync()`: Executes the IO effect
When you run this program, it will return the result `Some(2)` from the database.
**Execute with Scala CLI**
This program can be easily executed using Scala CLI:
```shell
scala-cli https://github.com/takapi327/ldbc/tree/master/docs/src/main/scala/02-Program.scala --dependency io.github.takapi327::ldbc-dsl:0.4.0 --dependency io.github.takapi327::ldbc-connector:0.4.0
```
## Second Program: Combining Multiple Queries
In real-world applications, you often want to execute multiple queries in a single transaction. The `DBIO` monad makes this easy. You can use for-comprehension to combine multiple queries into one larger program:
```scala 3
// Create a program by combining multiple queries
val program: DBIO[(List[Int], Option[Int], Int)] =
for
result1 <- sql"SELECT 1".query[Int].to[List] // Get results as a list
result2 <- sql"SELECT 2".query[Int].to[Option] // Get results as an Option
result3 <- sql"SELECT 3".query[Int].unsafe // Get the result directly
yield (result1, result2, result3)
```
In the above for-comprehension, we execute three different queries in sequence and bind each result to a variable. Finally, we return the three results as a tuple.
The execution method is the same as before:
```scala 3
// Create Connector and execute the program
val connector = Connector.fromDataSource(datasource)
program.readOnly(connector).map(println(_)).unsafeRunSync()
```
The execution result will look like `(List(1), Some(2), 3)`. This is a tuple containing the results of the three queries.
**Execute with Scala CLI**
```shell
scala-cli https://github.com/takapi327/ldbc/tree/master/docs/src/main/scala/03-Program.scala --dependency io.github.takapi327::ldbc-dsl:0.4.0 --dependency io.github.takapi327::ldbc-connector:0.4.0
```
## Third Program: Data Update Operations
In addition to reading, you can also create programs that write to the database. Here's an example of inserting a new record into a user table:
```scala 3
// Execute an INSERT statement and get the number of affected rows
val program: DBIO[Int] =
sql"INSERT INTO user (name, email) VALUES ('Carol', 'carol@example.com')".update
```
Using the `update` method, you can execute update queries such as INSERT, UPDATE, DELETE, etc. The return value is the number of affected rows (in this case, 1).
When executing update queries, you need to use the `commit` method instead of `readOnly` to commit the transaction:
```scala 3
// Create Connector, execute the update program, and commit
val connector = Connector.fromDataSource(datasource)
program.commit(connector).map(println(_)).unsafeRunSync()
```
The `commit` method automatically enables `AutoCommit` and commits the transaction after the query execution. This ensures that changes are persisted to the database.
**Execute with Scala CLI**
```shell
scala-cli https://github.com/takapi327/ldbc/tree/master/docs/src/main/scala/04-Program.scala --dependency io.github.takapi327::ldbc-dsl:0.4.0 --dependency io.github.takapi327::ldbc-connector:0.4.0
```
## Transaction Control
ldbc provides the following main transaction control methods:
- `readOnly(conn)`: Executes in a read-only transaction (data updates are not allowed)
- `commit(conn)`: Executes in auto-commit mode and commits changes upon success
- `rollback(conn)`: Executes with auto-commit off and rolls back at the end
- `transaction(conn)`: Executes with auto-commit off, commits on success, and rolls back on exception
For complex transactions, using the `transaction` method handles commits and rollbacks appropriately:
```scala 3
val complexProgram: DBIO[Int] = for
_ <- sql"INSERT INTO accounts (owner, balance) VALUES ('Alice', 1000)".update
_ <- sql"INSERT INTO accounts (owner, balance) VALUES ('Bob', 500)".update
count <- sql"SELECT COUNT(*) FROM accounts".query[Int].unsafe
yield count
// Create Connector and execute the complex program
val connector = Connector.fromDataSource(datasource)
complexProgram.transaction(connector).map(println(_)).unsafeRunSync()
```
In this program, if either INSERT fails, both changes are rolled back. Only when both succeed are the changes committed.
## Next Steps
Now that you've learned how to execute basic queries, let's take a closer look at [Parameterized Queries](/en/tutorial/Parameterized-Queries.md) next.
# Updating Data
Now that we've learned how to retrieve data in [Selecting Data](/en/tutorial/Selecting-Data.md), let's look at how to write data to the database. This page explains the basics of Data Manipulation Language (DML) such as INSERT, UPDATE, and DELETE.
## Basics of Data Updating
Write operations to the database behave somewhat differently from read operations because they change the state of the database. ldbc provides appropriate abstractions to safely perform these operations.
The basic flow for performing write operations is as follows:
1. Create an SQL query using the `sql` interpolator
2. Call the appropriate method (`.update`, `.returning`, etc.) to specify the type of query
3. Execute the query with `.commit()` or `.transaction()`
4. Process the results
## Inserting Data (INSERT)
### Basic INSERT Operation
To insert data, use the SQL `INSERT` statement and call ldbc's `.update` method. Here's an example of inserting a row into the `user` table:
```scala
// Method to insert a new user into the user table
def insertUser(name: String, email: String): DBIO[Int] =
sql"INSERT INTO user (name, email) VALUES ($name, $email)"
.update
```
Let's use this method to actually insert data and check the result:
```scala
// Create Connector
val connector = Connector.fromDataSource(datasource)
// Execute the insert operation
insertUser("dave", "dave@example.com").commit(connector).unsafeRunSync()
// The return value is the number of affected rows (in this case, 1)
// Check the inserted data
sql"SELECT id, name, email FROM user WHERE name = 'dave'"
.query[(Int, String, String)]
.to[Option]
.readOnly(connector)
.unsafeRunSync()
.foreach { case (id, name, email) =>
println(s"ID: $id, Name: $name, Email: $email")
}
```
The `.update` method returns the number of affected rows (in this case, 1).
### Retrieving Auto-generated Keys
In many cases, tables have auto-generated keys such as auto-increment IDs. To retrieve this auto-generated key value at the time of insertion, use the `.returning[T]` method:
```scala
// Method to insert and get the generated ID
def insertUserAndGetId(name: String, email: String): DBIO[Long] =
sql"INSERT INTO user (name, email) VALUES ($name, $email)"
.returning[Long]
```
Let's use this method to insert a new user and get the auto-generated ID:
```scala
// Create Connector
val connector = Connector.fromDataSource(datasource)
// Insert and get the auto-generated ID
val newUserId = insertUserAndGetId("frank", "frank@example.com")
.commit(connector)
.unsafeRunSync()
println(s"New user ID: $newUserId")
```
**Note**: The `.returning` method only works with columns that have `AUTO_INCREMENT` set in MySQL.
### Retrieving Inserted Data
If you want to retrieve all the information about the inserted data at the same time, you can combine two steps using the auto-generated key:
```scala
// Case class representing a user
case class User(id: Long, name: String, email: String)
// Method to insert a user and return the inserted user's information
def insertAndRetrieveUser(name: String, email: String): DBIO[Option[User]] =
for
id <- sql"INSERT INTO user (name, email) VALUES ($name, $email)".returning[Long]
user <- sql"SELECT id, name, email FROM user WHERE id = $id".query[User].to[Option]
yield user
```
Usage example:
```scala
// Create Connector
val connector = Connector.fromDataSource(datasource)
// Insert a user and get the inserted user's information
insertAndRetrieveUser("grace", "grace@example.com")
.commit(connector)
.unsafeRunSync()
.foreach { user =>
println(s"Inserted user: ID=${user.id}, Name=${user.name}, Email=${user.email}")
}
```
## Updating Data (UPDATE)
To update data, use the SQL `UPDATE` statement and call the `.update` method similarly:
```scala
// Method to update a user's email address
def updateUserEmail(id: Long, newEmail: String): DBIO[Int] =
sql"UPDATE user SET email = $newEmail WHERE id = $id"
.update
```
Usage example:
```scala
// Create Connector
val connector = Connector.fromDataSource(datasource)
// Update a user's email address
updateUserEmail(1, "alice+updated@example.com")
.commit(connector)
.unsafeRunSync()
// Check the updated data
sql"SELECT id, name, email FROM user WHERE id = 1"
.query[User]
.to[Option]
.readOnly(connector)
.unsafeRunSync()
.foreach { user =>
println(s"Updated user: ID=${user.id}, Name=${user.name}, Email=${user.email}")
}
```
### Updating with Multiple Conditions
It's also possible to update with complex conditions:
```scala
// Update email addresses for users matching a specific name
def updateEmailsByName(name: String, newEmail: String): DBIO[Int] =
sql"""
UPDATE user
SET email = $newEmail
WHERE name LIKE ${"%" + name + "%"}
""".update
```
This example updates the email address of all users who match the specified name pattern.
## Deleting Data (DELETE)
To delete data, use the SQL `DELETE` statement and call the `.update` method:
```scala
// Delete a user by ID
def deleteUser(id: Long): DBIO[Int] =
sql"DELETE FROM user WHERE id = $id"
.update
```
Usage example:
```scala
// Create Connector
val connector = Connector.fromDataSource(datasource)
// Delete a user
deleteUser(5)
.commit(connector)
.unsafeRunSync()
// Confirm deletion
sql"SELECT COUNT(*) FROM user WHERE id = 5"
.query[Int]
.unsafe
.readOnly(connector)
.unsafeRunSync() match {
case 0 => println("User with ID 5 has been deleted")
case n => println(s"User with ID 5 still exists (count: $n)")
}
```
### Deleting Multiple Rows
It's also possible to delete multiple rows at once that match a condition:
```scala
// Delete all users with email addresses from a specific domain
def deleteUsersByEmailDomain(domain: String): DBIO[Int] =
sql"DELETE FROM user WHERE email LIKE ${"%@" + domain}"
.update
```
## Batch Processing (Bulk Operations)
### Bulk Insertion of Multiple Rows
To efficiently insert many rows, you can specify multiple value sets in the `VALUES` clause:
```scala
import cats.data.NonEmptyList
// Bulk insert multiple users
def insertManyUsers(users: NonEmptyList[(String, String)]): DBIO[Int] =
val values = users.map { case (name, email) => sql"($name, $email)" }
(sql"INSERT INTO user (name, email) VALUES " ++ Fragments.values(values)).update
```
Usage example:
```scala
// Define multiple users
val newUsers = NonEmptyList.of(
("greg", "greg@example.com"),
("henry", "henry@example.com"),
("irene", "irene@example.com")
)
// Create Connector
val connector = Connector.fromDataSource(datasource)
// Execute bulk insertion
val insertedCount = insertManyUsers(newUsers).commit(connector).unsafeRunSync()
println(s"Number of rows inserted: $insertedCount") // Should output "Number of rows inserted: 3"
```
### Transactions for Multiple Operations
To atomically execute multiple operations, use transactions. This ensures that either all operations succeed or all operations fail (rollback):
```scala
// Example of inserting a user and related information for that user
def createUserWithProfile(name: String, email: String, bio: String): DBIO[Long] =
for
userId <- sql"INSERT INTO user (name, email) VALUES ($name, $email)".returning[Long]
profileId <- sql"INSERT INTO user_profile (user_id, bio) VALUES ($userId, $bio)".returning[Long]
yield userId
```
By executing this method using `.transaction`, both user insertion and profile insertion are processed as a single transaction:
```scala
// Create Connector
val connector = Connector.fromDataSource(datasource)
// Execute within a transaction
val userId = createUserWithProfile("julia", "julia@example.com", "Programmer")
.transaction(connector)
.unsafeRunSync()
println(s"Created user ID: $userId")
```
If the insertion into the `user_profile` table fails, the insertion into the `user` table will also automatically roll back.
## Selecting Query Execution Methods
ldbc provides the following query execution methods for data update operations:
- `.commit(connector)` - Executes a write operation in auto-commit mode (suitable for simple, single update operations)
- `.rollback(connector)` - Executes a write operation and always rolls back (for testing and verification)
- `.transaction(connector)` - Executes operations within a transaction, committing only upon success (suitable when you want to treat multiple operations as a single unit)
```scala
// Execution in auto-commit mode (simple single operation)
updateUserEmail(1, "new@example.com").commit(connector)
// Execution for testing (changes are not saved)
insertUser("test", "test@example.com").rollback(connector)
// Multiple operations within a transaction (all succeed or all fail)
(for {
userId <- insertUser("kate", "kate@example.com").returning[Long]
_ <- sql"INSERT INTO user_roles (user_id, role_id) VALUES ($userId, 2)".update
} yield userId).transaction(connector)
```
## Error Handling
It's also important to handle errors that may occur during data update operations. In ldbc, you can use the `IO` monad from `cats-effect` to process errors:
```scala
import cats.effect.unsafe.IORuntime
import cats.effect.IO
implicit val runtime: IORuntime = IORuntime.global
// Example of error handling
def safeUpdateUser(id: Long, newEmail: String): Unit = {
val result = updateUserEmail(id, newEmail)
.commit(connector)
.attempt // Convert IO result to Either[Throwable, Int]
.unsafeRunSync()
result match {
case Right(count) => println(s"Number of rows updated: $count")
case Left(error) => println(s"An error occurred: ${error.getMessage}")
}
}
// Execution example (updating with a non-existent ID)
safeUpdateUser(9999, "nonexistent@example.com")
```
## Summary
When performing data update operations with ldbc, it's good to remember the following points:
1. Use `.update` or `.returning[T]` (to retrieve auto-generated keys) for **insert operations**
2. Use `.update` for **update operations**, specifying target rows with a WHERE clause
3. Also use `.update` for **delete operations** (though it's a DELETE statement, the operation method is common)
4. Use `.transaction()` to execute **multiple operations** atomically
5. Use `.commit()` for simple **single operations**
6. Use `.rollback()` for **testing purposes** to discard changes
By appropriately combining these data update operations, you can efficiently and safely manipulate your database.
## Next Steps
Now you understand how to insert, update, and delete data in your database. At this point, you've learned all the basics of using ldbc. You've acquired the knowledge necessary for everyday database operations, including database connection, query execution, data reading and writing, and transaction management.
From here, we'll move on to more advanced topics. Let's start with [Error Handling](/en/tutorial/Error-Handling.md) to learn how to properly handle exceptions that can occur in database operations.
# ldbc Tutorial
ldbc is a type-safe MySQL database client written in Scala 3. It allows you to write SQL safely and concisely while leveraging Scala's powerful type system to reduce database operation errors.
## Key Features of ldbc
- **Type Safety**: Detects SQL query issues at compile time
- **Concise Syntax**: Intuitive query writing using SQL string interpolation
- **Cats Effect Integration**: Supports pure functional programming
- **Schema Auto-generation**: Generate Scala code from SQL
- **Query Builder**: Build type-safe queries without writing SQL directly
## Quick Start
Let's first take a look at the basic usage of ldbc. Here's a simple example of retrieving user information from the database and adding a new user:
```scala 3
import cats.effect.*
import cats.syntax.all.*
import ldbc.connector.*
import ldbc.dsl.*
// Database connection configuration
val datasource =
MySQLDataSource
.build[IO]("127.0.0.1", 3306, "ldbc")
.setPassword("password")
.setDatabase("ldbc")
.setSSL(SSL.Trusted)
// Executing queries
val program = for
// Retrieving user list
users <- sql"SELECT id, name FROM user".query[(Int, String)].to[List]
// Adding a new user
_ <- sql"INSERT INTO user (name, email) VALUES ('Taro Yamada', 'yamada@example.com')".update
// Checking user count after update
count <- sql"SELECT COUNT(*) FROM user".query[Int].unsafe
yield (users, count)
// Create Connector
val connector = Connector.fromDataSource(datasource)
// Executing the program
program.transaction(connector)
```
## How to Proceed with this Tutorial
This tutorial series is structured to help you learn ldbc step by step. We recommend proceeding in the following order:
### 1. Setup
First, let's prepare the environment for using ldbc.
- [Setup](/en/tutorial/Setup.md) - Preparing development environment and database
- [Connection](/en/tutorial/Connection.md) - How to connect to a database
### 2. Basic Operations
Next, let's learn frequently used features for everyday tasks.
- [Simple Program](/en/tutorial/Simple-Program.md) - Execute your first simple queries
- [Parameters](/en/tutorial/Parameterized-Queries.md) - Queries with parameters
- [Selecting Data](/en/tutorial/Selecting-Data.md) - Retrieving data with SELECT statements
- [Updating Data](/en/tutorial/Updating-Data.md) - INSERT/UPDATE/DELETE operations
- [Database Operations](/en/tutorial/Database-Operations.md) - Transaction management
### 3. Advanced Operations
After understanding the basics, let's move on to more advanced features.
- [Error Handling](/en/tutorial/Error-Handling.md) - Exception handling
- [Logging](/en/tutorial/Logging.md) - Query logging
- [Custom Data Types](/en/tutorial/Custom-Data-Type.md) - Support for custom data types
- [Query Builder](/en/tutorial/Query-Builder.md) - Type-safe query construction
- [Schema](/en/tutorial/Schema.md) - Table definitions
- [Schema Code Generation](/en/tutorial/Schema-Code-Generation.md) - Code generation from SQL
## Why Choose ldbc?
- **Low Learning Curve**: String interpolation that leverages your existing SQL knowledge
- **Safety**: Early bug detection through compile-time type checking
- **Productivity**: Reduction of boilerplate code and automatic code generation
- **Performance**: Optimized MySQL connection management
- **Benefits of Functional Programming**: Composability through Cats Effect integration
Now, let's start with [Setup](/en/tutorial/Setup.md)!
## Detailed Navigation
@:navigationTree {
entries = [ { target = "/en/tutorial", depth = 2 } ]
}