I'd wager that if you re-wrote those snippets as Ruby or Scala operating against something tabular like CSV files, with all the joins and aggregates and so on done in code, it would look uglier.
* ok sometimes sql (thinking specifically of SELECT statements) gets code-y, e.g. with inline formulas. But generally its more on the specification side.
Codebases I work in continue to use ORMs extensively, but when I need a query of any complexity I'm far more likely to start with raw SQL than to try to make the ORM do what I want. I'm far more likely to "let the database do the work" when I need to do any kind of data analysis or reconciliation. It is very good at what it does, and if you understand how it works it's very simple to do complex things, and quickly. For me, SQL often elicits a response of wonder, and even gratitude for its expressiveness and power.
I also suspect your feelings are rooted in syntactic aesthetics (there's little else in common between SQL and COBOL, for example). I can certainly agree that the syntax of SQL is not what I would choose (though I would not look to Ruby or Scala as examples of my preferences). But SQL is not just syntax, it's also a tool with incredibly expressive capabilities for viewing, analyzing and manipulating data effectively.
To you this is a bad thing, to me, this is exactly why I like SQL. I don't need to be in love with the elegance and majesty of a language I just need it to work and SQL typically just works and 9 times out of 10 it hooks up nicely with whatever high level language I'm using.
And for what it's worth, I've seen quite a few senior level SQL developers write some pythonic SQL queries that would put my regular Python programming to shame.
Don’t get me wrong, I see where SQL shines, but I also see where my (your, their, anyone’s) mind shines and where it will lose its traction and prediction abilities. People are fine with some level of declarativeness, but add some more and it turns into hardest puzzles where formulating it correctly is a hardly provable task itself. While that’s true for imperative code, it breaks along the way of your thinking, and not at randomly ‘optimized’ expressions. UB issues in C language group is essentially the same sort of trouble.
And although it may be a matter of opinion, but I find a query that runs one or more orders of magnitude faster than another query to be a thing of beauty.
There's precious little difference between
set.select { |x| x.a > 10 }
.group_by { |x| x.c }
.map { |c, xs| xs.length }
select count(*)
from set
where a > 10
group by c
Whether or not you're actually writing SQL, it's a good conceptual approach.
The ironic thing is that the way developers like to deal with data today is more like how they did in the early days of COBOL, to which SQL was an improvement.
The first computerized databases were navigational, which just means hierarchical objects. You "navigated" through the data to find the parts that were interesting for a given query, just like with JSON you might loop around through the properties. In 1973 Charles Bachman wrote a book called The Programmer as Navigator.
These data structures were insidious, because: (1) you wind up with duplicated data, vulnerable to getting out of sync with itself, and (2) complex queries can get slow. For example, imagine an array of Customer objects. Each has an Orders field, which is an array of Order objects. Each of those has, among other things, fields for the item name and description, and so on. With this structure, it's easy to fetch all the orders of a certain customer, but it's slow and complex for other queries, like the total number of orders for each item. For that, you might duplicate the data into a different structure. It was just like NoSQL, only there was no SQL at the time. It was PreSQL.
Programmers are immediately attracted to such data structures because they are amenable to the first few pages you have in mind to build. It's really easy to run those nested objects through a template and output HTML, and it's straightforward to take data from a form and save it as one of these objects. As your application grows and spirals, though, your original data structures become more and more cumbersome and less suited to the new pages you have to make.
This was a problem in the 1960s and 70s just as much as today, which is why E. F. Codd wrote his papers, most famously "A Relational Model of Data for Large Shared Data Banks." You might say, relational? Those old navigatorial objects sounded like they had lots of relationships. But it is a popular misconception that Relational here meant the relationships among tables (i.e., foreign keys). Dr. Codd was a mathematician, and he meant the mathematical term relation, which is essentially a grid of values, a table. So they were called relational databases not because you could relate one table to another but because they were databases made up of relations (tables).
Tabular data solves the speed problem in navigational data. But now fetching your data is even more tedious, if you have to navigate those tables by hand (Loop through Table A. If the value in cell 12 > 10, then save it to a temporary variable...). But in that very same paper, Codd also proposed a very high-level language for working with the tables. It wasn't called SQL. IBM came up with SQL specifically, after examining Codd's papers (who in fact was a researcher at IBM). Believe me, SQL was an improvement. Codd's original language, called Alpha, was mathematical hieroglyphics. The foundation was solid but the user friendliness was lacking. SQL was an attempt to have the same nature but resemble English instead of Mathematics.
But the two pillars, tabular data structures and a high-level query language, were introduced simultaneously and are both equally part of what makes SQL what it is. Which one would you like to remove?
Chesterton's Fence comes to mind when watching programmers meet SQL:
"In the matter of reforming things, as distinct from deforming them, there is one plain and simple principle; a principle which will probably be called a paradox. There exists in such a case a certain institution or law; let us say, for the sake of simplicity, a fence or gate erected across a road. The more modern type of reformer goes gaily up to it and says, 'I don't see the use of this; let us clear it away.' To which the more intelligent type of reformer will do well to answer: 'If you don't see the use of it, I certainly won't let you clear it away. Go away and think. Then, when you can come back and tell me that you do see the use of it, I may allow you to destroy it.'" --- https://en.wikipedia.org/wiki/Wikipedia:Chesterton%27s_fence
> If it helps, you don't really have to type SQL key words in all capitals ;) I stopped years ago.
I continue to urge my team to capitalize SQL keywords. This is because the vast majority of SQL queries in our codebases are embedded in another language, as a string. Syntax highlighting is not available (I know there are some tools for this in some environments for some host languages, but it's not widely available or even remotely a solved problem). Static analysis tools for this scenario are generally hard to come by. Every syntactical hint is a godsend for reading and comprehending these queries. I also encourage quoting every identifier even if it isn't strictly required, and extensively using whitespace to make a query's structure more apparent.
If I were writing and reading SQL under better circumstances, it's quite likely I would have different preferences.
But I have taken it one step further and reaped a boon with a more advanced technique: I extract as much as I can of those multi-line SQL statements into database views and, if absolutely necessary, functions. I define these in .sql files, so first of all: syntax highlighting! The strings embedded in my code therefore become mostly one-liners:
select * from view where a = ?
I version-control the SQL files right along with the normal codebase. Migrations aren't complicated for me, the way that some people complain about them. Then again, I am super-comfortable writing raw SQL. Especially with Postgres, which lets you wrap BEGIN and ROLLBACK around definitions and redefinitions of tables, views, functions --- all DDL --- it's very safe to test and idempotent to run over and over.
RE version control: yes, everything that interfaces with the database is in version control.
RE migrations: I also don't find them confounding, and I've mandated that my team write them all in raw SQL rather than reusing abstractions that were available in our environment, because what I discovered was that migrations were being altered unexpectedly over time where imported abstractions were changing. Everyone has benefited from this.
RE transactions: my only complaint is that (AFAIK, and would happily be corrected if I'm wrong), `BEGIN`, any operation, and `END` are separate statements. When I'm experimenting before testing a full migration, I'll often break down smaller problems and test them within a transaction in my preferred GUI (Postico). Which by default runs the statement where the cursor is placed, and only runs multiple statements if you (remember to) select them. I would love to be able to wrap an entire statement (or set of statements if need be) in a single `BEGIN ... RETURNING * ... ROLLBACK` statement.
psql> \i changes.sql
begin;
alter table t1 add column c ...;
alter table t2 drop column d ...;
alter table t3 alter column e ...;
drop view if exists v;
create view v as
select ...
;
rollback;
If I wanted to test out just some of the statements, I would comment out the others.
EDIT:
You can inspect the changes in flight by inserting a select-statement:
begin;
alter table t1 alter column c ...;
select *
from t1
where ...
;
rollback;
Edit: I also don't just use psql, because having normal editing controls that I'm used to in my operating system is muscle memory I leverage and also error prone if I try to use the wrong tool with the wrong muscle memory. I know how to move a caret around every single Mac app, but it works differently the moment I enter anything other than emacs in my terminal.
All true, but you still can't express a transaction as a single expression.
> Interesting, Hacker News's user interface puts no reply link below your comment. So I could not reply directly. I guess it allows only so many nested replies. This is probably an optimization for readability or to avoid flamewars
They just hide the reply link at a certain depth of threading. You can navigate to the parent comment (by parent link or datestamp depending) and reply.
If you've got tree or graph structures, you're better off denormalizing paths or transitive closures, to reduce the amount of indirection required to answer questions.
The bigger issue is the lack of performant abstractions - I copy-paste orders of magnitude more code in SQL than in any other language I've used. There are basically just views and functions, and both can carry substantial (read: multiple orders of magnitude) performance penalties compared to the copy-paste approach, especially when you try to nest or compose them. Materialized views rectify this somewhat but they come with various RDBMS-specific limitations and gotchas.
Probably the biggest single determinant of a SQL query's performance is the order of joins. The best join order is dependent on how many rows result after each join, which in turn depends on how selective the predicates are that can be applied to each table, and that depends on the data distribution. The database does this with statistics. That means it can change the join order - the nesting level of your respective loops - as the data distribution changes.