I'd say SQL is the most underrated engineering skill out there.
It amazes me when competent developers / analysts / data scientists don't know (any) SQL. Have they stopped teaching this in University or something?
It amazes me when competent developers / analysts / data scientists don't know (any) SQL. Have they stopped teaching this in University or something?
What is a trap I see often among ORM users is them running into the n+1 problem. However, that's actually a consequence of database implementations not being theoretically pure. If you had an ideal SQL database then their approach would be technically correct. They aren't wrong to think that way.
It's just that we don't have ideal SQL databases, so we have to resort to hacks to make things work in the real world. Why we need to break from the theoretical model and use those hacks can be difficult to understand if you aren't familiar with the implementation at a lower level.
That's what's amazing about ORMs handling the simple stuff.
ORM doesn't have anything to do with SQL statements. ORM only operates on the results those SQL statements produce.
You're probably thinking of the active record pattern, which combines ORM with query building into an interesting monstrosity. Indeed, query builders produce SQL statements and can save you from changing 500 odd raw SQL queries.
But as always: use the right tool for the job. A hammer is a great tool, but not suitable for removing screws (most of the time).
These are technically active record libraries. One of the popular ones is literally known as ActiveRecord, but the whole suite of them are implementations of the active record pattern.
ORM is simply the process of converting relations (i.e. sets of tuples, i.e. rows and columns) into structured objects and back again. This layer of concern is independent of query generation.
I’m mostly using entity framework core, which allows you to do quite complex queries, which can be really easy to read and maintain. But if you’re careless, you can create quite slow queries, that’s clear. But you can easily create slow queries with plain SQL too.
And nothing prevents you from using plain SQL libraries next to an ORM. Good ORMs expose the database connection, so you can do whatever you want.
Every tool can be abused.
And then they have these awful and subtle caching issues that mean if you fall back to SQL then you take a massive performance hit. Or if you run multiple servers against the same database you need another layer of complex caching infrastructure. Or if you update a table outside of the application you can get inconsistent results.
The whole thing is a nightmare of unnecessary complexity apparently intended to hide developers from the much lower complexity of databases.
ORMs are an inefficient, leaky and slow abstraction layer on top of a fast and complete abstraction layer.
I’ll never willingly use an ORM again.
Edited to add: the whole fragile contraption is done in an effort to move slices of the database into the memory context of a remote application. To what end? So we can update some counter and push the whole lot back out? It literally makes no sense.
1. Generate ORM entities from the database (usually there is a too for that) 2. Generate the database from ORM entities, and make changes via generated/manual migrations
You are supposed not to edit the generated side, just re-generate it on a change of the model.
Countless "replacements" for SQL have come around to make data accessible by non-expert, but regardless if your using a BI tool, reporting platform, ORM, whatever, pretty soon you're going to need to understand and express the logic, and that's when you really appreciate SQL's directness and terseness.
I took a course in which I learned quite a lot about SQL and in retrospect it was an extremely useful course to have taken.
The one I took was elective. I am willing to admit that from a professional ROI perspective, it was one of the best uses of time in my entire life and easily worth hundreds of thousands of dollars.
There's a woeful ignorance about what the relational data model is, how the industry arrived here, and how this is implemented. Problems or archaisms with SQL specifically become synonymous in people's heads with the relational model generally, and for a while that led down the quite problematic NoSQL road. Then slingshotted back to SQL -- but from my perspective SQL itself (not the relational model) is a problem. It doesn't compose well. It doesn't handle recursive relations well. It has an awkward syntax. It conflates concepts. It has an archaic datatype model. None of this is intrinsic to the relational model, but SQL becomes a limiting factor.
Disclaimer: I work for a DB company doing awesome stuff with the relational data model, but not SQL (RelationalAI) so am ... biased. Though I have always had those biases. :-)
https://cran.r-project.org/web/packages/data.table/vignettes...
I am not as familiar with R. Last time I worked in R (some years ago) equivalent R code was something like this caution I'm no expert in writing R so might be a better /more intuitive way...
Output_data <-merge(x=T1, y=T2, by="Date", all.x="True") %>% mutate(My_var = NAME) %>% fill(My_var)
In SQL the equivalent would need to use Over (Partition by) which is less intuitive for me to write.
It wasn't part of the mandatory coursework when I went to school. I really think it should be. It's not as though my CS program was 'pure theory', they taught lots of 'vocational' stuff but not SQL.
As for why people put off learning it.. I put off learning it longer than I care to admit because I thought I could simply do without it (ORMs, BerkeleyDB, etc), and fake it if put on the spot (I always understood the very basics, insofar as a SQL query can be understood if read as declarative English.) Ever since I bit the bullet and actually learned it properly, I've been kicking myself for not learning it upfront.
Most programs include parts of all three, but SQL per se (rather than like, relational algrebra) is pretty firmly in the professional training for software developers set, and schools that reject that aspect may not teach it.
In my experience recent code school/boot camp grads have as much or more practical sql as recent CS grads; probably because those schools are nearly totally focused on professionally applicable skills.
It should be mandatory along with understanding O notation and datastructures and algorithms, because really... unless you're doing a career which is like... 100% embedded development... you're going to be encountering databases and datamodeling as part of your career.
I'd be much happier if CS programs would graduate people who did a whole semester of first order logic, Date&Codd's foundational papers and why network&hierarchical databases are problematic, relational algebra / calculus, Datalog & friends, and then just toss in a "and this is how SQL does some of this but also mangles all of this..." at the end.
+ as a bonus, a DB implementation/internals course so people can understand what goes into query execution etc.
Because those people would then have some proper context before going off and butchering the world with ORMs and microservices...
But AFAIK so does my local university.