Migrating from JDBC DAO to R2DBC DAO

Prerequisites

Before you start, check whether your application uses JDBC DAO features that are not available in R2DBC DAO. If it does, you must change those parts of the application before completing the migration.

JDBC DAO	R2DBC DAO
`ImmutableEntityClass`	Not available.
`ImmutableCachedEntityClass`	Not available.
`EntityClass.view()` and `View`	Not available. Use `find()` instead.
`EntityClass.findWithCacheCondition()`	Not available.
`EntityClass.testCache(predicate)`	Not available. Use `EntityClass.testCache(entityId)` instead.
`Entity.writeValues`, `storeWrittenValues()`	Not available. Pending values belong to the transaction's `EntityCache` instead.
`EntityCache.maxEntitiesToStore`	Not available. The R2DBC entity cache does not evict entities.
`EntityCache.invalidateGlobalCaches()`	Not available. This functionality is part of the `ImmutableCachedEntityClass` machinery.
`warmUpReferences()`	Available, but does not support the `forUpdate` parameter.

Update dependencies

Replace the JDBC transport and DAO artifacts with their R2DBC counterparts:

implementation("org.jetbrains.exposed:exposed-core:1.6.0")
implementation("org.jetbrains.exposed:exposed-jdbc:1.6.0")
implementation("org.jetbrains.exposed:exposed-dao:1.6.0")

implementation("org.jetbrains.exposed:exposed-core:1.6.0")
implementation("org.jetbrains.exposed:exposed-r2dbc:1.6.0")
implementation("org.jetbrains.exposed:exposed-dao-r2dbc:1.6.0")

Use the R2DBC driver that corresponds to your database, for example:

implementation("com.h2database:h2:2.4.240")

implementation("io.r2dbc:r2dbc-h2:1.0.0.RELEASE")

Opt in to the experimental API

The R2DBC DAO API is experimental and requires an opt-in. To opt in for the entire module, add ExperimentalR2dbcDaoApi to the Kotlin compiler options:

kotlin {
    compilerOptions {
        optIn.add("org.jetbrains.exposed.v1.dao.r2dbc.ExperimentalR2dbcDaoApi")
    }
}

To opt in at a narrower scope, use @OptIn(ExperimentalR2dbcDaoApi::class) instead.

Update imports

The R2DBC DAO API is located in the org.jetbrains.exposed.v1.dao.r2dbc package.

For DAO, replace the org.jetbrains.exposed.v1.dao. package with org.jetbrains.exposed.v1.dao.r2dbc. across your imports. For example:

JDBC DAO	R2DBC DAO
`org.jetbrains.exposed.v1.dao.IntEntity`	`org.jetbrains.exposed.v1.dao.r2dbc.IntEntity`
`org.jetbrains.exposed.v1.dao.IntEntityClass`	`org.jetbrains.exposed.v1.dao.r2dbc.IntEntityClass`
`org.jetbrains.exposed.v1.dao.entityCache`	`org.jetbrains.exposed.v1.dao.r2dbc.entityCache`
`org.jetbrains.exposed.v1.dao.load`	`org.jetbrains.exposed.v1.dao.r2dbc.load`
`org.jetbrains.exposed.v1.dao.with`	`org.jetbrains.exposed.v1.dao.r2dbc.with`
`org.jetbrains.exposed.v1.dao.Referrers`	`org.jetbrains.exposed.v1.dao.r2dbc.Referrers`
`org.jetbrains.exposed.v1.dao.InnerTableLink`	`org.jetbrains.exposed.v1.dao.r2dbc.InnerTableLink`

Table definitions don't require changes. They are provided by the exposed-core module and are shared by both JDBC and R2DBC drivers.

Update transaction handling

R2DBC DAO operations are suspending, so the functions that contain them must be suspend when required. Replace the transaction() function with suspendTransaction():

import org.jetbrains.exposed.v1.jdbc.transactions.transaction

val client = transaction {
    Client.findById(id)
}

import org.jetbrains.exposed.v1.r2dbc.transactions.suspendTransaction

val client = suspendTransaction {
    Client.findById(id)
}

Update entity creation

R2DBC DAO provides two entity creation functions:

Function	Suspends	When the `INSERT` is issued	ID available after the call
`new()`	No	At the next flush	Only if the ID is explicitly assigned or otherwise available without an insert
`newSuspend()`	Yes	Before the call returns	Yes, including generated IDs

Use `newSuspend()` for immediate inserts

The newSuspend() function suspends and issues the INSERT before returning, so a generated ID is available immediately.

Use newSuspend() when the entity must be inserted before the function returns:

val client = Client.new {
    name = "Alice Johnson"
}
val id = client.id.value

val client = Client.newSuspend {
    name = "Alice Johnson"
}
val id = client.id.value

Use `new()` for deferred and batch inserts

The new() function does not issue the INSERT immediately. Instead, it schedules the insert in the transaction's entity cache. This can be useful when you want to create multiple entities and flush them together:

suspendTransaction {
    val tags = listOf("tech", "finance", "energy").map {
        name -> Tag.new {
            this.name = name
        }
    }
    
    flushCache()
    
    val ids = tags.map {
        it.id.value
    }
}

The pending inserts are flushed when required by subsequent database operations or when the transaction commits. Call flushCache() when you need to explicitly control when the pending inserts are sent to the database.

Update reference properties

R2DBC reference properties use an accessor because reading a reference can require a suspending database operation.

Change the referencedOn and optionalReferencedOn properties from var to val. Read the reference by calling the accessor and update it with the .set() function:

var broker by Broker referencedOn Clients.broker
var portfolio by Portfolio optionalReferencedOn Trades.portfolio

val name = client.broker.name
client.broker = otherBroker
trade.portfolio = null

val broker by Broker referencedOn Clients.broker
val portfolio by Portfolio optionalReferencedOn Trades.portfolio

val name = client.broker().name
client.broker.set(otherBroker)
trade.portfolio.set(null)

The backReferencedOn and optionalBackReferencedOn properties remain unchanged. The via property remains a var and takes a SizedCollection.

Collect DAO flows

In R2DBC DAO, SizedIterable extends Flow. As a result, DAO collections are represented as flows rather than Kotlin collections.

This applies to DAO collections such as referrers, via relations, all(), and find().

val names = client.portfolios.map { it.name }

val names = client.portfolios.toList().map { it.name }

Terminal operations such as count(), first(), firstOrNull(), and single() do not require any additional changes.

Attach entities across transactions

Unlike the JDBC DAO, in R2DBC DAO an entity loaded or created in one transaction is not automatically registered with another R2DBC transaction. This is because that requires a database check and a property setter cannot suspend.

To reuse entities across transactions, call the attach() function before modifying an entity from another transaction:

val item = transaction {
    Item.new {
        name = "foo"
    }
}

transaction {
    item.name = "bar"
}

val item = suspendTransaction {
    Item.newSuspend {
        name = "foo"
    }
}

suspendTransaction {
    Item.attach(item)
    item.name = "bar"
}

The attach() function throws EntityNotFoundException if the row no longer exists. If the current transaction already caches a different instance of the same row with unflushed changes, attach() throws IllegalStateException.

To replace the cached instance and discard its unflushed changes, use attach(item, force = true).