SQL is the perfect interface | Hacker News Reader

SQL is the perfect interface

dangoldin.com

24 pointsdangoldin13 comments

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.

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.

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.

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.

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.

Requiring the WHERE clause in DELETE FROM would probably have avoided some very expensive disasters, too...

I'll just come out and say it, modern SQL amazing and powerful but it is not a good language. The language spec shows a weird obsession with "natural language" style programming, sometimes at the cost of safety, consistency and expressiveness. It is not a good language to generate by code, nor to use directly on the command line. It is not a good language to efficiently access your stored data, nor to express relational algebra. But you're going to have to learn it, and modern databases do amazing things with it to make it work.

Agreed, I've always thought this, CTE's kind of do this, but unfortunately Postgres does not optimise them (one of it's few failings). The SQL standard should be changed to optionally allow FROM to come first. SQl is amazingly powerful, sometimes over verbose though e.g windows functions. Also Natural join should be improved, with an option to use Foreign Keys to determine columns to join on, instead of just common column names which can be risky.

It makes sense for the simple case but I can see it getting a bit hairy if you're doing a variety of joins.

Then it would be FROM X join Y on .. join Z on .. join (..) as T SELECT X.A, Y.B WHERE ..

I guess it's jarring but that's due to me not being used to it. Some people prefer doing joins using there where clause in which case the FROM ends up being in closer to location to the WHERE.

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

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.

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

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.

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

Reply on news.ycombinator.com