Having used it on an actual product and dealing with some of the pain points, it's my go-to since the typescript version (v5) came out.
The ORM uses Knex.js internally, which is very simple to drop into if you just want a query builder. Having Knex be accessible also makes it simple to just write your query in plain sql as well, or as the Lucid ORM has available, just fragments of your query (say the join statement) as raw sql: https://docs.adonisjs.com/reference/database/query-builder#w...
Along with debugging, printing out the sql, and support via the Adonisjs REPL "Ace", it makes for a very nice experience.