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.
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
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
Far too often do I see developers doing analytics by slurping an entire table across the network and performing calculations on it in the application. Of course it appears to work in development with tens of kilobytes of records and a local database, but as soon as it’s deployed to production it unleashes chaos.
I consider myself very good at SQL but I do prefer to use ORMs for most things. However there’s no arguing that many of them have done a lot to obscure the incredible power inside a relational database, and perhaps go too far in hiding exactly when data is computed remotely vs. being pulled and operated on locally.
Of course, that is a solution to a problem that people wish they had. Relational databases can run multi-million row queries in seconds, and then there's BigTable / BigQuery which scales SQL to improbable scales.
And even in that case, I/O and contention were inevitably the problems. Not CPU.
I’ve similarly heard myths that you should be judicious when writing indexes because they can affect insertion performance. I’ve again seen hundreds of cases where under-indexing killed performance and zero where over-indexing caused problems.
99.9% of the time, you’re not the crazy special case. And if somehow you are, the solutions required are going to be nuanced and involve a ton of specific measurement. It’s widely unlikely you’ll accidentally avoid these problems through something like this.
Moreover, calculations usually require just a small subset of columns in a table, and thus can use indexes efficiently, whereas grabbing all the columns to then filter them in application (because that's how many ORMs work, at least by default) becomes not only worse in terms of memory and networking, but also in terms of IO and CPU required on the DB side.
So overall I'm sceptical of that argument, unless there's clear proof from profiling that it's indeed the case.
But if you support all RDBMS you only can support the smallest intersection between them and can't use advanced features like CTE, window functions, JSON support etc.
That's not true at all, any more that it’s true of supporting all browsers with JS. You can use the advanced features where available, and implement logically (if not performance)-equivalent functionality using more basic functions where the advanced features aren’t available.
Or, if you are lazy, just have a reduced feature set available with less capable RDBMS engines. But, on any case, its simply not the case that an ORM that supports engines of varying capacity is limited to using only the least-common-denominator feature set.
I was talking about ORMs in general, not about the programmer in particular. So yes, you are right that if you have enough engineering resources you can support everything. But most ORMs don't help you with this, so it is not a feature of ORMs. You can always bypass it though.
The comparison with JS doesn't fit realy well, because JS is the tool you have to use. You don't have to use ORMs, plain SQL works fine as well.
So the ORM author can do the hard work of allowing advanced features to work across databases. And it's transparent to the developer using the ORM.
The real benefit of the ORM to me are that:
* some query results are cached
* the "unit of work" pattern allows me to distribute changes to an entity and then commit it as a transaction.
Anyway, the ability to suddenly switch RDBMS only works out in practice if you actively maintain support for multiple engines.
I’m always willing to buy technical books. I think it’s valuable to have material from different authors because they each have different perspectives and styles. E.g. CLRS vs Sedgewick vs Skiena. I also like to support the authors. However I’ll take a hard pass at this one.
My ability to level up is rarely due to the quality of the material, it’s more a function of how much time and effort into studying and learning. Time is the limiting factor in almost everything, not learning material.
In other words, learning isn’t about choosing “book A” vs “book B” but rather studying any books vs scrolling through HN, watching YouTube, or any of the other million blackholes of time.
It’s time, not money, you are spending.
- Practical SQL, No Starch Press. ($30)
https://nostarch.com/practicalSQL
- Use The Index Luke ($15)
https://use-the-index-luke.com/
- Database Systems Concepts & Design by Georgia Tech on Udacity (free)
https://www.udacity.com/course/database-systems-concepts-des...
I can easily pay the $100, I cannot easily find more time, especially when there are a bunch of other things I'm spending time on. At a lower price I would likely buy the book "just because".