12 ms·

So why not pure SQL, which everyone knows already?

scrollaway 8y ago
GraphQL has a lot of advantages over SQL, API-wise; namely it's a lot more natural to describe what you want to get back. But without getting into that, the point is that you usually don't want to give direct data access, with all that entails. You still want to be declaring the fields and properties of the data that users will be querying, and that usually sits a layer above your raw data. You can make it a SQL view if you like, and give access to those instead. But it's just a lot of hassle to solve a solved problem in an unfamiliar way. _______ Edit: Clarifying my stance on SQL views. I'm not dismissing it as a "bad tool", but there's good reasons it's not a popular one today. Writing SQL is not easy for everyone, and it's especially not easy for potential API consumers. The best APIs are those you grok. If you grok SQL, congratulations, but most devs don't, and APIs are usually designed for most people. Obviously, if SQL is the right tool for the job, use SQL. But don't start doing stuff like this for ideological reasons.
- tathougies 8y ago
  I mean SQL views have been a thing for literally decades longer than GraphQL. If any technology should be accused of causing a 'lot of hassle to solve a solved problem', it's graphql, not SQL.
  - atombender 8y ago
    If your original data is flat and your app can consume it flat, no worries. But SQL is a worse fit for structured data, so you end up with an ORM on top, which is what GraphQL sort of is. Typically web apps are written with web technologies that work with structured objects, and you want something like {title: "Stapler", price: 1230, category: {id: "123", name: "Office supplies"}, mainPhoto: {url: "https://..." https://...", width: 600, height: 300}} back to feed directly in the UI. Some databases (Postgres comes to mind) are better at letting you express this as a query which builds the structure using JSON functions in the SELECT part of the query. But it's not even close to ideal.
    - just_myles 8y ago
      "Some databases (Postgres comes to mind) are better at letting you express this as a query which builds the structure using JSON functions in the SELECT part of the query. But it's not even close to ideal." Depending on which version of postgres you have(Want to say pre 9.5.) it can be a pain. There are various functions that allow a JSON output and another to make it human readable. With regards to performance I couldn't say but I imagine it would not be as fast as GraphQL.
    - aaaaaaaaaab 8y ago
      You don’t need an ORM, just a big JOIN.
      - yen223 8y ago
        JOINs also return flat data structures, no?
        aaaaaaaaaab 8y ago
        Just iterate through the results and recover the hierarchical structure. It’s not exactly rocket science: rows = sql("SELECT * FROM parent LEFT JOIN child ON parent.id = child.parent_id") results = [] for row in rows { if row["parent.id"] != results.last.id { results.add({ id: row["parent.id"], children: [], ... }) } if row["child.id"] != null { results.last.children.add({ id: row["child.id"], ... }) } }
        laszlokorte 8y ago
        but now it's no longer a declarative query. What if you want to include grand children? or if you want to fetch just one child but include it's parent and it's siblings of another type like a product, it's category and the categories tags: {id: 23, name: 'Trackpad', category: {id: 42, name:'Equipment', tags: [{id:...}, {id:...}]}} Now instead of just adding two joins you have to build a custom loop with hand crafted conditions. Sure you can find a way to generalize that loop and put it into a library but still the query itself is separated from some kind of post processing that has be kept in sync.
- aidos 8y ago
  I’m a huge proponent of sql, for me it’s virtually impossible to beat as a query language. We played with wrapping our dB layer with graphql recently and I would say that the experience was pretty good. In part it’s because you’re working a layer up where you can have a more pluggable model. In the first run you can model your local dB structure, but it’s easy to glue more stuff into it from other sources and generally evolve the structure.
  - barrkel 8y ago
    I find it very easy to imagine a better SQL. Two things I'd fix: * make it explicit when a join is expected to have 1:1 semantics vs 1:n, because it's very easy to end up with duplicated rows otherwise * increase composability of the syntax; why do we need HAVING, why isn't WHERE enough? We have relational algebra, projections, filters, joins, folds, union, intersect etc. But the syntax of SQL is so idosyncratic, and at times antagonistic to the stream and set-like intuitions behind relational algebra.
    - aidos 8y ago
      Regarding your first point, I’m trying to imagine what that would look like. So you mean something you could put on there to say (join x [1:1] on blah)? In general the mistake that’s easiest to make is to get duplicate rows from another join later, which this wouldn’t help with - but maybe I’m misunderstanding.
      - barrkel 8y ago
        I don't really understand your comment about it always being join x+n that causes the problem instead of join x. What is different about later joins that is not true when the later join is the current join, when you get around to writing it? If joins for tables T1::T2 and T2::T3 are 1:1, they don't magically become 1:n when you do T1::T2::T3. To do it right, you'd need to mark up the relations with expected cardinality (effectively unique constraints), so that the DB would be able to verify ahead of time whether a join is going to be 1:1 or 1:n. That would be a better solution than a join working fine up until the cardinality expectation is violated and it suddenly stops working. If we had to stick with SQL syntax, it might be something like: select a.*, b.* from a join one b on b.id = a.b_id (Unique constraint from primary key on b.id. It may still filter down the set of rows in a, but it won't duplicate.) Or: select a.*, b.* from a join many b on b.some_key = a.some_key (No unique constraint on b.some_key) I think the interesting cardinality distinctions are 1:1, 1:n and 1:0. (The above syntax is ambiguous with aliases, so it wouldn't fly as is. But it gives a flavour.)
- dragonwriter 8y ago
  > You can make it a SQL view if you like, and give access to those instead. But it's just a lot of hassle to solve a solved problem in an unfamiliar way. It's kind of odd to use that to dismiss the solution that was established and widely available for 30 years longer, and which more people are probably familiar with.
  - scrollaway 8y ago
    See my edit. I'm not dismissing them, but the range of cases where they're the correct tool over a REST API, GraphQL, or something else... is very narrow.
- marcosdumay 8y ago
  Out of curiosity (didn't had the time to get up to date on it yet), how does GraphQL let you describe your returning data better? Is it because it has hierarchical types (types and collections inside types) or it's something else?
  - scrollaway 8y ago
    With GraphQL, you are creating what is essentially a 1:1 mapping of the structure of the data you want to get back. It's a lot more intuitive than joins and such.
  - eksemplar 8y ago
    A join in GraphQL looks like this: { getOrganisation(args){ name, employees{ Name, Position }} (I added commas to make it easier to read, but they are not supposed to be there) I know SQL, so I don’t think it’s necessarily easier, but what is easier, is non-sql-savvy people making efficient queries because GraphQL does a lot of the heavy lifting for you.
- walshemj 8y ago
  Why is writing SQL so hard for "most devs" I was moved to a Oracle based project (the management system for the uk's core internet) and only had a weeks training on PL/SQL. I got a high performance award after that project was completed and I don't consider myself a Rockstar developer
  - scrollaway 8y ago
    You have a week's worth more experience of training than "most devs". "Most devs" don't directly deal with SQL these days, because ORMs are good enough for day-to-day operations (and the non-day-to-day ends up being handled by people who actually know SQL). In fact, "most devs" rarely interact with databases. A lot of data sourcing is API driven these days. Now, I don't claim to like or dislike that particular state. But you have two choices: You can innovate, which carries risks (of being wrong; of not selling your idea correctly; of reinventing the wheel; of making the same mistakes others did; ...). Or, you can play it safe, and create APIs that most people will know how to use without having to go through a 1-week course, because short as that may sound, that's 167.5 more hours than anyone will reasonably be willing to invest in understanding your API. So at the end of the day: Use the right tool for the job. SQL for APIs is usually not the right tool.
WorldMaker 8y ago
Complexity? 1) Parsing SQL is complex, and full of decades of warts in expectation (dialectal differences between databases, among other things). There are few "drop in" SQL parser libraries that are trustworthy and support much more than a bare subset of the language. 2) Query Planning/Optimization is the "secret sauce" of most SQL databases, and is extremely hard in general. To use SQL as a general API outside of an implementation tied to a specific SQL vendor you'd presumably need a "general" query planner/optimizer that becomes a mini-version of an SQL database in itself. GraphQL's faults aside, it is quite straight-forward to parse (with multiple well known implementations of the parser), and for the most part query planning/optimization in GraphQL follows a simpler, more straight-forward map/reduce-esque "resolver" pattern than the Relational Calculus and general complexity of SQL query planners/optimizers.
- derefr 8y ago
  > There are few "drop in" SQL parser libraries that are trustworthy and support much more than a bare subset of the language. Libraries, no, but you don't need a parser library to parse SQL; SQL [even in its DBMS-specific dialects] is a regular language, and regular languages can be entirely formalized using a declarative grammar. You just need a grammar file in a standard format, and a parser-generator like GNU Bison to throw it through. Conveniently, existing FOSS RDBMSes like Postgres already generate their parsers from grammars, so they have grammar files just laying around for you to reuse (or customize to suit your needs): https://github.com/postgres/postgres/blob/master/src/backend/parser/gram.y https://github.com/postgres/postgres/blob/master/src/backend... > Query Planning/Optimization is the "secret sauce" of most SQL databases, and is extremely hard in general. To use SQL as a general API outside of an implementation tied to a specific SQL vendor you'd presumably need a "general" query planner/optimizer that becomes a mini-version of an SQL database in itself. Definitely the bigger problem of the two. Personally, I would suggest reversing the whole problem formulation. You don't need to take your existing system and make it "speak SQL." Instead, it's much simpler (and, in the end, more performant) to take an existing, robust RDBMS and hollow it out into this "mini-version" that queries through to your service. I've personally done this before: I created a Postgres Foreign Data Wrapper for my data service, then stood up a Postgres instance which has my data-source mounted into it as a set of foreign tables. I then created indices, views, stored procedures, etc. within Postgres, on top of these foreign tables; and then gave people the connection information for a limited user on the Postgres server, calling that "my service's SQL API." In this setup, all the query planning is on the Postgres side; my data source only needs to know enough to answer simple index range queries with row tuples. I don't really have to do much thinking about SQL now that it's set up; I just needed to understand enough, once, to write the foreign data wrapper.

GraphQL has a lot of advantages over SQL, API-wise; namely it's a lot more natural to describe what you want to get back. But without getting into that, the point is that you usually don't want to give direct data access, with all that entails. You still want to be declaring the fields and properties of the data that users will be querying, and that usually sits a layer above your raw data. You can make it a SQL view if you like, and give access to those instead. But it's just a lot of hassle to solve a solved problem in an unfamiliar way. _______ Edit: Clarifying my stance on SQL views. I'm not dismissing it as a "bad tool", but there's good reasons it's not a popular one today. Writing SQL is not easy for everyone, and it's especially not easy for potential API consumers. The best APIs are those you grok. If you grok SQL, congratulations, but most devs don't, and APIs are usually designed for most people. Obviously, if SQL is the right tool for the job, use SQL. But don't start doing stuff like this for ideological reasons.

tathougies 8y ago
I mean SQL views have been a thing for literally decades longer than GraphQL. If any technology should be accused of causing a 'lot of hassle to solve a solved problem', it's graphql, not SQL.
- atombender 8y ago
  If your original data is flat and your app can consume it flat, no worries. But SQL is a worse fit for structured data, so you end up with an ORM on top, which is what GraphQL sort of is. Typically web apps are written with web technologies that work with structured objects, and you want something like {title: "Stapler", price: 1230, category: {id: "123", name: "Office supplies"}, mainPhoto: {url: "https://..." https://...", width: 600, height: 300}} back to feed directly in the UI. Some databases (Postgres comes to mind) are better at letting you express this as a query which builds the structure using JSON functions in the SELECT part of the query. But it's not even close to ideal.
  - just_myles 8y ago
    "Some databases (Postgres comes to mind) are better at letting you express this as a query which builds the structure using JSON functions in the SELECT part of the query. But it's not even close to ideal." Depending on which version of postgres you have(Want to say pre 9.5.) it can be a pain. There are various functions that allow a JSON output and another to make it human readable. With regards to performance I couldn't say but I imagine it would not be as fast as GraphQL.
  - aaaaaaaaaab 8y ago
    You don’t need an ORM, just a big JOIN.
    - yen223 8y ago
      JOINs also return flat data structures, no?
      - aaaaaaaaaab 8y ago
        Just iterate through the results and recover the hierarchical structure. It’s not exactly rocket science: rows = sql("SELECT * FROM parent LEFT JOIN child ON parent.id = child.parent_id") results = [] for row in rows { if row["parent.id"] != results.last.id { results.add({ id: row["parent.id"], children: [], ... }) } if row["child.id"] != null { results.last.children.add({ id: row["child.id"], ... }) } }
        laszlokorte 8y ago
        but now it's no longer a declarative query. What if you want to include grand children? or if you want to fetch just one child but include it's parent and it's siblings of another type like a product, it's category and the categories tags: {id: 23, name: 'Trackpad', category: {id: 42, name:'Equipment', tags: [{id:...}, {id:...}]}} Now instead of just adding two joins you have to build a custom loop with hand crafted conditions. Sure you can find a way to generalize that loop and put it into a library but still the query itself is separated from some kind of post processing that has be kept in sync.
aidos 8y ago
I’m a huge proponent of sql, for me it’s virtually impossible to beat as a query language. We played with wrapping our dB layer with graphql recently and I would say that the experience was pretty good. In part it’s because you’re working a layer up where you can have a more pluggable model. In the first run you can model your local dB structure, but it’s easy to glue more stuff into it from other sources and generally evolve the structure.
- barrkel 8y ago
  I find it very easy to imagine a better SQL. Two things I'd fix: * make it explicit when a join is expected to have 1:1 semantics vs 1:n, because it's very easy to end up with duplicated rows otherwise * increase composability of the syntax; why do we need HAVING, why isn't WHERE enough? We have relational algebra, projections, filters, joins, folds, union, intersect etc. But the syntax of SQL is so idosyncratic, and at times antagonistic to the stream and set-like intuitions behind relational algebra.
  - aidos 8y ago
    Regarding your first point, I’m trying to imagine what that would look like. So you mean something you could put on there to say (join x [1:1] on blah)? In general the mistake that’s easiest to make is to get duplicate rows from another join later, which this wouldn’t help with - but maybe I’m misunderstanding.
    - barrkel 8y ago
      I don't really understand your comment about it always being join x+n that causes the problem instead of join x. What is different about later joins that is not true when the later join is the current join, when you get around to writing it? If joins for tables T1::T2 and T2::T3 are 1:1, they don't magically become 1:n when you do T1::T2::T3. To do it right, you'd need to mark up the relations with expected cardinality (effectively unique constraints), so that the DB would be able to verify ahead of time whether a join is going to be 1:1 or 1:n. That would be a better solution than a join working fine up until the cardinality expectation is violated and it suddenly stops working. If we had to stick with SQL syntax, it might be something like: select a.*, b.* from a join one b on b.id = a.b_id (Unique constraint from primary key on b.id. It may still filter down the set of rows in a, but it won't duplicate.) Or: select a.*, b.* from a join many b on b.some_key = a.some_key (No unique constraint on b.some_key) I think the interesting cardinality distinctions are 1:1, 1:n and 1:0. (The above syntax is ambiguous with aliases, so it wouldn't fly as is. But it gives a flavour.)
dragonwriter 8y ago
> You can make it a SQL view if you like, and give access to those instead. But it's just a lot of hassle to solve a solved problem in an unfamiliar way. It's kind of odd to use that to dismiss the solution that was established and widely available for 30 years longer, and which more people are probably familiar with.
- scrollaway 8y ago
  See my edit. I'm not dismissing them, but the range of cases where they're the correct tool over a REST API, GraphQL, or something else... is very narrow.
marcosdumay 8y ago
Out of curiosity (didn't had the time to get up to date on it yet), how does GraphQL let you describe your returning data better? Is it because it has hierarchical types (types and collections inside types) or it's something else?
- scrollaway 8y ago
  With GraphQL, you are creating what is essentially a 1:1 mapping of the structure of the data you want to get back. It's a lot more intuitive than joins and such.
- eksemplar 8y ago
  A join in GraphQL looks like this: { getOrganisation(args){ name, employees{ Name, Position }} (I added commas to make it easier to read, but they are not supposed to be there) I know SQL, so I don’t think it’s necessarily easier, but what is easier, is non-sql-savvy people making efficient queries because GraphQL does a lot of the heavy lifting for you.
walshemj 8y ago
Why is writing SQL so hard for "most devs" I was moved to a Oracle based project (the management system for the uk's core internet) and only had a weeks training on PL/SQL. I got a high performance award after that project was completed and I don't consider myself a Rockstar developer
- scrollaway 8y ago
  You have a week's worth more experience of training than "most devs". "Most devs" don't directly deal with SQL these days, because ORMs are good enough for day-to-day operations (and the non-day-to-day ends up being handled by people who actually know SQL). In fact, "most devs" rarely interact with databases. A lot of data sourcing is API driven these days. Now, I don't claim to like or dislike that particular state. But you have two choices: You can innovate, which carries risks (of being wrong; of not selling your idea correctly; of reinventing the wheel; of making the same mistakes others did; ...). Or, you can play it safe, and create APIs that most people will know how to use without having to go through a 1-week course, because short as that may sound, that's 167.5 more hours than anyone will reasonably be willing to invest in understanding your API. So at the end of the day: Use the right tool for the job. SQL for APIs is usually not the right tool.

WorldMaker 8y ago

Complexity? 1) Parsing SQL is complex, and full of decades of warts in expectation (dialectal differences between databases, among other things). There are few "drop in" SQL parser libraries that are trustworthy and support much more than a bare subset of the language. 2) Query Planning/Optimization is the "secret sauce" of most SQL databases, and is extremely hard in general. To use SQL as a general API outside of an implementation tied to a specific SQL vendor you'd presumably need a "general" query planner/optimizer that becomes a mini-version of an SQL database in itself. GraphQL's faults aside, it is quite straight-forward to parse (with multiple well known implementations of the parser), and for the most part query planning/optimization in GraphQL follows a simpler, more straight-forward map/reduce-esque "resolver" pattern than the Relational Calculus and general complexity of SQL query planners/optimizers.

derefr 8y ago
> There are few "drop in" SQL parser libraries that are trustworthy and support much more than a bare subset of the language. Libraries, no, but you don't need a parser library to parse SQL; SQL [even in its DBMS-specific dialects] is a regular language, and regular languages can be entirely formalized using a declarative grammar. You just need a grammar file in a standard format, and a parser-generator like GNU Bison to throw it through. Conveniently, existing FOSS RDBMSes like Postgres already generate their parsers from grammars, so they have grammar files just laying around for you to reuse (or customize to suit your needs): https://github.com/postgres/postgres/blob/master/src/backend/parser/gram.y https://github.com/postgres/postgres/blob/master/src/backend... > Query Planning/Optimization is the "secret sauce" of most SQL databases, and is extremely hard in general. To use SQL as a general API outside of an implementation tied to a specific SQL vendor you'd presumably need a "general" query planner/optimizer that becomes a mini-version of an SQL database in itself. Definitely the bigger problem of the two. Personally, I would suggest reversing the whole problem formulation. You don't need to take your existing system and make it "speak SQL." Instead, it's much simpler (and, in the end, more performant) to take an existing, robust RDBMS and hollow it out into this "mini-version" that queries through to your service. I've personally done this before: I created a Postgres Foreign Data Wrapper for my data service, then stood up a Postgres instance which has my data-source mounted into it as a set of foreign tables. I then created indices, views, stored procedures, etc. within Postgres, on top of these foreign tables; and then gave people the connection information for a limited user on the Postgres server, calling that "my service's SQL API." In this setup, all the query planning is on the Postgres side; my data source only needs to know enough to answer simple index range queries with row tuples. I don't really have to do much thinking about SQL now that it's set up; I just needed to understand enough, once, to write the foreign data wrapper.