My favorite book for learning SQL is “The Art of PostgreSQL”. https://theartofpostgresql.com/

I found the combination of real-world problems, general SQL advice, and the broad range of topics to be a really good book. It took my SQL from “the database is not much more than a place to persist application data” to “the application is not much more than a way to match commands to the database”. It’s amazing how much bespoke code is doing a job the database can do for you in a couple of lines.

I'm slowly moving in that direction. Recently started using (pg-typed)[1] in our projects and its amazing, as it gives you the types from the database into typescript, and not the "general types from tables", but the exact specific types for each individual query.

Coupled with the same thing going the other direction where we get types from our api contracts (OpenAPI/Swagger) with (laminar)[2] means that our app is very close to the "if it compiles it will run" territory.

ORMs do give you a lot of convenience though. Things like "run this additional query every time you request this entity" thing for example like for logical delete, which is unpleasant to replicate in your database. But Postgres is so freaking powerful its more of the fact that we don't know how to do it properly than it not offering a good solution.

[1] https://github.com/adelsz/pgtyped [2] https://github.com/ovotech/laminar

snorremd

The blog post[1] by the author about ORMs was what convinced me to purchase the book. I've had too many discussions with colleagues and tech friends struggling with N+1 problems and processing too much stuff in the application when they use mainstream ORMs to think it is a good idea. ORMs make the regular CRUD stuff simpler, but seem to make some more complex queries and transactions harder. There also seems to be an impedance mismatch between SQLs relational model and the OOP object relationship model.

Edit: I know there are ways to avoid N+1 problems with ORMs, but it seems to more easily sneak into code when your SQL queries look just like your application level code and you could easily enumerate over some SQL result, perform some action, and think that it builds an efficient query.

I've recently been working with a hobby project where I use the Clojure HoneySQL[2] library which essentially lets you build SQL queries as you normally would, but in Clojure's EDN syntax. It treats SQL queries as data. You can super easily evaluate them to get the resulting raw SQL query strings. There is no magic behind it and it encourages you to use the full power of your db.

[1] https://theartofpostgresql.com/blog/2019-09-the-r-in-orm/ [2] https://github.com/seancorfield/honeysql