4 ms·

No. SQL is actually pretty third-rate at expressing data and relationships. My preferred way of expressing data and relationships is the programming language I

by _sh 13y ago

No. SQL is actually pretty third-rate at expressing data and relationships. My preferred way of expressing data and relationships is the programming language I am writing in. The problem with SQL is that it is not an API, it's a DSL. Which usually means source-code-in-source-code, string concatenation/injection attacks, and crappy type translations ('I want to store a double, what column type should I use? FLOAT? NUMERIC(16,8)?'). Even as a DSL it's pretty low-brow: just look at how vastly different the syntax is between insert and update, or 'IS NULL'. For all those who love SQL, consider having to address your filesystem with it. Directories are tables, foreign-keyed to their parent, files are rows. There's a good reason why this nightmare isn't real: APIs are preferred over DSLs for this use case. And so too for databases, because they are the same abstraction. Don't get me wrong, I love relational algebra and the Codd model, but SQL just aint it. SQL has survived because of its one and only strength: cross-platform. And like all cross-platform technologies, such as Java bytecode and Javascript, its rightful place is a compilation target for saner, richer, more expressive technologies. This is why I always use an ORM and have vowed to never, ever, write a single line of SQL again.

spion 13y ago
How about hybrid sql-builder / data grouper solution? Not limited to ORM methods - get the full power of SQL instead. Not string concatenation - get the full power of the language to build queries. Also, the ability to get join results in either flat or grouped form. For example https://github.com/doxout/anydb-sql https://github.com/doxout/anydb-sql (shameless plug)
- _sh 13y ago
  Nice. This is exactly what I mean when I talk about ORMs. See how everything's nicer when its an API?
j-kidd 13y ago
I like your comparison of SQL to JavaScript. However, personally I love SQL and always use an ORM. My vow is to never have a line of SQL in my application source code. This is perfectly doable with SQLAlchemy, though not with crappy ORM such as ActiveRecord. Indeed, I blame ActiveRecord for making NoSQL popular. When your ORM doesn't create foreign key for you, it is a slippery slope to blatant denormalization and eventually NoSQL. EDIT: The other party to blame would be MySQL with its painfully slow "must-make-a-copy-of-everything" ALTER TABLE.
tensor 13y ago
This is a reasonable comment, but nosql databases do nothing to address it. Nor do ORM libraries.
adamconroy 13y ago
I like ORMs but in my experience the best approach is to use a hybrid of ORM, views and sprocs. Ideally each sproc will return the results of querying a view or at a minimum the identical columns, then the views become 1st class entities in your ORM like anything else (except for updatable views which I shy away from). So personally I vow never to write an insert, update or delete again, but I am certainly happy to write queries and tune them if necessary. The one thing that trumps nosql / denormalisation in my opinion is materialised views. Materialised views are a thing of beauty that allow for the design integrity of normalized data and the performance of denormalised data. It seems most people don't use them / understand them because they use b-grade free database engines. You never stop hearing people complain about nulls,types/precedence and joins in SQL, but seriously it isn't that hard to learn. These are the main things that people complain about and regurgitate endlessly, so a little effort would be a big reward.
darkmoth 13y ago
I wrote a bayesian document classifier for one project I was working on - in SQL. Training the system took one INSERT and a small word-split function. Classifying the documents took one SELECT. Even in F#, I couldn't have written a more elegant or more performant solution. In a procedural language it would have been a mess of loops and roundtrips. Good SQL is almost a pure description of your desired result, with none of the "this is how you should do it" cruft. I don't have anything against ORMS - they're almost mandatory due to O-R-impedance mismatch - but too often I see them used instead of operations that should rightly be server-side. And none of US have injection problems, because we're binding our parameters, right? ;-)

by _sh 13y ago

spion 13y ago

How about hybrid sql-builder / data grouper solution? Not limited to ORM methods - get the full power of SQL instead. Not string concatenation - get the full power of the language to build queries. Also, the ability to get join results in either flat or grouped form. For example https://github.com/doxout/anydb-sql https://github.com/doxout/anydb-sql (shameless plug)

_sh 13y ago
Nice. This is exactly what I mean when I talk about ORMs. See how everything's nicer when its an API?

j-kidd 13y ago

I like your comparison of SQL to JavaScript. However, personally I love SQL and always use an ORM. My vow is to never have a line of SQL in my application source code. This is perfectly doable with SQLAlchemy, though not with crappy ORM such as ActiveRecord. Indeed, I blame ActiveRecord for making NoSQL popular. When your ORM doesn't create foreign key for you, it is a slippery slope to blatant denormalization and eventually NoSQL. EDIT: The other party to blame would be MySQL with its painfully slow "must-make-a-copy-of-everything" ALTER TABLE.

tensor 13y ago

This is a reasonable comment, but nosql databases do nothing to address it. Nor do ORM libraries.

adamconroy 13y ago

I like ORMs but in my experience the best approach is to use a hybrid of ORM, views and sprocs. Ideally each sproc will return the results of querying a view or at a minimum the identical columns, then the views become 1st class entities in your ORM like anything else (except for updatable views which I shy away from). So personally I vow never to write an insert, update or delete again, but I am certainly happy to write queries and tune them if necessary. The one thing that trumps nosql / denormalisation in my opinion is materialised views. Materialised views are a thing of beauty that allow for the design integrity of normalized data and the performance of denormalised data. It seems most people don't use them / understand them because they use b-grade free database engines. You never stop hearing people complain about nulls,types/precedence and joins in SQL, but seriously it isn't that hard to learn. These are the main things that people complain about and regurgitate endlessly, so a little effort would be a big reward.

darkmoth 13y ago

I wrote a bayesian document classifier for one project I was working on - in SQL. Training the system took one INSERT and a small word-split function. Classifying the documents took one SELECT. Even in F#, I couldn't have written a more elegant or more performant solution. In a procedural language it would have been a mess of loops and roundtrips. Good SQL is almost a pure description of your desired result, with none of the "this is how you should do it" cruft. I don't have anything against ORMS - they're almost mandatory due to O-R-impedance mismatch - but too often I see them used instead of operations that should rightly be server-side. And none of US have injection problems, because we're binding our parameters, right? ;-)