SQL: One of the Most Valuable Skills(craigkerstiens.com)
craigkerstiens.com
SQL: One of the Most Valuable Skills
https://www.craigkerstiens.com/2019/02/12/sql-one-of-the-most-valuable-skills/
9 comments
String appending parameterized SQL statements is all sorts of awful. I for one have accepted my EF/LINQ overlords for the general sanity it provides to most queries.
However, I don’t touch any of the “ORM” features other than certain conveniences like .Include() for trivially handling trivial joins, and most of the code ends up with an almost 1:1 translation to the actual SQL statement.
Same with SQLAlchemy Core in python, though the expression API is distinctly less pleasant than LINQ.
Anything that doesn’t fit cleanly in LINQ usually ends up as a View or procedure in the DB anyways (except for DELETE statements, which EF is uniquely awful with; requiring the rows to be fetched first before turning around and actually deleting them)
However, I don’t touch any of the “ORM” features other than certain conveniences like .Include() for trivially handling trivial joins, and most of the code ends up with an almost 1:1 translation to the actual SQL statement.
Same with SQLAlchemy Core in python, though the expression API is distinctly less pleasant than LINQ.
Anything that doesn’t fit cleanly in LINQ usually ends up as a View or procedure in the DB anyways (except for DELETE statements, which EF is uniquely awful with; requiring the rows to be fetched first before turning around and actually deleting them)
Pretty much no one is doing string appending to build SQL statements for a long time now. You use positional parameters [0] which also addresses problem of SQL injection attacks.
[0] https://www.postgresql.org/docs/current/sql-expressions.html...
[0] https://www.postgresql.org/docs/current/sql-expressions.html...
That’s what I meant by parameterized queries.
But composing strings together to forge a SQL query (outside the inputs) is generally a terrible, no-good, awful job. E.G. optionally adding filters to a query.
Largely because the SQL language itself does not compose well, due to its general inconsistency in just about every expression, and it’s awkward/unnecessary strictness in things as simple as SELECT/FROM/WHERE order and evaluation order
But composing strings together to forge a SQL query (outside the inputs) is generally a terrible, no-good, awful job. E.G. optionally adding filters to a query.
Largely because the SQL language itself does not compose well, due to its general inconsistency in just about every expression, and it’s awkward/unnecessary strictness in things as simple as SELECT/FROM/WHERE order and evaluation order
LINQ is an interesting case as I can legitimately imagine only using LINQ for 5-10 years as Microsoft is dedicated to it, and I understand that all .NET languages use it.
However, outside of the .NET world, there is no common ORM that I would use between languages and between projects. Learning an ORM repeatedly is much, much worse than learning SQL once, especially considering the low quality of some of them (I'm thinking Sequelize here, but I'm sure there are others).
For 99% of cases, I don't do string appending; I create different SQL files for different situations and select the file at runtime depending on the situation. 10 SQL files? Sure, I don't care. It's better than the hours of going through JavaScript code at 3AM and trying to understand how to do some random feature that may or may not be implemented in the ORM layer.
Incidentally, I am curious, how do you keep stuff like Views and Stored Procedures version controlled?
However, outside of the .NET world, there is no common ORM that I would use between languages and between projects. Learning an ORM repeatedly is much, much worse than learning SQL once, especially considering the low quality of some of them (I'm thinking Sequelize here, but I'm sure there are others).
For 99% of cases, I don't do string appending; I create different SQL files for different situations and select the file at runtime depending on the situation. 10 SQL files? Sure, I don't care. It's better than the hours of going through JavaScript code at 3AM and trying to understand how to do some random feature that may or may not be implemented in the ORM layer.
Incidentally, I am curious, how do you keep stuff like Views and Stored Procedures version controlled?
One change I have seen in recent years is that I find fewer and fewer new engineers learn SQL, especially folks entering the industry from bootcamps. And conversely, more and more folks from a business/econ/etc background learn at least a little bit along with excel.
SQL skill is nice but wish we stopped with all these “leet” queries. They should really focus on teaching people how to write queries that are performant.
Most apps these days use document store like MongoDB and ElasticSearch for storing and scaling data.
We do use a RDBMS as well but mostly for the transactional part and we get away with just using simple SQL, no join and complex stuff.
We do use a RDBMS as well but mostly for the transactional part and we get away with just using simple SQL, no join and complex stuff.
It's something of a misconception that an RDBMS should be used for transactional workloads.
RDBMSs are table stores; in a sense if anything that's a model better suited for analytical than transactional not that that's really a fair conclusion either
RDBMSs are table stores; in a sense if anything that's a model better suited for analytical than transactional not that that's really a fair conclusion either
Moreover, I can actually implement requests for complex logic fairly easily. Instead of trying to understand how the ORM works (which is a problem in of itself with a lot of ORM documentation, due to the sheer number of corner cases), I can either implement or google an example.
One of the nicer parts to working w/ SQL is that even different dialects of SQL offer helpful ideas on solving a problem. Yes, MySQL might not have materialized views or what have you, but seeing a Postgres solution will at least point you in the right direction. (Using ORMs just adds a level of indirection that you don't need IMO)