Getting Started
JDBC with Compiler plugin
Mikrom comes with a compiler plugin that will generate RowMapper and ParameterMapper implementations for you. This means that you
can use standard data classes to create ergonomic, yet type-safe, queries.
Add the Mikrom dependency and Gradle plugin to your project:
plugins {
id("io.github.kantis.mikrom") version "0.7.0"
}
dependencies {
implementation("io.github.kantis:mikrom-jdbc:0.7.0")
}
Then, all you need is to create a JDBC connection (javax.sql.DataSource), a Mikrom DataSource and a Mikrom instance:
@JvmInline
value class BookTitle(val title: String)
@MikromResult
data class SavedBook(
val id: Int,
val author: String,
val title: BookTitle, // Value classes are supported out of the box
val numberOfPages: Int
)
fun main() {
val dbConnection = DriverManager.getConnection("jdbc:your_database_url")
val datasource = JdbcDataSource(dbConnection) // Note: you can pass a HikariCP data source here, if you want connection pooling.
val mikrom = Mikrom {
// configure your Mikrom instance here.
}
}
Querying the database
Simply write your SQL, and use the Mikrom.queryFor method to execute it.
In order to query for data classes, a RowMapper must be registered for that type.
To use a where-statement, just add the parameter. Parameters can be passed either as named parameters or as positional parameters.
dataSource.transaction {
// using named parameters
mikrom.queryFor<Book>(
"SELECT * FROM books WHERE number_of_pages > :minPages",
mapOf("minPages" to 200)
)
// or using positional parameters
mikrom.queryFor<Book>(
"SELECT * FROM books WHERE number_of_pages > ?", 200
)
}
Inserting data
Use the Mikrom.execute method to insert data.
@MikromParameter
data class CreateBookCommand(val author: String, val title: BookTitle, val numberOfPages: Int)
dataSource.transaction {
// Using named parameters with a generated ParameterMapper
mikrom.execute(
"""
INSERT INTO books(author, title, number_of_pages)
VALUES (:author, :title, :numberOfPages)
""",
CreateBookCommand("JRR Tolkien", BookTitle("The Hobbit"), 310),
CreateBookCommand("George Orwell", BookTitle("1984"), 328),
)
// or, using positional parameters
mikrom.execute(
"INSERT INTO books(author, title, number_of_pages) VALUES (?, ?, ?)",
listOf("JRR Tolkien", "The Hobbit", 310),
listOf("George Orwell", "1984", 328),
)
}
Transactions
All database operations must be wrapped in a transaction. The execute and queryFor methods are only
defined in the context of a transaction, so attempting to use them without one will result in a compilation error.
dataSource.transaction {
mikrom.execute(
"INSERT INTO books (author, title, number_of_pages) VALUES (:author, :title, :numberOfPages)",
Book("JRR Tolkien", BookTitle("The Hobbit"), 310),
Book("George Orwell", BookTitle("1984"), 328),
)
mikrom.queryFor<Book>("SELECT * FROM books") shouldBe
listOf(
Book("JRR Tolkien", "The Hobbit", 310),
Book("George Orwell", "1984", 328),
)
}
R2DBC with coroutines
mikrom-r2dbc exposes the same API as suspending functions, on top of an R2DBC ConnectionPool.
The compiler plugin, RowMappers, ParameterMappers and converters are shared with JDBC.
Create an io.r2dbc.pool.ConnectionPool for your driver and wrap it in a PooledR2dbcDataSource:
import io.github.kantis.mikrom.suspend.execute
import io.github.kantis.mikrom.suspend.executeStreaming
import io.github.kantis.mikrom.suspend.queryFor
import io.github.kantis.mikrom.suspend.queryForFlow
val connectionFactory = PostgresqlConnectionFactory(
PostgresqlConnectionConfiguration.builder()
.host("localhost")
.database("books")
.username("user")
.password("secret")
.build(),
)
val pool = ConnectionPool(ConnectionPoolConfiguration.builder(connectionFactory).maxSize(10).build())
val dataSource = PooledR2dbcDataSource(pool)
val mikrom = Mikrom {}
Imports
The suspending execute, executeStreaming, queryFor and queryForFlow live in the
io.github.kantis.mikrom.suspend package, so they can coexist with the blocking ones. Make sure you import the
right variant.
Transactions
Every operation runs inside suspendingTransaction { }. The block commits when it returns normally, rolls back when
it throws or when it returns the Rollback marker, and always hands the connection back to the pool afterwards.
dataSource.suspendingTransaction {
mikrom.execute(
"INSERT INTO books (author, title, number_of_pages) VALUES (:author, :title, :numberOfPages)",
CreateBookCommand("JRR Tolkien", BookTitle("The Hobbit"), 310),
CreateBookCommand("George Orwell", BookTitle("1984"), 328),
)
val longReads = mikrom.queryFor<Book>("SELECT * FROM books WHERE number_of_pages > :minPages", mapOf("minPages" to 320))
}
If the block is cancelled, for example because the surrounding request timed out, the transaction is rolled back and the connection is still returned to the pool.
Queries: queryFor versus queryForFlow
The suspending queryFor loads the whole result and returns a List<T>, exactly like the blocking API. The list is
plain data, so it is safe to return from the transaction block.
val books: List<Book> = dataSource.suspendingTransaction {
mikrom.queryFor<Book>("SELECT * FROM books")
}
For results too large to hold in memory, queryForFlow returns a cold Flow<T> instead. Nothing is sent to the
database until the flow is collected, and collecting it uses the transaction's connection.
Collect the flow inside the transaction
Collect a queryForFlow result (collect { }, toList(), first() …) before the suspendingTransaction
block returns. A flow that escapes the block fails with The connection is closed when it is eventually
collected, because the connection has already been returned to the pool.
// OK: rows are streamed and processed while the transaction is open
dataSource.suspendingTransaction {
mikrom.queryForFlow<Book>("SELECT * FROM books").collect { book -> index(book) }
}
// Broken: the flow is collected after the connection was released
val flow = dataSource.suspendingTransaction {
mikrom.queryForFlow<Book>("SELECT * FROM books")
}
flow.toList() // IllegalStateException: The connection is closed
Parameters
Parameters work the same way as with JDBC: a List binds positionally, a Map<String, Any?> resolves :named
parameters, and a @MikromParameter class (or a type with a registered ParameterMapper) is mapped to named
parameters.
Named parameters are portable. Mikrom rewrites them to whatever positional syntax the driver expects
($1 for PostgreSQL, @P1 for SQL Server, ? for the rest), so :minPages works unchanged across databases.
Positional parameters are passed through to the driver as written, so use the driver's own placeholder syntax:
mikrom.queryFor<Book>("SELECT * FROM books WHERE number_of_pages > $1", listOf(320)) // PostgreSQL, H2
mikrom.queryFor<Book>("SELECT * FROM books WHERE number_of_pages > ?", listOf(320)) // MySQL, MariaDB
mikrom.queryFor<Book>("SELECT * FROM books WHERE number_of_pages > @P1", listOf(320)) // SQL Server
Nulls need a type
R2DBC drivers must know the SQL type of a NULL parameter, so a bare null in a parameter list or map is
rejected. Pass TypedNull(Int::class) instead. @MikromParameter classes do this automatically for nullable
properties, see Parameter Types.
Streaming parameters
For large inserts, executeStreaming accepts the positional parameter sets as a Flow<List<Any?>> and executes the
statement once per emitted set. It suspends until the flow completes, so the transaction cannot commit with inserts
still in flight.
dataSource.suspendingTransaction {
mikrom.executeStreaming(
"INSERT INTO books (author, title, number_of_pages) VALUES ($1, $2, $3)",
booksToImport.map { listOf(it.author, it.title, it.numberOfPages) },
)
}
When the parameter sets are already in memory, prefer execute with several lists or objects: they are bound to a
single statement and sent as one batch.