4 ms·

> Reading, writing and maintaining hand-written SQL is hard and error prone. I see this attitude a lot on HN, often whenever any ORM comes up, and it's often t

by greggyb 11y ago

> Reading, writing and maintaining hand-written SQL is hard and error prone. I see this attitude a lot on HN, often whenever any ORM comes up, and it's often taken as axiomatic. I'm honestly curious to hear people's opinions about what makes SQL so much more difficult to use correctly than other programming languages or environments. I come from a background in BI and data warehousing, so I'm pretty comfortable with SQL as a second language, as I like to call it. Here are a few things I understand as unique in SQL, some of which clearly do add unique difficulties: * Everything not a SELECT is stateful - unit testing becomes difficult * Syntax/semantics are far from most "normal" languages (not C-like, not similar to shell scripting, not OO, not functional, verbose in a style similar to COBOL, the hated) I, personally, do not find large SQL codebases particularly difficult to work with correctly or understand. Perhaps this is a combination of ignorance and Stockholm Syndrome since most of my experience is in databases and heavy SQL work. I'd like to understand better why the attitude in the excerpt I quoted seems so common. Ninja edit: Additionally, I find a lot of power and expressiveness in the relational model. Again, perhaps because of the focus of work I do I find SQL indispensable and very easy to use for both exploratory programming and data analysis, as well as robust programming e.g. for ETL processing. Note: I still use ETL tools and frameworks, but this is not to remove SQL from the equation, but for the better dependency tracking and process monitoring.

coderzach 11y ago
The problem with sql for me has been the lack of composition. Once a sql string is generated it can't be extended or modified. When I'm writing pure sql it's generally not a problem, however, most sql I write is in part generated by some user input. By the end, a query that allows user input and extracts common reusable expressions, is a Frankenstein's monster of unreadable string interpolation.
tome 11y ago
SQL is an awfully-designed language and has WATs coming out of its eyeballs. Two things (out of many) off the top of my head: 1. Every subselect must be given a name, even if you never use that name. 2. This orders by the second column SELECT * from table ORDER BY 2; whereas this orders by the constant 1 + 1, i.e. 2. SELECT * from table ORDER BY 1 + 1;
T-R 11y ago
SQL is incredibly low level - Modelling Sum Types requires giving up correctness guarantees, performance, or both. - It's not expressive enough to get performance or abstraction - I can't communicate "this is a one-to-one relationship", "this is append-only", "this is associative", or "these things commute" in anything but comments. And my co-workers need to remember to make those joins, and think about how they can use that information to hand-optimize, because the optimizer sure as hell isn't going to do it for them. - There's no abstraction/composition of anything at lower than a full query granularity, so everything needs to be inlined and repeated everywhere - this makes conceptually small changes, like going from single- to multi-column primary keys or vice-versa, require rewriting practically every view/query. - Some optimizer WATs are required by the spec ('where' clauses can be propagated down the AST even past another clause that does type refinement; CTEs always materialize the result set and act as optimization walls), others may be implementation dependent (At least in Postgres, "Union All", instead of acting like a simple stream concatenation, more often than not materializes the whole result set and pages it out to disk - your 0.2ms query now takes 2 seconds). SQL is "Lazy when the stars align" and optimized by "this won't break things in the trivial case" - most effort in non-trivial queries is spent trying to convince the optimizer not to hurt you on a whim. Don't get me wrong, the devs do absolutely impressive stuff with what they've got - these quirks are clearly what needs to be done to compensate for the spec, and the spec can't change for compatibility resons - but it doesn't make it easy to work with. - Things like missing a column in a join clause (like when changing that primary key) cause significant bugs that don't actually throw an exception/type error anywhere - they just change the size of your result set. This could be completely fixed with type-level literals. - Types are internally inconsistent, and supplied functions are pretty much ad-hoc "we thought this might be useful". Type conversions regularly involve first converting to a string or wrapping in a nested query. - The syntax is pants-on-head ridiculous for anything non-trivial, making education difficult and maintenance tedious. (especially window functions - SELECT a, b, LAST_VALUE(b) OVER (ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS x). The problem, though, isn't just the amount of code and maintenance issues this creates - it's the sheer amount of information that everyone on your team who touches the database needs to know and not overlook just to not create subtle bugs and significant performance issues. And all this knowledge is completely non-transferrable. SQL is so bad that standard practice is just to not do anything on the database unless you have to; eating the connection overhead, adding yet another cache, and having a layer of wrapper functions are just considered the cost of doing business, leading a lot of people to think SQL is perfectly sufficient for what it does.
thesz 11y ago
The problem with SQL is not awl per se, but glue between awl and regular programming languages. Mapping awl collections to c types is difficult due to difference of semantics. That is the problem.

by greggyb 11y ago

coderzach 11y ago

The problem with sql for me has been the lack of composition. Once a sql string is generated it can't be extended or modified. When I'm writing pure sql it's generally not a problem, however, most sql I write is in part generated by some user input. By the end, a query that allows user input and extracts common reusable expressions, is a Frankenstein's monster of unreadable string interpolation.

tome 11y ago

SQL is an awfully-designed language and has WATs coming out of its eyeballs. Two things (out of many) off the top of my head: 1. Every subselect must be given a name, even if you never use that name. 2. This orders by the second column SELECT * from table ORDER BY 2; whereas this orders by the constant 1 + 1, i.e. 2. SELECT * from table ORDER BY 1 + 1;

T-R 11y ago

SQL is incredibly low level - Modelling Sum Types requires giving up correctness guarantees, performance, or both. - It's not expressive enough to get performance or abstraction - I can't communicate "this is a one-to-one relationship", "this is append-only", "this is associative", or "these things commute" in anything but comments. And my co-workers need to remember to make those joins, and think about how they can use that information to hand-optimize, because the optimizer sure as hell isn't going to do it for them. - There's no abstraction/composition of anything at lower than a full query granularity, so everything needs to be inlined and repeated everywhere - this makes conceptually small changes, like going from single- to multi-column primary keys or vice-versa, require rewriting practically every view/query. - Some optimizer WATs are required by the spec ('where' clauses can be propagated down the AST even past another clause that does type refinement; CTEs always materialize the result set and act as optimization walls), others may be implementation dependent (At least in Postgres, "Union All", instead of acting like a simple stream concatenation, more often than not materializes the whole result set and pages it out to disk - your 0.2ms query now takes 2 seconds). SQL is "Lazy when the stars align" and optimized by "this won't break things in the trivial case" - most effort in non-trivial queries is spent trying to convince the optimizer not to hurt you on a whim. Don't get me wrong, the devs do absolutely impressive stuff with what they've got - these quirks are clearly what needs to be done to compensate for the spec, and the spec can't change for compatibility resons - but it doesn't make it easy to work with. - Things like missing a column in a join clause (like when changing that primary key) cause significant bugs that don't actually throw an exception/type error anywhere - they just change the size of your result set. This could be completely fixed with type-level literals. - Types are internally inconsistent, and supplied functions are pretty much ad-hoc "we thought this might be useful". Type conversions regularly involve first converting to a string or wrapping in a nested query. - The syntax is pants-on-head ridiculous for anything non-trivial, making education difficult and maintenance tedious. (especially window functions - SELECT a, b, LAST_VALUE(b) OVER (ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS x). The problem, though, isn't just the amount of code and maintenance issues this creates - it's the sheer amount of information that everyone on your team who touches the database needs to know and not overlook just to not create subtle bugs and significant performance issues. And all this knowledge is completely non-transferrable. SQL is so bad that standard practice is just to not do anything on the database unless you have to; eating the connection overhead, adding yet another cache, and having a layer of wrapper functions are just considered the cost of doing business, leading a lot of people to think SQL is perfectly sufficient for what it does.

thesz 11y ago

The problem with SQL is not awl per se, but glue between awl and regular programming languages. Mapping awl collections to c types is difficult due to difference of semantics. That is the problem.