Async DB access with vertx-mysql-postgresql-client

Recently (since vertx 3.6.3) a new submodule to access database asynchronously was released. In this post, I will give an example of how to use it.

Photo by Joshua Sortino on Unsplash

In previous vertx versions, when you want to access relational databases such as MySQL or PostgreSQL there were a couple of alternatives.

First one is the JDBC Client. JDBC is the standard when accessing databases in Java applications. However, in the reactive world, this is not an ideal option because the driver itself is blocking and any try to go around that will eventually get to some kind of a wrapper workers pool.

Second, If you’re using PostgreSQL it is possible to use the reactiverse client. I haven’t tried that but its performance looks promising.

Last, vertx support vertx-mysql-postgresql-client, which as the name implies supports both PostgreSQL and MySQL. Historically, this used Mauricio driver under the hood. But that driver is not supported anymore and has few open issues that I described in a couple of previous blog posts like this:

Sometimes a small bug-fix can lead to an avalanche of Pull Requests

Since version 3.6.3. vertx introduced a new module that uses jasync-sql which is sort of a drop-in replacement for Mauricio driver.

Show me the code

I wrote a simple example of how to use it. The code looks like this:

When a request is coming, using the MySQL client a connection is obtained, queries the database and show the result to the user.

You can see the full code here.

Once you start a MySQL server and run the example head over to http://localhost:8080/ and you should see something like this:

Got response 1

The new driver still lacks documentation but since it is compatible with the old driver all the documentation is the same: https://vertx.io/docs/vertx-mysql-postgresql-client/java/.

Just replace the maven dependency with:

<dependency>
    <groupId>io.vertx</groupId>
    <artifactId>vertx-mysql-postgresql-client-jasync</artifactId>
    <version>${project.version}</version>
</dependency>

You can also see the full example on the vertx examples github (still as a pull-request):

add sample for vertx-mysql-postgresql-client by oshai · Pull Request #333 · vert-x3/vertx-examples

Enjoy!