News for hackers. Stuff that matters.
Re:SQL as an API language by skrebbel on Thursday October 04, 2018 @04:13PM (#18143270)
Note that this is solely in the context of a static data set. That makes this a relatively limited idea, since most datasets are either dynamic or small enough to just copy over. I write this not to dismiss Datasette, which looks like an excellently designed tool for the problem it's meant to tackle, but mostly as a response to the title of this particular HN submission, "SQL as an API language" which suggests maybe more than "just" querying static data.

Thing is, I really want to use/expose SQL as an API language for dynamic data.

E.g. the author writes:

> On hidden implementation details: since we are publishing static data rather than maintaining an evolving API, we can mostly ignore this issue. If you are really worried about it you can take advantage of canned queries and SQL view definitions to expose a carefully selected forward-compatible view into your data.

At my company (https://talkjs.com) we explored making our public API SQL-based for the chat & message data that our customers host with us. It would solve lots of customer requests in one go, much more so than adding a new REST resource for every kind of question a customer might want to ask our API. What's been holding us back is exactly this: Even if we'd carefully design a set of database views to guarantee backward compatibility, doing so will severely limit the kinds of database refactorings we'd be able to do. Especially when it comes to adding redundancy, denormalizing stuff, etc.

If anyone else has experience with exposing queryable (SQL or otherwise) dynamic APIs directly to customers, I'd love to hear your insights. How did you tackle this problem?

Re:SQL as an API language by pjungwir on Thursday October 04, 2018 @05:46PM (#18143943)
I've done it before. Some day I'll blog about it, but in the meantime here are a few places I've brought it up in HN comments:

https://news.ycombinator.com/item?id=14464929

https://news.ycombinator.com/item?id=15335915

https://news.ycombinator.com/item?id=17820410

I agree with the concern about schema flexibility, although since you'd be exposing only a derived, flattened, OLAP-oriented database, you can still change your OLTP schema without breaking compatibility. You just have to maintain the mapping from OLTP->OLAP. You could even version the OLAP schemas so that you have a controlled way of getting customers onto newer structures. If I were taking that approach I'd consider what Stripe has said about how they build "mappings" from one API version to another.

My email is in my profile if you'd like to talk more about this! SQL-as-an-API is a big interest of mine.

Re:SQL as an API language by btown on Thursday October 04, 2018 @06:26PM (#18144229)
The Stripe article in question, for others' reference: https://stripe.com/blog/api-versioning .

Your comments strike very close to home! In one project, to support Tableau analysis for business analytics, I did the opposite of "wrapping a JSON API around a relational database" - I used https://www.torodb.com/ to wrap a derived Postgres database around our Mongo source of truth! (Laugh if you want - it got us to launch!)

GraphQL, for all its power, can't support arbitrary aggregates the way SQL can. And when working with web-scale data, you want a server to be doing those calculations, not the client.

I continue to work on systems with constantly-reprocessing data pipelines feeding the primary records that users interact with; per the above, they can feed derived databases that support SQL queries as well, whether or not we give that full flexibility to the API user. What I should write a blog post about is how we think of optimistic updates; just like in a rich client-side app where you want to optimistically update client-side data with how you might expect it to appear after receiving confirmation of your transaction, you also want to optimistically update derived-database data with how you might expect it to appear after it flows fully through an asynchronous data pipeline that overwrites that derived database. It's optimistic-updating all the way down, with the deepest layer (a more-or-less immutable event stream, incorporating things pulled in from the outside world) operating as the source of truth. The trick with a system like this is to build the right abstractions (and they're very domain-specific) to make it a joy to program in - more Django than PySpark - but it's definitely possible.

Re:SQL as an API language by siscia on Thursday October 04, 2018 @05:00PM (#18143614)
Maybe I am a little biased, but once you define what SQL interface you want to provide to your users (so the schema of the tables) you have basically decoupled your internal data structure from the view of the client.

At this point the only thing you need to do is to update the user facing database whenever necessary and keep it consistent with your view of the world.

Yes, you have a new problem since you had to maintain the costumer view consistent with your view, but it may be a price worth paying.

The way I would test this implementation is with RediSQL (https://RediSQL.com) that I wrote (so I am definitely biased) but it may be worthed to test it.

Re:SQL as an API language by scrollaway on Thursday October 04, 2018 @04:40PM (#18143469)
What you're describing is exactly what GraphQL sets out to solve. Most of the flexibility and declarativeness of SQL, but still sitting a layer above the raw data itself.
Re:SQL as an API language by skrebbel on Friday October 05, 2018 @04:20AM (#18146560)
GraphQL is just a query language. It has exactly the same problems in this respect as SQL - once a certain query from a customer that you never imagined they'd write works, it has to keep working, even if your underlying data model totally changes.
Re:SQL as an API language by scrollaway on Friday October 05, 2018 @05:00AM (#18146706)
This has a greatly reduced effect. What you have to keep updated is the accessible properties.

As I said, it's similar to writing a SQL view and giving access to that. Difference being, GraphQL is a lot more appropriate especially for web REST endpoints

Re:SQL as an API language by aikah on Thursday October 04, 2018 @04:46PM (#18143521)
> Most of the flexibility and declarativeness of SQL,

does graphQL support aggregate functions like count,sum,average and co?

Re:SQL as an API language by ilikehurdles on Thursday October 04, 2018 @06:32PM (#18144269)
The answer is muddier than what the other comments say. It depends on how you design your schema. It can support whatever you need it to support. Aggregations aren't impossible, but they have to be designed into the schema. A very quick example query with aggregates _may_ look like this:

    {
      searchFriends(first_name: {startsWith: "John"} age: {gte: 30}) {
        count
        aggregate {
          total_age: sum(field: age)
          avg_age: average(field: age)
          name_count: distinct(field: first_name)
        }
        nodes {
          first_name
          last_name
          age
          gender
          location
        }
      }
    }
But there are many different ways to model this.
Re:SQL as an API language by scrollaway on Thursday October 04, 2018 @05:00PM (#18143613)
No, that's why I said most; and that's also what makes it more appropriate for APIs -- you don't want uncontrolled performance hitting you like this; if you wish to allow for aggregates, you expose them as properties.
Re:SQL as an API language by derefr on Thursday October 04, 2018 @06:48PM (#18144368)
> you don't want uncontrolled performance hitting you like this

On RDBMSes with multitenancy support, you can simply apply resource constraints and quotas (in CPU time, CPU percentage, wall-clock time, memory, disk usage, etc.) to each user and to each connection. (You just have to ensure that each separate customer ends up hitting the DBMS as a separate DB user.)

The entire point of the OLAP model is to allow clients to ask their own questions. Some of those questions may be too expensive to answer—modern DBMSes are smart enough to realize which queries those are, and allow the sysadmin to restrict users from doing them. But that restriction should always be a matter of scale, not of kind. Users should be able to do a very complex query over a small data-set, or a very simple query over a huge data-set, or a combination, as long as they aren't actually impacting the performance of the cluster for other users.

Re:SQL as an API language by scrollaway on Thursday October 04, 2018 @07:32PM (#18144558)
Sure. And if that's a model that is appropriate for your API users, go for it.

But usually, it won't be appropriate. And by usually, I mean it will almost never be appropriate to adopt a model like this for your users. And I say this having been in the very situation where it was appropriate and we did expose it.

Re:SQL as an API language by derefr on Thursday October 04, 2018 @08:02PM (#18144658)
Alternately, what's wrong with just tracking the expense of the user's query and then passing the costs of it back to them? Like most "scale-free" DBaaSes (Dynamo, BigQuery, etc.) do.

This is assuming your users are paying for your API, mind you. I have no idea why you'd want to provide an online relational data-access service for free. (The "free" option for OLAP is, and has always been, "we give you a .zip of CSV files, and you ETL them into your own DB to look at them." That's how things like government GIS data or Amazon's and Google's hosted "public datasets" work—you pay for online access, or you get free offline snapshot dumps.)

Re:SQL as an API language by scrollaway on Friday October 05, 2018 @12:40AM (#18145774)
You are the only one who has been mentioning OLAP so far. It's just one use case among many.
Re:SQL as an API language by derefr on Friday October 05, 2018 @12:53AM (#18145820)
OLAP is the only use-case where queries that require complex aggregate computations with potentially-unbounded runtimes are relevant. When you subtract GraphQL from SQL, the difference is "arbitrary OLAP reporting capabilities." So I think it's fair to focus on this use-case when talking about what exactly having an SQL API gets you (over having a GraphQL API.)
Re:SQL as an API language by scrollaway on Friday October 05, 2018 @01:42AM (#18145962)
I'm not really sure what you're trying to communicate here. Arbitrary aggregates are not usually an appropriate thing to expose to users as part of an API. Where they're actually relevant doesn't change that.
Re:SQL as an API language by derefr on Friday October 05, 2018 @02:23AM (#18146079)
Maybe you don't understand the original point of SQL as a language? It's a language where humans—usually, employees who are granted access to data, or customers who are paying for access to data—type up descriptions of reports they want to receive, in a formal language; send it off to a remote system; and then those reports, magically, come back. In between, there can be anything—maybe a bunch of interns manually collating data from thousands of Excel spreadsheets. But usually there's a DBMS, an automated system whose job is to do whatever is necessary to turn the data it has, into the reports people need—no matter how complex a task that turns out to be. Because it's a complex task that needs doing.

I don't think it matters whether you call that "an API"; I think your implicit point here is that "an API" is something that you expose to the general public, and this is ...not that. But if we're talking about the benefit people get from having SQL-shaped access to your data model, then this is what it is: the ability to specify complete, arbitrarily-complex reports that they want your system to calculate out for them.

To me, the entire point of "an SQL API", as opposed to any other kind of API, is that it allows the data in your system to serve the needs of people who need those reports. Other users can be satisfied with pre-defined reports. The point of SQL is to serve the needs of users with arbitrary moment-to-moment needs. Users who need to explore and decompose your data in ways you never considered. Users who need to experiment inside your data model; to investigate it, to audit it.

With any other kind of API, such people would be too constrained by your pre-built possible queries to satisfy their needs; and so would instead need a whole ETL pipeline built for them, to take your raw data out of your system, and put it into their own (OLAP) system, where they could then ask that system to generate their reports.

If you expose an SQL API, you obviate all of that—the customers or employees who need to generate complex reports from your data can just ask your system to go do that.

---

Consider a government agent tasked with auditing Facebook's GDPR compliance. How would they be enabled to do that?

Facebook certainly can't just take an offline dump of the entirety of Facebook's data and give it to the auditor, in order for them to play with it in their own OLAP systems. It won't fit, and that's a GDPR violation in-and-of-itself besides.

Alternately, dumping a random sampling of their data might miss important low-frequency edge-cases.

That leaves online examination of their data.

It's not already in relational form—most of it is in Cassandra, a NoSQL database. So Facebook can't just give them access "to their RDBMS"—they don't have one.

But none of these other systems directly support the kinds of arbitrarily-complex queries the auditor needs to do.

What would Facebook need to build, for this use-case?

Why, an SQL API gateway server, of course—one that wraps their entire data warehouse, allowing the auditor to issue arbitrary SQL queries that reach out to join together and process data from all of Facebook's services.

That's when you have "an SQL API."

---

And there are more use-cases than just "auditors."

If, for example, the reason you're collecting data is to do analytics to it (this is e.g. advertising data), then the point of your business is selling your customers the ability to arbitrarily analyze that data. You might have an online GUI for doing basic BI-style break-downs, but the only "fallback" interface that your customers will be truly satisfied with is an SQL API.

Re:SQL as an API language by scrollaway on Friday October 05, 2018 @02:49AM (#18146196)
This is a lot of text to essentially say what I've been repeating: If it's the right tool, use it. I never said it's never the right tool. What you are describing isn't the usual scenario for API consumers.
Re:SQL as an API language by Scaevolus on Thursday October 04, 2018 @05:05PM (#18143662)
No, largely because it can make query performance harder to evaluate: https://github.com/graphql/graphql-js/issues/855
Re:SQL as an API language by sbr464 on Thursday October 04, 2018 @05:43PM (#18143922)
I would say yes, but you have to add in the resolvers. I've built these resolvers once and they are dynamically added to the GraphQL schema for all types that support them (different number types etc), and aren't explicitly disabled or enabled.
Re:SQL as an API language by icebraining on Thursday October 04, 2018 @05:07PM (#18143682)
SPARQL, part of the Linked Data / Semantic Web toolset, does support aggregate functions.
Re:SQL as an API language by nl on Friday October 05, 2018 @08:29AM (#18147553)
True, but wow SPARQL is a terrible language.
Re:SQL as an API language by forkandwait on Thursday October 04, 2018 @04:45PM (#18143511)
So why not pure SQL, which everyone knows already?
Re:SQL as an API language by WorldMaker on Thursday October 04, 2018 @06:27PM (#18144233)
Complexity?

1) Parsing SQL is complex, and full of decades of warts in expectation (dialectal differences between databases, among other things). There are few "drop in" SQL parser libraries that are trustworthy and support much more than a bare subset of the language.

2) Query Planning/Optimization is the "secret sauce" of most SQL databases, and is extremely hard in general. To use SQL as a general API outside of an implementation tied to a specific SQL vendor you'd presumably need a "general" query planner/optimizer that becomes a mini-version of an SQL database in itself.

GraphQL's faults aside, it is quite straight-forward to parse (with multiple well known implementations of the parser), and for the most part query planning/optimization in GraphQL follows a simpler, more straight-forward map/reduce-esque "resolver" pattern than the Relational Calculus and general complexity of SQL query planners/optimizers.

Re:SQL as an API language by derefr on Thursday October 04, 2018 @06:52PM (#18144392)
> There are few "drop in" SQL parser libraries that are trustworthy and support much more than a bare subset of the language.

Libraries, no, but you don't need a parser library to parse SQL; SQL [even in its DBMS-specific dialects] is a regular language, and regular languages can be entirely formalized using a declarative grammar. You just need a grammar file in a standard format, and a parser-generator like GNU Bison to throw it through.

Conveniently, existing FOSS RDBMSes like Postgres already generate their parsers from grammars, so they have grammar files just laying around for you to reuse (or customize to suit your needs): https://github.com/postgres/postgres/blob/master/src/backend...

> Query Planning/Optimization is the "secret sauce" of most SQL databases, and is extremely hard in general. To use SQL as a general API outside of an implementation tied to a specific SQL vendor you'd presumably need a "general" query planner/optimizer that becomes a mini-version of an SQL database in itself.

Definitely the bigger problem of the two.

Personally, I would suggest reversing the whole problem formulation. You don't need to take your existing system and make it "speak SQL." Instead, it's much simpler (and, in the end, more performant) to take an existing, robust RDBMS and hollow it out into this "mini-version" that queries through to your service.

I've personally done this before: I created a Postgres Foreign Data Wrapper for my data service, then stood up a Postgres instance which has my data-source mounted into it as a set of foreign tables. I then created indices, views, stored procedures, etc. within Postgres, on top of these foreign tables; and then gave people the connection information for a limited user on the Postgres server, calling that "my service's SQL API."

In this setup, all the query planning is on the Postgres side; my data source only needs to know enough to answer simple index range queries with row tuples. I don't really have to do much thinking about SQL now that it's set up; I just needed to understand enough, once, to write the foreign data wrapper.

Re:SQL as an API language by WorldMaker on Thursday October 04, 2018 @08:40PM (#18144817)
> a regular language

SQL varies somewhere between a context-free language and a context-sensitive language between dialects and/or edge cases.

Pedantry aside, certainly there are open source grammars for particular dialects. But it is the whole package of "drop in" library including at least some basic semantic modelling that doesn't exist, certainly not to the extent of say an XML or a GraphQL parser library.

> Personally, I would suggest reversing the whole problem formulation. You don't need to take your existing system and make it "speak SQL." Instead, it's much simpler (and, in the end, more performant) to take an existing, robust RDBMS and hollow it out into this "mini-version" that queries through to your service.

Which again, was complexity that I mentioned trying to avoid ("outside of an implementation tied to a specific SQL vendor"). You might not think it a big deal to tie your application to a single database vendor, but that's not always a good option. GraphQL as a language/platform is largely agnostic, supports a large variety of options for backend storage and makes it very easy to support heterogeneous data sources, because it is almost always as simple as "write a function to get the data" (and maybe "write a function to guess how expensive it would be to get the data", if you are optionally using a basic quota system). It does sound like Postgres Foreign Data Wrappers make some of that possible in that very specific database environment, but certainly it's a lot more complex of an option, which gets back to my root problem statement that "complexity" is most probably the reason SQL isn't getting used as much traction as an API option as for instance GraphQL.

Re:SQL as an API language by derefr on Thursday October 04, 2018 @09:36PM (#18145062)
> "complexity" is most probably the reason SQL isn't getting used as much traction as an API option as for instance GraphQL.

There's a much more obvious reason: there's no such thing as an "ANSI SQL wire protocol." If you want to take advantage of an existing ecosystem of client libraries that speak SQL to your server (and why wouldn't you?), you literally have to choose a specific vendor's RDBMS wire protocol to mimic, and then tell your clients to configure their client library as if your service was an instance of that RDBMS.

Amazon's Aurora? You need to speak the MySQL wire protocol.

CockroachDB? It speaks the Postgres wire protocol.

If there was such a thing as an "ANSI SQL wire protocol"—and especially if there was such a thing as a "packed binary ANSI-SQL wire protocol"—then I think you'd see adoption of SQL as a lingua franca for things that are not, themselves, SQL RDBMSes take off. As it is, SQL can't be a lingua franca of anything–because there is no "SQL standard" in the sense of an IETF RFC networking standard you can just build your server around. "ANSI SQL" is a language standard (specifically, a mapping from a grammar to a semantics); not a protocol standard.

There isn't even an SQL-wire-protocol standardization committee. Not even a working group. Not even a random FOSS project to attempt to create a "reference ANSI-SQL protocol specification." It's just... not there. (It's kind of bizarre, actually.)

Re:SQL as an API language by Volt on Friday October 05, 2018 @12:35PM (#18149523)
But ODBC exists. Or am I missing something?
Re:SQL as an API language by WorldMaker on Friday October 05, 2018 @02:26PM (#18150488)
Would you use ODBC to communicate to a web API?

Also, ODBC is not quite the abstraction layer I think parent was referring to, because ODBC only (barely) abstracts getting SQL from a client to a server, it doesn't abstract any of the semantics of the communication because it entirely punts that to a "driver" model. ODBC is slightly better than RS-232, but it's about the same abstraction layer from what I've seen.

Re:SQL as an API language by scrollaway on Thursday October 04, 2018 @04:48PM (#18143531)
GraphQL has a lot of advantages over SQL, API-wise; namely it's a lot more natural to describe what you want to get back. But without getting into that, the point is that you usually don't want to give direct data access, with all that entails.

You still want to be declaring the fields and properties of the data that users will be querying, and that usually sits a layer above your raw data.

You can make it a SQL view if you like, and give access to those instead. But it's just a lot of hassle to solve a solved problem in an unfamiliar way.

_______

Edit: Clarifying my stance on SQL views. I'm not dismissing it as a "bad tool", but there's good reasons it's not a popular one today. Writing SQL is not easy for everyone, and it's especially not easy for potential API consumers.

The best APIs are those you grok. If you grok SQL, congratulations, but most devs don't, and APIs are usually designed for most people.

Obviously, if SQL is the right tool for the job, use SQL. But don't start doing stuff like this for ideological reasons.

Re:SQL as an API language by tathougies on Thursday October 04, 2018 @05:13PM (#18143720)
I mean SQL views have been a thing for literally decades longer than GraphQL. If any technology should be accused of causing a 'lot of hassle to solve a solved problem', it's graphql, not SQL.
Re:SQL as an API language by atombender on Thursday October 04, 2018 @05:45PM (#18143931)
If your original data is flat and your app can consume it flat, no worries. But SQL is a worse fit for structured data, so you end up with an ORM on top, which is what GraphQL sort of is. Typically web apps are written with web technologies that work with structured objects, and you want something like {title: "Stapler", price: 1230, category: {id: "123", name: "Office supplies"}, mainPhoto: {url: "https://...", width: 600, height: 300}} back to feed directly in the UI.

Some databases (Postgres comes to mind) are better at letting you express this as a query which builds the structure using JSON functions in the SELECT part of the query. But it's not even close to ideal.

Re:SQL as an API language by just_myles on Thursday October 04, 2018 @06:03PM (#18144072)
"Some databases (Postgres comes to mind) are better at letting you express this as a query which builds the structure using JSON functions in the SELECT part of the query. But it's not even close to ideal."

Depending on which version of postgres you have(Want to say pre 9.5.) it can be a pain. There are various functions that allow a JSON output and another to make it human readable. With regards to performance I couldn't say but I imagine it would not be as fast as GraphQL.

Re:SQL as an API language by profalseidol on Sunday October 07, 2018 @06:00PM (#18162857)
In this context, what do you think of Datomic? Wish they make it the usual open source with a paid version with more features.
Re:SQL as an API language by atombender on Sunday October 07, 2018 @06:30PM (#18162973)
I don't know Datomic very well, but everything I've read makes it sound a bit like a modern take on an object database. Certainly interesting, but as long as it's commercial software I'm not going to touch it. Its close coupling (AFAIK) to Clojure is also not interesting to me.
Re:SQL as an API language by aaaaaaaaaab on Thursday October 04, 2018 @06:35PM (#18144286)
You don’t need an ORM, just a big JOIN.
Re:SQL as an API language by yen223 on Thursday October 04, 2018 @08:37PM (#18144807)
JOINs also return flat data structures, no?
Re:SQL as an API language by aaaaaaaaaab on Friday October 05, 2018 @04:23AM (#18146571)
Just iterate through the results and recover the hierarchical structure. It’s not exactly rocket science:

    rows = sql("SELECT * FROM parent LEFT JOIN child ON parent.id = child.parent_id")
    results = []
    for row in rows {
        if row["parent.id"] != results.last.id {
            results.add({ id: row["parent.id"], children: [], ... })
        }
        if row["child.id"] != null {
            results.last.children.add({ id: row["child.id"], ... })
        }
    }
Re:SQL as an API language by atombender on Friday October 05, 2018 @05:41PM (#18151964)
You have to handle arbitrarily nested hierarchies.

You also need to support referential equality — so that objects with the same type and ID map to the same in-memory JS instance — which requires building an identity map that can live across multiple paginated result set pages.

You have to support untangling the object trees for UPDATE statements, which will require tracking dirtiness to avoid unnecessary requests. Then something similar to deconstruct new objects into multiple INSERTs. Both require a knowledge of foreign-key relationships unless you run all constraints in DEFERRED mode.

Now you're building an ORM.

(Your query also makes assumptions about order and structure that you cannot make in the general case. The devil is in the details.)

Re:SQL as an API language by laszlokorte on Friday October 05, 2018 @08:10AM (#18147440)
but now it's no longer a declarative query. What if you want to include grand children? or if you want to fetch just one child but include it's parent and it's siblings of another type like a product, it's category and the categories tags:

   {id: 23, name: 'Trackpad', category: {id: 42, name:'Equipment', tags: [{id:...}, {id:...}]}}
Now instead of just adding two joins you have to build a custom loop with hand crafted conditions. Sure you can find a way to generalize that loop and put it into a library but still the query itself is separated from some kind of post processing that has be kept in sync.
Re:SQL as an API language by verbify on Friday October 05, 2018 @08:39AM (#18147617)
Nice bit of code.

Just to clarify, wouldn't you need to order by parent_id for the row["parent.id"] != results.last.id to work? Otherwise, say you're ordering by child.date_modified, you could end up with two identical parent_ids in your array?

Re:SQL as an API language by aaaaaaaaaab on Friday October 05, 2018 @10:52PM (#18153225)
Yeah, I forgot the ORDER BY clause, but you get the idea.
Re:SQL as an API language by dragonwriter on Thursday October 04, 2018 @05:36PM (#18143887)
> You can make it a SQL view if you like, and give access to those instead. But it's just a lot of hassle to solve a solved problem in an unfamiliar way.

It's kind of odd to use that to dismiss the solution that was established and widely available for 30 years longer, and which more people are probably familiar with.

Re:SQL as an API language by scrollaway on Thursday October 04, 2018 @06:03PM (#18144077)
See my edit. I'm not dismissing them, but the range of cases where they're the correct tool over a REST API, GraphQL, or something else... is very narrow.
Re:SQL as an API language by aidos on Thursday October 04, 2018 @05:31PM (#18143853)
I’m a huge proponent of sql, for me it’s virtually impossible to beat as a query language.

We played with wrapping our dB layer with graphql recently and I would say that the experience was pretty good. In part it’s because you’re working a layer up where you can have a more pluggable model. In the first run you can model your local dB structure, but it’s easy to glue more stuff into it from other sources and generally evolve the structure.

Re:SQL as an API language by davidgould on Thursday October 04, 2018 @08:51PM (#18144853)
HAVING is not the same thing as WHERE. WHERE filters the query to produce a result set. HAVING filters the result set.

    select id, count(*) - 1 as nduplicates
       from parts where color = 'red'
       group by id
       having count(*) > 1;
WHERE cannot filter on the count(*) because WHERE is applied during the query before the count is known.

I agree SQL is kind of warty, and it would be great if it were easier to compose from programs, but HAVING is not redundant to WHERE.

Re:SQL as an API language by irishsultan on Friday October 05, 2018 @07:31AM (#18147261)
But that's equivalent to the following query which doesn't use having, only select

    select * from (select id, count(*) - 1 as nduplicates
       from parts where color = 'red'
       group by id) _inner
       where nduplicates > 0;

Now obviously that doesn't read as easily, but WHERE can filter out the count(*) perfectly.
Re:SQL as an API language by aidos on Friday October 05, 2018 @04:04AM (#18146500)
I believe this should have been in response to its sibling. The set theorist in me agrees - the two operations are working on 2 different sets at 2 different levels of abstraction.
Re:SQL as an API language by davidgould on Sunday October 07, 2018 @01:00AM (#18158917)
Yes, replied to the off by one post.
Re:SQL as an API language by barrkel on Thursday October 04, 2018 @06:43PM (#18144337)
I find it very easy to imagine a better SQL.

Two things I'd fix:

* make it explicit when a join is expected to have 1:1 semantics vs 1:n, because it's very easy to end up with duplicated rows otherwise

* increase composability of the syntax; why do we need HAVING, why isn't WHERE enough? We have relational algebra, projections, filters, joins, folds, union, intersect etc. But the syntax of SQL is so idosyncratic, and at times antagonistic to the stream and set-like intuitions behind relational algebra.

Re:SQL as an API language by aidos on Friday October 05, 2018 @04:08AM (#18146513)
Regarding your first point, I’m trying to imagine what that would look like. So you mean something you could put on there to say (join x [1:1] on blah)? In general the mistake that’s easiest to make is to get duplicate rows from another join later, which this wouldn’t help with - but maybe I’m misunderstanding.
Re:SQL as an API language by barrkel on Friday October 05, 2018 @05:32AM (#18146807)
I don't really understand your comment about it always being join x+n that causes the problem instead of join x. What is different about later joins that is not true when the later join is the current join, when you get around to writing it? If joins for tables T1::T2 and T2::T3 are 1:1, they don't magically become 1:n when you do T1::T2::T3.

To do it right, you'd need to mark up the relations with expected cardinality (effectively unique constraints), so that the DB would be able to verify ahead of time whether a join is going to be 1:1 or 1:n. That would be a better solution than a join working fine up until the cardinality expectation is violated and it suddenly stops working.

If we had to stick with SQL syntax, it might be something like:

   select a.*, b.*
   from a
   join one b on b.id = a.b_id
(Unique constraint from primary key on b.id. It may still filter down the set of rows in a, but it won't duplicate.)

Or:

   select a.*, b.*
   from a
   join many b on b.some_key = a.some_key
(No unique constraint on b.some_key)

I think the interesting cardinality distinctions are 1:1, 1:n and 1:0.

(The above syntax is ambiguous with aliases, so it wouldn't fly as is. But it gives a flavour.)

Re:SQL as an API language by aidos on Friday October 05, 2018 @09:07AM (#18147763)
So the idea is that when you write the queries with these explicit join requirements, you'll want the query to fail if someone changes the schema later in such a way that the requirement is no longer met? If so, it seems like something that comes up so infrequently as to not warrant the noise, but maybe I'm still misunderstanding.

What I was saying was that often people have T1::T2 (1-1) and then throw in a later join T2::T3 (1-many) without considering the implications for the T1 (maybe they were doing a count(T1.id)). At least that seems to be an aspect that confuses developers newer to sql (that the many join is duplicating other parts of the data).

Re:SQL as an API language by barrkel on Saturday October 06, 2018 @03:40AM (#18154035)
It comes up quite a bit if you have repeated data in batches where the batches are a first class concept. Any relations need to be qualified by both normal fk id and batch id, or else you bring back across all batches.

Think something like measurements samples that come in an envelope with metadata, or account updates that come in a statement, that kind of thing.

Re:SQL as an API language by walshemj on Thursday October 04, 2018 @06:31PM (#18144265)
Why is writing SQL so hard for "most devs" I was moved to a Oracle based project (the management system for the uk's core internet) and only had a weeks training on PL/SQL.

I got a high performance award after that project was completed and I don't consider myself a Rockstar developer

Re:SQL as an API language by scrollaway on Thursday October 04, 2018 @06:40PM (#18144309)
You have a week's worth more experience of training than "most devs".

"Most devs" don't directly deal with SQL these days, because ORMs are good enough for day-to-day operations (and the non-day-to-day ends up being handled by people who actually know SQL). In fact, "most devs" rarely interact with databases. A lot of data sourcing is API driven these days.

Now, I don't claim to like or dislike that particular state. But you have two choices: You can innovate, which carries risks (of being wrong; of not selling your idea correctly; of reinventing the wheel; of making the same mistakes others did; ...). Or, you can play it safe, and create APIs that most people will know how to use without having to go through a 1-week course, because short as that may sound, that's 167.5 more hours than anyone will reasonably be willing to invest in understanding your API.

So at the end of the day: Use the right tool for the job. SQL for APIs is usually not the right tool.

Re:SQL as an API language by marcosdumay on Thursday October 04, 2018 @06:08PM (#18144109)
Out of curiosity (didn't had the time to get up to date on it yet), how does GraphQL let you describe your returning data better? Is it because it has hierarchical types (types and collections inside types) or it's something else?
Re:SQL as an API language by scrollaway on Thursday October 04, 2018 @06:41PM (#18144320)
With GraphQL, you are creating what is essentially a 1:1 mapping of the structure of the data you want to get back. It's a lot more intuitive than joins and such.
Re:SQL as an API language by eksemplar on Friday October 05, 2018 @01:32AM (#18145937)
A join in GraphQL looks like this:

{ getOrganisation(args){ name, employees{ Name, Position }}

(I added commas to make it easier to read, but they are not supposed to be there)

I know SQL, so I don’t think it’s necessarily easier, but what is easier, is non-sql-savvy people making efficient queries because GraphQL does a lot of the heavy lifting for you.

Re:SQL as an API language by btown on Thursday October 04, 2018 @06:00PM (#18144040)
What about a stable materialized view (either materialized by SQL that is kept up to date with your source-of-truth tables as they're migrated... or, if necessary, by a custom real-time data pipeline to feed it) to service reads? And an event-based, insert-only schema to service writes?

I'm always inspired by Martin Kleppmann's talk about the architecture his team built at LinkedIn (and subsequently spun off as a separate business): https://www.confluent.io/blog/turning-the-database-inside-ou... . An event-sourced system like that, one that can denormalize and transform data in any way that's needed for security and performance, could theoretically support a SQL-like API. It could even support exposing SQL-like triggers for real-time updates to untrusted clients (or, in LinkedIn's case, practically limitless internal teams).

With chat being your domain, it's possible that event sourcing into a SQL database would be a great paradigm match for you. I've seen it work wonders.

Re:SQL as an API language by ako on Thursday October 04, 2018 @05:48PM (#18143955)
Never have query APIs on your operational databases. Either use a read-only slave, or another database filled through an ETL process. This allows you to refactor your main database, without impacting your API. Also you avoid the performance impact of the APIs on your main database.

As an alternative for SQL you can consider oData, it's standardized REST compatible generic query language, similar to graphql, but supported in BI tooling like PowerBI, tableau and excel.

Re:SQL as an API language by z3t4 on Thursday October 04, 2018 @06:11PM (#18144129)
You could create the views, but add a deprication period, log queries and automatically remind users querying the-to-be-deprecated views. A customer that runs SQL queries directly will probably tolerate having to rewrite their SQL queries from time to time.
Re:SQL as an API language by barrkel on Thursday October 04, 2018 @06:34PM (#18144279)
We give people a series of dropdowns on columns on tabular data, and they can do things like IN and BETWEEN queries for date and numeric data, as well as literal equality for string data.

The catch is that they can do this filtering on columns that are, behind the scenes, retrieved via joins. And the data set being searched may be many millions of rows. And it needs to come back interactively, i.e. no more than a second or two. And the schema is customer-defined.

I implemented a syntax tree that is composed from the various columnar dropdown filters and get passed as a big AND to the API. The API essentially recieves a full syntax tree for a predicate, but each field referenced in the predicate might be only available through a series of joins, or via the main table - it requires some analysis.

The database backing this is MySQL. MySQL has a fairly simple query optimizer, and it is reliably poor at being given complex predicates over queries with lots of joins. So, we split up the query: we try and tear off ANDed predicate parts that relate to the core table, push them into a derived table (i.e. nested query) along with the sorting predicate, if possible. We then join in just the tables required for evaluating the rest of the predicate, in the next level out. And finally, in the outermost query, we do all the joins required for the final page of results, joins that are necessary to retrieve all the columns that haven't been used by filters or sorts.

With this level of analysis of the predicate being executed, we could do things like reject overly complex queries. As it is, our underlying data model is limited somewhat in the expected volume on a time period basis, and we have some defaults over the maximum time window that can be searched, so it's mostly under control.

Occasionally new devs add in extra complex joins with derived tables that are a little bit too complex for interarctive querying, and staying on top of that is a challenge. It's needing a bunch more performance testing before releases, whereas we've been mostly able to get by with retrospective performance monitoring until now. But the team is bigger, not every query modification gets sufficiently expert eyes, and so we need to beef up our processes.

Re:SQL as an API language by o1lab on Thursday October 04, 2018 @04:53PM (#18143570)
If anyone is looking for apis for mysql : xmysql will be really helpful.

https://github.com/o1lab/xmysql/

Xmysql is just one command to generate REST APIs for any mysql database.

Re:SQL as an API language by BerislavLopac on Thursday October 04, 2018 @04:48PM (#18143529)
> Note that this is solely in the context of a static data set. That makes this a relatively limited idea

Not if you use the event sourcing approach.

Re:SQL as an API language by GorgeRonde on Thursday October 04, 2018 @07:00PM (#18144434)
> If anyone else has experience with exposing queryable (SQL or otherwise) dynamic APIs directly to customers, I'd love to hear your insights. How did you tackle this problem?

This is something I've been experimenting with lately. The problem with APIs is that we conceive them as opaque abstractions. This is reassuring when you need to tweak the background and avoid breaking the informal contract a public API has with its clients. But this also mean you need to start over if you want to implement joins or filters. This is both what REST and GraphQL does over SQL which already has this. I'd also argue that SQL is a more specified and more broadly accepted standard than REST or GraphQL. So here is how it goes:

1. First, I use a SQL DSL that is written in a data-based language rather than being string based as SQL is by default. Since I write in Clojure, Clojure being a Lisp, Code being Data, this DSL is just EDN, a clone of JSON.

2. I add an abstraction layer on top of this DSL to make SQL more malleable and more lispy with respect to certain things (in particular you can't nest arbitrary SQL like you can with Lisp code).

3. I add another abstraction layer on top of the INSERT/CREATE/UPDATE/DELETE verbs (or actually the DSL's equivalents) so that any record inside any table can have its own set of custom, optional attributes/columns and can be queried, filtered and joined like any other field. Any SQL type is supported and EDN/JSON data is supported as well.

We're done with the DB, now let's turn to the API.

4. I add new verbs implemented by complex queries (point 2) that decompose into the enhanced INSERT/CREATE/UPDATE/DELETE verbs (point 3). Since the Clojure SQL DSL I'm extending supports churning of ingoing and outgoing DB data I also use that i.e. not everything is run in the database, and there can be multiple travels between the server and the db for the same verb -> This is how I blend database "code" (queries) with the service's business code.

These verbs are used to make the API deterministic (it's a critical service, you don't want the same stuff happening twice by mistake).

Example: CONJURE my_record BY {linked_client_id: 123} VALUES {field: "abc"}. Like ActiveRecord's find_or_create_by.

This verb is used as both a replacement for CREATE and SELECT (but receives/returns only one record).

This implies two things:

- The client doesn't have to store any data from my service anymore, not even ids. He just needs to send to the service data that allows it to uniquely identify the operation in question (like one of its id).

- Forcing the client into this deterministic mindset allows it to think less about the state of my service which is particularly useful against timeouts (when you're left not knowing if the operation has been carried out by the service) and simplifies the tying of the client's business logic with that of the service.

This is also why we need to be able to accept arbitrary data from the client in a flexible way (point 3).

5. I just stupidly expose the DSL (remember it's JSON-like by essence since it's Clojure code): I read payloads sent by the client, make sure the code is restricted to the DSL (is not actual evil Clojure code), then eval it.

6. As for the client, I just find the best SQL ORM in the target language and implement an adapter for my service's exposed SQL. At this point I basically just have to convert the ORM's AST into the Clojure-like form required by the service. Querying the service just feels like querying a local database.

7. The service also needs to handle basic retries against timeouts. As for "pagination", I use PostgreSQL streaming abilities in synchrony with that of HTML + lazy sequences on the client side. This also allows very large datasets to be processed. As for joins between the client's db and my service, this can be done using a WHERE ID IN (...) clause, which might be supported by the ORM natively. This might also be the occasion to implement some caching to alleviate the network load.

This is what I tried, and having achieved partially what's sketched on this roadmap, I think the overall approach I have here with respect to the concept of abstraction is to use it as a transparent structure throughout the implied levels (db -> server business logic -> API -> client -> client) in a bet whose goal is not to loose the expression power of the underlying RELATIONAL model since that's what is already used in the DB, it's how the API is designed and most likely it's what the client is using to model its own business.

I also like how the concept of API just melts away, hidden behind the client's ORM.