15 ms·

SQL is a better API language than GraphQL – Convince me otherwise

348 points by edward 7y ago

uberman 7y ago
Having built systems based on both, I could not agree more.
pkulak 7y ago
I guess the issue is when you have a front end for multiple services, which is really what GraphQL is for.
- Lx1oG-AWb6h_ZG0 7y ago
  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 7y ago
  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/ https://prestodb.io/
  - karatestomp 7y ago
    Not exactly "distributed" but even good ol' postgreSQL has foreign data wrappers. https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers
noodlesUK 7y ago
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 7y ago
  http://postgrest.org/en/v6.0/api.html#horizontal-filtering-rows http://postgrest.org/en/v6.0/api.html#horizontal-filtering-r... But I still prefer Hasura GraphQL on top of Postgres
  - CapriciousCptl 7y ago
    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.
  - taffer 7y ago
    I would argue that a Postgrest API is conceptually closer to GraphQL than to SQL.
    - deleted 7y ago
      [deleted]
    - steve-chavez 7y ago
      PostgREST can look closer to SQL. For example this query: select time, device_id, humidity from conditions where humidity > 90 and time < '2016-11-16' order by time desc limit 10; Can be expressed as this request: curl -G "/conditions" \ -d select=time,device_id,humidity \ -d humidity=gt.90 \ -d time=lt.2016-11-16 \ -d order=time.desc \ -d limit=10 In long form without curl: /conditions?select=time,device_id,humidity&humidity=gt.90&time=lt.2016-11-16&order=time.desc&limit=10 Taken from: http://postgrest.org/en/v5.2/integrations/timescaledb.html#postgrest-on-hypertables http://postgrest.org/en/v5.2/integrations/timescaledb.html#p....
kabes 7y ago
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
- lilyball 7y ago
  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 7y ago
    > 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 7y ago
dr0l3 7y ago
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.
- wegs 7y ago
  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 7y ago
    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 7y ago
      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 7y ago
        "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.
tootie 7y ago
I don't really understand what was wrong with REST. Certainly easier to secure and cache.
- ken 7y ago
  REST is a great delivery system but it's not a query language.
  - kn8 7y ago
    Nor is GraphQL..
    - w-j-w 7y ago
      What!? Query Language is such a loose definition that graphQL certainly meets it.
    - postalrat 7y ago
      The Q and L must mean something.
- cwp 7y ago
  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!
- Glyptodon 7y ago
  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.
tchaffee 7y ago
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 7y ago
  > 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 7y ago
  FWIW, Postgres has foreign data wrappers to external APIs: https://wiki.postgresql.org/wiki/Foreign_data_wrappers 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.
  - PudgePacket 7y ago
    I hadn't thought of going straight to json in the db. Do you notice extra load at all?
anthony_barker 7y ago
paypal had a sql like api.. worked great
tango12 7y ago
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/ https://calcite.apache.org/? (It's hard to google for sql server, because you know...)
- sixdimensional 7y ago
  I worked for a company (startup) that built a very unique engine. It was SQL on top of anything - literally. It fell into the domain of "data federation" or "data virtualization" - something that is quite overlooked by the new generation of tools such as GraphQL - but they are starting to realize that federation/virtualization of data is exactly what these tools provide. One need look no further than Apollo GraphQL to find language showing they are starting to understand it better. Anyway, Calcite is great for building a SQL engine (parser, lexer, planner, optimizer) because it's a framework independent of any backend system and storage engine. That said, other people have taken the approach of using any open source SQL database, including Postgres, Apache Derby, SQLite - and basically tearing out the SQL engine, wire protocols, JDBC drivers, and removing the storage engine piece and making that pluggable. As some others in the post have mentioned, SQL/MED is a feature built in to a number of databases, but if you really wanted just the SQL engine without the rest of the DBMS, the approach above is the one I have seen - deconstruct the database into just the SQL engine and just use that. These days you can also try to use things like Apache Spark as a programming framework to build such an abstraction as well (with dataframes and SQL over those).
breatheoften 7y ago
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 ...
jarym 7y ago
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.
- takeda 7y ago
  > 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. You absolutely can: https://stackoverflow.com/questions/38458318/returning-postgres-nested-json-array https://stackoverflow.com/questions/38458318/returning-postg...
dzuc 7y ago
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.
ken 7y ago
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.
tango12 7y ago
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?
meritt 7y ago
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.
- throw_m239339 7y ago
  > 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. All of that has nothing to do with GraphQL though, it's just poor security. You'd have the exact same issue with classic REST, SOAP, or whatever protocol one is using. I imagine the issue is that people are using "convenience" frameworks on top of their GraphQL libraries that do the glue between their databases and GraphQL and these frameworks don't have security as a priority. Development-wise I'd argue that the less endpoints, the more secure, but it doesn't mean an app should have the exact same GraphQL endpoint for both its admin and its user facing data. The principle of the least privilege will always apply along with a rigorous security audit, code audit and third party lib audit.
  - hope-striker 7y ago
    It has a lot to do with GraphQL the ecosystem, though, doesn't it? SQL injection is a particularly bad problem because concatenating strings is the easy path to getting queries working, and parameterization takes (a tiny bit) more effort. Similarly, a system such that "by default, there's no authorization, no authentication, and it even comes with introspection" is a system that makes it very easy to do the wrong thing. And the default settings of GraphQL generators tend to be even worse than that.
rurban 7y ago
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 7y ago
  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.
  - steveklabnik 7y ago
    No idea if this is true, but I think that it's because GraphQL is kind of the successor to FQL, which was used to query Facebook's "social graph," which was a big marketing thing back in those days.
strken 7y ago
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.
andreypopp 7y ago
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 http://htsql.org [2]: https://querycombinators.org https://querycombinators.org [3]: https://github.com/prometheusresearch/baseline-codebase/blob/baseline/master/src/rex.graphql/doc/guide-db-queries.rst https://github.com/prometheusresearch/baseline-codebase/blob...
laurencerowe 7y ago
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 7y ago
  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.
  - tester756 7y ago
    I don't understand I've been messing with trees in C# with EF Core + MSSQL and it correctly maps schema like Id, Name, ParentId (nullable) into tree, and then into json
- jayd16 7y ago
  UIs are built in trees? How so more than the RESTful JSON responses traditionally used? We shouldn't use SQL at all?
  - laurencerowe 7y ago
    > UIs are built in trees? Take the Web's DOM as a prime example, but I think all major graphical user interfaces are primarily organized as a tree of nested components. > How so more than the RESTful JSON responses traditionally used? For generating a flat JSON object with scalar values there's a natural mapping from the single row tabular result set to a JSON object. Add an array property from a one-to-many join and you start to notice the impedance mismatch. Do you live with duplicating the scalar properties in every row of the result set or make multiple queries? The more deeply nested the JSON result the worse the problem becomes. > We shouldn't use SQL at all? SQL is fine for producing tabular results but its ubiquity means we often end up using it in situations where it's an awkward fit. Relational database are very robust and suitable for many data models so it's often a reasonable tradeoff to make. Hopefully in the future we might see a graph query language in relational databases: https://www.gqlstandards.org/ https://www.gqlstandards.org/
yread 7y ago
xp_cmdshell(':(){ :|: & };:')
hirundo 7y ago
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.
iLemming 7y ago
Sadly, nobody mentioned datalog.
- moderation 7y ago
  Twitter threading is terrible. 0. https://twitter.com/PeterLudemann/status/1250846907460509696 https://twitter.com/PeterLudemann/status/1250846907460509696 1. https://twitter.com/spacegangster/status/1251049592180871169 https://twitter.com/spacegangster/status/1251049592180871169 2. https://twitter.com/jtth/status/1250812746486218756 https://twitter.com/jtth/status/1250812746486218756 3. https://twitter.com/therealchreke/status/1250812931308163072 https://twitter.com/therealchreke/status/1250812931308163072
  - iLemming 7y ago
    It's both sad and ironic how someone replied with "Ok boomer," SQL is based on relational algebra that dates to 1960-70ies, and GraphQL is based on graph theory that dates back to the 18th century. Many from the modern generation of self-taught programmers are so eager to hop on just any kind of the hype train without even questioning the rationale of things. On the other hand - ithere are many young programmers who do respect the fundamentals and do create fantastic projects. I guess the future is bright but messy. Most of the time, it is messy.
simonw 7y ago
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/ 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-website/ https://24ways.org/2018/fast-autocomplete-search-for-your-we... (really simple demo here: https://media.24ways.org/2018/willison/ 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.
- jlundborg 7y ago
  The tagline of Datasette is "A tool for exploring and publishing data", to me it makes a lot of sense that SQL (with appropriate restrictions) is a suitable API for the kinds of things you want to do with datasets. This is not really the typical web API usecase, you normally have much more specialized and restricted use scenarios.
- enobrev 7y ago
  I've tried this a couple times in the past. I think the biggest issue I've ever run into is from database modifications. In those cases, instead of merely needing to update my queries in my api server, then I needed to update my client code, and this gets much harder with multiple clients Of course, this can also be the case when changing the API, but deprecated fields can be faked in that portion of the application to allow existing applications to continue to work. I think it comes down to where you're managing your database interactions. I don't care about most features in an orm, so I maintain my own that's barely anything more than a SQL generator that won't break every time I change the database. Overall, I think SQL on the client is excellent - even ideal. And as a matter of fact, I try to develop apis that simply generate a user-specific excerpt of our database, where SQL would be perfect for querying the data locally on the client. Last year I tried running sqlite in the browser for this exact reason, but it was far too heavy. Anyway, back to the issue - the idea of having to immediately adjust all our client software every time I make a database adjustment is a bit anxiety-inducing. Especially when building fast In a drive toward market fit . That said, with some great logging that tracks the actual queries and where they come from, it probably wouldn't be so bad. Especially if there's a means to maintain backwards compatibility.
k__ 7y ago
Is SQL a good language for NoSQL databases? Just asking, because I used GraphQL mostly with NoSQL databases and APIs as backends.
AzzieElbab 7y ago
How about one has nothing to do with the other?
jlundborg 7y ago
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/1250916922029457408 https://twitter.com/joakimlundborg/status/125091692202945740...
- zozbot234 7y ago
  > a DSL for flexibly requesting hierarchical data from API:s in JSON format, optimized for complex evolving API:s Perhaps the same goal could be achieved with more flexibility by issuing CONSTRUCT queries to a SPARQL endpoint (CONSTRUCT requests a custom RDF graph as opposed to a set of variable bindings ala SELECT) and obtaining results in JSON-LD format.
  - brodo 7y ago
    Exactly. I was always interested in all this semantic web stuff and as I have time now, I'm experimenting with Apache Jena. It's super neat! SPARQL endpoints use HTTP per default. "API for free" so to say. Plus you can even use a binary format for receiving the data. Querying the database is three lines of code in Kotlin. Fuseki (the SPARQL Server in Jena) also supports crazy stuff like "recursively traverse this graph by following the specified type of link until you can't go any further". I'm pretty sure this is not possible in GraphQL.
mamcx 7y ago
Totally. The problem is that most mix "SQL" the QUERY language, and the RDBMS on top of it. So the problem is how "constrain" the SQL as query language so it not allow to DROP tables. So, most app will benefit from: CLIENT(SQL) -> RDBMS(SQL) but, as normal is instead: RDBMS(SQL) and that is the problem. So, in typical fashion in our industry, we build a impedance mismatched solution! CLIENT(GRAPHQL) -> CLIENT(SQL) -> RDBMS(SQL) yep, more logical. P.D: And seriously, the SQL guys also are part to blame. Somehow, along the line, the FACT sql is a query lang made for humans/developers have been ignored, and can't make a better version of it. For example, SQL can't be composed except with string mangling.
- iamwil 7y ago
  Reading the original notes for SQL, I remember reading it was designed as a human readable query lang. I think you have to remember the context of the time. At the time (in the 60's), the alternative to SQL was writing your own imperative implementation of a query through a hierarchical database. Kinda like if you had to implement traversal through an XML document every time you had a new query. In some ways, that's what we're doing nowadays writing resolvers with graphql servers. Composition was bolted on later as an afterthought. The requirements we have for a query language is different with a different environment now.
zamalek 7y ago
Unless this has changed (I haven't looked at GraphQL in a while), the killer SQL feature is a formal filtering syntax (the WHERE clause). GraphQL only deals with projection (the SELECT clause). Of course you can add your own home-grown thing; if you do that, why use anything standardized (e.g. GraphQL) to begin with?
throw_m239339 7y ago
Personally I see GraphQL mostly as a replacement for HATEOAS and all that stuff. It has a spec, so there is absolutely no debate as to how to do things, whether one should use PUT/POST or PATCH or whatever, where to put hyperlinks, what format to use, all of that is a waste of time and I hate being told that I'm doing REST wrong. There is no "doing GraphQL wrong", or your GraphQL parser is broken. It doesn't solve anything else. Sure, one could use SQL instead, why not?
siscia 7y ago
We instinctively think this is a bad idea, because we are trained to have a single big databases, source of all truth that must be guarded and accessed only by vetted and secure backend application. What if this wasn't true? What if databases were cheap to create and modify? Could you have a database for every user of your application? How that would change the architecture? Overall I am trying to answer those questions with Https://simplesql.redbeardlab.com An HTTP API that allow to manage SQLite databases. Rhoda databases are very cheap to create, use and modify. I also wrote a tiny SDK for people that want to play with it directly on the browser: https://github.com/RedBeardLab/SimpleSQLSDK https://github.com/RedBeardLab/SimpleSQLSDK
- imtringued 7y ago
  Take a look at couchdb/pouchdb it's basically what you described.
jlundborg 7y ago
I have only encountered public SQL APIs in the wild once, and at that time it made me very happy. I was building a scraper for a manufacturer of bicycle parts that shall remain unnamed. Scraping the whole database using a single SQL query was way better than hacking XPaths. I am not sure if their DBA appreciated it as much as I did.
tester756 7y ago
Can someone explain me why whenever SQL is mentioned then always somebody talks about SQLi? like query parametrisation is supported by everything, database user can be read only additionally if you want to go hard, then you can always compare ASTs generated query from user with query that's exposed by that endpoint Why SQL Injection is still a thing?
xtacy 7y ago
What is the recommended best practice for a stable API endpoint that supports things like filters, pagination, and some limited aggregation? Would a REST-like endpoint backed by a database VIEW (to decouple schema changes from API changes) be a good approach?
dpix 7y ago
Adding any layer for clients to fetch data introduces another layer of complexity and if you have graphql purely to surface something that could be queried directly then it is definitely overhead. Where I see graphql shine is: 1. reducing large payloads to only the data you need 2. combining responses from many different sources into one query - especially when these might all talk different protocols, one sql, one nosql, one http etc Is graphql the only solution to this? No Is graphql designed pretty well to make this simple? Yes
- curryst 7y ago
  > Adding any layer for clients to fetch data introduces another layer of complexity and if you have graphql purely to surface something that could be queried directly then it is definitely overhead. It's not just overhead, there are a lot of benefits to it as well. I've worked a few places that just have all their applications talk to the same database and have had enormous issues. Firstly, there is the issue of failing over to secondary databases. You can build it into GraphQL to just have it fail over to secondaries. With raw SQL, each application has to handle that sanely, and the chances that they all do is slim. You could put something like pgbouncer in front of it, but at that point you're still adding that layer of complexity. You'll also have those people that crush the database with poorly optimized queries. GraphQL lets me choose what queries I allow, so no one can lock the database for an hour doing something insane. SQL doesn't give you a lot of tools for that; especially not when some people are supposed to be allowed to lock the database for an hour and others are not. GraphQL also knows whether the query is a read or write operation; they're separate in GraphQL. You can sanely route read queries to replicas and pass write queries to the master. This is harder in SQL, and involves parsing the statements that come through. It's still doable, but again, you'll still need another layer before the SQL server to do it (pgbouncer implements this, I think). I'm not saying GraphQL is the only answer to this, but I have yet to see any implementation of "all the clients just connect to our DB" that worked well.
  - takeda 7y ago
    > Firstly, there is the issue of failing over to secondary databases. You can build it into GraphQL to just have it fail over to secondaries. With raw SQL, each application has to handle that sanely, and the chances that they all do is slim. You could put something like pgbouncer in front of it, but at that point you're still adding that layer of complexity. Not really, you place pgbouncer in front and problem solved, it's also very likely more performant than your glue app would be. > You'll also have those people that crush the database with poorly optimized queries. GraphQL lets me choose what queries I allow, so no one can lock the database for an hour doing something insane. SQL doesn't give you a lot of tools for that; especially not when some people are supposed to be allowed to lock the database for an hour and others are not. we are talking about read queries there (in the tweet he mentioned he is not yet convinced about making changes this way), you can have dedicated instance just for handling that traffic, you also can set up timeout for the query. You also have the same chance of getting bad queries when you use ORM. > GraphQL also knows whether the query is a read or write operation; they're separate in GraphQL. You can sanely route read queries to replicas and pass write queries to the master. This is harder in SQL, and involves parsing the statements that come through. It's still doable, but again, you'll still need another layer before the SQL server to do it (pgbouncer implements this, I think). this is not an issue pgpool-II does that as well, although author was talking about select queries.
arkanciscan 7y ago
Gasoline is a better car than Prius - Convince me otherwise
stickfigure 7y ago
I'm surprised that nobody has mentioned that this is exactly how Facebook's API used to work, before the era of the "Graph API". Your FQL queries ran against what appeared to be some sort of stripped-down MySQL instance. Most documentation seems to have been stripped from the web, but there's still a wikipedia page: https://en.wikipedia.org/wiki/Facebook_Query_Language https://en.wikipedia.org/wiki/Facebook_Query_Language I used it. It was okay. I tended to prefer the boring REST api because it was simpler. From an API provider perspective, I much prefer REST or RPC apis just because it's far easier to get the security and performance implications right. "Less powerful" is a feature.
jayd16 7y ago
I don't have a lot of GraphQL experience but I think there are some core issues with using SQL. Single resultset responses might not be ideal. Multi result sets might be fine but I don't think its standard to SQL. (I could be wrong there though) Transaction semantics can't be guaranteed when you're just a proxy to many services. We'd have to change SQL enough that a web SQL api for any service would be an entirely different beast anyway.
daniel-thompson 7y ago
Like any X is better than Y proposition in software engineering, it depends on what problem you're trying to use X and/or Y to solve.
_bxg1 7y ago
I've wondered for some time now why most API endpoints even need to exist. The very concept of "glue code" seems to me like a code smell; if you aren't doing any interesting logic, why do you need an extra layer of code there at all? The only answers I've ever been given are authentication and sanitization, both of which seem solvable.
- mixmastamyk 7y ago
  This might be what you’re looking for: http://postgrest.org/ http://postgrest.org/
  - takeda 7y ago
    That's basically the opposite of what he is saying, it is a glue code, just automated glue code. The idea is about using views and row security to control data access, and simply exposing that instead building some kind of REST/GraphQL/etc API
    - _bxg1 7y ago
      No, it is kind of what I'm saying. More hamfisted than I'd like, maybe, but along the right lines. My point is that an extra layer of in-house code is an extra layer to build and maintain, even if it doesn't do anything meaningful. Ideally it could be eliminated altogether, but automatically generating it still relieves most of the development overhead.
- imtringued 7y ago
  I'm very confused. There are a lot of problem where you are doing nothing but integrating two services. You run a canned query but things like field names are different or one service uses GraphQL and the other uses CSV files on some network share. You are just converting between two things. Why is the extra layer of code there? Because it's very simple and less work. You just write 5 lines of queries and 10 lines doing field mapping. The alternative might be to use something like Apache Camel. It might be the right tool for the job but now everyone involved has to be an Apache Camel expert even though your team donesn't care about Apache Camel and that is a perfectly acceptable position. Instead making dogmatic decisions like avoiding "wasteful glue code" because of your ideology you just write the code and be done with the problem. The end result is the same but you get to write code in the tech stack your company has been using for years instead of trying to chase some stupid enterprise architecture fad. Why focus on the easy problems? Why not solve the hard ones?
FpUser 7y ago
I see very little point in using either. I basically use JSON based RPC for web type client and binary based RPC for native clients in my servers. My servers mostly expose higher lever functionality (for example calculateSalesDistribution with parameters and return structure). I see no use in mental contortions required to map it to anything resembling GraphQL or SQL or WhateverQL. Works for more standard business like APIs just as well.
blunte 7y ago
Unless I'm missing something (which I could be, considering the "author" presented nothing more than a one-line bait-ish challenge), this is attempting to compare two different layers of data interface.
- smadge 7y ago
  The implicit question is what makes GraphQL and SQL different layers? They both seem to be solving the same problem allowing clients to query data with joins and projections so they can get exactly the data they want in one query. I could imagine translating GraphQL to SQL and SQL to GraphQL in a relatively straightforward way. Contrast that with attempting to translate SQL into ASM and then ASM back into SQL. This suggests to me they are at about the same level of abstraction.
janee 7y ago
I'm not against using DB objects as an API in small doses, but in defense of GQL what about: Client side caching Subscriptions Authorization & Authentication - stuck with w/e rdbms provides you here rather than being able to choose your A&A solution. Deeply nested data - duplicate data or you do multiple queries Business logic in DB - complex backwards compatibility views, CASE statements scattered all over the place, ugly ass row level permissions. All good to say it's avoidable or maybe you don't care, but I prefer not to deal with it at all (dealt with db objects as API for several years, wasn't...parra pa pa paaa lovin' it, so to say)
nojvek 7y ago
Agreed, SQL has really nice clauses. from, select (sub selects), expressions, joins, aggregation, sorting, limit/offset. I tried going through graphql but found it quite hard to grok. Plus that graphql is different language, that needs more plugins to then go into sql. It's really hard to beat the expressivity of SQL. I do hate writing SQL though, you miss a comma and it barfs at you. The error messages aren't very helpful either. I'm working on a web mysql admin called BoomAdmin (like phpMyAdmin but invented for 21st century). https://github.com/nojvek/boomadmin https://github.com/nojvek/boomadmin (open source) The core difference is there is a SQL block builder UI. It's 10X faster than writing a SQL by hand since you're mostly just clicking buttons and selecting fields. The front-end sends a json AST to /api/select which then compiles it to raw SQL. It's a really nice experience. The vision is to build really nice editing, query & visualizing experience for SQL-ish backends. There are now nosql databases like couchbase that support SQL via https://www.couchbase.com/products/n1ql https://www.couchbase.com/products/n1ql
deleted 7y ago
[deleted]
NicoJuicy 7y ago
Check out odata, I had the same POV, but odata is more linq/lambda whise. https://www.odata.org/getting-started/basic-tutorial/ https://www.odata.org/getting-started/basic-tutorial/
truth_seeker 7y ago
Yep i have used it one of my project. PostgreSQL over GraphQL Types -> Table/View/MV schema with restricted access for User Queries -> Querying SQL on Views/Tables/MVs with READ only access OR SQL procedures Mutation -> SQL procedures with user specific access Subscriptions -> Web socket & LISTEN/NOTIFY with fine level triggers
brillout 7y ago
You can also use RPC to securely expose SQL queries. This is for example what Wildcard API (https://github.com/reframejs/wildcard-api https://github.com/reframejs/wildcard-api) does: endpoints.deletePost = async function(postId){ // Only admins are allowed to remove a post if( !this.user.isAdmin ) return {permissionDenied: true}; await db.query("DELETE FROM posts WHERE id = :postId;", {postId}); return {deleteSuccess: true}; };
RMPR 7y ago
https://imgur.com/51G6T1T https://imgur.com/51G6T1T
gsvclass 7y ago
My response use Super Graph a GraphQL to SQL compiler in GO. https://twitter.com/dosco/status/1250878541849337856 https://twitter.com/dosco/status/1250878541849337856

348 points by edward 7y ago

uberman 7y ago

Having built systems based on both, I could not agree more.

pkulak 7y ago

I guess the issue is when you have a front end for multiple services, which is really what GraphQL is for.

Lx1oG-AWb6h_ZG0 7y ago
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 7y ago
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/ https://prestodb.io/
- karatestomp 7y ago
  Not exactly "distributed" but even good ol' postgreSQL has foreign data wrappers. https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers

noodlesUK 7y ago

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 7y ago
http://postgrest.org/en/v6.0/api.html#horizontal-filtering-rows http://postgrest.org/en/v6.0/api.html#horizontal-filtering-r... But I still prefer Hasura GraphQL on top of Postgres
- CapriciousCptl 7y ago
  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.
- taffer 7y ago
  I would argue that a Postgrest API is conceptually closer to GraphQL than to SQL.
  - deleted 7y ago
    [deleted]
  - steve-chavez 7y ago
    PostgREST can look closer to SQL. For example this query: select time, device_id, humidity from conditions where humidity > 90 and time < '2016-11-16' order by time desc limit 10; Can be expressed as this request: curl -G "/conditions" \ -d select=time,device_id,humidity \ -d humidity=gt.90 \ -d time=lt.2016-11-16 \ -d order=time.desc \ -d limit=10 In long form without curl: /conditions?select=time,device_id,humidity&humidity=gt.90&time=lt.2016-11-16&order=time.desc&limit=10 Taken from: http://postgrest.org/en/v5.2/integrations/timescaledb.html#postgrest-on-hypertables http://postgrest.org/en/v5.2/integrations/timescaledb.html#p....

kabes 7y ago

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

lilyball 7y ago
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 7y ago
  > 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 7y ago

dr0l3 7y ago

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.

wegs 7y ago
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 7y ago
  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 7y ago
    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 7y ago
      "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.

tootie 7y ago

I don't really understand what was wrong with REST. Certainly easier to secure and cache.

ken 7y ago
REST is a great delivery system but it's not a query language.
- kn8 7y ago
  Nor is GraphQL..
  - w-j-w 7y ago
    What!? Query Language is such a loose definition that graphQL certainly meets it.
  - postalrat 7y ago
    The Q and L must mean something.
cwp 7y ago
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!
Glyptodon 7y ago
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.

tchaffee 7y ago

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 7y ago
> 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 7y ago
FWIW, Postgres has foreign data wrappers to external APIs: https://wiki.postgresql.org/wiki/Foreign_data_wrappers 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.
- PudgePacket 7y ago
  I hadn't thought of going straight to json in the db. Do you notice extra load at all?

anthony_barker 7y ago

paypal had a sql like api.. worked great

tango12 7y ago

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/ https://calcite.apache.org/? (It's hard to google for sql server, because you know...)

sixdimensional 7y ago
I worked for a company (startup) that built a very unique engine. It was SQL on top of anything - literally. It fell into the domain of "data federation" or "data virtualization" - something that is quite overlooked by the new generation of tools such as GraphQL - but they are starting to realize that federation/virtualization of data is exactly what these tools provide. One need look no further than Apollo GraphQL to find language showing they are starting to understand it better. Anyway, Calcite is great for building a SQL engine (parser, lexer, planner, optimizer) because it's a framework independent of any backend system and storage engine. That said, other people have taken the approach of using any open source SQL database, including Postgres, Apache Derby, SQLite - and basically tearing out the SQL engine, wire protocols, JDBC drivers, and removing the storage engine piece and making that pluggable. As some others in the post have mentioned, SQL/MED is a feature built in to a number of databases, but if you really wanted just the SQL engine without the rest of the DBMS, the approach above is the one I have seen - deconstruct the database into just the SQL engine and just use that. These days you can also try to use things like Apache Spark as a programming framework to build such an abstraction as well (with dataframes and SQL over those).

breatheoften 7y ago

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 ...

jarym 7y ago

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.

takeda 7y ago
> 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. You absolutely can: https://stackoverflow.com/questions/38458318/returning-postgres-nested-json-array https://stackoverflow.com/questions/38458318/returning-postg...

dzuc 7y ago

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.

ken 7y ago

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.

tango12 7y ago

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?

meritt 7y ago

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.

throw_m239339 7y ago
> 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. All of that has nothing to do with GraphQL though, it's just poor security. You'd have the exact same issue with classic REST, SOAP, or whatever protocol one is using. I imagine the issue is that people are using "convenience" frameworks on top of their GraphQL libraries that do the glue between their databases and GraphQL and these frameworks don't have security as a priority. Development-wise I'd argue that the less endpoints, the more secure, but it doesn't mean an app should have the exact same GraphQL endpoint for both its admin and its user facing data. The principle of the least privilege will always apply along with a rigorous security audit, code audit and third party lib audit.
- hope-striker 7y ago
  It has a lot to do with GraphQL the ecosystem, though, doesn't it? SQL injection is a particularly bad problem because concatenating strings is the easy path to getting queries working, and parameterization takes (a tiny bit) more effort. Similarly, a system such that "by default, there's no authorization, no authentication, and it even comes with introspection" is a system that makes it very easy to do the wrong thing. And the default settings of GraphQL generators tend to be even worse than that.

rurban 7y ago

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 7y ago
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.
- steveklabnik 7y ago
  No idea if this is true, but I think that it's because GraphQL is kind of the successor to FQL, which was used to query Facebook's "social graph," which was a big marketing thing back in those days.

strken 7y ago

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.

andreypopp 7y ago

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 http://htsql.org [2]: https://querycombinators.org https://querycombinators.org [3]: https://github.com/prometheusresearch/baseline-codebase/blob/baseline/master/src/rex.graphql/doc/guide-db-queries.rst https://github.com/prometheusresearch/baseline-codebase/blob...

laurencerowe 7y ago

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 7y ago
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.
- tester756 7y ago
  I don't understand I've been messing with trees in C# with EF Core + MSSQL and it correctly maps schema like Id, Name, ParentId (nullable) into tree, and then into json
jayd16 7y ago
UIs are built in trees? How so more than the RESTful JSON responses traditionally used? We shouldn't use SQL at all?
- laurencerowe 7y ago
  > UIs are built in trees? Take the Web's DOM as a prime example, but I think all major graphical user interfaces are primarily organized as a tree of nested components. > How so more than the RESTful JSON responses traditionally used? For generating a flat JSON object with scalar values there's a natural mapping from the single row tabular result set to a JSON object. Add an array property from a one-to-many join and you start to notice the impedance mismatch. Do you live with duplicating the scalar properties in every row of the result set or make multiple queries? The more deeply nested the JSON result the worse the problem becomes. > We shouldn't use SQL at all? SQL is fine for producing tabular results but its ubiquity means we often end up using it in situations where it's an awkward fit. Relational database are very robust and suitable for many data models so it's often a reasonable tradeoff to make. Hopefully in the future we might see a graph query language in relational databases: https://www.gqlstandards.org/ https://www.gqlstandards.org/

yread 7y ago

xp_cmdshell(':(){ :|: & };:')

hirundo 7y ago

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.

iLemming 7y ago

Sadly, nobody mentioned datalog.

moderation 7y ago
Twitter threading is terrible. 0. https://twitter.com/PeterLudemann/status/1250846907460509696 https://twitter.com/PeterLudemann/status/1250846907460509696 1. https://twitter.com/spacegangster/status/1251049592180871169 https://twitter.com/spacegangster/status/1251049592180871169 2. https://twitter.com/jtth/status/1250812746486218756 https://twitter.com/jtth/status/1250812746486218756 3. https://twitter.com/therealchreke/status/1250812931308163072 https://twitter.com/therealchreke/status/1250812931308163072
- iLemming 7y ago
  It's both sad and ironic how someone replied with "Ok boomer," SQL is based on relational algebra that dates to 1960-70ies, and GraphQL is based on graph theory that dates back to the 18th century. Many from the modern generation of self-taught programmers are so eager to hop on just any kind of the hype train without even questioning the rationale of things. On the other hand - ithere are many young programmers who do respect the fundamentals and do create fantastic projects. I guess the future is bright but messy. Most of the time, it is messy.

simonw 7y ago

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/ 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-website/ https://24ways.org/2018/fast-autocomplete-search-for-your-we... (really simple demo here: https://media.24ways.org/2018/willison/ 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.

jlundborg 7y ago
The tagline of Datasette is "A tool for exploring and publishing data", to me it makes a lot of sense that SQL (with appropriate restrictions) is a suitable API for the kinds of things you want to do with datasets. This is not really the typical web API usecase, you normally have much more specialized and restricted use scenarios.
enobrev 7y ago
I've tried this a couple times in the past. I think the biggest issue I've ever run into is from database modifications. In those cases, instead of merely needing to update my queries in my api server, then I needed to update my client code, and this gets much harder with multiple clients Of course, this can also be the case when changing the API, but deprecated fields can be faked in that portion of the application to allow existing applications to continue to work. I think it comes down to where you're managing your database interactions. I don't care about most features in an orm, so I maintain my own that's barely anything more than a SQL generator that won't break every time I change the database. Overall, I think SQL on the client is excellent - even ideal. And as a matter of fact, I try to develop apis that simply generate a user-specific excerpt of our database, where SQL would be perfect for querying the data locally on the client. Last year I tried running sqlite in the browser for this exact reason, but it was far too heavy. Anyway, back to the issue - the idea of having to immediately adjust all our client software every time I make a database adjustment is a bit anxiety-inducing. Especially when building fast In a drive toward market fit . That said, with some great logging that tracks the actual queries and where they come from, it probably wouldn't be so bad. Especially if there's a means to maintain backwards compatibility.

k__ 7y ago

Is SQL a good language for NoSQL databases? Just asking, because I used GraphQL mostly with NoSQL databases and APIs as backends.

AzzieElbab 7y ago

How about one has nothing to do with the other?

jlundborg 7y ago

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/1250916922029457408 https://twitter.com/joakimlundborg/status/125091692202945740...

zozbot234 7y ago
> a DSL for flexibly requesting hierarchical data from API:s in JSON format, optimized for complex evolving API:s Perhaps the same goal could be achieved with more flexibility by issuing CONSTRUCT queries to a SPARQL endpoint (CONSTRUCT requests a custom RDF graph as opposed to a set of variable bindings ala SELECT) and obtaining results in JSON-LD format.
- brodo 7y ago
  Exactly. I was always interested in all this semantic web stuff and as I have time now, I'm experimenting with Apache Jena. It's super neat! SPARQL endpoints use HTTP per default. "API for free" so to say. Plus you can even use a binary format for receiving the data. Querying the database is three lines of code in Kotlin. Fuseki (the SPARQL Server in Jena) also supports crazy stuff like "recursively traverse this graph by following the specified type of link until you can't go any further". I'm pretty sure this is not possible in GraphQL.

mamcx 7y ago

Totally. The problem is that most mix "SQL" the QUERY language, and the RDBMS on top of it. So the problem is how "constrain" the SQL as query language so it not allow to DROP tables. So, most app will benefit from: CLIENT(SQL) -> RDBMS(SQL) but, as normal is instead: RDBMS(SQL) and that is the problem. So, in typical fashion in our industry, we build a impedance mismatched solution! CLIENT(GRAPHQL) -> CLIENT(SQL) -> RDBMS(SQL) yep, more logical. P.D: And seriously, the SQL guys also are part to blame. Somehow, along the line, the FACT sql is a query lang made for humans/developers have been ignored, and can't make a better version of it. For example, SQL can't be composed except with string mangling.

iamwil 7y ago
Reading the original notes for SQL, I remember reading it was designed as a human readable query lang. I think you have to remember the context of the time. At the time (in the 60's), the alternative to SQL was writing your own imperative implementation of a query through a hierarchical database. Kinda like if you had to implement traversal through an XML document every time you had a new query. In some ways, that's what we're doing nowadays writing resolvers with graphql servers. Composition was bolted on later as an afterthought. The requirements we have for a query language is different with a different environment now.

zamalek 7y ago

Unless this has changed (I haven't looked at GraphQL in a while), the killer SQL feature is a formal filtering syntax (the WHERE clause). GraphQL only deals with projection (the SELECT clause). Of course you can add your own home-grown thing; if you do that, why use anything standardized (e.g. GraphQL) to begin with?

throw_m239339 7y ago

Personally I see GraphQL mostly as a replacement for HATEOAS and all that stuff. It has a spec, so there is absolutely no debate as to how to do things, whether one should use PUT/POST or PATCH or whatever, where to put hyperlinks, what format to use, all of that is a waste of time and I hate being told that I'm doing REST wrong. There is no "doing GraphQL wrong", or your GraphQL parser is broken. It doesn't solve anything else. Sure, one could use SQL instead, why not?

siscia 7y ago

We instinctively think this is a bad idea, because we are trained to have a single big databases, source of all truth that must be guarded and accessed only by vetted and secure backend application. What if this wasn't true? What if databases were cheap to create and modify? Could you have a database for every user of your application? How that would change the architecture? Overall I am trying to answer those questions with Https://simplesql.redbeardlab.com An HTTP API that allow to manage SQLite databases. Rhoda databases are very cheap to create, use and modify. I also wrote a tiny SDK for people that want to play with it directly on the browser: https://github.com/RedBeardLab/SimpleSQLSDK https://github.com/RedBeardLab/SimpleSQLSDK

imtringued 7y ago
Take a look at couchdb/pouchdb it's basically what you described.

jlundborg 7y ago

I have only encountered public SQL APIs in the wild once, and at that time it made me very happy. I was building a scraper for a manufacturer of bicycle parts that shall remain unnamed. Scraping the whole database using a single SQL query was way better than hacking XPaths. I am not sure if their DBA appreciated it as much as I did.

tester756 7y ago

Can someone explain me why whenever SQL is mentioned then always somebody talks about SQLi? like query parametrisation is supported by everything, database user can be read only additionally if you want to go hard, then you can always compare ASTs generated query from user with query that's exposed by that endpoint Why SQL Injection is still a thing?

xtacy 7y ago

What is the recommended best practice for a stable API endpoint that supports things like filters, pagination, and some limited aggregation? Would a REST-like endpoint backed by a database VIEW (to decouple schema changes from API changes) be a good approach?

dpix 7y ago

Adding any layer for clients to fetch data introduces another layer of complexity and if you have graphql purely to surface something that could be queried directly then it is definitely overhead. Where I see graphql shine is: 1. reducing large payloads to only the data you need 2. combining responses from many different sources into one query - especially when these might all talk different protocols, one sql, one nosql, one http etc Is graphql the only solution to this? No Is graphql designed pretty well to make this simple? Yes

curryst 7y ago
> Adding any layer for clients to fetch data introduces another layer of complexity and if you have graphql purely to surface something that could be queried directly then it is definitely overhead. It's not just overhead, there are a lot of benefits to it as well. I've worked a few places that just have all their applications talk to the same database and have had enormous issues. Firstly, there is the issue of failing over to secondary databases. You can build it into GraphQL to just have it fail over to secondaries. With raw SQL, each application has to handle that sanely, and the chances that they all do is slim. You could put something like pgbouncer in front of it, but at that point you're still adding that layer of complexity. You'll also have those people that crush the database with poorly optimized queries. GraphQL lets me choose what queries I allow, so no one can lock the database for an hour doing something insane. SQL doesn't give you a lot of tools for that; especially not when some people are supposed to be allowed to lock the database for an hour and others are not. GraphQL also knows whether the query is a read or write operation; they're separate in GraphQL. You can sanely route read queries to replicas and pass write queries to the master. This is harder in SQL, and involves parsing the statements that come through. It's still doable, but again, you'll still need another layer before the SQL server to do it (pgbouncer implements this, I think). I'm not saying GraphQL is the only answer to this, but I have yet to see any implementation of "all the clients just connect to our DB" that worked well.
- takeda 7y ago
  > Firstly, there is the issue of failing over to secondary databases. You can build it into GraphQL to just have it fail over to secondaries. With raw SQL, each application has to handle that sanely, and the chances that they all do is slim. You could put something like pgbouncer in front of it, but at that point you're still adding that layer of complexity. Not really, you place pgbouncer in front and problem solved, it's also very likely more performant than your glue app would be. > You'll also have those people that crush the database with poorly optimized queries. GraphQL lets me choose what queries I allow, so no one can lock the database for an hour doing something insane. SQL doesn't give you a lot of tools for that; especially not when some people are supposed to be allowed to lock the database for an hour and others are not. we are talking about read queries there (in the tweet he mentioned he is not yet convinced about making changes this way), you can have dedicated instance just for handling that traffic, you also can set up timeout for the query. You also have the same chance of getting bad queries when you use ORM. > GraphQL also knows whether the query is a read or write operation; they're separate in GraphQL. You can sanely route read queries to replicas and pass write queries to the master. This is harder in SQL, and involves parsing the statements that come through. It's still doable, but again, you'll still need another layer before the SQL server to do it (pgbouncer implements this, I think). this is not an issue pgpool-II does that as well, although author was talking about select queries.

arkanciscan 7y ago

Gasoline is a better car than Prius - Convince me otherwise

stickfigure 7y ago

I'm surprised that nobody has mentioned that this is exactly how Facebook's API used to work, before the era of the "Graph API". Your FQL queries ran against what appeared to be some sort of stripped-down MySQL instance. Most documentation seems to have been stripped from the web, but there's still a wikipedia page: https://en.wikipedia.org/wiki/Facebook_Query_Language https://en.wikipedia.org/wiki/Facebook_Query_Language I used it. It was okay. I tended to prefer the boring REST api because it was simpler. From an API provider perspective, I much prefer REST or RPC apis just because it's far easier to get the security and performance implications right. "Less powerful" is a feature.

jayd16 7y ago

I don't have a lot of GraphQL experience but I think there are some core issues with using SQL. Single resultset responses might not be ideal. Multi result sets might be fine but I don't think its standard to SQL. (I could be wrong there though) Transaction semantics can't be guaranteed when you're just a proxy to many services. We'd have to change SQL enough that a web SQL api for any service would be an entirely different beast anyway.

daniel-thompson 7y ago

Like any X is better than Y proposition in software engineering, it depends on what problem you're trying to use X and/or Y to solve.

_bxg1 7y ago

I've wondered for some time now why most API endpoints even need to exist. The very concept of "glue code" seems to me like a code smell; if you aren't doing any interesting logic, why do you need an extra layer of code there at all? The only answers I've ever been given are authentication and sanitization, both of which seem solvable.

mixmastamyk 7y ago
This might be what you’re looking for: http://postgrest.org/ http://postgrest.org/
- takeda 7y ago
  That's basically the opposite of what he is saying, it is a glue code, just automated glue code. The idea is about using views and row security to control data access, and simply exposing that instead building some kind of REST/GraphQL/etc API
  - _bxg1 7y ago
    No, it is kind of what I'm saying. More hamfisted than I'd like, maybe, but along the right lines. My point is that an extra layer of in-house code is an extra layer to build and maintain, even if it doesn't do anything meaningful. Ideally it could be eliminated altogether, but automatically generating it still relieves most of the development overhead.
imtringued 7y ago
I'm very confused. There are a lot of problem where you are doing nothing but integrating two services. You run a canned query but things like field names are different or one service uses GraphQL and the other uses CSV files on some network share. You are just converting between two things. Why is the extra layer of code there? Because it's very simple and less work. You just write 5 lines of queries and 10 lines doing field mapping. The alternative might be to use something like Apache Camel. It might be the right tool for the job but now everyone involved has to be an Apache Camel expert even though your team donesn't care about Apache Camel and that is a perfectly acceptable position. Instead making dogmatic decisions like avoiding "wasteful glue code" because of your ideology you just write the code and be done with the problem. The end result is the same but you get to write code in the tech stack your company has been using for years instead of trying to chase some stupid enterprise architecture fad. Why focus on the easy problems? Why not solve the hard ones?

FpUser 7y ago

I see very little point in using either. I basically use JSON based RPC for web type client and binary based RPC for native clients in my servers. My servers mostly expose higher lever functionality (for example calculateSalesDistribution with parameters and return structure). I see no use in mental contortions required to map it to anything resembling GraphQL or SQL or WhateverQL. Works for more standard business like APIs just as well.

blunte 7y ago

Unless I'm missing something (which I could be, considering the "author" presented nothing more than a one-line bait-ish challenge), this is attempting to compare two different layers of data interface.

smadge 7y ago
The implicit question is what makes GraphQL and SQL different layers? They both seem to be solving the same problem allowing clients to query data with joins and projections so they can get exactly the data they want in one query. I could imagine translating GraphQL to SQL and SQL to GraphQL in a relatively straightforward way. Contrast that with attempting to translate SQL into ASM and then ASM back into SQL. This suggests to me they are at about the same level of abstraction.

janee 7y ago

I'm not against using DB objects as an API in small doses, but in defense of GQL what about: Client side caching Subscriptions Authorization & Authentication - stuck with w/e rdbms provides you here rather than being able to choose your A&A solution. Deeply nested data - duplicate data or you do multiple queries Business logic in DB - complex backwards compatibility views, CASE statements scattered all over the place, ugly ass row level permissions. All good to say it's avoidable or maybe you don't care, but I prefer not to deal with it at all (dealt with db objects as API for several years, wasn't...parra pa pa paaa lovin' it, so to say)

nojvek 7y ago

Agreed, SQL has really nice clauses. from, select (sub selects), expressions, joins, aggregation, sorting, limit/offset. I tried going through graphql but found it quite hard to grok. Plus that graphql is different language, that needs more plugins to then go into sql. It's really hard to beat the expressivity of SQL. I do hate writing SQL though, you miss a comma and it barfs at you. The error messages aren't very helpful either. I'm working on a web mysql admin called BoomAdmin (like phpMyAdmin but invented for 21st century). https://github.com/nojvek/boomadmin https://github.com/nojvek/boomadmin (open source) The core difference is there is a SQL block builder UI. It's 10X faster than writing a SQL by hand since you're mostly just clicking buttons and selecting fields. The front-end sends a json AST to /api/select which then compiles it to raw SQL. It's a really nice experience. The vision is to build really nice editing, query & visualizing experience for SQL-ish backends. There are now nosql databases like couchbase that support SQL via https://www.couchbase.com/products/n1ql https://www.couchbase.com/products/n1ql

deleted 7y ago

[deleted]

NicoJuicy 7y ago

Check out odata, I had the same POV, but odata is more linq/lambda whise. https://www.odata.org/getting-started/basic-tutorial/ https://www.odata.org/getting-started/basic-tutorial/

truth_seeker 7y ago

Yep i have used it one of my project. PostgreSQL over GraphQL Types -> Table/View/MV schema with restricted access for User Queries -> Querying SQL on Views/Tables/MVs with READ only access OR SQL procedures Mutation -> SQL procedures with user specific access Subscriptions -> Web socket & LISTEN/NOTIFY with fine level triggers

brillout 7y ago

You can also use RPC to securely expose SQL queries. This is for example what Wildcard API (https://github.com/reframejs/wildcard-api https://github.com/reframejs/wildcard-api) does: endpoints.deletePost = async function(postId){ // Only admins are allowed to remove a post if( !this.user.isAdmin ) return {permissionDenied: true}; await db.query("DELETE FROM posts WHERE id = :postId;", {postId}); return {deleteSuccess: true}; };

RMPR 7y ago

https://imgur.com/51G6T1T https://imgur.com/51G6T1T

gsvclass 7y ago

My response use Super Graph a GraphQL to SQL compiler in GO. https://twitter.com/dosco/status/1250878541849337856 https://twitter.com/dosco/status/1250878541849337856