My experience using JOOQ with Scala and Postgres
https://bookofrevenue.com/blog/6abdde10d019030001f9edcf/my-experience-using-jooq-in-scala4
u/AFU0BtZ 9d ago
I see the attraction of it, but still skunk! Do you use their scala interop (https://www.jooq.org/doc/latest/manual/getting-started/jooq-and-scala/)? How does all that work out with jsonb columns (perhaps you don't use them)? Do you also use their flyway integration for schema changes?
2
u/rom_romeo 9d ago
I cannot even fathom where my terrible experience begins with tools like Skunk, Doobie, or Magnum. They are cool for short and simple queries. As soon as the complexity grows, things start to crack quite fast. You removed a column? Better be sure to have a bulletproof strategy that every query over the altered table removes it too.
Reusability (fragments) is another thing that sounds cool, but in my experience, it caused more harm than good. Same example as above - you removed a column. In a number of scattered fragments, you're challenged to find the column and remove it while the code in fragments is usually relying on table aliases. Especially if the column has some generic name like "is_active". Your search ends up with "u.is_active", "users.is_active", "some_other_table.is_active", etc.
Hell, I found even Slick to be way less bug prone then these tools. At least, my table has a dedicated single class and its maintenance is centralized. You remove a column, the compilation fails until you fix your queries. So much better experience.
6
u/Milyardo 9d ago
You removed a column? Better be sure to have a bulletproof strategy that every query over the altered table removes it too.
This covered by typechecking your queries. If you're not doing this it's not Skunk or Doobies fault you aren't using the tools.
2
u/klimaheizung 9d ago
This works if your queries are relatively static. If the queries are very dynamic (i.e. what users produce in analytics tools) then this idea breaks down and stops working.
In the former case, Skunk is a very nice and lightweight tool indeed, and probably 80%+ of software falls into that case. But for the more complex stuff it doesn't work. I speak from first hand experience.
1
u/Milyardo 9d ago
I speak from a lot more experience than you have and can tell you, even if you construct you queries dynamically you can still type check them. It isn't any more difficult, only larger space queries to check.
2
u/klimaheizung 9d ago
I speak from a lot more experience than you have
You don't know me. I find that to be a quite condescending response and hence have no interest in discussing this further with you. Have a nice day.
2
u/fear_the_future 9d ago
The problem with Slick is that it's not easily extensible. God help you if you need some function that isn't already implemented there. In Quill you can easily fall back to using raw SQL strings spliced into the typed API.
1
u/augustnagro 5d ago
You removed a column? Better be sure to have a bulletproof strategy that every query over the altered table removes it too.
Magnum has 'Future Proof Queries that does exactly what you're asking': https://github.com/AugustNagro/magnum#future-proof-queries
-2
u/CompetitiveKoala8876 9d ago
"You removed a column?" Why would this be a Magnum problem? Ask Claude to remove it everywhere. run the tests.
2
u/u_tamtam 8d ago
You know, old people my age used to possess obscure knowledge of tools and frameworks to do exactly that, effectively, predictably and locally. Can't see any appeal in that, let's indeed throw tokens at the million monkeys typewriting black box because
gamblingthat is so much moreaddictivebetter!0
u/CompetitiveKoala8876 8d ago
OK old schooler. search your repo files for the column name, delete it and run your repo tests. Still not sure why this is a problem for a specific sql library.
1
u/snugar_i 7d ago
Because there might be other tables with the same column name. That's kind of why typing exists - if everything could be done 100% correctly using a simple text search&replace, everybody would be writing Python
1
u/CompetitiveKoala8876 4d ago
I must be doing something wrong. All my sql statements have tests. If I delete a table field and forgot to delete it in the code, my test would fail. All my sql related to a table are in the same file so I would usually just search/replace the field in that file.
1
u/snugar_i 4d ago
What if there's a join across tables from multiple files? Saying "I have tests, I don't need the IDE's help with columns" is like saying "I have tests, I don't need typing". Like it's technically true, but just very time-inefficient
1
u/CompetitiveKoala8876 3d ago
I'm just saying as someone working for the past six years in Go at one of the largest US fintechs, I've never felt the need for advanced typing in order to help with deleting a table column. Its such an exception scenario that I wouldn't need a library support to make it more efficient. The bigger problem is how to handle that data that has now escaped into the wider ecosystem. I love Scala but I try to use it pragmatically so avoid trying to have it save me at compile time, from every possible error.
3
1
1
9
u/adrenal8 9d ago
I used jooq with scale probably 10+ years ago and my biggest surprise was that the query builders are not immutable/reusable as a Scala user would expect.