Hibernate Native Query With Panache Repository In Reactive Quarkus (With Mutiny) for PostgreSQL Timescale DB

I am try to write native query to use the features of the PostgreSQL Timescale DB in quarkus application. This application use reactive features with Munity.

https://docs.timescale.com/api/latest/hyperfunctions/last/

This is the table of the DB

CREATE TABLE IF NOT EXISTS telemetry_data
(
    telemetry_data_id serial NOT NULL,
    telemetry_attribute_id integer NOT NULL,
    date_time timestamp without time zone NOT NULL,
    data_value float
);

SELECT create_hypertable('telemetry_data', 'date_time');

CREATE UNIQUE INDEX idx_telemetry_attribute_id_date_time
ON telemetry_data(telemetry_attribute_id, date_time);

ALTER TABLE IF EXISTS public.telemetry_data
    ADD FOREIGN KEY (telemetry_attribute_id)
    REFERENCES telemetry_attribute (telemetry_attribute_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

I am try to use native Query for this application to use this time scale DB features. This is the query I want to run.

SELECT 
first(telemetry_data_id, date_time) FILTER(WHERE date_time IS NOT NULL) AS beg_temp,
last(telemetry_data_id, date_time) FILTER(WHERE date_time IS NOT NULL) AS end_temp
FROM telemetry_data
WHERE date_time >= '2023-06-21 14:52:28' 
AND telemetry_attribute_id = 44

This is the Panache Repository class I am using in my quarkus application. How I can build this Query. But the thing is I want to use this and return a Uni.

package abc...........;

import abc.....TelemetryData;
import abc.....Result;
import io.quarkus.hibernate.reactive.panache.PanacheRepositoryBase;
import io.quarkus.panache.common.Parameters;
import io.smallrye.mutiny.Uni;

import javax.enterprise.context.ApplicationScoped;
import javax.persistence.EntityManager;

@ApplicationScoped
public class TelemetryDataRepository implements PanacheRepositoryBase<TelemetryData, Integer> {

    private final EntityManager entityManager;

    public TelemetryDataRepository(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

   public Uni<Result> getAbc() {

        String nativeQuery = "SELECT \n" +
                "first(telemetry_data_id, date_time) FILTER(WHERE date_time IS NOT NULL) AS beg_temp,\n" +
                "last(telemetry_data_id, date_time) FILTER(WHERE date_time IS NOT NULL) AS end_temp\n" +
                "FROM telemetry_data\n" +
                "WHERE date_time >= '2023-06-21 14:52:28' \n" +
                "AND telemetry_attribute_id = 44 ";

        return entityManager.createNativeQuery(nativeQuery, Result.class).getSingleResult();

    }

}

I think is it not ok to create Uni object using this result and return it? Some one cal help? Thank you...

The problem is that you are injecting the JPA EntityManager (not reactive), you should use the Mutiny.SessionFactory or Panache.withSession:

With Panache:

   public Uni<Result> getAbc() {
        String nativeQuery =  ...;

        return Panache.withSession( session -> session
            .createNativeQuery( nativeQuery ).getSingleResultOrNull()
        );
   }
}

With regular Hibernate Reactive:

...
import org.hibernate.reactive.mutiny.Mutiny;
import jakarta.inject.Inject;

   ...

   @Inject
   Mutiny.SessionFactory sf

   public Uni<Result> getAbc() {
        String nativeQuery =  ...;

        return sf.withSession( session -> session
            .createNativeQuery( nativeQuery ).getSingleResultOrNull()
        );
    }

}