Issue with Hibernate Reactive & CockroachDB

[discordgr]

Hello,

I'm trying to build a simple crud application on quarkus with hibernate reactive & cockroachDB but it seems that every time i try to persist an object (Using the active record pattern) in the database , nothing happens. The thing is that when i used the pgclient, it behaved fine. Am i missing something obvious here?

I have a very simple class and a resource as shown below:

import io.quarkus.hibernate.reactive.panache.PanacheEntity;
import javax.persistence.Entity;

@Entity
public class Tile extends PanacheEntity {

    public String ownerId;
    public Long version;
    public int ACL;

}

import gr.panayiotis.model.Tile;
import io.smallrye.mutiny.Uni;
import org.jboss.resteasy.reactive.RestResponse;

import javax.enterprise.context.ApplicationScoped;
import javax.transaction.Transactional;
import javax.ws.rs.*;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;

@Path("/api/v1/tile")
@ApplicationScoped
@Produces(MediaType.APPLICATION_JSON)
public class TileResource {

    @GET
    @Path("{id}")
    public Uni<Response> readTileById(@PathParam("id") Long id){

        return Tile.<Tile>findById(id)
                .onItem().transform(t -> {
                    if ( t != null ){
                        return Response.ok(t).status(Response.Status.OK).build();
                    }else{
                        return Response.status(Response.Status.NOT_FOUND).build();
                    }
                })
                .onFailure().retry().atMost(3);
    }

    @POST
    public Uni<Response> createTile(){
        Tile tile = new Tile();
        tile.ownerId = "";
        tile.version = 1L;
        tile.ACL = 0;

        return tile.<Tile>persist()
                .onItem().transform(t -> Response.ok(t).status(Response.Status.CREATED).build())
                .onFailure().recoverWithItem(() -> Response.status(RestResponse.Status.NOT_IMPLEMENTED).build());
    }
}

My application properties are as follows:

quarkus.datasource.db-kind=postgresql
quarkus.datasource.username=root
quarkus.datasource.reactive.url=vertx-reactive:postgresql://localhost:26257/developdb
quarkus.hibernate-orm.database.generation = drop-and-create

This is a post request:

And this is a get request with id: 2

Also i used this class to init the database with one entry and see if i get a different behavior. The GET request worked fine for id: 1 but the POST request created another entry with id: 1.

@QuarkusMain
public class Main {

    public static void main(String[] args) {
        Quarkus.run(MyApp.class, args);
    }

    public static class MyApp implements QuarkusApplication {

        @Inject
        PgPool client;

        private void initdb() {
            client.query("DROP TABLE IF EXISTS tile").execute()
                    .flatMap(r -> client.query("CREATE TABLE tile (id INT8 NOT NULL,\n" +
                            "acl INT4 NOT NULL,\n" +
                            "ownerid VARCHAR(255) NULL,\n" +
                            "version INT8 NULL,\n" +
                            "CONSTRAINT \"primary\" PRIMARY KEY (id ASC),\n" +
                            "FAMILY \"primary\" (id, acl, ownerid, version))").execute())
                    .flatMap(r -> client.query("INSERT INTO tile (id, acl, ownerid, version) VALUES (1, 0, '', 1)").execute())
                    .await().indefinitely();
        }

        @Override
        public int run(String... args) throws Exception {
            initdb();
            Quarkus.waitForExit();
            return 0;
        }
    }

}

And this is my terminal in the cockroachDB cluster. With the pgclient, my table is updated and gets the entry with id 1. With the POST request, nothing happens on the table.

Could someone tell me what am i doing wrong?

[quarkus-bot[bot]]

/cc @DavideD, @Sanne, @gavinking

[gavinking]

I guess you need to use withTransaction() or flush().

[DavideD]

What's the point of the flush method then?

[DavideD]

I'm not against it but maybe we just need to improve the javadoc

[discordgr]

For what is worth, in my crud application with reactive mongo and Panache, that method was not required so i figured it would be the same with postgres. But i'm nowhere near experienced to know what's considered a good practice or not, or even why it is required.

[Sanne]

What's the point of the flush method then?

memory management: if you're accumulating lots of changes in memory, you might need to flush those to the database rather than keeping it all in the Session.

[DavideD]

Ah, right. Thanks