Data is the New Gold – How to Work with Databases in Quarkus

Photo by Rene Böhmer on Unsplash

By using Quarkus you can write lightweight and lightning-fast Java applications that scale on OpenShift/Kubernetes in a matter of milliseconds. But to achieve linear scalability and resilience of your application you should strive to create stateless applications. If you scale up multiple application instances that all connect to the same database, this will result in a bottleneck essentially halting all performance gain after a certain threshold.

While there are valid use cases for stateless applications especially in Microservice architectures, such as media processing or analysis steps, at the end of the day almost every application architecture will need to persist data at some point. Be it related to users, customers, product catalogs or even financial transactions. You can mitigate the performance impact of a singular database by either distributing data across datastores for each Microservice or decoupling write operations by leveraging streaming systems such as Kafka.

With these architectural considerations in mind, let’s look at how we can persist data in a Quarkus application.

Getting Started

Create a new Quarkus application by going to the Quarkus App Generator. You can keep the application details as is.

As we will use a Postgres database for this example we will add the JDBC Driver – PostgreSQL and the Agroal (Connection Pooling) extensions by checking the checkbox.

Next download the generated project, unzip it and open it in your favorite IDE.

First Connection

To set up an initial database connection and create a first database table to work with, we will add a class that drops and creates a table upon startup. For a more professional approach you may look at extensions such as Liquibase or Flyway.

Add class DBInitializer.java to you project in src/main/java/org/acme

package org.acme;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.enterprise.context.ApplicationScoped;
import javax.enterprise.event.Observes;
import javax.inject.Inject;
import javax.sql.DataSource;

import org.eclipse.microprofile.config.inject.ConfigProperty;

import io.quarkus.runtime.StartupEvent;

//Initialized a databe schema in case of a local DB test 
@ApplicationScoped
public class DBInitializer {

    @Inject
    @ConfigProperty(name = "myapp.schema.create", defaultValue = "false")
    boolean schemaCreate;

    @Inject
    DataSource dataSource;

    void onStart(@Observes StartupEvent ev) {

        if (schemaCreate)
            initdb();
    }

    private void initdb() {

        System.out.println("Init DB");

        try (Connection connection = dataSource.getConnection()) {

            try (Statement stmt = connection.createStatement()) {

                stmt.execute("DROP TABLE IF EXISTS particles");

                stmt.execute("CREATE TABLE particles (name VARCHAR(255))");

                ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS rowcount FROM particles");

                rs.next();

                System.out.println("Table contains " + rs.getInt("rowcount") + " rows");

            } catch (SQLException e) {
                System.out.println("Error processing statement " + e);
            }

        } catch (SQLException e) {
            System.out.println("Error processing connection " + e);
        }
    }

}Code language: JavaScript (javascript)

We are listening to the startup event of the application and then execute some SQL statements directly on the datasource.

Notice the config property myapp.schema.create. This is set to false by default to prevent dropping and creating tables in production but for our local test we will activate it in local Dev mode by adding to src/main/resources/application.properties

%dev.myapp.schema.create=trueCode language: JavaScript (javascript)

As this property is now active, DBInitializer will drop can recreate a simple table called particles and do a quick sanity-check on the number of rows by doing a SQL Select.

Using the JDBC driver directly is of course quite low-level and but can sometimes be useful to have full control over the database.

In the next chapters we will learn to work with databases on a higher level but for now this is enough to do a first connection test.

Spinning up Your First Database

Normally you would need to setup a local instance of Postgres, configure it, add users, schemas etc. which can be very tedious, especially when you need to test several database versions or even types.

As developer joy is one of the biggest imperatives of the Quarkus framework, it has got you covered. There is a feature called DevServices that can automatically launch and wire up required databases in a local container runtime. Make sure you have a local Docker installation and your current user has privileges to access the daemon service.

Then go ahead and fire up your app in supersonic Quarkus Dev mode. On a terminal run:

$ mvn quarkus:dev

You should see something like this:

Without you having to configure anything a Postgres database was launched in a container and the JDBC-connection was all setup automatically. Isn’t that magic?

JPA Made Simple with Panache

As an app dev you will usually want to work with Java objects and let a JPA framework figure out the heavy lifting. Quarkus brings an extension that makes handling of Entities even even easier. Let’s add Panache to your app. You can even do this in another terminal while your app is still running.

./mvnw quarkus:add-extension -Dextensions="io.quarkus:quarkus-hibernate-orm-panache"

Add a Panache entity to persist particle objects called ParticleEntity.java

package org.acme;

import javax.persistence.Entity;

import io.quarkus.hibernate.orm.panache.PanacheEntity;

@Entity
public class ParticleEntity extends PanacheEntity {
    public String name;

    @Override
    public String toString() {
        return "ParticleEntity [name=" + name + "]";
    }
    
}
Code language: CSS (css)

Change the sample GreetingResource.java to add a list and an add restful endpoints for our particles. For brevity we are working with String responses here but you can of course add JSON support quite easily.

package org.acme;

import javax.transaction.Transactional;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;

@Path("/hello-particles")
public class GreetingResource {

    @GET
    @Produces(MediaType.TEXT_PLAIN)
    public String hello() {
        return "Hello Particle";
    }


    @GET
    @Path("/particles")
    @Produces(MediaType.TEXT_PLAIN)
    public String getAllParticles() {
        return ParticleEntity.listAll().toString();
    }

    @Transactional
    @POST
    @Path("/particle/{name}")
    @Produces(MediaType.TEXT_PLAIN)
    public String addParticles(@PathParam("name") String name) {

        ParticleEntity particleEntity = new ParticleEntity();
        particleEntity.name = name;
        particleEntity.persist();
        return particleEntity.toString();
    }


}Code language: JavaScript (javascript)

Now all that is left to do is to tell Panache to handle the database schema. So we will deactivate our DBInitalizer and just let Panache handle the drop-and-create.

In application.properties set:

%dev.myapp.schema.create=false
%dev.quarkus.hibernate-orm.database.generation = drop-and-createCode language: JavaScript (javascript)

Now call you new endpoint to list your particles.

$ curl http://localhost:8080/hello-particles/particles
[]%                                                                Code language: JavaScript (javascript)

We seem to be all out of particles, so let’s add one:

$ curl -X POST http://localhost:8080/hello-particles/particle/atom 
ParticleEntity [name=atom]%                                       Code language: JavaScript (javascript)

Call the list again:

$ curl http://localhost:8080/hello-particles/particles
[ParticleEntity [name=atom]]%                                      Code language: JavaScript (javascript)

And there is our persisted particle straight from the database.

What about Hibernate?

Since Panache is built on top of Hibernate you can of course use this JPA framework directly, for example by injecting an EntityManager like this

@Inject
    EntityManager em; Code language: CSS (css)

and defining ParticleEntitity.java the Hibernate way:

package org.acme;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;

@Entity
public class ParticleEntity {
    private Long id;
    private String name;

    @Id
    @SequenceGenerator(name = "particleSeq", sequenceName = "particle_id_seq", allocationSize = 1, initialValue = 1)
    @GeneratedValue(generator = "particleSeq")
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "ParticleEntity [id=" + id + ", name=" + name + "]";
    }
}Code language: JavaScript (javascript)

As well as setting our GreetingResource.java methods to use the EntityManager:

package org.acme;

import javax.inject.Inject;
import javax.persistence.EntityManager;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.transaction.Transactional;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;

@Path("/hello-particles")
public class GreetingResource {

    @Inject
    EntityManager em;

    @GET
    @Produces(MediaType.TEXT_PLAIN)
    public String hello() {
        return "Hello Particle";
    }


    @GET
    @Path("/particles")
    @Produces(MediaType.TEXT_PLAIN)
    public String getAllParticles() {
        CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
        CriteriaQuery<ParticleEntity> query = criteriaBuilder.createQuery(ParticleEntity.class);
        query.from(ParticleEntity.class);
        return em.createQuery(query).getResultList().toString();
    }

    @Transactional
    @POST
    @Path("/particle/{name}")
    @Produces(MediaType.TEXT_PLAIN)
    public String addParticles(@PathParam("name") String name) {

        ParticleEntity particle = new ParticleEntity();
        particle.setName(name);
        em.persist(particle);
        return particle.toString();
    }


}Code language: JavaScript (javascript)

Test your restful endpoints again and you should now have persistence running with plain Hibernate.

Going Reactive

But how about taking your database access performance to the next level? The regular JDBC driver will use as many threads from the connection pool as you have specified and if these are blocked, all other access calls will have to wait.

The reactive database driver will hand off connections from the thread to make sure there is no blocking, resulting in better performance.

To switch to Hibernate Reactive change your extensions to the reactive versions, so your Maven dependencies look like this in the pom.xml:

<dependencies> 
    <dependency>
      <groupId>io.quarkus</groupId>
      <artifactId>quarkus-junit5</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>io.rest-assured</groupId>
      <artifactId>rest-assured</artifactId>
      <scope>test</scope>
    </dependency>   
    <dependency>
      <groupId>io.quarkus</groupId>
      <artifactId>quarkus-hibernate-reactive-panache</artifactId>
    </dependency>
    <dependency>
        <groupId>io.quarkus</groupId>
        <artifactId>quarkus-reactive-pg-client</artifactId>
    </dependency>
    <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-resteasy-reactive</artifactId>
    </dependency>
  </dependencies>Code language: HTML, XML (xml)

Make sure to delete DBInitalizer.java as not to interfere with the reactive driver!

Then in the GreetingResource.java inject a new Mutiny Session. Mutiny is the reactive framework from the Smallrye project. Change the two methods to use the reactive database interface and retrieve an Uni object. Uni is an asynchronous response object. As we have added the Reactive Resteasy Extension as well, we can just return the Uni(s) directly.

package org.acme;

import java.util.List;

import javax.inject.Inject;
import javax.transaction.Transactional;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;

import org.hibernate.reactive.mutiny.Mutiny;

import io.smallrye.mutiny.Uni;

@Path("/hello-particles")
public class GreetingResource {

    @Inject
    Mutiny.Session mutinySession;


    @GET
    @Produces(MediaType.TEXT_PLAIN)
    public String hello() {
        return "Hello Particle";
    }

    @GET
    @Path("/particles")
    @Produces(MediaType.TEXT_PLAIN)
    public Uni<List<ParticleEntity>> getAllParticles() {
        return mutinySession
                .createNamedQuery( "Particles.findAll", ParticleEntity.class )
                .getResultList();
    }

    @Transactional
    @POST
    @Path("/particle/{name}")
    @Produces(MediaType.TEXT_PLAIN)
    public Uni<Response> addParticles(@PathParam("name") String name) {

        
        ParticleEntity particle = new ParticleEntity();
        particle.setName(name);

        return mutinySession
                .persist(particle)
                .chain(mutinySession::flush)
                .map(ignore -> Response.ok(particle).status(201).build());
    }

}Code language: JavaScript (javascript)

As last step add a NamedQuery to ParticleEntity.java so we can retrieve the list of particles more easily.

...
@Entity
@NamedQuery(name = "Particles.findAll", query = "SELECT p FROM ParticleEntity p ORDER BY p.name")
public class ParticleEntity {
    private Long id;
    private String name;

...Code language: PHP (php)

Now test adding a new Particle and retrieve a list again. We are in full high-performance, reactive mode now.

Connecting to a Live Database

Up to now we have use the DevService database, but for deployment you will want to use specific connection details.

You can add these to the application.properties and with the %prod prefix and they will only become active when not in Dev mode.

For example :

%prod.quarkus.datasource.db-kind = postgresql
%prod.quarkus.datasource.username = hibernate
%prod.quarkus.datasource.password = hibernate
%prod.quarkus.datasource.jdbc.url = jdbc:postgresql://localhost:5432/hibernate_dbCode language: JavaScript (javascript)

Next Steps

This was a walkthrough over some of the different options you have when persisting data from your supersonic, subatomic Quarkus app. It is all about choice so choose the approach that works best for your use case and your experience.

You may also want to look at the integration of NoSQL databases such as MongoDB which are popular with Microservice apps. If you looking for even more performance for your lightning fast app, you can even setup Quarkus to additionally to use a shared Infinispan InMemory Cache before your database.