I am looking to port some NodeJS code to Kotlin, h...

# getting-started

[Arjan van Wieringen]

I am looking to port some NodeJS code to Kotlin, however I am running into issues with selecting the DB technology. Normally I'd just go to Exposed, but the code has some pretty complicated Postgres queries in there and we are used to write 'raw' queries (of course, with SQL injection escaping). For typical crud we use an ORM or DAO, but some queries require calling custom postgres functions or use complex features I couldn't directly find in Exposed.

In NodeJS we typically have access to different layers:
• 'raw' connection(pool) where we can do things like connection.query("select x from y where z = $1", values)
• queryBuilder to built it in a more convenient way connection.queryBuilder.select('x').from('y').where('z', 'foo')
• orm layer
These are all covered by the pg package and maybe a small ORM on top.

I've looked at Exposed, jOOQ, and subsequently JDBC and R2DBC as underlying tech. Correct me if I am wrong but I have noticed the following:
• R2DBC is not really 'popular' or used? Is there a reason that there isn't yet a great coroutine based Postgres or more generic DB driver? Exposed is JDBC based and as such locking threads. Since JDBC is still highly dominant I suspect it isn't that big of an issue actually in most cases?
• Exposed doesn't support raw prepared statements?
I can get there for 90% with Exposed, but the problem I have with DSL's like Exposed or jOOQ is that they almost never fully cover a DB dialect (for instance, jOOQ doesn't support the Postgres JSONB syntax). Therefore I always want an exit hatch to go to SAFE raw queries using prepared statements.

What would be the proper way forward? Please mind, it can be completely true that I've overlooked something....

EDIT: Sigh, after tying I see that jOOQ almost covers all my use-cases for the first and second bullet :D

[John Smith]

r2dbc is fine, I've used it in the past with a significant load, after some early hiccups, it became quite stable
jdbc with coroutines is fine as well, if you create a wrapper of your own and run on a set of dedicated threads, you won't get too many threads, since your connection pool is still limited

[John Smith]

for jooq/jsonb operators, you can create your own quite easily
for example, an operator to check if a certain jsonb string field is equal to some other string:
DSL.field("{0}->>{1}", String::class.java, field, name).eq(value)
where field is Field<JSONB>

[Arjan van Wieringen]

Thanks! I think I’ll go to JDBC for now with jOOQ on top.

[John Smith]

yup, especially when virtual threads will become actually usable, switching from regular threads to virtual ones will be super easy

[Arjan van Wieringen]

I see that pgjdbc already switched to reentrant locks

[dave08]

You could also try #C03JF82SDHA