The Rise of SQL
spectrum.ieee.org
spectrum.ieee.org
It's wasted investment learning an ORM because its not knowledge that you can carry from project to project and throughout your career.
Any time spent problem solving or learning the weird intricacies of ANY ORM is time thrown in the garbage bin.
The largest community, the widest range of problem solving minds are available to help with your database issues - if you're using raw SQL.
Program the platform - use SQL directly as much as you possibly can.
ORMs have problems, but some are good enough and widely used enough that you might be able to take them from project to project (e.g. Rails’ ActiveRecord).
SQL is good, but it can get tedious to intermingle fully ‘raw’ SQL with other languages. There are some good non-ORM database libraries whose features go beyond this (I develop one called Zapatos for Postgres/TypeScript).
The non-ORM SQL libraries / APIs that I know offer APIs where you provide an SQL query template, where you e.g. list your arguments as \1 \2 etc, and then you also specify the values of those arguments. The API then fills in the arguments in a injection safe manner.
I don't think anyone is arguing for raw string manipulation here.
https://www.postgresql.org/docs/14/libpq-exec.html
> The primary advantage of PQexecParams over PQexec is that parameter values can be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping.
For example one cannot usually do "... order by ? ?", passing in a column name and ascending/descending. And this is commonly needed for say paginated tables where the user can choose which column to sort by. Similarly, I've worked with some advanced seaarch screens that would need to dynamically add additional clauses to "WHERE", and in the end, that will come down to some form of string concatenation, even if parameters get represented as "?" and passed separately.
But unfortunately doing that with string concatenation leaves more opportunity for injection errors to slip in.
Its actually rather unfortunate pretty much no SQL database allows passing a query as an already parsed AST. It would be really neat if a query builder pattern in software could instead of building up a string could construct a binary representation of the AST, such that no injection was possible, and simply hand it off to the engine. The AST would dictate that this value is a column name, and because we are already an AST, there is no need to escape this value, since the engine won't need to reparse it. Or this value is a string, but once again, no reparsing from string needed, so no escaping needed. (Serialization format could have a length prefix or whatever).
Every single place* you can write raw SQL, you can do it with a prepared statement, and then you don't have injection and the other nasty stuff.
*At least for all the big players I have used.
OTOH, each database implements their own set of SQL features, so has their own SQL dialect. This is visible on stack overflow where the database name is a big component of the question / answer.
I still agree with you though that SQL is superior to having a separation layer between the database. SQL is already supposed to be the human readable interface to a library.
That said, I know SQL well enough so was open to an ORM the company was using.
My problem with ORMs, at least the ones I've used, is that they seem to be kid gloves for people afraid of SQL rather than some time saving or optimizing option. Often the code was longer, harder to read and reason about, and less performant when using an ORM vs inline SQL.
I do like query builders though. But I think you need to understand and have easy access to the SQL that is being generated.
ORM vs raw SQL is a false dichotomy.
I think it's extremely important to learn SQL first. But I don't think it hurts to learn an ORM or two along the way.
Then it's important to learn how to optimize performance. Just getting results with SQL is easy. Thinking about how to do so quickly can be more difficult.
Lastly ORMs deal with the issues an application typically has to deal with anyways. Such as mapping to objects, detecting changes, caching, concurrency, etc... So by learning an ORM it might give insight into ways to handle these things even if you don't need a full blown ORM.
This can be addressed with typesafe SQL-builders like jooq [1]. I got an OLAP application with plenty of gnarly SQL broken up in individual business rules.
The best is that I don't even touch the SQL much. I expose it read-only to the users, hidden under an advanced mode button; and when they want to change things it often comes already written in SQL (mostly filters and projections). New queries come in requests to mix already-existing parts. And it helps technical knowledge be shared. Win-win all around.
Jooq works well with CRUD/OLTP apps as well. And when you have a problem, you don't have to debug both the ORM and SQL.
> Then it's important to learn how to optimize performance.
Starting with IO at the db level, and execution plan in the db. And ending right there without an ORM. Neither you nor your ORM are going to be smarter than the planner. For starters do you even collect statistics about your data? And your ORM doesn't even know what the downstream processing will be.
> Lastly ORMs deal with the issues an application typically has to deal with anyways. Such as mapping to objects,
Hopefully included with the typesafe SQL builder, but not always needed.
> detecting changes,
Plenty of SQL features for an audit trail
> caching,
Well maybe there. But if read-availability is your problem, reading from replicas gets you very far.
> concurrency
Comes out of the box with MVCC RDBMS; ie good'ol postgres, mysql
----
I'm not going back to ORMs
/rant
I love query builders! Especially type safe ones. But if you have a complex domain doing updates can get messy. You are going to write a lot of code mapping domain objects to SQL updates. It's not insurmountable, but you are basically re-inventing an ORM. Maybe find a lightweight ORM for this purpose (The command side of CQRS).
But yeah if you don't need one that's great! I wasn't trying to convince anyone to use one, just that they can be worthwhile to learn.
Personally I like using either direct SQL or query builder style interface for reads/queries. Then I like using an ORM with domain objects for commands/updates. But I deal with a lot of complex domains in finance, supply chain optimization, production planning, etc...
An object-oriented approach is better suited to highly interactive, line-of-business applications. Data records are not nearly so numerous in such applications. They live in client space for minutes or hours, not microseconds. During their unpredictable stay, they appear in various guises on multiple screens. Users change them. They're roiled in complex business logic. The consensus of architects is that such data records are best represented as business objects. The open question is how best to shuttle data between database and business object form.
ORM is one such answer. Not the answer.
The ORMs I have used, hibernate and doctrine, allow you to use raw SQL, then they do the object mapping for you.
The raw SQL vs ORM is a false dichotomy.
I find that modelling SQL tables first removes the “where in the JSON-like tree should this live” question - often a single object type lives in many places in the tree.
Breaking data into atomic units and then building up any JSON-like structures you require with queries is very flexible, and solves persistence first.
ORMs seem to solve persistence second after modelling your data as JSON-like in-language objects.
Persistence needs to be solved as disk is cheaper than RAM and your program cannot live forever with data in RAM.
I can't imagine anyone preferring to use raw SQL and hand code the mapping from SQL field results to objects. The argument that it's not transferrable knowledge isn't even ORM-specific. You can make the same argument for any hotshot $framework or even $language that you think might fade away in a couple years. There's always a trade off between adopting unproven frameworks and reinventing your own wheel (or coding at a lower abstraction level with all its drawbacks). Even proven tech can go out of fashion, as was during the "NoSQL" craze a few years ago. It's just a matter of making good predictions with industry knowledge. There's no hard law of nature that SQL is going to survive another 10 years (though admittedly that's quite likely).
The number of people agreeing with your blanket, inflexible statement makes me wonder whether I'm in the wrong forum...
If you know both the ORM and SQL well, you can save a lot of time during development. I've been using NHibernate for over 10 years and now also getting into Entity Framework Core.
I did see bad usage of ORMs and this was always caused by either a lack of SQL and database knowledge, or a lack of knowledge on how ORMs work.
Currently SQL (SQLite), Perl, PHP, Bash, text files, csv (psv), HTML, JS, CSS.
The trick I use to protect against them is to run my test suite against multiple Python versions using GitHub Actions, e.g. here: https://github.com/simonw/sqlite-utils/blob/main/.github/wor...
1. Users, network transport, etc (except for SQLite)
2. The SQL language
3. The query optimizer
4. Transaction functionality
5. Persistent store functionality
If you want to write a new database language, you need to either generate all of the above functionality, or "compile" your language down to SQL. What I'd love to see is having an official intermediate layer between #2 and #3, similar to llvm and gcc's bytecodes, with a range of different language front-ends; and ideally, the ability for a language to integrate directly into their type system.
Imagine writing queries natively within Rust or Golang, knowing that the types would be checked properly by the compiler; but that the query could be executed efficiently inside the database itself.
In practice, SQL dialects consist of a collection of functions in addition to standard operators. These functions have varying naming, functionality, and edge case behavior -- often conflicting. Papering over this is a difficult problem.
From a SQL database point of view, the SQL interface is well tested and required by end users. Adding an additional, rarely used interface to get to expose the same functionality is of questionable value. Using SQL as the plan serialization works pretty well.
SQL performance is usually the single hardest thing to optimize in a project, and anything non trivial often requires server specific knowledge.
SQL is probably one of the most useful things to learn when it comes to software development. It's probably also one of the hardest things to master just because you often need to know how things actually work under the hood and design around that.
SQL is awesome!
I started working with SQL in about 1997 and in my experience I've seen a fairly gradual set of changes, and the move from one DB to another - MySQL to Sybase to Oracle to Postgres - has proven pretty straightforward, with most things staying either identical or similar to the point of total non-issue.
The reason I say this is to try and dissuade anyone from rejecting SQL as "yet another tech moving so fast it's barely worth learning". SQL is very stable and is worth picking up on any major platform - your knowledge gained will 100% be a valuable investment!
What's fast moving about Javascript? It has certainly picked up some features along the way, and compatibility across implementations has improved, but the code I remember writing 25 years ago would still work just fine. Overall, it's quite stable and slow moving.
The 'ORMs' of the Javascript world, to stick with the analogy, have a reputation of being replaced every few months, but that's a different topic entirely. ORMs of the SQL world aren't much better. All the more reason to focus on the core language in both cases.
He said no. I asked "why". And he said there is no need, because the ORM is enough. (He has been coding for a few years).
Every year that passes SQL is becoming more and more important to learn. It has really become mandatory these days because of the volume of data that we are all collecting.
It was seen as 'unsexy'.