SQL is useful but we’ve spent decades to abstract it away, because the Java/SQL articulation is always circumvoluted, and juniors do without.
Then they load two tables in Java and join them manually in memory; or they fetch the principal record, and use the getter to retrieve the dependency, so Hibernate does one query per row.
I accuse all ORMs, especially Hibernate, of making it too easy to skip SQL, and I accuse Java of not supporting multiline strings. We have failed, in most languages, at making it easy enough to write SQL yourself. JPA is nice to cache records in memory, but it’s been way more harmful to performance that it became hard to write SQL directly.
You are right, regarding the wastefulness. Additionally, there are extra risks involved when data needs to be transferred between a storage and compute facility.
To a degree I get why architects these days like to separate everything into individual narrow and easier to manage/tuned services. But separating business logic from the data it runs on may equally be a fundamental mistake, when it comes to guarding integrity of both data and the logic acting on it.
There are good argument for advocating that actually ALL business logic should be implemented alongside the data it runs on, inside the database itself. Only the logic specific for presentation should exist separately, in whatever front-end is used. I've seen that done successfully, several times in fact. It does require some programming skills which I've not seen
in most programmers these days though. Even less among the young dogs.
Maybe the key question here is: does that say something about proper software design / architecture, or about the state of what programmers these days actually know about the fundamentals.
I believe it's easy to hate on something like SQL from a position of ignorance (to be clear: SQL does have serious shortcomings, without a doubt). Still, in nearly 30 years I've not seen anything come along that is fundamentally better (and gaining enough traction to succeed).
As for those who like to complain that stored procedures are too limited because you would have to implement everything in SQL (I've heard that one a few times too many), plenty of databases have extension to program stored procedures in different languages (e.g. python).
The problem with SQL is integration with the rest of the code. I’ve already mentioned that Java not supporting multiline strings made it exceedingly hard to write SQL in-app and incentivized the use of frameworks; It is equally true that it is easier to deploy a .jar rather than a jar + stored procedures.
To update a stored procedure, you have to execute SQL (as in, load a client, deal with exceptions, then actively load an instruction and execute it), then deal with “what happens if there is already a procedure with the same name, what if it’s not ours, what if you don’t have permissions”, etc. If the upside of SQL is that is is functional, ie you declare what results you want and let the DBMS decide how to execute your query (ultimate declarative language), it is really funny that the DDL is instead an instructive language where you provide commands, and it fails miserably if you give the same instruction twice. They got it all wrong! DDL should be descriptive not prescriptive! It should not be “CREATE TABLE” but “TABLE ___ IS ___”!
It’s all about difficulty; If Postgres accepted “git push” to deploy stored procedures, we’d immediately see webservers implemented in SQL ;)
There is also a security aspect to it, with stored procedures, even with stolen creditials there is very granular acess, that would have to be otherwise provided with tons of views.
I think it is a consequence of six month bootcamps and then be allowed to call themselves "engineers" (in countries that allow such things).
> There is also a security aspect to it, with stored procedures, even with stolen creditials there is very granular acess, that would have to be otherwise provided with tons of views.
In fact, now that you mention this .. I've seen a few times how an application had complete granular access control on every individual record, based on the specific (personal) user credentials you would connected with to the database (PostgreSQL). There are some really interesting things possible. It always makes me cringe (at least a bit), when I see yet another application access a database with just a single set of (admin) user credentials. So much missed potential.
> I think it is a consequence of six month bootcamps and then be allowed to call themselves "engineers"
Certainly true, but I think commercial companies that constantly try to market their products/services as "making everything so much more easier, without a need to understand all that pesky complicated matter" are at least equally as much to blame for the apparent degradation of skills and knowledge.
I'm well aware that almost every generation looks at the younger generation thinking: "what little do these young people actually know". To a degree no doubt in their own ignorance, lacking real understanding of all the new things currently in use. On the other hand, I think this shift from actually understanding technology towards professionals increasingly just becoming convenient (and easy to replace/retrain) "tool jockeys" is nonetheless a real thing too.
That personally saddens me, because I know how much easier it will be for greedy commercial interests (think the current tech giants) to manipulate/hijack technology, with total disregard for actual technological progress (or even quality). A growing group of "professionals" who can't even tell the difference anymore, sure won't help.
> It always makes me cringe (at least a bit), when I see yet another application access a database with just a single set of (admin) user credentials. So much missed potential.
I wanted to use separate credentials, but the problem with app credentials which match userland credentials is that connection pools require that you use one single set of credentials. And they exist because opening a cnx to the db is slow, so it goes faster when you preopen 20 connections.
Then they load two tables in Java and join them manually in memory; or they fetch the principal record, and use the getter to retrieve the dependency, so Hibernate does one query per row.
I accuse all ORMs, especially Hibernate, of making it too easy to skip SQL, and I accuse Java of not supporting multiline strings. We have failed, in most languages, at making it easy enough to write SQL yourself. JPA is nice to cache records in memory, but it’s been way more harmful to performance that it became hard to write SQL directly.