I'm probably one of those programmers you think "use SQL quite happily". You're wrong.
SQL is an abstraction with myriad problems in both its core design and its disjointed implementations. ORMs and similar interfaces are crappy abstractions on top of a crappy abstraction. You get the problems of SQL combined with the problems of the ORM.
Preferring to remove a set of problems and be left with only the problems of SQL is not the same thing as liking SQL, it is merely preferring the lesser of two evils.
Well, I like SQL, but let me play devil's advocate and guess at some of the ways people may consider it "bad":
- SQL queries flatten structured and hierarchical relationships into unstructured rows, often requiring very creative join operations to get at the exact set of data you want, after which you then have to convert everything back to the structured data you wanted in the first place. ORM's are a leaky abstraction on top of that, which means that when you use them you end up dealing with two problems instead of one.
- SQL is superficially simple, but at scale the simplicity is a lie. Thhe performance trade-offs you make require you to understand the internals of the database you're running on and how it interprets the query, and often you feel like you're dealing with a too high level API. Often you're resigned to "tricks" to force the engine to do the right thing, like optimizer hints, special index types or denormalized data.
- SQL has no ability to express relexation of the ACID principles. If you're streaming weather data into a SQL database, it could be ok to drop inserts here and there to gain throughput for the data analysis operations. SQL has no abilities to express such a thing (except in db-specific hacks).
Even those reasons are really stretching for it these days.
There are numerous ways of dealing with hierarchical or otherwise structured data using relational DBs. Support for recursive CTEs is becoming more common, which makes such querying quite clean and simple. And with many relational systems offering support for storing and manipulating XML and JSON data, storing more structured data becomes a non-issue.
I think that SQL managing to hide a lot of the underlying implementation details is one of its strongest points. In many situations the defaults are more than sufficient, saving developers and users a significant time. Yes, there are situations where more control over the exact handling is needed, but it gives ways of managing this. At least a simplified, high-level abstraction is offered; many NoSQL systems immediately force you to take a very low-level approach for even the simplest of queries.
And I don't see the ability to intentionally lose data as something that's good, either. The database should be concerned with doing everything in its power to protect and ensure the integrity of the data it has been given. If it's fine to lose some of that data, then whatever or whoever is providing that data should just not provide it to the DB in the first place.
So while some people may present those as possible disadvantages of SQL, I just don't think the arguments really hold up upon further scrutiny.
> SQL has no ability to express relexation of the ACID principles. If you're streaming weather data into a SQL database, it could be ok to drop inserts here and there to gain throughput for the data analysis operations. SQL has no abilities to express such a thing (except in db-specific hacks).
Why should the language control the underlying data store to that level? Also, SQL itself is not defined by or defines ACID principles. You can (and some people do) run SQL over very non-ACID databases (everyone using MyISAM or SQLite?)
I personally feel that tying the data-store so close to the query language is a recipe for unneeded complexity. Though, the transaction isolation levels may be close to what you're talking about (though, I don't think there is anyway to tell a db like PostgreSQL "Go ahead, lose this. I don't care about it".)
> SQL is superficially simple, but at scale the simplicity is a lie. Thhe performance trade-offs you make require you to understand the internals of the database you're running on and how it interprets the query, and often you feel like you're dealing with a too high level API. Often you're resigned to "tricks" to force the engine to do the right thing, like optimizer hints, special index types or denormalized data.
Please tell me of anything where it isn't true that the more intimately you know your tools the better you can use them. Quite honestly most query planners are really friggin' good at what they do (just like a compiler, or are you a person that still claims hand tune assembly is the answer to everything).
EDIT: PostgreSQL does allow unlogged tables that are not crash safe, though I don't think they're randomly lose rows, but are faster. So there are some semantics that allow you to control that, but they are probably vendor-specific (getting into my complexity argument). Thanks Myon on #postgresql for pointing that out.
There is Asynchronous Commit feature in postgres, that allows you to gain performance by allowing postgres to lose some commited transactions. It can be controlled on transaction level and is exactly :"Go ahead, lose this. I don't care about it". Manual: http://www.postgresql.org/docs/current/static/wal-async-comm...
Like an unlogged table that's only not crash safe. I think GP was talking about rows actually not being written to the db during normal circumstances. Maybe I'm misunderstanding what GP is asking for.
Hm. "rows actually not being written to the db during normal circumstances". A database that acts like /dev/null ? Like BLACKHOLE MySQL storage engine? I can not imagine a real use case, where it will be needed. If you allow a database to lose "some" rows it may well lose all of them. What is the purpose of a database that does not store your data?
I'm not the one who said "If you're streaming weather data into a SQL database, it could be ok to drop inserts here and there to gain throughput for the data analysis operations. SQL has no abilities to express such a thing (except in db-specific hacks)."
I can understand wanting an unlogged table for the speedup (missing the last sec of weather data won't cause huge problems and I'm more forgiving of missing data when a whole machine fails), but just to be able to randomly drop things seems scary.
I know we're not supposed to comment just to say you agree with the above post, but: OMG yes! Everything you just wrote. Relational algebra is a great concept for a data-store language, but why are we stuck with the COBOL of data-store languages?
I thought COBOL was hated because it's very verbose, the programs written in it are very complex because of the domain, and contain warts because they're so old.
I think COBOL is just being thrown around here as a euphemism for old people used this. ORMs are 1000x more verbose than SQL. Map-reduce queries are no shorter, require you to denormalize your data ahead of time in frequently nonobvious (and always inflexible) ways, and have vastly different syntaxes and semantics depending on which product you're using. They're definitely new, though.
All the stuff the grandparent post mentioned. Insane 3-value boolean logic. The fact that the result-set must be in the form of a table instead of a graph of data (when the client code wants a graph). Even just moving the "select" keyword to the end of the query so query-writing tools can provide some autocomplete.
Even simple things like providing a simple syntax for walking foreign key relations instead of cumbersome Join operations. Using any ORM worth its salt, I can say customer.company, whereas in native SQL I have to say "from customer join company on customer.companyid = company.companyid".
To me, SQL is like Common LISP. A fantastic invention of a bygone era that has stood the test of time... but is stubbornly resistant to real improvement and shows too many warts of its age.
And again, as the grandparent said, the cases where the abstraction fails and you end up having to figure out how exactly it's implementing your query. Abstraction failures make a tool worse-than-useless, because it means I have to not only understand the problem the tool is trying to solve, but I have to understand the tool and the quirks of its handling of the problem.
I don't want to imply that ORMs are better (the last thing we need is another abstraction layer) or NoSQL is better (throwing the baby out with the bath-water there) just that SQL is old and has not seen the kind of improvement that other languages have seen.
> The fact that the result-set must be in the form of a table instead of a graph
Don't use a hammer to screw in a screw. There are graph databases (which support ACID, btw) out there for a reason.
> A fantastic invention of a bygone era that has stood the test of time... but is stubbornly resistant to real improvement and shows too many warts of its age.
I don't get this. It is improving and it's stood the test of time because it solves a problem and solves it well.
> And again, as the grandparent said, the cases where the abstraction fails and you end up having to figure out how exactly it's implementing your query. Abstraction failures make a tool worse-than-useless, because it means I have to not only understand the problem the tool is trying to solve, but I have to understand the tool and the quirks of its handling of the problem.
I don't quite get you. When a SQL query fails, it's not because you need to understand the database arch better. It's like when a C program fails, you don't need to understand GCC to fix it. You do however, need to understand how things are being used to pull the most performance out of it as possible, just like a regular language and a compiler.
Paraphrasing Rich Hickey: It's primarily a user oriented language and there is no underlying abstraction for external programs or systems. In the end programmatic access boils down to using StringBuilders.
* SQL not being relational algebra
* SQL implementations being incompatible
Both are real but I have not experienced either of these being a problem in real usage. Despite the problems caused by NULLs and that SQL allows duplicates I do not think the alternative would be better for real world programming. And the incompatibilities are avoided by picking one database per project and sticking with it.
Even if you reduce it to its most basic issues, you missed a third: Incompleteness of implementations.
If you have not experienced problems as a result of these core issues, it honestly makes me feel you must not have a lot of experience -- at least not in diverse projects and environments.
That you think picking one database is some sort of viable solution in the general case amplifies that feeling. That is so often simply not an option, and even where supporting only one database at a time is an option, you absolutely cannot guarantee that you will not have to migrate later. I've been through that pain many times, it is a real-world problem.
I honestly think you should look at the database just like any other piece of your code, you cannot just replace GTK with WX Widgets and except there to be no migration work. And most of these differences between databases have nothing to do with SQL, but rather databases not implementing it correctly for legacy reasons.
I would not expect a new database query language or NoSQL databases to do this better.
SQL is an abstraction with myriad problems in both its core design and its disjointed implementations. ORMs and similar interfaces are crappy abstractions on top of a crappy abstraction. You get the problems of SQL combined with the problems of the ORM.
Preferring to remove a set of problems and be left with only the problems of SQL is not the same thing as liking SQL, it is merely preferring the lesser of two evils.