CRUD REST API Using Vert.x and PostgreSQL in Kotlin

In the previous article, I have shown you how to build a simple REST API using Vert.x. This time we will make a further step which we will build a CRUD REST API and save the data to our database using PostgreSQL. Without any further ado, let’s get started.

Before you start, I would suggest you to copy the code and paste it to your favorite code editor then read it side by side with the explanation below. Hopefully it can help you to digest the code.

* Updated in Jun 30:
- Refactor the code (chaining emission) in RxJava way
- Update several explanation as well

Reactive PostgreSQL client

First thing first we must add PostgreSQL client dependency in our build.gradle in order to call the PostgreSQL function on our code.

As you can see on the line 45, we add vertx-pg-client there. Then on the line 48 is optional if you using Scram SASL SCRAM-SHA-256 in your PostgreSQL authentication.

Modify HttpServerVerticle.kt to implement PostgreSQL client

We will add several things here, first we will add the connection config — line 19 until 26, second we will add a new instance of pool client — line 28 until 31.

On the line 19, we use PgConnectOptions() to set the appropriate connection config. Then line 28 we use PgPool.newInstance() to create new pool client instance.

Then the rest of the code, we implement the PostgreSQL query execution on each methods. But before we execute our query, we need to acquire a connection from the pool. As you can see on line 52, we call pgPoolClient.rxGetConnection() — anyway I use RxJava in order to avoid the callback hell. This method will return the SqlConnection instance, then we can call the query() method from it. To execute the query, we must call .rxExecute() — Why rxExecute? Because we use ReactiveX Java library, this is optional though.

If you execute the SELECT query then the call .rxExecute() method will return the set of row which has been retrieved. You must itterate each rows to get the data inside it — line 58. We call .map() method to retrieve the emission from rxExecute and we map each data to JsonArray and return its value. Then we call .doFinally() — this method will return the emission from previous method — and call sqlConnection.close() to close the connection.

If you have a condition inside your query and need to pass a value to it then you can use SqlConnection.preparedQuery() method. The SQL string can refer to parameters by position, using the database syntax `$1`, `$2`, etc — you can see on line 112. After that you must pass the parameters inside .rxExecute() method.

And also don’t forget that you must call SqlConnection.close(), everytime you’ve got the data from the query execution. Because if we don’t close it then everytime we call .rxGetConnection(), it will use the pool resource, so then if there is no more pool then it will raise an error or your sql connection will hang until it can find a free pool resource.

You can found the complete source on https://github.com/merizrizal/vertx-kotlin-rest-api/tree/crud-postgresql