The Case Against SQL (scattered-thoughts.net) 297
Long-time Slashdot reader RoccamOccam shares "an interesting take on SQL and its issues from Jamie Brandon (who describes himself as an independent researcher who's built database engines, query planners, compilers, developer tools and interfaces).
It's title? "Against SQL." The relational model is great... But SQL is the only widely-used implementation of the relational model, and it is: Inexpressive, Incompressible, Non-porous. This isn't just a matter of some constant programmer overhead, like SQL queries taking 20% longer to write. The fact that these issues exist in our dominant model for accessing data has dramatic downstream effects for the entire industry:
- Complexity is a massive drag on quality and innovation in runtime and tooling
- The need for an application layer with hand-written coordination between database and client renders useless most of the best features of relational databases
The core message that I want people to take away is that there is potentially a huge amount of value to be unlocked by replacing SQL, and more generally in rethinking where and how we draw the lines between databases, query languages and programming languages...
I'd like to finish with this quote from Michael Stonebraker, one of the most prominent figures in the history of relational databases:
"My biggest complaint about System R is that the team never stopped to clean up SQL... All the annoying features of the language have endured to this day. SQL will be the COBOL of 2020..."
It's been interesting to follow the discussion on Twitter, where the post's author tweeted screenshots of actual SQL code to illustrate various shortcomings. But he also notes that "The SQL spec (part 2 = 1732) pages is more than twice the length of the Javascript 2021 spec (879 pages), almost matches the C++ 2020 spec (1853) pages and contains 411 occurrences of 'implementation-defined', occurrences which include type inference and error propagation."
His Twitter feed also includes a supportive retweet from Rust creator Graydon Hoare, and from a Tetrane developer who says "The Rust of SQL remains to be invented. I would like to see it come."
It's title? "Against SQL." The relational model is great... But SQL is the only widely-used implementation of the relational model, and it is: Inexpressive, Incompressible, Non-porous. This isn't just a matter of some constant programmer overhead, like SQL queries taking 20% longer to write. The fact that these issues exist in our dominant model for accessing data has dramatic downstream effects for the entire industry:
- Complexity is a massive drag on quality and innovation in runtime and tooling
- The need for an application layer with hand-written coordination between database and client renders useless most of the best features of relational databases
The core message that I want people to take away is that there is potentially a huge amount of value to be unlocked by replacing SQL, and more generally in rethinking where and how we draw the lines between databases, query languages and programming languages...
I'd like to finish with this quote from Michael Stonebraker, one of the most prominent figures in the history of relational databases:
"My biggest complaint about System R is that the team never stopped to clean up SQL... All the annoying features of the language have endured to this day. SQL will be the COBOL of 2020..."
It's been interesting to follow the discussion on Twitter, where the post's author tweeted screenshots of actual SQL code to illustrate various shortcomings. But he also notes that "The SQL spec (part 2 = 1732) pages is more than twice the length of the Javascript 2021 spec (879 pages), almost matches the C++ 2020 spec (1853) pages and contains 411 occurrences of 'implementation-defined', occurrences which include type inference and error propagation."
His Twitter feed also includes a supportive retweet from Rust creator Graydon Hoare, and from a Tetrane developer who says "The Rust of SQL remains to be invented. I would like to see it come."
OK (Score:5, Interesting)
Great; what's his idea for a replacement?
Re: (Score:3)
Re: (Score:3)
After my painful years of being abused by UniVerse, I will consider it far too soon if I have to ever use a Pick-derivative again. They were fine for the 1970's and 1980's; but beyond that, they were an unmitigated disaster that far outlived their usefulness.
Re:OK (Score:5, Funny)
Great; what's his idea for a replacement?
150 million nested case statements?
Re:OK (Score:5, Interesting)
Personally I'd decouple the syntax of the query language from the DB engines by using a tokenized or byte code style middle language like LLVM did for compilers. Then you use your SQL flavor or something else entirely as a front end query language. Database engines could more easily compete on feature set when you don't have to rewrite all your legacy SQL. I'm sure this is a gross oversimplification but there's my 2 cents.
Re: (Score:3, Interesting)
This used to be how it worked in the 1960's. The current "dynamic execution" of SQL is an invention of the 1990's (in particular Sybase was the first product to support dynamic query execution. Microsoft loved it so much they stole Sybase and renamed it Microsoft SQL Server. Other vendors then began to support "dynamic execution" of SQL).
Re:OK (Score:5, Informative)
Not stolen. This was a 3-way deal between Sybase, Ashton Tate, and Microsoft.
http://www.orafaq.com/maillist... [orafaq.com]
Re:OK (Score:5, Funny)
Hey now. This is Slashdot.
We've always accused Microsoft of stealing everything and we aren't about to change that tradition now!
Re:OK (Score:5, Interesting)
As well, there are many simpler dialects you can write your queries in and the engines will generate the SQL queries by themselves. Have a look at hibernate for example. The hibernate database drivers will even adjust the final SQL queries depending on which flavor of back-end database you are using usually without any modifications to the Hibernate Query Language (HQL).
I know this isn't a perfect one size fits all solution but I think it is still worth mentioning.
Re:OK (Score:5, Insightful)
Great Marxist ideology applied to data languages. Vilify something without a clue as what to replace it with, with some bogus statistics about how wrong it is.
And TFSA (you can guess at what it stands for, but starts with the T is for The and A is for Author) doesn't notice the obvious: SQL is not being actively taught to students outside programming. Isn't that a problem? Even many programmers don't know it well if at all. I picked up a book in 1998 and learned a bit by myself, adopting SQL in 1999 when MySQL was probably just launched, and was able to do all the things I needed. The worst of it all was when I tried some libraries that abstracted some things, and made a huge amount of waste, 100x slower that in should and unusable. After creating my own code, I could run a complex site with 200,000 unique monthly visitors that was snappy and entirely driven by databases (including blob storage and very complex queries), running on a slow pentium on a U1 10,000 from where I resided without even needing a reboot in over 3 years.
Nothing is preventing things better than SQL from happening at all. His posts amount to zero calorie bashing, all attention seeking without anything interesting to add. Actually, what we need is to find a way to have Open Services, like we have an open internet based on a protocol were multiple actors can be part of the network, with interoperability at the core. If you don't like the upcoming Open Services manifesto and movement, you don't yet understand it. It will come. Many are a bit tired of "big bro" using and extending Open Source to restrict us.
Re: (Score:3)
Worth mentioning: there have been tons and tons of attempts to replace SQL.
Re:OK (Score:4, Interesting)
Worth mentioning: there have been tons and tons of attempts to replace SQL.
And they all failed. Generally because the programmers involved didn't understand the relational model they were trying to replace. And even if they did, the wider programming community complained that it was too hard or different from what they learned in school, or isn't sexy enough. Someone above suggested LLVM, which is completely inappropriate for the task. And that's why nobody can replace SQL. You keep comparing it to a general purpose language. It isn't, it is a way to expression relational algebra and with a bit of extension (UDFs) it is mathematically equivalent to the pure functional model. But finding people who can both a) program at a systems level and b) understand the math of relational algebra and predicate calculus is hard. Too many folks who were completely unqualified for such work attempted it with great fan fair (NoSQL).
Building such a system is both hard and risky. But the risk doesn't come from the difficulty of writing such a system. It comes from the fad based programming language market today. Try to get data scientists to use a functional language which is well suited for their work (hint a sys-admins language like Python is very poorly suited). Try to get PHP devs to learn a language that doesn't introduce seconds of extra latency (costing their companies millions in lost customers and revenue). Face it, the problem with software is all the devs who aren't really interested in software. Who are in the industry because they were told it was the only way to make real money. And they don't care that the new thing is better than the old thing. They just don't want to do one second more work than they have to or learn anything new. They paid for a fancy degree after all, that should have taught them all they need to know.
Re: (Score:2)
Face it, the problem with software is all the devs who aren't really interested in software.
I really regret that programming makes so much money for this exact reason.
Re: (Score:2)
Not to belittle your efforts and success, however this sounds pretty silly: After creating my own code, I could run a complex site with 200,000 unique monthly
200,000 / 30 -> 6666 visitors per day
6666 / 24 -> 278 visitors per hour
278 / 60 -> 5 visitors per minute
Seems not reasonable to calculate it down to "per second" :P
So, did you typo somewhere? Or did you have a super strange speed problem, which makes your final result to shine out?
Re:OK (Score:4, Insightful)
I studied economics and greatly respect standards, programming and technology in general. Note the title, "Against SQL" without proposing anything, not even clearly defining the problem. Look at his language:
"dominant model"
"dramatic downstream effects"
"the entire industry"
"massive drag on quality and innovation"
"renders useless most of the best features of relational databases"
"The core message that I want people to take away"
"huge amount of value to be unlocked"
"rethinking where and how we draw the lines"
"I'd like to finish with this quote from..."
"most prominent figures in the history"
That is not the normal language an engineer like Linus or anybody that actually does anything useful would ever use.
Re: (Score:3)
Re:OK (Score:5, Informative)
The relational model is based on the Relational Algebra, and it can be boiled down to six operators, of which three are derived from Set Theory: set union, set difference and the Cartesian product. Additional, the three operations projection, selection and rename are used. All other operators, like the join, can be modelled from this basic operations. The relational model is secure from a programming point of view, that means, that in finite time, it returns a finite set of results. The relational model doesn't have the Halting problem (yes, it is not Turing complete). It is procedural, while most programming languages are descriptive.
You have to understand those basic concepts to make full use of a relational database. If you just use it as a fancy way to store tables, you are using it wrong, as it forces you to reimplement all the relational goodies in your application, badly.
Re: (Score:2)
Re:OK (Score:4, Insightful)
It is [SQL] procedural, while most programming languages are descriptive. :P (Confused?)
That is wrong, but not sure if you wanted to say that the other way around and got drawn away.
SQL is descriptive, most programming languages are imperative. And "procedural" is only a subset of imperative.
BTW: most SQL dialects support stored procedures, too
PostQuel (Score:3)
Should I mention that the original query language of Postgres was not SQL, it was PostQuel [wikipedia.org]
. Many past databases that embraced SQL began with something different. Perhaps we should mine the past for ideas of a superior query language.
Or perhaps the more direct approach would be to return to dBase 3 syntax, of which I have a dim memory.
Re: (Score:3)
The trouble with SQL is that is a bad implementation of the Relational model. The relational model is great, SQL is not.
One of the "features" of SQL that break the relational model is duplicates, as already explained by C.J. Date a long time ago in his paper "Double Trouble, Double Trouble".
Re:OK (Score:4, Insightful)
The point of this story isn't replacing relational databases. It's replacing SQL as the interface language for relational databases.
Re: (Score:2)
The author just wants a language that works like SQL, on relational databases, but with a better syntax.
The article literally starts with "The relational model is great [praise of the relational model] but SQL is the only widely-used implementation of the relational model [criticism of SQL]"
I have seen some half assed attempts, but nothing convincing enough to replace SQL.
Hibernate? (Score:2)
lseek() (Score:2)
Re: (Score:2)
Replacement, courtesy of C. J. Date (Score:3)
The folks who invented the relational model, Codd and Date, suggested a simple relational algebra. See "Sql and Relational Theory, How to Write Accurate SQL Code", 3rd Ed by C. J. Date (Sebastipol, CA, O'Reily and Associates) https://www.oreilly.com/librar... [oreilly.com]
Eg, (P JOIN S) WHERE Pname > Sname
Re: (Score:2)
There's no reason you can't use SQL like that (I usually do). It has a lot of advantages, not the least being that it's actually portable.
" SQL queries taking 20% longer to write." (Score:3)
Using JavaScript to support an argument? (Score:4, Insightful)
Re: (Score:3)
Yeah, but it's verbose, which makes it expressive and compressable, and its as leaky as a sieve which makes it porous, thus meeting all the criteria the author is looking for in a replacement.
Someone dislikes a language (Score:4, Insightful)
Someone dislikes a language. That can be said for literally EVERY FUCKING COMPUTER LANGUAGE in history. Someone somewhere will rant and bitch about it, especially on social media. That doesn't make this newsworthy.
Re:Someone dislikes a language (Score:4, Insightful)
It's more than that. The problem is that SQL has often become the only way to interact with a database system. So much so that you have to write SQL code inside of your (for instance) C code even if you just want to do a simple primary key lookup.
Re: Someone dislikes a language (Score:2)
Re: (Score:2)
Well, of course you would use a library. Preferably a component from a library.
Re: (Score:2)
I admit to being inexperienced in database programming, but in a recent project for work, I found all the SQL syntax was creating a barrier between me and the data. I was using the SQLite library, which is pretty lightweight compared to a full server-client system. I thought it was save me some trouble. It turns out I was vastly overthinking the job. I did not even need a database, because the dataset would never get very big, and the queries were simple.
When I got going on the job, and started getting some
Re: (Score:2)
SQL is a query language, not a programming language. You manage data, not programs, with it. Often times, and I am not an expert, but stand with my experience, people would start to put code in the database that should have not been there to start with.
Re:Someone dislikes a language (Score:5, Insightful)
That's one of the features. Prevents people from poking at the data willy-nilly. Database people call it "integrity."
Ah, so you found your problem.
Re: (Score:2)
And it's really gross that your program submits its SQL code as ascii. Which century were we living in again?
ODBC fail [Re:Someone dislikes a language] (Score:3)
That's probably more about middle-ware than SQL itself, because writing "SELECT * FROM {myTable} WHERE id={myID}" is dirt easy. (Sample string replacement syntax only.)
What's needed is a formal HTTP protocol for talking to RDBMS. ODBC used to be the go-to standard
Re: (Score:3)
What's needed is a formal HTTP protocol for talking to RDBMS. ODBC used to be the go-to standard for it, but has rotted from vendor fights and from being binary-tied, which had 32-to-64 bit upgrade headaches.
ODBC is not a wire protocol. It is a interface specification and shim layer. There is nothing wrong with ODBC. It has not "rotted" and is not going anywhere. As for using HTTP hard pass.
A common "Unix philosophy" is to not tie a protocol to binary formats. ODBC violated that rule and we can see why it's a good rule.
There is no protocol involved with ODBC. ODBC is like OpenGL. Each vendor provides an implementation to the specified interface.
Re:Someone dislikes a language (Score:5, Informative)
No kidding. The reason why SQL exists is that it's "Structured", it's kinda-human readable, and the SQL databases themselves resemble spreadsheets, thus making them hand-editable with very small queries.
There's a lot of stuff I go in and hand-edit on my servers because the overlying CMS either made a mistake, or the user made a mistake, and it's easier to just go to the SQL command line than try to load up a GUI for the database because that opens a security hole in the server.
Likewise fixing security issues are easier to do with the SQL command line.
Here's what happens, every time. The longer a "new" language exists, the closer it starts resembling C++/Java (not Javascript), Javascript has so far resisted that, but as of ES6, all web browsers since 2015 now support making Javascript look like poorly written C++ code.
SQL can not look like C++, and that's probably why some developers have a beef with it.
> Design flaws in the SQL language resulted in a language with no library ecosystem and a burdensome spec which limits innovation.
The only thing wrong with SQL is that various engines don't implement it the same, so you get "innovation" just usually at the expense of portability, otherwise you'd see people dump Oracle at the drop of a hat for anything cheaper to use and cheaper to support.
> Make it porous.
> Allow defining new types, functions, indexes, plan operators etc via wasm plugins (with the calling convention etc in the spec).
Yuck, no. WASM has been the worst evil flung upon the Javascript language, and you want to bring it to SQL so the SQL engine? I've seen enough "exploits" on SQL databases that make the database just spinlock because of bad escapes, and you want to introduce things to crash the database server, no, don't bring Javascript garbage into SQL.
> That code belongs in the application layer. Use the right tool for the job! If your database query language is not the right tool for querying data, that seems like a problem.
The database should not be doing application-layer activities at all. Period. Anyone who suggests otherwise, is literately asking for a thing that is not a database. It's like asking Microsoft Excel to play Chess. Yes you can technically do it, but that's not an efficient use of Excel.
Re: (Score:2)
The reason why SQL exists is that it's "Structured", it's kinda-human readable, and the SQL databases themselves resemble spreadsheets
No, those are only cosmetic reasons. The reason SQL exists is because it expresses a relational algebra.
Re: (Score:2)
*sigh* (Score:5, Insightful)
This is just another JavaScript junkie that fails to understand the reasoning behind the design. SQL has been around for nearly 50 years because it's highly functional, simple, robust, and reliable. JavaScript people make and discard frameworks on an annual basis, so I'm sure he's got a great idea for his latest project but it's likely to be discarded and replaced repeatedly with something of ever greater complexity before someone decides to restart the cycle.
JavaScript has the weird problem of being so flexible that many programmers fall into the metalanguage trap, so everything gets reinvented at a very rapid rate.
Re: (Score:3)
COBOL has been around for 60 years because it's highly functional, simple, robust, and reliable... Well then, gotta get that out there.
The difference is that COBOL has competition. SQL does has not really had much competition after the 70s. Did SQL magically become the optimal language paradigm when first invented such that no competition was ever needed? I dunno, I don't know relational databases at all, but I know languages and SQL seems somewhat unique its uniqueness within a field.
My guess here is t
Re: (Score:2)
Database admins are a special breed of dinosaur, they are perfectly happy doing things they way they have always done them and always will be.
Re: (Score:2)
Ok, then, I do know lots about computer languages. What alternatives to SQL are there with any traction?
Yeah totally agree... (Score:5, Informative)
This is just another JavaScript junkie that fails to understand the reasoning behind the design. SQL has been around for nearly 50 years because it's highly functional, simple, robust, and reliable.
I have to say when I her people complain about SQL I have to wonder if they've done anything really complex... because you are writing real queries you find all of those strange aspects of SQL start to make a lot of sense, even more sense if you've ever tried to write code to build a database...
I'm not saying it's impossible to think that some data an SQL replacement will come along, but I think it would have to be by someone who understood SQL's strengths completely first, to know if the new thing they were working on was any better.
On a side note, a long time ago I found the book "SQL for Smarties" to be really helpful. If you ever are working with SQL I highly recommend it, if it's still around...
Re: (Score:2)
For me, the worst part of SQL is that it forces all my data into a 2D table. In the database, it's in a bunch of sets, not in rows.
Fixing this requires a few minor changes to the language though, not a massive overhaul. imo.
Re: (Score:3)
For me, the worst part of SQL is that it forces all my data into a 2D table. In the database, it's in a bunch of sets, not in rows.
A table is a set of persisted rows. Are you sure you understand DBs as well as you think you do?
Re: (Score:3)
I feel like I know what he's referring to. When you join 2 tables, and it's a master/detail (ie: one to many) join, you get repeated rows in the master table. This results in your application having to loop through the rows doing a "if field x is different that the previous loop, instantiate a whole new data structure...otherwise, just append a new entry to the data structure we used in the previous loop". It happens when the multi-table relationship is flattened down to a single 2D table. I think he's just
Re: (Score:2)
SQL could easily be improved (Score:2)
SELECT I.VALUE, C.NAME FROM INVOICE AS I RIGHT OUTER JOIN CUSTOMER AS C WHERE I.CUSTOMER_ID = C.CUSTOMER_ID
vs
SELECT I.VALUE, I.CUSTOMER.NAME FROM INVOICE
Hmm. I can see why a JavaScript hacker might have trouble with the former. Such a common case that should have been fixed 50 years ago. (I.CUSTOMER would be the name of the ref integrity constraint.)
But then again, I doubt whether a JavaScript hacker really understands what can be done with SQL.
The SQL wars are long over. We now need to ignore the lesso
Re: (Score:3)
From what I can tell, the problem that GraphQL and the like are solving is that young developers (a.k.a Javascript junkies) can't be bothered to learn SQL. Everything else has been abstracted and optimized down to the equivalent of sticking LEGOs together, but SQL will always demand that a developer actually knows things.
"Getting stuff done" has almost fully replaced having tangible skills.
go back to relational algebra notations (Score:5, Insightful)
Personnally, I'd be pretty happy if we could move away from SQL and just express queries using relational algebra notations. Because that's pretty much what SQL is (at least for select-type queries), it is a textual representation of expression of relational algebra.
It's fundamentally a tree, let's represent it as a tree! We do that commonly to express mongodb queries.
Though I suppose nowadays anyone who actually write SQL queries is doing it wrong, so maybe who cares?
Re:go back to relational algebra notations (Score:4, Insightful)
MySQL user to have some rubbish page about why foreign keys were bad 'because it made application programming harder' (and by a staggering co-incidence, MySQL didn't have them at the time - good lord, who would have thought it). They've scrubbed that from the web now but this article is just more of the same moaning.
SQL is a syntax for set theory. If your data is in sets, use it. If it's not, don't.
Re: (Score:2)
Sure SQL is some syntax to express some operations on sets. More precisely, it is a language to write relational algebra expressions. (The mapping is almost perfect with some tiny variance around the edges.) That reliance of SQL being easy to transform into relational algebra expression is the basis of most query optimizers.
https://en.wikipedia.org/wiki/... [wikipedia.org]
Re: (Score:2)
In my experience, the relational algebra part of SQL is the stuff very few people really understand.
99% of the time relational is overkill. People just want some kind of backing store for their. For years MS Access got nulls wrong -- it didn't understand that null = null is *false* -- and it didn't get fixed because it didn't matter to most users, to whom null = null seemed self evident.
Where you have some kind of rich, non-transactional database that you are explore in many alternative ways, relational i
Re: (Score:2)
The problem is that the return set comes as a 2D table. It would be nice if we could get an actual tree instead of a table result. This doesn't require a replacement of SQL though, it's just a minor addition.
Re: (Score:2)
Re: (Score:2)
SQL databases are stored as graphs internally. It's only when they return something that it turns into a table.
Re: (Score:3)
SQL databases are stored as graphs internally.
Nope. They are stored as tables. And meta data to manage those tables. They literally look like a single Excel sheet for each table.
Easy to verify by querying the meta date, e.g. every row in any table has an internal row id, which can be queried. Depending on DB, that can even be a unique ID over all rows over all tables. However usually it is just unique for the table it self.
Re: (Score:2)
I entirely agree with you, and the TFA, and I've been saying this for _years_.
SQL, the language, was designed in a time when "making it look like English" seemed a good way to make languages user friendly (see also COBOL). That didn't work. No manager is typing in their own queries.
What we did get was something that makes composition next to impossible (what the TFA has under 'incompressible'). Ever tried generating queries that combine other generated queries?
That was actually my problem with the so-called
Re: (Score:2)
I've been thinking a good start for a query language would be a functional language.
It would be. Slick did this for Scala (but Slick is no longer actively developed). The DataFrame API in Spark does the same thing. Those are good APIs for querying a DB. But the general programming community didn't like Slick because it was different from Hibernate/SQL which is what they knew. The DataFrame API in Spark isn't used nearly enough and often Spark jobs are solely RDDs (which are 1/10th the speed of the DB kernels). The thing holding back innovation often isn't technical, it is about the w
Languages persist for a reason (Score:2)
Just a proper standard adherence would be a win (Score:5, Insightful)
My biggest grip is that every RDB engine seems to implement its own SQL dialect, if only there was a true adherence to one SQL standard this already would help to move the industry forward.
I dream of a world where you can dump a model from one RDB and import it into another without having to make extensive modifications.
Re: (Score:3, Insightful)
Re: (Score:3)
I don't see how this cartoon is applicable to SQL. There is currently only one relational query language standard in common use. If there were say 5, the cartoon would be relevant.
For frequent needs, having a couple of overlapping standards is often a good thing because it supplies both competition, and variation that may be a better fit per niche.
For example, a query language for "big" systems/apps may have a different flavor than one for smaller systems, as certain conventions work better under each scena
Re: (Score:2)
Re: (Score:2)
My hope is that Postgres and MariaDB could become as dominant in this space as Linux is in OS, so that the Open Source community could force this kind of convergence.
Re: (Score:2)
My hope is that Postgres and MariaDB could become as dominant in this space as Linux is in OS, so that the Open Source community could force this kind of convergence.
MariaDB (and MySQL) kill me in that their double dash comment requires a space between that and the comment text. Coming from other DBs where you can mark and comment out code, and that don't require that space , it's a pain experience.
Re: (Score:2)
While I agree with you - unless a person is actually directly meddling with the database itself from its console, people shouldn't be writing direct-to-the-backend SQL calls anyway. Most languages do offer a way to abstract that up to at least a consistent SQL syntax.
Now, having said that - I pretty regularly find it useful to directly meddle with the database from its console, and it's definitely annoying to have to think "okay this is postgresql, so it wants this..." or "this is sqlite, so I have to type
Re: (Score:2)
You cannot realistically deal with SQL databases without thinking about the generated SQL or being able to understand the generated queries. And SQL is just shit. As a simple example, there is no easy way to turn a select statement into an update statement in the general case. You can turn single-table selects into updates with just some ugly massaging of the query, but as soon as you deal with multiple tables, it all falls apart.
Re: (Score:3)
As a simple example, there is no easy way to turn a select statement into an update statement in the general case. You can turn single-table selects into updates with just some ugly massaging of the query, but as soon as you deal with multiple tables, it all falls apart.
Many SQL dialects have "upserts" that do just this.. consider:
select all of my shitty fields -- from all of my shitty tables
from table a join table b join table c join table d...
where something = something else
and something else = that other thing
and something else = that other thing
and something else = that other thing
and something else = that other thing
all you do is change the select line
to
update c set oldshit = newshit
and all of the sudden table referenced by alias c's oldshit becomes newshit.
There are
Re: (Score:2)
Yes, we use ORM frameworks, but we find that sometimes you still need to write direct calls.
Hrm. (Score:3)
Well, if you do not like it, do not use it.
And by the way, "relational data storage" existed long before SQL.
SQL is not dependent on the underlying storage semantics.
You can use SQL to query a hierarchical or network extended (CODASYL) data stores, for example.
Although in the yesterdays many CODASYL type data stores sprouted extensions that permitted the use of SQL, it was more useful to use the natural navigational interfaces. SQL was simply a method of "declaring what is wanted" and "letting the computer figure out the how" rather than write the navigational code oneself. While it may be implemented on top of a relational data model, this is not and never was a requirement.
I've done plenty of db work sans sql (Score:5, Interesting)
I started with Ingres back in the day, and did queries in Quel. Actually, in university I took a DB class and Quel and SQL were just part of it. We talked about tuples and all that before we ever got to an implementation. We also talked about and used prolog. The class was expansive.
I stuck with Quel long past when I should have. In the 90s, I worked on some projects with FoxPro and we did plenty of DB manipulation without using a query language. It allowed the programmer to step through tables using cursors and do anything without using SQL. But when the SQL parser became a thing it was much easier to do multi-table data lookups with SQL. The amount of code that one SQL statement could replace was staggering. I had a business partner who didn't know SQL so everything was done the old-fashioned way for him.
Similar to dbase/foxpro is the whole dbm file format and interfaces.
Other things exist, but there's a reason that SQL is popular.
Who is this clown? (Score:4, Insightful)
SQL is my favorite language: it's terse, expressive, and can even still be fun after more than twenty years.
Re: (Score:3, Informative)
Re: (Score:3)
There is a reason nobody bothers with that.
Making the joins explicit means you code doesnt break if the table is restructured.
You want the joins to be explicit to also inform the person reading the code 10 years later of what is happening, and for it not to be magic.
Magic join syntax would be the first thing I disabled, it is was a standard feature of any db.
Too much SQL or not enough? (Score:5, Interesting)
I was in an Oracle shop for my whole career (a common experience; I actually LOL'd when I saw one of the criticisms was "implementation dependent" - for most large-corporate employees, you only see one or two implementations in a whole career).
Then I learned Postgres because of Post GIS, and discovered that SQL can do a whole lot more than I thought. (Later, I found that Oracle had kept up with a lot of it, I just hadn't been keeping up with Oracle.)
I realized that we could probably cut out a whole lot of procedural code in our reports and queries by just really using the database features: fake "columns" that were actually calculated (something I'd first seen with Delphi in the 90s, and loved), really using views to be always-available reports, rather than writing report queries. Building your own in-database functions. And Postgres lets you do that programming in a choice of languages, but the functions become part of the database, not a separate program.
All that is beyond most of the users I served. Really basic SQL was NOT beyond them, though, and any possible replacement for it has to pass that bar, for me: non-programmers have to be able to at least do simple things with it, because no developer can think of their every need. SQL was invented to be the idiot's access to a database - idiots who couldn't/wouldn't touch COBOL or anything like it - certainly not Rust or whatever. You have to be able to write one-line "programs" that work.
I await that proposal eagerly. But not with breath held in.
Re: (Score:2)
rbrander absolutely nailed it.
If someone that ACTUALLY understands how DB's work AND has an accurate business model of what the data and goal is then you start with a great useful DB.
Then feature creep happens and by then maintenance/development has been farmed out to "DBA's" who seem to have acquired that title because they could spell it.
(I say that due primarily to experience with what happened at USWest after they hired the Tata dude as the CIO.)
Total travesty.
Don't blame Oracle, or SQL, or Oracle's PL-
Damn... (Score:2)
Is it bitch about SQL time again? Time flies.
Great (Score:3)
These guys remind me of the mice in the Belling the Cat fable: https://en.wikipedia.org/wiki/... [wikipedia.org]
All those problems except the important one (Score:2)
How about fixing the trillion dollar mistake first ... the fucking escape characters.
Give all parameter strings a length in the query language and use an editor which hides the length, colour codes the string and automatically stores the length for you. Being able to use plain Vi is not a good reason to throw billions at criminals and unnecessary downtime each year.
Re: (Score:2)
Not me personally, but yes that and similar shit still happens all day every day. Bandaiding it above the language level is fragile and insufficient.
I would venture... (Score:5, Interesting)
Now, just like any other programming language, it is incredibly easy to make something that works, but does so very poorly. This, too, isn't a valid reason to say that the tool itself is bad, but rather that the tool user needs more experience.
Granted, there are some parts of Sql that feel like they are from the 70s
The author posits that "The need for an application layer with hand-written coordination between database and client renders useless most of the best features of relational databases". I would imagine he is referring to things like Entity Framework. My best advice here is to not use these things, but rather to write in terms of Stored Procedures which provide a strong API on which your application can bind. Think of the DB itself as a micro-service. If your application has (or generates) Sql embedded in it, you are in for a world of hurt in terms of long term maintainability. EF and the like are great if you are limited in terms of development time
SQL, underrated least-worst answer, misapplied (Score:5, Insightful)
Every SQL replacement I've seen sucks even worse. That's why I am cynical about this. Every data querying API sucks. Maybe slightly less than SQL, but every one I've seen that is even slightly friendlier than SQL was so crippled, we still needed native SQL to close the gaps in their language.
That said, SQL does suck. Unlike Java, the best practices are not well documented. There's 1 formatting standard in Java...when people argue, they argue about spaces vs tabs or whether the brace goes a newline....stupid shit...for SQL, there's no common standard. Is everything in caps? I think so, but I don't really know...and no one does. How does one distinguish between variables, keywords, columns, and tables? I guess you just have to memorize what's a keyword for your platform and hope for the best. In Java and most languages, naming patterns are firmly established for variables vs classnames vs constants. There HAS to be a better way, but whatever it is, I haven't yet seen it.
DB design is a dark art. All my life, I have been told best practices, I've absorbed, but I can't find them documented anywhere, other than an occasional blog. In Java, every basic best practice is well documented, in print, if not official documentation. There is little ambiguity.
The final problem is RDBMS style databases and SQL are a poor fit for most applications. They're designed for inventory management, well-defined structures. In the classic when all you have is a hammer, everything looks like a nail...they're used for so many applications that don't fit the relational model. Need to store user applied data with a dynamic and variable structure?...well, your boss won't let you use Mongo, so you have to figure out how to code a tree in Oracle...something it is very poorly designed for.
Most applications I have worked on take user input and apply it dynamically. Some customers have a complex nested structure, some have a simple one. It's rare I get to work on one where everything follows a precise template. The most popular class of applications store user configuration. The configuration is often nested and can vary widely from customer to customer. Even most commerce situations have this level of complexity. Few merchants just sell you simple goods. Most who are hiring want deep customization, which often ends up being represented as a variable-structure tree.
The final problem is most applications just use persistence + reporting. They gather a bunch of info from a user, save it, and retrieve it in the exact same format later. Relational structures are terrible fits for that. You end up breaking a JSON document into a bunch of SQL statements only to reassemble them right back into the way you inserted it. This is a terrible fit for an RDBMS, only justified by some report that requires it in a slightly different format like once a week.
So...DBs...terrible fit for most applications, but the admins do what they know and they know Oracle better than cassandra or Mongo, so we try to fit a document-based application or a loosely structured one into a relational model....at the peril of our time, and the company's money....I warn them they're wasting their money and there's a much better way to solve their problem, but they typically ignore me and my team and do what they want anyway. As a professional, it's mildly frustrating, but I do this the money...I can do good work for you or waste your money at your direction...either way, I am doing this to support my family. Doing good work is a means to a paycheck for me, not my religion.
Re: (Score:2)
Perhaps you're just the fellow to write the 'SQL Best Practices' book - I think you'll find writing to be an emotionally satisfying task. Also, in the process of writing a book you actually think about things that never occurred to you until they are suggested by putting your thoughts to words. One caveat, though - writing a technical book is VERY time consuming, if done correctly.
Re: (Score:3)
Sigh, we just don't LEARN in this field (Score:3)
I figure this is at least the 4th generation of arguments. (1) when SQL and the Relational model was first proposed, (2) when we had implementations and the performance was poor; (3) when object databases were going to be The Big Thing; (4) now this one. Of course, your enumeration may differ.
The point is that a lot of these arguments have been made before, so the starting point -should be- "here's what's different from the last times we argued this."
But ours is not a learning field. Friends have been collecting "things we've forgotten" - topics in computer science/IT/software engineering that were investigated, debated, even resolved, and then forgotten until someone discovers The New Thing.
(Another example, the recent post here on Rust verification. That reminded me of the substantial efforts into the formal specification of Ada83 and then the work on the SPARK subset with its associated theorem prover. But of course, Ada is not 'politically correct' to mention in Computer Science, despite what was actually demonstrated in life-cycle costs.)
Inexpressive, Incompressible, Non-porous (Score:3)
Just like the author's use of the English language.
The problem (Score:2)
This isn't news. (Score:3)
That SQL is strange at best and a serious resource bottleneck on regular days has been known ever since people have been using it as a regular persistence api. DB experts have been scratching their heads for decades over why in God's name the scripting, web and Java camp has been using a language meant as an end user interface back in the 1970ies to automate DBs. I don't get it either, it's probably something that 'just happened'and I've stopped wondering a few years ago and just play along.
The NoSQL thing that started a while back was the correct idea, with the only problem being that it was started by people ago didn't get relations and ditched those right along with SQL, not really solving the SQL problem. Until someone just takes a table engine and builds a platforms native transactions right on top of it I guess we're stuck with SQL as some strange traditional DB api. I only use a fraction of it with Maria and MySQL, so I couldn't really care that much anyway, aside from performance that is.
Re:Wrong (Score:5, Insightful)
Re: (Score:2)
Re: (Score:2)
To be fair, at least on the Mac and iOS the auto-correct functionality replaces any instance of "its" with "it's". And, even if you notice and go back to fix it, various things seem to re-trigger the "fix", which you may not notice.
The only solution I've found is to remove that completely from the auto-correct list; but I'm betting very few people do that (although I eventually did).
Re: (Score:2)
To be fair, at least on the Mac and iOS the auto-correct functionality replaces any instance of "its" with "it's".
And at least on IOS it offers you the usage of the word you type "as is" *before* the autocorrect kicks in. So you can happily type "its" and have that exact sequence inserted into your text.
Re: (Score:2)
Odd. Mine insists on doing the opposite. It's very annoying.
Apparently in that previous sentence it can't form an opinion about which is correct so it will just shut up and take either.