Structure of quarkus with jooq

Question

I have app backend on php. But recently i created same backend on Quarkus and deployed it on kubernetes with database MYSQL. I'm routing half users on php backend and half qaurkus from app. PHP backend working fine but have issue with quarkus backend it stuck after few hours and request latency increase more than one minute and sometimes timeout. Well after checking some stacks mostly i see that it stuck at getConnection in datasource I'm suing agroal. Its not database issue because meanwhile if i check app with php that is working fine without any issue or delay. Also i tried increasing and decreasing min/max pool size but still same issue. I will share my structure of using quarkus, agroal and jooq with mysql. I think issue is with that.

I'm using kotlin and DSLContext is object which is static.

Application.kt

@ApplicationScoped
class AppLifecycleBean {
    @Inject
    lateinit var dsl: DSLContext
    @Inject
    lateinit var launchMode: LaunchMode

    fun onStart(@Observes ev: StartupEvent?) {
        DBHelper.init(dsl)
    }

    fun onStop(@Observes ev: ShutdownEvent?) {
    }

}

DBHelper.kt

object DBHelper {
    lateinit var db: DSLContext
    private set

    fun init(db: DSLContext){
        this.db = db
    }
}

As you can see I'm initializing JOOQ at start of application as singleton and then setting its instance in object in DBHelper to keep using it instead creating new. So i doubt may be that is issue do i need to create new instance of DSLContet everytime by injecting. Then i created models to access database like this

    open class Model {
        protected val db
        get() = DBHelper.db
    }
object UserModel: Model() {
   fun getUserbyId(id: Int){
       db.selectFrom.......
   }
}

At last I'm accessing models like this

@Path("/mobile/user")
@Produces(MediaType.APPLICATION_JSON)
class UserApiController() : Controller() {
    @GET
    @Path("/get_profile")
    fun getProfile(
            @QueryParam("user_id") userId: Int
    ): Response {
        val user = UserModel.getbyId(userId)
        return Response.success(data = user)
    }
}

I think some where resources leak

Answer 1

Consider injecting the DataSource bean instead of using DSLContext and allowing the connection pool to manage the connections.

If you are not already using it, I suggest using the jooq-quarkus extension, which can be found at:

https://github.com/quarkiverse/quarkus-jooq

Additionally, please refer to this article on preventing leaks and using DataSource from jooq:

https://blog.jooq.org/how-to-prevent-jdbc-resource-leaks-with-jdbc-and-with-jooq/