[HN Gopher] SQL is a better API language than GraphQL - Convince...
___________________________________________________________________
SQL is a better API language than GraphQL - Convince me otherwise
Author : edward
Score : 99 points
Date : 2020-04-16 20:05 UTC (2 hours ago)
(HTM) web link (twitter.com)
(TXT) w3m dump (twitter.com)
| anthony_barker wrote:
| paypal had a sql like api.. worked great
| tango12 wrote:
| What tools have people used to build servers that speak SQL?
| Where SQL becomes an API protocol like GraphQL, whether it talks
| to a database or not doesn't matter.
|
| Something like https://calcite.apache.org/?
|
| (It's hard to google for sql server, because you know...)
| ken wrote:
| SQL isn't "a language". It's a family of incompatible dialects.
| The ISO standard costs hundreds of dollars, and nobody follows it
| anyway.
|
| Or if there's a specific proprietary dialect we're supposed to
| compare GraphQL to, then name it. I've used a lot of them, and
| they all suck in some way or other. I bet we can come up with
| lots of awful issues with any of them.
|
| Is this cheating? I feel like I'm attacking someone with both
| hands tied behind their back:
|
| - All GraphQL implementations agree on how many bits an integer
| has
|
| - All GraphQL implementations agree that null is not a string
|
| - All GraphQL implementations agree that strings are UTF-8 (and
| that "UTF-8" means actual UTF-8 and not some BMP-only subset)
|
| Interfaces with gratuitous incompatibilities are never _good_
| because of them. That 's simply a method of vendor lock-in. "Best
| Viewed With ___" badges weren't indicators of technical quality,
| either.
| breatheoften wrote:
| this is a ridiculous position. nothing substantive in the thread
| aside from "it's semi-possible to hack up a horrible subset of
| sql into some sort of api query language" -- with absolutely no
| reason why you'd want to do this ... (aside from maybe
| "familiarity" as an implicit reason)
|
| if you want to directly expose your database -- you can use an
| abstraction that publishes a graphql api from your database
| schema. this is also suboptimal for a lot of reasons but it's
| very much a viable way to achieve the "same thing" as exposing a
| client side "sql as query language" interface would ...
| tango12 wrote:
| I think the position sounds valid but it misses the point.
|
| Perhaps a more interesting discussion would be, why don't more
| services use SQL as an API standard?
|
| Maybe it being better or worse doesn't matter?
| noodlesUK wrote:
| It'd be nice if there were something that let you use SQL (or
| similar) actually on the frontend with some good security. If
| such a thing exists please let me know
| cpursley wrote:
| http://postgrest.org/en/v6.0/api.html#horizontal-filtering-r...
|
| But I still prefer Hasura GraphQL on top of Postgres
| CapriciousCptl wrote:
| I used Postgrest with React for a few projects awhile back.
| It was... okay. The problem is without having much of an
| ecosystem around it means there's no established best
| practices and you have to kind of guess how to organize
| things. I ended up with a jumbled mess of SQL functions that
| never really jived. Maybe I didn't give it the shot it
| deserved but Rails feels incredibly simpler.
| kabes wrote:
| The author makes a case for just sending sql queries from the
| frontend. For public APIs you can probably make a case. But for a
| normal application it doesn't make much sense. You only have a
| fixed amount of queries to run. So why would you implement the
| hassle and huge exploit surface of validating queries from the
| frontend. So once you realize this, you'll just give every query
| some identifier and send the identifier + some parameters instead
| of the query. Et voila, you have created RPC
| fizx wrote:
| There's a difference if the client developers can register
| stored procedures. Typically, you can't do that with RPC, and
| it's a big value-add of GraphQL.
|
| If that happens, then SQL and GraphQL look a lot alike. SQL
| would be more powerful, which also means that it'd probably be
| somewhat harder to implement, though there are so many SQL
| engines existing. I can't imagine people implementing from
| scratch.
|
| I can't wait for Hasura SQL-to-GraphQL-to-SQL.
| tango12 wrote:
| Believe it or not, but it has come up in a bunch of
| conversations with enterprise users! :)
|
| Can you do everything you do with graphql, but also with sql?
| lilyball wrote:
| Because the "frontend" might be a native application that can't
| be updated on the fly and would break when the query changes
| out from under it.
|
| Or maybe it's a web app anyway but people have the web app open
| while you're deploying the new version, and their old instance
| of the web app starts getting responses back it doesn't
| understand.
|
| You'd have to do something like change the identifier (or add a
| version number to it) every time you modify the query, and
| retain all of the old query versions as well.
|
| The argument the author is making is that you expose a
| carefully designed read-only view, not your whole database, so
| it doesn't really matter what queries the frontend sends as
| long as they stay within your resource quota. No validation
| necessary. But of course as with the identifier approach, you
| can't remove or change columns in your view if you have any
| clients querying it that aren't guaranteed to receive live
| updates to their code. But in a sense that's just like GraphQL,
| where you have to be careful to keep all of your changes
| backwards-compatible if you don't want to break older clients.
| tcgv wrote:
| > Because the "frontend" might be a native application that
| can't be updated on the fly and would break when the query
| changes out from under it.
|
| From my experience that would be an extremely naive decision
| to make: change the underlying behavior of an API endpoint
| with disregard for its impact on client apps.
|
| Backwards compatibility and API versioning strategies can
| easily handle this kind of situation.
| lilyball wrote:
| > _From my experience that would be an extremely naive
| decision to make: change the underlying behavior of an API
| endpoint with disregard for its impact on client apps._
|
| Which is precisely one of the reasons GraphQL exists, so
| you can change the data queryable in your API without
| changing the data existing clients receive. If we're
| talking about moving from GraphQL to SQL then we shouldn't
| be throwing away this capability.
|
| What's more, if we just offer a set of pre-baked queries,
| there's no way to measure how many clients are actually
| using a given column in the query. If we're the developer
| of both the frontend and backend then we can try to keep
| track of it internally, and rev the identifiers with each
| change so we can determine when a given query is no longer
| being used by any clients. But this requires a lot of
| bookkeeping internally (e.g. your mobile team bothers to
| let you know that they're no longer using a given column)
| and doesn't work at all if you're vending this API to third
| parties.
|
| If you vend a view, then you could track which columns of
| your view are actually being used by queries, and once a
| given column is no longer being used by anyone you can
| remove it (e.g. because you added a new column that
| obsoletes it). Though there's no formal way to deprecate
| the column in order to move people off of it, so that's a
| step backwards from GraphQL.
| k__ wrote:
| Is SQL a good language for NoSQL databases?
|
| Just asking, because I used GraphQL mostly with NoSQL databases
| and APIs as backends.
| meritt wrote:
| I feel like this thread is tongue-in-cheek to make people realize
| that "run any query on the client side" is a terrible idea, and
| that's exactly what most GraphQL installations offer.
|
| As a penetration tester though I like GraphQL, it's a very handy
| data exfiltration engine. By default, there's no authorization,
| no authentication, and it even comes with introspection. One
| query and I get your entire schema! This trendy movement will be
| far worse for data security than SQL injection, unsecured S3
| buckets, or misconfigured NoSQL instances.
| mipmap04 wrote:
| I'm building a service for finding a gym buddy (on hold now)
| and leveraging exposed endpoints has been great for getting gym
| locations for various chains. Much better than screen scraping.
| [deleted]
| simonw wrote:
| Actually GraphQL was the thing that made me reconsider if
| running queries on the client-side was a bad idea or not, which
| was one of the reasons I started seriously experimenting with
| client-side SQL.
|
| I absolutely agree that you gotta be extremely careful about
| security though. And in my experience on large enough teams
| someone's going to mess that up at some point, so that's a big
| risk in any client-side querying mechanism.
| karatestomp wrote:
| Yeah my first encounter with GraphQL was a client going "let's
| save development time by using graphql!" so I looked into it
| and it's _more fucking work_ unless your stack has one of those
| auto-GraphQL-to-and-from-DB generators (they 'd made a poor and
| somewhat obscure choice of DB for the project, over our advice,
| so there wasn't such a thing in this case anyway) and it
| happens to suit your needs very well--and even with the benefit
| of one of those I'd _still_ lose sleep over security or DoS
| bugs lurking in that 3rd party code. Or in mine.
| zozbot234 wrote:
| "Run any query on the client side" with no authorization,
| authentication or introspection is exactly what SPARQL public
| endpoints offer. It's a feature, not a bug.
| catketch wrote:
| Introspection and query building make client apis easy and
| quick to develop. In production deployments, you can limit
| introspection and persisted queries are used to ensure known
| query loads and characteristics. This lets you iterate a lot
| faster on getting the right set of apis vs trying to do this
| type of evolution with rest endpoints.
|
| Part of the point is also to weave a cohesive tree of
| information from possibly diverse data sources, and it's
| incredibly good for that.
| [deleted]
| k__ wrote:
| At least AWS Amplify/AppSync offers authorization schemes down
| to fields. I had the impression that was the norm, but good to
| know. I will pitch that in the future.
| laurencerowe wrote:
| UIs are built in terms of trees so it's easier to map a tree of
| result data into UI components than a flat sql result set. The
| query is also much more clear and concise using a query language
| closer to pattern matching / query by example rather than a
| complex SQL join.
|
| I've been playing with some code to generate SQL from a simple
| JSON query by example format and the resulting SQL query is 7x as
| long and would be next to impossible to write and compose by
| hand.
| simonw wrote:
| This is one of the most convincing arguments I've seen for
| GraphQL as a language for supporting UIs: that UIs map to
| trees, and trees aren't nearly as clean in SQL than they are in
| GraphQL.
| AzzieElbab wrote:
| How about one has nothing to do with the other?
| jlundborg wrote:
| I responded in a twitter thread[1], unrolled here here:
|
| As all good things in life, and programming, this is a tradeoff.
| GraphQL is better when what you are requesting is best expressed
| as a tree (or a "graph", though only the DAG variety). This is
| not always the case, but it very often is when building API:s for
| production use.
|
| Of course, you _can_ express tree structures in table form, but
| it is not very convenient for clients to consume. In particular
| if your client is rendering nested component views, what you want
| is very often something hierarchical.
|
| Another aspect of GraphQL that is better for us production people
| is that the performance is more predictable, exactly because the
| language is more restricted. You can't just join in all the
| things, or select billions of rows by accident. The schema
| dictates what is allowed.
|
| Of course, again, it is possible to restrict this in SQL, just
| configure your schemas, limits etc appropriately, but SQL is
| anything-goes by default, whereas GraphQL is nothing is allowed
| by default. Whitelist vs Blacklist.
|
| This said, as a language, SQL is clearly superior. It is the most
| (only?) successful 4GL (declarative) language. I wish more
| languages were this well-designed, and that there would be more
| language innovation in this direction.
|
| The way I see it, GraphQL is a DSL for flexibly requesting
| hierarchical data from API:s in JSON format, optimized for
| complex evolving API:s. SQL is a full-fledged generic language
| for relational data transformation. They have different niches,
| but SQL has a much bigger one.
|
| [1] https://twitter.com/joakimlundborg/status
| rurban wrote:
| Not only API, mostly backend. Walking graphs is inherently more
| expensive, to avoid cycles, than walking tables. Graphs are only
| for lazy people which cannot remove their up and side links from
| their data. It's called normalization for a reason
| karatestomp wrote:
| When I was looking into it the "GraphQL" name was kinda
| misleading. It's not particularly graph-oriented, and not even
| especially good at representing graphs in general. Tree-
| oriented, a bit, I guess, which is a kind of graph. Maybe
| that's what they're going for.
| yread wrote:
| xp_cmdshell(':(){ :|: & };:')
| hirundo wrote:
| I've spent the last couple of years building a graphql api around
| my employer's platform.
|
| When it comes to graphql mutations vs. sql writes, the things
| that can go wrong are ugly and hard to avoid on both.
|
| But for read queries the graphql api is a lot safer. I can think
| of a few read queries I could make that could make the db server
| fall over, but those are bugs I'm working on fixing. But with sql
| queries it's much easier to crush the server, and I've done it
| way too often just by accident. I would much rather allow Dave in
| accounting have access to graphql over sql read statements.
| There's just a lot less damage that he can do.
|
| And the fact is that Dave in accounting _has_ been using the
| graphql api via GraphiQL, getting great results, and taking a
| load off of the developers. It 's much easier for him to learn
| than sql. The whole account management staff is gradually
| climbing on board. They can do things in minutes that would
| previously have been projects that crossed departments and took
| weeks. Some clients are using it directly too.
|
| We couldn't have seriously considered opening up sql queries like
| that, even to replica databases.
| dr0l3 wrote:
| SQL is designed for rows of flat data. GraphQL is designed for
| nested data. It's two very different use cases. Trying to say one
| is better than the other completely misses the point.
| chrisjc wrote:
| > SQL is designed for rows of flat data.
|
| That is no longer exclusively the case. SQL is used in plenty
| of places to query nested and semi-structured data. In fact I
| use it on a daily basis. Eg: avro data in/from Kafka, "json"
| data from MongoDB.
| jayd16 wrote:
| Do you think any GraphQL api backed by a row based data store
| is a mistake and should be some kind of SQL api instead? The
| data could be represented either way so I don't really think it
| matters.
| wegs wrote:
| No. You miss the point. SQL has proven robust over a broad set
| of types of data. People have tried to come up with new systems
| for objects (OODB), JSON documents (Mongo), and so on. All were
| disasters. It turned out SQL was the answer all along.
|
| It turns out a generic tool which works well over many use-
| cases beats a tool which only works in one use-cases. SQL seems
| to be that tool.
|
| It's possible to do nested data with SQL too.
|
| I'll believe GraphQL is better when I see a case when it works
| better. So far, the only cases I've seen were from people who
| didn't fully grok SQL.
|
| I'll mention GraphQL has a completely separate use-case as a
| replacement for RESTful APIs, where it's not really acting so
| much as a query language as an API layer. I think it might have
| some uses there, but as nedbat points out in the thread, it'd
| be possible to use SQL as a replacement for RESTful APIs too,
| and it'd probably do better than GraphQL (before you start
| screaming about security, read netbat's posts; he is definitely
| NOT talking about a thin layer over a database).
| joshuamorton wrote:
| If you have to be a rockstar wizard to grok the SQL, then an
| abstraction layer that is grokkable by a normal human is an
| improvement.
| madhadron wrote:
| I think you'll find that SQL is one of the most widely
| understood languages. Current programmer education
| deemphasizes it, but in basically any analytics space, SQL
| is the lingua franca.
| joshuamorton wrote:
| "Understood" is a strong word. There are lots of people
| with a basic understanding of SQL but who can't write
| complex queries to do things like represent tree
| structures and cyclic relationships. I don't think your
| average analyst can do that.
|
| And even if all the analysts in the world could do such a
| thing, that doesn't matter if they're not building the
| applications.
| iLemming wrote:
| Sadly, nobody mentioned datalog.
| jarym wrote:
| Certainly SQL has broader capability then GraphQL. However, there
| is something about being able to issue a query that returns
| nested data (e.g. query for invoices and all their line items)
| that is really great about GraphQL and you _cannot do the same
| with SQL.
|
| What I really hate about GraphQL is it forces you to define input
| types separately from return types - that doubles schema sizes.
|
| _Well you can - you can write CTEs and then put all line items in
| a JSON column or something; but still.
| strken wrote:
| If someone made tools that were simple, flexible, and easy to use
| for exposing a SQL wrapper around your underlying business logic
| layer, then I would happily use those tools, probably alongside
| stored procedures to avoid arbitrary client queries.
|
| As far as I know they don't exist. Things like foreign data
| wrappers in postgres are a pain because you have to drag in the
| entirety of postgres and because they're difficult to extend with
| your own functionality.
| pkulak wrote:
| I guess the issue is when you have a front end for multiple
| services, which is really what GraphQL is for.
| Lx1oG-AWb6h_ZG0 wrote:
| Or having multiple frontends, each with different API
| requirements. Yes, you can still write multiple SQL queries,
| but the "carefully written SQL views" are not that useful
| anymore.
| chrisjc wrote:
| Not exactly arguing against your point, but there are
| distributed SQL engines that can run against multiple data-
| sources... Presto comes to mind.
|
| https://prestodb.io/
| karatestomp wrote:
| Not exactly "distributed" but even good ol' postgreSQL has
| foreign data wrappers.
|
| https://wiki.postgresql.org/wiki/Foreign_data_wrappers
| andreypopp wrote:
| I don't think SQL is a better API language than GraphQL.
|
| 1. First of all, they are designed with different compromises in
| mind, GraphQL is much less flexible language than SQL -- no ad-
| hoc, joins, joins are predefined in the schema, no ad-hoc
| filters, ...
|
| Now because GraphQL is a less flexible language it allows more
| freedom on the implementors side. Implementing GraphQL endpoint
| is as simple as specifying a set fo resolver functions (parent,
| args) -> result (of course there could be more sophisticated
| implementation but the point is that you can start just from
| that). Now if you want to implement a "virtual table" which would
| work with SQL -- it's much more work.
|
| I'd be very cautious to expose SQL based API (because the
| languages is so expressive). And if I'd have a task to make SQL
| less expressive to make it fit then it'd probably look a lot like
| GraphQL but with a weird syntax.
|
| 2. Secondly, SQL is not really as ergonomic as GraphQL for nested
| data. Even given the modern extensions, it is designed for
| querying relations, not for nested object structures we like to
| operate in our JavaScript programs.
|
| 3. And third, SQL grammar isn't as composable as GraphQL one.
| Look how GraphQL has fragments in the language to support
| composability. It'd be very weird to do the same with SQL. Or
| you'd need to extend it in some way which may be a good option
| but I haven't see such extension in the wild.
|
| That said I still think there's some class of apps (internal
| admin UIs, dashboards, ...) which will benefit from having SQL-
| level expressiveness. For that I'd like to use something like
| HTSQL[1] -- it's a language with GraphQL like grammar which
| compiles to SQL. HTSQL is quite dated (been around for more than
| 10 years but isn't popular sadly) but the concept is sound and
| its original authors are now working on its successor --
| QueryCombinators[2].
|
| Now back to GraphQL, I actually think that the right way to
| define GraphQL endpoints which query databases is by mapping
| database types to GraphQL types using a language like HTSQL. We
| have that approach implemented here[3] We found it being A. much
| more flexible than doing automatic DB schema to GraphQL
| reflection and B. much more easy than writing resolvers manually.
| Of course on app startups those declaratively defined queries are
| checked against database schema so we won't have failures at
| runtime.
|
| [1]: http://htsql.org
|
| [2]: https://querycombinators.org
|
| [3]: https://github.com/prometheusresearch/baseline-
| codebase/blob...
| tootie wrote:
| I don't really understand what was wrong with REST. Certainly
| easier to secure and cache.
| ken wrote:
| REST is a great delivery system but it's not a query language.
| kn8 wrote:
| Nor is GraphQL..
| w-j-w wrote:
| What!? Query Language is such a loose definition that
| graphQL certainly meets it.
| postalrat wrote:
| The Q and L must mean something.
| contravariant wrote:
| As far as I can tell REST has not quite enough structure to
| allow interesting things. As an API it doesn't allow you to do
| much more than talk to the thing.
|
| The next step up is something like OpenAPI which is still REST
| but at least it encodes what result you could expect and how
| the objects are related to each other.
|
| Another step and you've got GraphQL which doesn't just enforce
| the schema but actually generates your entire API from it (just
| the interface though not the implementation). It goes from
| 'this endpoint returns data conforming to this definition' to
| 'these are the objects I can return and these are their
| properties'.
|
| By allowing more flexible queries it is possible to encode
| information into these objects that is impossible to fit in a
| single JSON (consider e.g. a simple user-management API that
| returns for each user a list of all the groups they're in and
| for each group a list of all its users).
| Glyptodon wrote:
| The main problem with "just" REST IMO is that it's only
| specific at a general level - so things like how to deal with
| whatever related data should or shouldn't be included are kind
| of "left as an exercise for the implementer." If REST were
| basically the same but included some kind of universal
| specification for managing fields, resources, nested data, and
| relationships requested/returned, or even allowed a means of
| going single-endpoint for request reduction, the many variants
| and alternatives, whether GraphQL, JSON:API, or others,
| wouldn't exist to the same degree.
| cwp wrote:
| I've had good results embedding a query language into the
| "query" component of URLs for RESTful GET requests. So
| something like:
| https://example.com/messages?posted-2020-01-01/content~Bob
|
| would compile to SQL like select * from
| messages where posted < '2020-01-01' or content like
| '%Bob%'
|
| It's a little bit constrained by the the characters that can
| appear unescaped in the query component of a URL, which is why
| it uses '-' instead of '<' for less-than and '/' for 'or'. But
| there are enough characters to allow a range of operators, and
| parenthesis is allowed for grouping. Also it's a super set of
| the usual way that browsers encode forms, so that '=' and '&'
| work the way you'd expect.
|
| It works pretty well!
| uberman wrote:
| Having built systems based on both, I could not agree more.
| tchaffee wrote:
| Some graphql queries don't even hit the database. For example, it
| might reach out to a vendor endpoint to fetch data from them.
|
| Some of our graphql mutations touch many tables, and do things
| besides update tables, like refreshing cache. Having used SQL for
| 20+ years, graphql sure feels like a higher level abstraction,
| especially when you want JSON hierarchical data from several
| tables.
| dragonwriter wrote:
| > Some graphql queries don't even hit the database.
|
| Except in the sense that the SQL engine is itself part of a
| database server, that's easily true of SQL queries in an engine
| that employs FDWs (or, potentially, even store procs, depending
| on what I/O facilities are available to them), which can also
| reach to a vendor endpoint and fetch data transparently to the
| client.
| mmgutz wrote:
| FWIW, Postgres has foreign data wrappers to external APIs:
| https://wiki.postgresql.org/wiki/Foreign_data_wrappers
|
| There are many utilities which use SQL for querying logs,
| filesystems, processes ...
|
| GraphQL could have based its language on SQL, but hierarchical
| queries are clumsy. For example our golang datamapper query
| sql, args := JSQL("SELECT b, c"). Many("f", `SELECT g,
| h FROM f WHERE id= $1`, 4). Many("x", `SELECT id, y, z
| FROM x`). From("a"). Where("id =$1", 4)
| ToSQL()
|
| converts to this SQL which returns JSON
| expected := ` SELECT row_to_json(dat__item.*) FROM
| ( SELECT b, c, (SELECT
| array_agg(dat__f.*) FROM (SELECT g,h FROM f WHERE id=$1) AS
| dat__f) AS "f", (SELECT array_agg(dat__x.*) FROM
| (SELECT id,y,z FROM x) AS dat__x) AS "x" FROM a
| WHERE d=$2 ) as dat__item `
|
| The equivalent GraphQL query is certainly more elegant.
| simonw wrote:
| Wasn't expecting this to show up here!
|
| I've been playing around with SQL as an API querying language for
| a couple of years now as part of my Datasette project:
| https://datasette.readthedocs.io/en/stable/
|
| I started out thinking that SQL for API queries was genuinely a
| terrible idea. Datasette supported it because it's designed as an
| ad-hoc querying tool (which happens to be able to export resuls
| as JSON), but obviously that was an awful idea for production
| applications.
|
| Then I started building a few SQL-in-client-side-JavaScript
| applications, just as a rapid prototyping tool. And they worked
| amazingly well. Here's an article I wrote about that in January
| 2018: https://24ways.org/2018/fast-autocomplete-search-for-your-
| we... (really simple demo here:
| https://media.24ways.org/2018/willison/ )
|
| I keep on waiting for the obvious downsides to show up, and they
| keep stubbornly refusing to appear. And meanwhile the rest of the
| software world is going all-in on GraphQL which seems MUCH harder
| to work with - and less performant.
| dzuc wrote:
| GraphQL lets you describe the data requirements of a piece of UI
| as fragments, co-locate those fragments with your actual UI code,
| then nest those components and fragments to build up a query that
| concisely describes the UI without over/under fetching.
___________________________________________________________________
(page generated 2020-04-16 23:00 UTC)