My take on this is that it's not always the best idea to abstract-out SQL. You see, the SQL itself is too valuable abstraction, and also a very "wide" one. Any attempt to hide it behind another abstraction layer will face these problems:
- need to learn secondary API which still doesn't cover the whole scope of SQL
- abstraction which is guaranteed to leak, because any time you'll need to optimize - you'll need to start reason in terms of SQL and try to force the ORM produce SQL you need.
- performance
- deceptive simplicity, when it's super-easy to start on simple examples, but it's getting increasingly hard as you go. But at the point you realize it doesn't work (well) - you already produced tons of code which business will disallow you to simply rewrite
I’ve taken more and more to thinking of them as a zero sum tool.
Super fast and easier to use force multiplier in the beginning, but eventually you break free of the siren song and run into some negative that eats away at your time until you reach that “if you had just sucked it up and written the damn sql you’d be done yesterday” stage.
This just seems like a normal part of the growth curve. You cannot simultaneously build an infinitely scalable solution and complete something in a reasonable timeframe with the features that users will pay for. If you get to the point where you have enough users to justify working on efficiency or scaling out your infrastructure that’s a sign that you are winning. Unsuccessful companies never have to clean up their tech debt. For successful companies, it is a constant balance. You’re lucky to ever be in a position to have to clean up your short sightedness from previous work. By the time Facebook needed to mature beyond their PHP codebase, they were already wildly successful by every metric and had the resources to tackle such a problem. Early stage CRUD APIs should absolutely be generated and use the shitty ORM generated queries. By the time you run into serious performance issues with the ORM generated queries, you should be successful enough and have enough runway to plan a better future.
The vast majority of companies like this don’t fail because their UI is too slow. It’s because they don’t have “essential” features that other platforms do. If you have good monitoring and metrics, you should be able to find the bottleneck in your ORM and resolve it before any users even notice. And that means you’re hand rolling a few queries instead of the entire data storage layer.
"...premature optimization is the root of all evil."
Sometimes you just wanna get stuff out there, other times you're winning and you wanna give users the best experience. Many people have had to do both. You start with an ORM, eventually your queries are slow and all, you gradually reap them out. Almost every Engineer I know has had to do that at some POINT. Nonetheless, I am not about writing SQL for a simple barbing saloon booking app that I am not sure anybody will eventually use.
Yes this applies to a lot of abstractions of SQL, this one (inspired by Entity Framework/Linq) however works _with_ the grain by more or less by finding a sweet-spot between the SQL and the source language and most importantly doesn't try to hide the SQL.
My experience with Linq over the years has been great, only time I've needed to go raw SQL was to supply index hints (you can add that to Linq but we opted not to) and doing special things with merge statements. But EF allows you do submit raw SQL queries where needed.
The important part is, when you have a good system that actually provides benefits(Linq is properly typed) and doesn't get in the way or produce weird SQL then it'll work out.
I've only needed to use around 10 raw SQL queries where Linq failed to hundreds or maybe thousands of Linq queries where it worked perfectly well and this includes some fairly heavy queries.
Yes, yes and yes. ORM are marvelous when you do not know well SQL. With experience, you always end up needing to learn more about SQL. In the end, ORM is as much a hindrance as a help. So instead of spending energy learning the ORM of the day, it's better to invest in longer lasting technologies like SQL.
I know SQL and I like ORMs. For most simple CRUD, an ORM is fine. I don’t understand how they are “as much a hindrance as a help”; using an ORM only adds functionality, it cannot prevent you from using SQL against the data source in the same manner you would if you weren’t using an ORM.
It’s really just syntactic sugar for the subset of very basic queries that are easily expressed in the ORM. If other parts of your codebase are expecting ORM objects, it’s maybe two lines of code to re-wrap your SQL-fetched PK values back into ORM ducks.
The way I've always put it is "ORMs make the easy stuff a bit easier, and the harder stuff way harder." Just learn SQL, it's not that hard and it's a much better, transferable skill.
This is one of 4 reasons why I'm building pg-nano [1] and honestly the main catalyst. The other 3 reasons are: I still want to call my Postgres functions from TypeScript in a safe manner; I want declarative schemas with generated migrations; and I want the ability to write compile-time plugins that can generate SQL or TypeScript through introspection.
It's not released yet, but give it a look :) (v0.1 is almost done)
Have you use BI tools, such as Looker, Tableau, and the like?
LookerML is their abstracted version - but they always have an expander panel for seeing the sql.
---
What I would like is to use this in reverse - such that I can feed it a JSON output from my GPT bots Tribute - and use this to craft a sql schema dynamically into a more structured way where my table might be a mark-down version of the {Q} query - and it does SQL to create table if not exist, insert [these objects from this json for these things into this DB, now these json objects from this output into this other DB. Now I am pulling data into the DB that I can then RAG off as I fill it with Cauldrons of Knowledge I am scvraping for my rabbit-hole project thingamijiggers.
- need to learn secondary API which still doesn't cover the whole scope of SQL
- abstraction which is guaranteed to leak, because any time you'll need to optimize - you'll need to start reason in terms of SQL and try to force the ORM produce SQL you need.
- performance
- deceptive simplicity, when it's super-easy to start on simple examples, but it's getting increasingly hard as you go. But at the point you realize it doesn't work (well) - you already produced tons of code which business will disallow you to simply rewrite
(knowledge based on my own hard experiences)