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.
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.
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.
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
does graphQL support aggregate functions like count,sum,average and co?
{
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.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.
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.
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.)
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.
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.
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.
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.
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.)
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.
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.
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.
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"], ... })
}
}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.)
{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.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?
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.
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.
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.
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.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.
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.)
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).
Think something like measurements samples that come in an envelope with metadata, or account updates that come in a statement, that kind of thing.
I got a high performance award after that project was completed and I don't consider myself a Rockstar developer
"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.
{ 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.
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.
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.
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.
https://github.com/o1lab/xmysql/
Xmysql is just one command to generate REST APIs for any mysql database.
Not if you use the event sourcing approach.
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.
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?