Exposed Vert.x SQL Client

Execute Exposed statements with Vert.x Reactive SQL Client

Supported DBs

• PostgreSQL with Reactive PostgreSQL Client and Exposed PostgreSQL support
• MySQL with Reactive MySQL Client and Exposed MySQL support
• Oracle with Reactive Oracle Client and Exposed Oracle support
• Microsoft SQL Server with Reactive MSSQL Client and Exposed SQL Server support

Important notes

Experimental

This library is experimental now. The APIs are subject to change (especially those marked with @ExperimentalEvscApi).

Exposed DAO APIs are not supported

Brief overview of the implementation

This library works by first producing the prepared SQL from an Exposed Statement with an Exposed transaction, then translating and passing the SQL to the Vert.x SQL client for execution, and finally transforming the retrieved result Vert.x SQL client Row into the Exposed ResultSet. With the JdbcTransactionExposedTransactionProvider (recommended), a single JDBC transaction can be reused across multiple SQL preparation calls in a single thread/Verticle for better performance; with the DatabaseExposedTransactionProvider (fallback), the Exposed transaction for preparing a SQL is as short and as lightweight as possible to improve performance. And also when executing without a transaction, Vert.x SQL client's pipelining feature can be enabled, which greatly improves performance for simple queries and is not supported by JDBC and R2DBC for PostgreSQL as far as I know.

Performance

TechEmpower Framework Benchmarks

The requests per second results for the related portions from the TechEmpower Framework Benchmarks Continuous Benchmarking results started on 2026-02-20 are as follows:

Benchmark portion	Description	Single query	Multiple queries	Fortunes	Data updates
vertx-web-kotlinx-postgresql	Vert.x baseline	1,214,765	82,517	788,562	45,345
vertx-web-kotlinx-exposed-vertx-sql-client-postgresql	Vert.x with this library	1,053,504 (87%)	85,582 (104%)	651,353 (83%)	45,741 (101%)
vertx-web-kotlinx-exposed-r2dbc-postgresql-separate-pool-size-8	Vert.x with Exposed R2DBC directly (replacing the Vert.x SQL client)	206,403 (17%)	25,358 (31%)	140,590 (18%)	9,617 (21%)
vertx-web-kotlinx-r2dbc-postgresql	Vert.x with R2DBC (replacing the Vert.x SQL client), for comparison	475,865 (39%)	30,344 (37%)	453,820 (58%)	13,252 (29%)
ktor-netty-exposed-jdbc-dsl	Ktor with Exposed JDBC	179,734 (15%)	29,846 (36%)	144,771 (18%)	24,154 (53%)
ktor-netty-exposed-r2dbc-dsl	Ktor with Exposed R2DBC	104,288 (9%)	21,448 (26%)	75,634 (10%)	7,140 (16%)

Based on the requests-per-second numbers above, with the JdbcTransactionExposedTransactionProvider introduced in v0.8.0, this library achieves 87% of the baseline throughput in Single query and 83% in Fortunes (a single SQL select query of all the records with manipulation and encoding to HTML in each request), and matches the baseline in Multiple queries (20 separate select SQL queries in each request) and Data updates (20 updates per request).

Since TFB recently sunset, you can verify this yourself by cloning the repo and running:

./tfb --test vertx-web-kotlinx-exposed-vertx-sql-client-postgresql vertx-web-kotlinx-postgresql

Add to your dependencies

The Maven coordinates

"com.huanshankeji:exposed-vertx-sql-client-$module:$libraryVersion"

Important note : compatibility with Exposed

If you encounter issues likely caused by compatibility with Exposed, please try using the same version of Exposed this library depends on. The current development version (v0.8.1-SNAPSHOT) depends on Exposed v1.1.1, while the latest released version v0.8.0 depends on Exposed v1.0.0.

API documentation

See the hosted API documentation for the APIs.

Basic usage guide

Here is a basic usage guide (since v0.5.0).

Add the dependencies

Add the core module to your dependencies with the Gradle build script:

implementation("com.huanshankeji:exposed-vertx-sql-client-core:$libraryVersion")

And add an RDBMS module, for example, the PostgreSQL module:

implementation("com.huanshankeji:exposed-vertx-sql-client-postgresql:$libraryVersion")

Create a DatabaseClient

Create an EvscConfig as the single source of truth:

val evscConfig = ConnectionConfig.Socket("localhost", user = "user", password = "password", database = "database")
    .toUniversalEvscConfig()

Local alternative with Unix domain socket:

val evscConfig = defaultPostgresqlLocalConnectionConfig(
    user = "user",
    socketConnectionPassword = "password",
    database = "database"
).toPerformantUnixEvscConfig()

Create an Exposed Database with the ConnectionConfig.Socket, which can be shared and reused in multiple Verticles:

val exposedDatabase = evscConfig.exposedConnectionConfig.exposedDatabaseConnectPostgresql()

Create a Vert.x SqlClient with the ConnectionConfig, preferably in a Verticle:

val sqlClient = createPgClient(vertx, evscConfig.vertxSqlClientConnectionConfig)
val pool = createPgPool(vertx, evscConfig.vertxSqlClientConnectionConfig)
val sqlConnection = createPgConnection(vertx, evscConfig.vertxSqlClientConnectionConfig)

Create a DatabaseClient with the provided Vert.x SqlClient and a transaction provider, preferably in a Verticle:

val databaseClient = DatabaseClient(
    vertxSqlClient,
    PgDatabaseClientConfig(JdbcTransactionExposedTransactionProvider(exposedDatabase))
)

About StatementPreparationExposedTransactionProvider:

The DatabaseClient uses a StatementPreparationExposedTransactionProvider to manage Exposed transactions for SQL statement preparation. There are two options:

• JdbcTransactionExposedTransactionProvider (recommended): Reuses a single JDBC transaction for all SQL preparation calls. This approach provides better performance by avoiding the overhead of creating a new transaction for each SQL preparation. This is the recommended option for most use cases.

  Note: This depends on a closed Transaction (properties and functions used including identity and .db.dialect etc.). It's not guaranteed that Exposed APIs won't change in the future, and creating Statements and calling prepareSQL may require an open Transaction based on a connection in future Exposed versions. It also depends on the withThreadLocalTransaction API which is marked @InternalApi at the moment.

• DatabaseExposedTransactionProvider: Creates a new transaction for each SQL preparation call. This is kept as a fallback solution in case the JdbcTransactionExposedTransactionProvider approach has issues with future Exposed API changes (see note above).

Alternatives to EvscConfig

The EvscConfig was initially designed to incorporate support for Unix domain sockets, is currently experimental and subject to change, and may be overkill for some use cases.

Alternative 1: use ConnectionConfig directly

If you don't use Unix domain sockets in your code, you can create a ConnectionConfig.Socket directly as the single source of truth:

val connectionConfig = ConnectionConfig.Socket("localhost", user = "user", password = "password", database = "database")

Alternative 2: use the original Exposed and Vert.x APIs

In a more custom way, you can create the Exposed Database with the original Database.connect APIs, and the Vert.x SqlClient with the original ***Builder and ***Connection APIs.

Example table definitions

object Examples : IntIdTable("examples") {
    val name = varchar("name", 64)
}

val tables = arrayOf(Examples)

Use exposedTransaction or transaction from Exposed to execute original blocking Exposed code

For example, to create tables:

databaseClient.exposedTransaction {
    SchemaUtils.create(*tables)
}

You can also use the transaction API from Exposed directly:

transaction(exposedDatabase) {
    SchemaUtils.create(*tables)
}

Or use the thread-local Database instance implicitly following Exposed conventions:

transaction {
    SchemaUtils.create(*tables)
}

If you execute blocking Exposed statements inside Verticles or event loop threads that you shouldn't block, you should use Vert.x Vertx.executeBlocking or Coroutines Dispatchers.IO.

CRUD (DML and DQL) operations with DatabaseClient

Core APIs

With these core APIs, you create and execute Exposed Statements. You don't need to learn many new APIs, and the Statements are more composable and easily editable. For example, you can move a query into an adapted subquery.

val insertRowCount = databaseClient.executeUpdate(buildStatement { Examples.insert { it[name] = "A" } })
assert(insertRowCount == 1)
// `executeSingleUpdate` function requires that there is only 1 row updated and returns `Unit`.
databaseClient.executeSingleUpdate(buildStatement { Examples.insert { it[name] = "B" } })
// `executeSingleOrNoUpdate` requires that there is 0 or 1 row updated and returns `Boolean`.
val isInserted = if (dialectSupportsInsertIgnore)
    databaseClient.executeSingleOrNoUpdate(buildStatement { Examples.insertIgnore { it[name] = "B" } })
else
    databaseClient.executeSingleOrNoUpdate(buildStatement { Examples.insert { it[name] = "B" } })
assert(isInserted)

val updateRowCount =
    databaseClient.executeUpdate(buildStatement { Examples.update({ Examples.id eq 1 }) { it[name] = "AA" } })
assert(updateRowCount == 1)

// The Exposed `Table` extension function `select` doesn't execute eagerly so it can also be used directly.
val exampleName = databaseClient.executeQuery(Examples.select(Examples.name).where(Examples.id eq 1))
    .single()[Examples.name]
assert(exampleName == "AA")

databaseClient.executeSingleUpdate(buildStatement { Examples.deleteWhere { id eq 1 } })
if (dialectSupportsDeleteIgnore) {
    val isDeleted =
        databaseClient.executeSingleOrNoUpdate(buildStatement { Examples.deleteIgnoreWhere { id eq 2 } })
    assert(isDeleted)
}

Transaction and savepoint APIs (experimental)

Experimental transaction and savepoint APIs named with* are provided based on the existing ones in Vert.x SQL Client. A transaction or savepoint is rolled back automatically when an exception is thrown inside. Note that Vert.x SQL Client pipelining is not supported with transactions.

Extension CRUD DSLs

The extension CRUD DSL APIs are similar to those in Exposed. With them, your code becomes more concise compared to using buildStatement, but it might be more difficult when you need to share Exposed Statements especially Querys for reuse and composition, for example, when adapting a DatabaseClient.select extension DSL call for reuse as a subquery. In such a case, you may inline the DatabaseClient.select with IntelliJ IDEA into code that invokes Exposed's select DSL and pass it to DatabaseClient.executeQuery, and then extract the Query built with Exposed's select.

Also, these APIs are more experimental and subject to change because of transaction requirement changes between Exposed versions.

Gradle dependency configuration:

implementation("com.huanshankeji:exposed-vertx-sql-client-crud:$libraryVersion")

Example code:

databaseClient.insert(Examples) { it[name] = "A" }
if (dialectSupportsInsertIgnore) {
    val isInserted = databaseClient.insertIgnore(Examples) { it[name] = "B" }
    assert(isInserted)
} else
    databaseClient.insert(Examples) { it[name] = "B" }

val updateRowCount = databaseClient.update(Examples, { Examples.id eq 1 }) { it[name] = "AA" }
assert(updateRowCount == 1)

val exampleName1 =
    databaseClient.select(Examples, { select(Examples.name).where(Examples.id eq 1) }).single()[Examples.name]
assert(exampleName1 == "AA")
val exampleName2 =
    databaseClient.selectSingleColumn(Examples, Examples.name, { where(Examples.id eq 2) }).single()
assert(exampleName2 == "B")

if (dialectSupportsExists) {
    val examplesExist = databaseClient.selectExpression(exists(Examples.selectAll()))
    assert(examplesExist)
}

val deleteRowCount1 = databaseClient.deleteWhere(Examples) { id eq 1 }
assert(deleteRowCount1 == 1)

if (dialectSupportsDeleteIgnore) {
    val deleteRowCount2 = databaseClient.deleteIgnoreWhere(Examples) { id eq 2 }
    assert(deleteRowCount2 == 1)
}

Highly experimental: Extension CRUD DSLs with Exposed GADT mapping

Please read that library's basic usage guide first. Here are examples of this library that correspond to that library's CRUD operations.

These APIs are also highly experimental and subject to change.

Gradle dependency configuration (only needed since v0.5.0):

implementation("com.huanshankeji:exposed-vertx-sql-client-crud-with-mapper:$libraryVersion")

Example code:

val directorId = 1
val directorDetails = DirectorDetails("George Lucas")
databaseClient.insertWithMapper(Directors, directorDetails, Mappers.directorDetails)

val episodeIFilmDetails = FilmDetails(1, "Star Wars: Episode I – The Phantom Menace", directorId)
// insert without the ID since it's `AUTO_INCREMENT`
databaseClient.insertWithMapper(Films, episodeIFilmDetails, Mappers.filmDetailsWithDirectorId)

val filmId = 2
val episodeIIFilmDetails = FilmDetails(2, "Star Wars: Episode II – Attack of the Clones", directorId)
val filmWithDirectorId = FilmWithDirectorId(filmId, episodeIIFilmDetails)
if (dialectSupportsIdentityInsert)
    databaseClient.insertWithMapper(Films, filmWithDirectorId, Mappers.filmWithDirectorId) // insert with the ID
else
    databaseClient.insertWithMapper(Films, episodeIIFilmDetails, Mappers.filmDetailsWithDirectorId)

val fullFilms = databaseClient.selectWithMapper(filmsLeftJoinDirectors, Mappers.fullFilm) {
    where(Films.filmId inList listOf(1, 2))
}
assert(fullFilms.size() == 2)

Common issues

"No transaction in context."

If you encounter java.lang.IllegalStateException: No transaction in context. in your code, inspect the exception stacktrace and try these options:

1. wrap the Statement creation call with databaseClient.statementPreparationExposedTransaction { ... }.

   For example, this can happen if you call Query.forUpdate() without a transaction. In such a case, you can also use our Query.forUpdateWithTransaction() instead.

2. If your function call has a parameter with WithExposedTransaction in its name, try setting it to true. To make things easier, you can also set autoExposedTransaction to true in DatabaseClientConfig when creating the DatabaseClient. Note that when using DatabaseExposedTransactionProvider, this slightly degrades performance, but with JdbcTransactionExposedTransactionProvider (recommended), the overhead is minimal.

Some Exposed APIs implicitly require a transaction and we can't guarantee that such exceptions are always avoided, as Exposed APIs are not fully decoupled and designed to serve this library, the transaction requirements in APIs sometimes change between versions and our APIs may need to evolve accordingly.