On Thu, Sep 12, 2019 at 01:01:59PM +0300, Lassi Kortela wrote:
> Been wondering about this for a few years.

The reason SQL made it big is that it was a standardised way of doing
queries.  Before SQL, all we had was what is now called "NoSQL":
databases which did not speak a common query language.  They all had
their own interfaces which were very much tied to the implementation.

> 2. DB library wastes time parsing the programmer's SQL into a binary format
> that is easy for the DB engine to optimize and execute.
>
> Given that billions of queries are done around the world every second, why
> isn't there a standard binary format for this stuff? That format could be
> generated directly from e.g. a S-expression based Lisp DSL, would support
> easy metaprogramming, and would instantly avoid all the quoting gotchas and
> all the unnecessary parsing.

Its time probably has come.

> There are many DSLs (even many without object-relational mapping, such as
> Roda for Ruby or Korma for Clojure) but they all convert to SQL under the
> hood for no particular reason.

The reason is that this is the only way that popular databases expose.

> Some DBs (Oracle, Postgres) have custom drivers or protocols that presumably
> use a binary format. Could something be generalized from these?

The binary format used over the wire in the protocol is binary but that
only applies to the values being sent back and forth.  The queries are
still represented as strings.

You might like to take a look at EdgeDB, which is a project that's
trying to replace SQL by a more composable language.  They're doing this
by replacing the parser in PostgreSQL if I'm not mistaken.

https://edgedb.com/

There's some really cool stuff going on there.

And for the record, I totally agree with you, having direct access to
the AST or something like it would make things so much simpler.  SQL
is a baroque mess and the syntax is too irregular to make it easy to
compose programmatically.

Cheers,
Peter