In my current project (work) I've chosen to use JDBI for communication between model and database. For me it is the perfect level of abstraction whereby it still feels like I'm just writing SQL queries but also avoiding a lot of the boilerplate code that comes with having to manually manage DB connections.

I still have to write the actual code that maps the result set to objects, but I've found this is a very small tax to pay, especially with Kotlin's data classes.

I used an ORM in a previous job (RoR ActiveRecord). I didn't find it an altogether horrible experience, but there where many cases where we would get these 'leaky abstractions' in the wrong direction from the model -> database schema. There were also a lot of cases where we would realise that some ActiveRecord query we were doing was unintentionally loading entire tables into memory (our fault, not ActiveRecord's fault). This was usually remedied quite easily by just RTFMing the docs, but my feeling is we could have avoided it in the first place if we'd used a lower level strategy.

Personally, when developing a new feature I always like to start by thinking about the database representation first and then working my way up. I think having this sensitivity to how it should be represented in the database can allow you to avoid many of the pitfalls that may come with using a more opaque ORM.

Another valuable attribute I get from using JDBI is it's dead easy to mock (like heavyweight ORMs), so unit testing stuff that interacts with it is super simple.