JDBC is commonly used in Scala to access databases, however, JDBC uses blocking IO whenever it access the database, blocking the current thread. therefore developers need to be careful when using JDBC in an asynchronous application.
async-db is a thin wrapper around JDBC that includes a preconfigured thread pool and provides a non blocking API to connect to a database, usingFuture.
The thread pool is a simpler version of the thread pool used by slick:
-
It uses a blocking queue to queue requests, which are processed FIFO
-
The queue is limited and this limit is configurable, once the queue is full the api caller requests will be rejected
-
The threads created are daemon threads
-
One thread will be created per database connection, so that jdbc blocking calls do not affect other connections.
Hikari-CP is used to manage the connection pool.
Get <version> from the maven central badge above.
libraryDependencies += "eu.0io" %% "async-db" % "<version>"
ivy"eu.0io::async-db:<version>"
You'll also need to add the JDBC drivers for the database you need to connect to.
Create a configuration for your database and thread pools in application.conf:
Create or edit an application.conf in your classpath with the following configurations:
your-app {
database {
jdbcurl = "jdbc:postgresql://host:port/schema"
jdbc-properties = {
reWriteBatchedInserts = true # You can put any property specific to your JDBC connector here
}
thread-pool = {
queueSize = 1000
}
db-pool = {
properties = { // Hikari CP specific pool configurations, see https://github.com/brettwooldridge/HikariCP#gear-configuration-knobs-baby
registerMbeans = true
maximumPoolSize = 10
minimumIdle = 10
}
}
}
}
Create a Database:
import com.typesafe.config.ConfigFactory
import eu._0io.async_db.Database
val config = ConfigFactory.load().getConfig("your-app.database")
implicit lazy val db = Database.fromConfig(config)You can then share this db instance on your application and execute SQL queries:
val id = db.withConnection { c: Connection =>
// Your jdbc code to use `Connection`
}
// id has type Future[_]Run transactions with withTransaction:
val id = db.withTransaction { c =>
// Your jdbc code to use `Connection`
}The transactions will be committed if the function passed as argument succeeds or is rollbacked if an exceptions is thrown. For finer control of the transaction behavior, you can use withConnection directly.
Both async-db and hikari-cp will publish metrics using dropwizard metrics. This can be enabled by passing a metrics collector when initializing a Database:
val metricRegistry = new MetricRegistry
implicit lazy val db = Database.fromConfig(config, Option(metricRegistry))You can then enable the dropwizard reporters, for example for the console reporter:
val reporter = ConsoleReporter.forRegistry(metricRegistry).convertRatesTo(TimeUnit.SECONDS).convertDurationsTo(TimeUnit.MILLISECONDS).build
reporter.start(10, TimeUnit.SECONDS)Will report:
-- Gauges ----------------------------------------------------------------------
HikariPool-1.pool.ActiveConnections
value = 0
HikariPool-1.pool.IdleConnections
value = 10
HikariPool-1.pool.MaxConnections
value = 10
HikariPool-1.pool.MinConnections
value = 10
HikariPool-1.pool.PendingConnections
value = 0
HikariPool-1.pool.TotalConnections
value = 10
async-db.db.HikariPool-1.io-thread-pool.active-count
value = 0
async-db.db.HikariPool-1.io-thread-pool.core-pool-size
value = 10
async-db.db.HikariPool-1.io-thread-pool.pool-size
value = 8
async-db.db.HikariPool-1.io-thread-pool.queue-size
value = 0
async-db.db.HikariPool-1.io-thread-pool.task-count
value = 8
-- Histograms ------------------------------------------------------------------
HikariPool-1.pool.ConnectionCreation
count = 9
min = 4
max = 40
mean = 9.78
stddev = 10.81
median = 6.00
75% <= 8.00
95% <= 40.00
98% <= 40.00
99% <= 40.00
99.9% <= 40.00
HikariPool-1.pool.Usage
count = 9
min = 2
max = 116
mean = 27.56
stddev = 39.35
median = 4.00
75% <= 34.00
95% <= 116.00
98% <= 116.00
99% <= 116.00
99.9% <= 116.00
-- Meters ----------------------------------------------------------------------
HikariPool-1.pool.ConnectionTimeoutRate
count = 0
mean rate = 0.00 events/second
1-minute rate = 0.00 events/second
5-minute rate = 0.00 events/second
15-minute rate = 0.00 events/second
-- Timers ----------------------------------------------------------------------
HikariPool-1.pool.Wait
count = 9
mean rate = 30.69 calls/second
1-minute rate = 0.00 calls/second
5-minute rate = 0.00 calls/second
15-minute rate = 0.00 calls/second
min = 0.02 milliseconds
max = 0.22 milliseconds
mean = 0.06 milliseconds
stddev = 0.06 milliseconds
median = 0.04 milliseconds
75% <= 0.05 milliseconds
95% <= 0.22 milliseconds
98% <= 0.22 milliseconds
99% <= 0.22 milliseconds
99.9% <= 0.22 milliseconds
You will need mill installed.
To run tests you'll need a postgres instance:
docker run --name async-db -p 5432:5432 -e POSTGRES_PASSWORD=root -e POSTGRES_USER=async-db -d postgres:13.3-alpine
Then run tests:
mill async-db[_].test
