
SQL is the perfect interface - dangoldin
http://dangoldin.com/2017/04/11/sql-is-the-perfect-interface/
======
kevinmannix
I personally love SQL. It's beautiful to write, and once you "get it" the
syntax is truly powerful. There always seems to be a lot of hate around it,
and sometimes I think that's simply because it's an "old" technology. As
someone who's recently out of school, many of my peers prefer NoSQL due to the
rapid speed of iteration and low learning cost (until it's too late, that is).

Also, relational database design is hard to wrap your mind around at first -
but once it clicks it's quite the eureka moment. I think people forget that it
is a learned pattern and can be frustrating to someone who hasn't taken the
time to learn, so the alternative of a NoSQL db is much more attractive.

~~~
adjkant
I've often heard that any good NoSQL database will likely end up implementing
SQL constraints. From what I've seen, I tend to agree. I have never seen a
project that SQL couldn't work well for. That said, I haven't touched some of
the AI/ML stuff that I'm sure has some valid and unique uses of it. My point
is going more towards general software products.

~~~
pc2g4d
I've seen in person how an attempt at creating a novel data processing engine
just ended up reimplementing the vast majority of the SQL standard. It's
almost like a fundamental property of nature.

------
elcritch
The mathematical theory that SQL builds on is very solid, and being a
mathematical construct I'd argue makes it an "ideal interface" as it captures
almost all useful data-table operations in a consistent and concrete logical
framework.

SQL as a language? Meh. Powerful but often clunky and many of the most
powerful constructs are implementation specific (support CTE's vary a lot). It
always strikes me that almost every project seems to ditch direct SQL coding
for ORM's. The best code interface to SQL I've found is Elixir's Ecto project,
which turns functional constructs into appropriate SQL statements. Otherwise
both Postgres and MySQL use proprietary binary wire formats. New DB's like
cockroachdb choose an existing binary protocol, or use ODBC. Not really sql
interfaces, as in the textual ansi standard.

Still it seems like an industry standard around something like RethinkDB's
query AST supporting generalized set of tuple-calculus or relational algebra
operations and extra features could provide a much better and consistent code-
to-DB interface. Then whether it's a key-value store supporting only a few
operations or a full "SQL" interface, it could possibly enable much more
innovation and even cooperation. For example, a relational-algebra functional
DSL or sql-text interface could be used on the same data store. The one major
missing feature in this scheme would be transactions, though it's almost
certain the mathematics for describing transactions on datasets has been
hashed out many times.

------
mayoff
If I could make one change to SQL, it would be to put the FROM clause before
the SELECT clause. This would make it so much easier to do good
autocompletion.

~~~
combatentropy
I have thought the same thing, because it would match the order of other
statements (insert, update, and delete) which begin with the source.

In fact I would put the select after the where clause:

    
    
        from t
        where x = 'y'
        select a, b, c
        order by a, b, c

~~~
kedean
The problem with that order is that where often depends on what has been
selected. If you select max(a) as ma, you should know that before you try to
compare it to something.

~~~
combatentropy
This doesn't work, at least in PostgreSQL:

    
    
       select b, max(a) as ma
       from t
       group by b
       where ma > 100
    
       ERROR:  column "ma" does not exist
    

You would have to use the having clause:

    
    
       select b
       from t
       group by b
       having max(a) > 100

------
raarts
Agree completely. It's even my opinion that the entire concept of ORMs was
misguided to begin with and just started out as a cop out to prevent learning
SQL, using the (admittedly) ubiquity of SQL injections as an excuse.

Every time I seen an ORM used it ended up causing performance problems and/or
needed hand-optimized SQL.

Regardless, SQL rules.

~~~
rini17
I agree, with exception: If the ORM exercises compile-time checking, that is
an advantage against SQL, where errors only happen at runtime.

