With Doobie you manually write your queries, and then map the results into the objects in your domain model. Nothing is generated for you. OTOH, nothing is hidden and you are free to write queries as optimized and specialized as you need. The real selling point of Doobie is a typesafe API for manipulating and combining queries, and fragments of queries, into larger wholes. This works very well when your application interfaces with a database it doesn't own.
With Slick you get access to a DSL that lets you layout how your tables look. From there Slick offers an api that let's you treat SQL tables as-if they are basically mutable collections, with Slick handling all the SQL generation itself. You also get DDL, so that you can automate db creation and upgrades. This work very well when your application owns and controls the database it is connecting to.
Both of these have diverged from that traditional ORM model. Slick bills itself as FRM, or Functional Relational Mapping. And Doobie is embedded queries on steroids.
I don't remember what the problems were with Slick that leave such a bad feeling when I hear its name -- that was 5 years ago -- but I had problems with Quill just last year. I was trying to use it to generate an efficient "in" query against a two column composite primary key, and nothing seemed to work. Since it uses macro magic, one of my attempts triggered an internal compiler error instead of normal compiler feedback.
I ended up dropping Quill for ScalikeJDBC:
It seems to be less popular/active than other libraries, but it is dead simple to use, even for developers new to Scala. I write exactly the SQL I want just like I would in psql. There is little-to-no magic. I think that the only slightly magical feature I use is variable interpolation into SQL ("SQLInterpolation") that prevents injection attacks. It actually has capabilities to automatically map tables/columns into different structures and generate code for you, but my team doesn't use any of that. We just write SQL.
wow, the bar for "not dead" is really high these days :-)
AND 2019 wasn't really better. 3.3.2 (3.3.3 was mid 2020) was in JUNE 2019 and 3.3.0 was at the beginning of 2019. there won't be a 3.4 anytime soon, if nobody steps up and starts maintaining it, there won't be a 3.4 at all.
slick is heavy on macros it will not survive scala 3
Doobie (optionally with Quill Syntax) would be left and become likely the "Hibernate of Scala-Land".
That's not too bad news imho.
Having said that I think Quill looks nicer (https://getquill.io/) if you want a DSL like that.
https://tpolecat.github.io/doobie/docs/17-Quill.html
That's by far the best DB access layer I've ever worked with.
Having used Hibernate in a Java project, I would argue using Hibernate is always a mistake. Way too much complexity/brokenness for basically no benefit.
One (arguably) negative consequence of this is that Scala's collections don't play nice with Hibernate and similar ORMs. Due to the way they work, both Scala and Hibernate wanted to "take over" your collections, and obviously they cannot do so at the same time. Of course, it's arguable whether this is a flaw with Scala or with Hibernate, but for the programmers who just wants to get things done that's irrelevant.
It used to be a big deal a few years ago, to the point teams in my company decided to either drop Hibernate or drop Scala.
PS: if I remember correctly, early Scala adopters had reached the consensus that ORMs didn't play nice with Scala's FP style anyway, and so using Hibernate was a mistake. Possibly this attitude changed later.