Why not use SQL instead of GraphQL?





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







13















Recently I learned about GraphQL which claims to be superior to RESTful. However, I started wondering why don't we simply put SQL statements into an HTTP GET request.



For example, in GraphQL I would write



{
Movie(id: "cixos5gtq0ogi0126tvekxo27") {
id
title
actors {
name
}
}
}


Which isn't much simpler than its SQL counterpart



SELECT id, title FROM movies WHERE id = cixos5gtq0ogi0126tvekxo27;
SELECT actors.name FROM actors, actors_movies WHERE actors.id == movies.actor_id AND movie.id == cixos5gtq0ogi0126tvekxo27;


Maybe we can URL-encode the query and send to the server



GET endpoint?q=SELECT%20id%2C%20title%20FROM%20movies%20WHERE%20id%20%3D%20cixos5gtq0ogi0126tvekxo27%3B%0ASELECT%20actors.name%20FROM%20actors%2C%20actors_movies%20WHERE%20actors.id%20%3D%3D%20movies.actor_id%20AND%20movie.id%20%3D%3D%20cixos5gtq0ogi0126tvekxo27%3B HTTP/1.1


Yes, the query URL can be too long, but you can put it into the body of a POST request if you don't care about REST compliance. (By the way, I think the HTTP RFC need be revised for REST to make sense: capping the length of query strings mixes implementation with specification at the very beginning)



Directly issuing SQL from the client also has the advantage of




  1. No server-side code/library is required to parse GraphQL, reducing development time.

  2. No server-side overhead is needed to parse GraphQL, reducing runtime.

  3. SQL statements are much more flexible than GraphQL because (in most cases) the latter will reduce to SQL anyway.

  4. Everyone knows SQL.


So, what the advantages GraphQL have over SQL?










share|improve this question









New contributor




nalzok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 34





    Little Bobby Tables.

    – Philip Kendall
    Apr 6 at 7:05






  • 1





    1. I can still DoS you with arbitrarily complicated SQL queries. 2. There's no chance a malicious actor would ever obtain a valid key...

    – Philip Kendall
    Apr 6 at 7:16








  • 1





    @PhilipKendall You are right, but using GraphQL (or REST or whatever) doesn't solve these problems either, right?

    – nalzok
    Apr 6 at 7:18








  • 6





    @nalzok: SQL is Turing-complete, which means it is impossible to validate statically.

    – Jörg W Mittag
    Apr 6 at 7:45






  • 3





    This is very simple to understand why it's a terrible idea. Implement it yourself. At some point, you will realise that your are investing the time mostly in 1 thing: security. Not too later you will feel somewhat upset because you are implementing a caped TOAD. Then you will realise how hard is mapping rows all over the system and you will try to reinvent the ORM wheel on both sides: client and server. By the time you give up, your PM will ask you for report: how is the users' service going? Is it done?"...

    – Laiv
    Apr 6 at 12:24




















13















Recently I learned about GraphQL which claims to be superior to RESTful. However, I started wondering why don't we simply put SQL statements into an HTTP GET request.



For example, in GraphQL I would write



{
Movie(id: "cixos5gtq0ogi0126tvekxo27") {
id
title
actors {
name
}
}
}


Which isn't much simpler than its SQL counterpart



SELECT id, title FROM movies WHERE id = cixos5gtq0ogi0126tvekxo27;
SELECT actors.name FROM actors, actors_movies WHERE actors.id == movies.actor_id AND movie.id == cixos5gtq0ogi0126tvekxo27;


Maybe we can URL-encode the query and send to the server



GET endpoint?q=SELECT%20id%2C%20title%20FROM%20movies%20WHERE%20id%20%3D%20cixos5gtq0ogi0126tvekxo27%3B%0ASELECT%20actors.name%20FROM%20actors%2C%20actors_movies%20WHERE%20actors.id%20%3D%3D%20movies.actor_id%20AND%20movie.id%20%3D%3D%20cixos5gtq0ogi0126tvekxo27%3B HTTP/1.1


Yes, the query URL can be too long, but you can put it into the body of a POST request if you don't care about REST compliance. (By the way, I think the HTTP RFC need be revised for REST to make sense: capping the length of query strings mixes implementation with specification at the very beginning)



Directly issuing SQL from the client also has the advantage of




  1. No server-side code/library is required to parse GraphQL, reducing development time.

  2. No server-side overhead is needed to parse GraphQL, reducing runtime.

  3. SQL statements are much more flexible than GraphQL because (in most cases) the latter will reduce to SQL anyway.

  4. Everyone knows SQL.


So, what the advantages GraphQL have over SQL?










share|improve this question









New contributor




nalzok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 34





    Little Bobby Tables.

    – Philip Kendall
    Apr 6 at 7:05






  • 1





    1. I can still DoS you with arbitrarily complicated SQL queries. 2. There's no chance a malicious actor would ever obtain a valid key...

    – Philip Kendall
    Apr 6 at 7:16








  • 1





    @PhilipKendall You are right, but using GraphQL (or REST or whatever) doesn't solve these problems either, right?

    – nalzok
    Apr 6 at 7:18








  • 6





    @nalzok: SQL is Turing-complete, which means it is impossible to validate statically.

    – Jörg W Mittag
    Apr 6 at 7:45






  • 3





    This is very simple to understand why it's a terrible idea. Implement it yourself. At some point, you will realise that your are investing the time mostly in 1 thing: security. Not too later you will feel somewhat upset because you are implementing a caped TOAD. Then you will realise how hard is mapping rows all over the system and you will try to reinvent the ORM wheel on both sides: client and server. By the time you give up, your PM will ask you for report: how is the users' service going? Is it done?"...

    – Laiv
    Apr 6 at 12:24
















13












13








13


6






Recently I learned about GraphQL which claims to be superior to RESTful. However, I started wondering why don't we simply put SQL statements into an HTTP GET request.



For example, in GraphQL I would write



{
Movie(id: "cixos5gtq0ogi0126tvekxo27") {
id
title
actors {
name
}
}
}


Which isn't much simpler than its SQL counterpart



SELECT id, title FROM movies WHERE id = cixos5gtq0ogi0126tvekxo27;
SELECT actors.name FROM actors, actors_movies WHERE actors.id == movies.actor_id AND movie.id == cixos5gtq0ogi0126tvekxo27;


Maybe we can URL-encode the query and send to the server



GET endpoint?q=SELECT%20id%2C%20title%20FROM%20movies%20WHERE%20id%20%3D%20cixos5gtq0ogi0126tvekxo27%3B%0ASELECT%20actors.name%20FROM%20actors%2C%20actors_movies%20WHERE%20actors.id%20%3D%3D%20movies.actor_id%20AND%20movie.id%20%3D%3D%20cixos5gtq0ogi0126tvekxo27%3B HTTP/1.1


Yes, the query URL can be too long, but you can put it into the body of a POST request if you don't care about REST compliance. (By the way, I think the HTTP RFC need be revised for REST to make sense: capping the length of query strings mixes implementation with specification at the very beginning)



Directly issuing SQL from the client also has the advantage of




  1. No server-side code/library is required to parse GraphQL, reducing development time.

  2. No server-side overhead is needed to parse GraphQL, reducing runtime.

  3. SQL statements are much more flexible than GraphQL because (in most cases) the latter will reduce to SQL anyway.

  4. Everyone knows SQL.


So, what the advantages GraphQL have over SQL?










share|improve this question









New contributor




nalzok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.












Recently I learned about GraphQL which claims to be superior to RESTful. However, I started wondering why don't we simply put SQL statements into an HTTP GET request.



For example, in GraphQL I would write



{
Movie(id: "cixos5gtq0ogi0126tvekxo27") {
id
title
actors {
name
}
}
}


Which isn't much simpler than its SQL counterpart



SELECT id, title FROM movies WHERE id = cixos5gtq0ogi0126tvekxo27;
SELECT actors.name FROM actors, actors_movies WHERE actors.id == movies.actor_id AND movie.id == cixos5gtq0ogi0126tvekxo27;


Maybe we can URL-encode the query and send to the server



GET endpoint?q=SELECT%20id%2C%20title%20FROM%20movies%20WHERE%20id%20%3D%20cixos5gtq0ogi0126tvekxo27%3B%0ASELECT%20actors.name%20FROM%20actors%2C%20actors_movies%20WHERE%20actors.id%20%3D%3D%20movies.actor_id%20AND%20movie.id%20%3D%3D%20cixos5gtq0ogi0126tvekxo27%3B HTTP/1.1


Yes, the query URL can be too long, but you can put it into the body of a POST request if you don't care about REST compliance. (By the way, I think the HTTP RFC need be revised for REST to make sense: capping the length of query strings mixes implementation with specification at the very beginning)



Directly issuing SQL from the client also has the advantage of




  1. No server-side code/library is required to parse GraphQL, reducing development time.

  2. No server-side overhead is needed to parse GraphQL, reducing runtime.

  3. SQL statements are much more flexible than GraphQL because (in most cases) the latter will reduce to SQL anyway.

  4. Everyone knows SQL.


So, what the advantages GraphQL have over SQL?







architecture database web-development api web-services






share|improve this question









New contributor




nalzok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




nalzok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 2 days ago









whatsisname

25.1k136788




25.1k136788






New contributor




nalzok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked Apr 6 at 7:04









nalzoknalzok

1789




1789




New contributor




nalzok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





nalzok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






nalzok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.








  • 34





    Little Bobby Tables.

    – Philip Kendall
    Apr 6 at 7:05






  • 1





    1. I can still DoS you with arbitrarily complicated SQL queries. 2. There's no chance a malicious actor would ever obtain a valid key...

    – Philip Kendall
    Apr 6 at 7:16








  • 1





    @PhilipKendall You are right, but using GraphQL (or REST or whatever) doesn't solve these problems either, right?

    – nalzok
    Apr 6 at 7:18








  • 6





    @nalzok: SQL is Turing-complete, which means it is impossible to validate statically.

    – Jörg W Mittag
    Apr 6 at 7:45






  • 3





    This is very simple to understand why it's a terrible idea. Implement it yourself. At some point, you will realise that your are investing the time mostly in 1 thing: security. Not too later you will feel somewhat upset because you are implementing a caped TOAD. Then you will realise how hard is mapping rows all over the system and you will try to reinvent the ORM wheel on both sides: client and server. By the time you give up, your PM will ask you for report: how is the users' service going? Is it done?"...

    – Laiv
    Apr 6 at 12:24
















  • 34





    Little Bobby Tables.

    – Philip Kendall
    Apr 6 at 7:05






  • 1





    1. I can still DoS you with arbitrarily complicated SQL queries. 2. There's no chance a malicious actor would ever obtain a valid key...

    – Philip Kendall
    Apr 6 at 7:16








  • 1





    @PhilipKendall You are right, but using GraphQL (or REST or whatever) doesn't solve these problems either, right?

    – nalzok
    Apr 6 at 7:18








  • 6





    @nalzok: SQL is Turing-complete, which means it is impossible to validate statically.

    – Jörg W Mittag
    Apr 6 at 7:45






  • 3





    This is very simple to understand why it's a terrible idea. Implement it yourself. At some point, you will realise that your are investing the time mostly in 1 thing: security. Not too later you will feel somewhat upset because you are implementing a caped TOAD. Then you will realise how hard is mapping rows all over the system and you will try to reinvent the ORM wheel on both sides: client and server. By the time you give up, your PM will ask you for report: how is the users' service going? Is it done?"...

    – Laiv
    Apr 6 at 12:24










34




34





Little Bobby Tables.

– Philip Kendall
Apr 6 at 7:05





Little Bobby Tables.

– Philip Kendall
Apr 6 at 7:05




1




1





1. I can still DoS you with arbitrarily complicated SQL queries. 2. There's no chance a malicious actor would ever obtain a valid key...

– Philip Kendall
Apr 6 at 7:16







1. I can still DoS you with arbitrarily complicated SQL queries. 2. There's no chance a malicious actor would ever obtain a valid key...

– Philip Kendall
Apr 6 at 7:16






1




1





@PhilipKendall You are right, but using GraphQL (or REST or whatever) doesn't solve these problems either, right?

– nalzok
Apr 6 at 7:18







@PhilipKendall You are right, but using GraphQL (or REST or whatever) doesn't solve these problems either, right?

– nalzok
Apr 6 at 7:18






6




6





@nalzok: SQL is Turing-complete, which means it is impossible to validate statically.

– Jörg W Mittag
Apr 6 at 7:45





@nalzok: SQL is Turing-complete, which means it is impossible to validate statically.

– Jörg W Mittag
Apr 6 at 7:45




3




3





This is very simple to understand why it's a terrible idea. Implement it yourself. At some point, you will realise that your are investing the time mostly in 1 thing: security. Not too later you will feel somewhat upset because you are implementing a caped TOAD. Then you will realise how hard is mapping rows all over the system and you will try to reinvent the ORM wheel on both sides: client and server. By the time you give up, your PM will ask you for report: how is the users' service going? Is it done?"...

– Laiv
Apr 6 at 12:24







This is very simple to understand why it's a terrible idea. Implement it yourself. At some point, you will realise that your are investing the time mostly in 1 thing: security. Not too later you will feel somewhat upset because you are implementing a caped TOAD. Then you will realise how hard is mapping rows all over the system and you will try to reinvent the ORM wheel on both sides: client and server. By the time you give up, your PM will ask you for report: how is the users' service going? Is it done?"...

– Laiv
Apr 6 at 12:24












4 Answers
4






active

oldest

votes


















25














Basically, abstraction.



SQL requires your clients to know your exact database structure, which is not good. On top of that, analysing the SQL in order to perform special operations based on the value sent as the input is a really difficult thing to do. There are entire softwares which are pretty much responsible only for that. Do you know what those are? If you have guessed the databases, you are right.



Thanks to not exposing the SQL directly, you are not limiting the consumer of the API to the internal representation of your database. You easily expose only what you want to expose.



And since clients of the API depend only on the abstraction, you are free to have as many layers as possible between the API input and the actual database (security, caching, loading data from multiple databases on a single request,...).



For public services, exposing a database directly is pretty much never the right approach. If you however have a few internal systems, sure, your approach might make sense but even then it might just be easier to connect to application A's database directly from Application B by giving the database credentials to the Application B, rather than trying to come up with a custom HTTP interface for the database SQL language.






Why can't I just compare the URL (or SQL query) against keys in Redis
before performing the actual query on the RDBMS?




Because it's not easy. Even if someone uses a very simple query, such as:



SELECT st.id, jt.name
FROM some_table st
INNER JOIN join_table jt ON jt.some_table_id = st.id
WHERE st.name = 'hello
world' AND st.type = 'STANDARD'


how do you make sure the result is properly cached? This query includes newlines, but someone could just as well write the query in the following way:



SELECT st.id, jt.name FROM some_table st INNER JOIN join_table jt ON jt.some_table_id = st.id WHERE st.name = 'hello
world' AND st.type = 'STANDARD'


and it's still supposed to be cached in the same way as the one above. I have specifically included a where in which a string search contains a new line, so simply finding line endings and replacing them with a space is not going to work here, parsing the request correctly would be much more complicated.



And even if you do fix that, another query could switch the order of conditions and the query would look like this:



SELECT st.id, jt.name
FROM some_table st
INNER JOIN join_table jt ON jt.some_table_id = st.id
WHERE st.type = 'STANDARD' AND st.name = 'hello
world'


and another request could contain a redundant WHERE argument, like this:



SELECT st.id, jt.name
FROM some_table st
INNER JOIN join_table jt ON jt.some_table_id = st.id
WHERE st.type = 'STANDARD' AND st.name = 'hello
world' AND st.stype = 'STANDARD'


All of those queries are still supposed to return the same result, should be cached in the same way. But handling all of the possible options is pretty much impossible. That's why you cannot simply compare the URL against keys in Redis.






share|improve this answer


























  • This is a nice answer, but please see the update.

    – nalzok
    2 days ago



















12














In theory there is no reason you can't expose an SQL interface like this.



In practice SQL is far too powerful to be effectively limited to the security scope you want to expose.



Even if you allow only read access, a bad query can still hog resources.



Other languages such as graphQL are designed to be exposed. They are merely giving users a filter option on what they could already see.



The benefit of using these languages is that they have gone through all the things you would want to stop users doing in SQL and taken them off the table.






share|improve this answer





















  • 1





    Thanks for the answer, but could you explain how GraphQL solve the problem of resource draining? A rogue GraphQL query can still say “tell me everything about each movie and their actors“, resulting in a huge graph, and exhausting my DBMS and network.

    – nalzok
    2 days ago











  • But i can write a recursive SQL query that will lock your table and prevent other users from running any queries at all

    – Ewan
    2 days ago






  • 4





    the problem is not so much restricting access to tables, or deleting, but the shear complexity of SQL. will you allow temp table creation? what about executing CLI? loops? transactions? sub selects? cursors? how will you distinguish when the use of these things is acceptable and when its 'bad'

    – Ewan
    2 days ago



















1














As others have mentioned, exposing SQL directly in the api is a very bad option. GraphQL, despite it's name, is not an abstraction for SQL, but for any data store or even other services.



If you are looking for an abstraction that is closer to SQL, you might want to have a look at odata (if you happen to work in .NET backends, though maybe other implementations exist).






share|improve this answer































    0














    if you want expose SQL like GraphQL, you will could need something like GraphQL, because you will need hide the important information and select what you want to show in the API, this for security.



    GraphQl and SQL are different things, SQL is the language to query DataBase and GraphQL is only to manage the data from API,in API you will need make yours schemas to show and querys to manage it, etc.



    in any API you will need make those things for simply security, but if you want something that is free data access maybe it would work, you know so many alternatives in the software world






    share|improve this answer






















      protected by gnat 2 days ago



      Thank you for your interest in this question.
      Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



      Would you like to answer one of these unanswered questions instead?














      4 Answers
      4






      active

      oldest

      votes








      4 Answers
      4






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      25














      Basically, abstraction.



      SQL requires your clients to know your exact database structure, which is not good. On top of that, analysing the SQL in order to perform special operations based on the value sent as the input is a really difficult thing to do. There are entire softwares which are pretty much responsible only for that. Do you know what those are? If you have guessed the databases, you are right.



      Thanks to not exposing the SQL directly, you are not limiting the consumer of the API to the internal representation of your database. You easily expose only what you want to expose.



      And since clients of the API depend only on the abstraction, you are free to have as many layers as possible between the API input and the actual database (security, caching, loading data from multiple databases on a single request,...).



      For public services, exposing a database directly is pretty much never the right approach. If you however have a few internal systems, sure, your approach might make sense but even then it might just be easier to connect to application A's database directly from Application B by giving the database credentials to the Application B, rather than trying to come up with a custom HTTP interface for the database SQL language.






      Why can't I just compare the URL (or SQL query) against keys in Redis
      before performing the actual query on the RDBMS?




      Because it's not easy. Even if someone uses a very simple query, such as:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.name = 'hello
      world' AND st.type = 'STANDARD'


      how do you make sure the result is properly cached? This query includes newlines, but someone could just as well write the query in the following way:



      SELECT st.id, jt.name FROM some_table st INNER JOIN join_table jt ON jt.some_table_id = st.id WHERE st.name = 'hello
      world' AND st.type = 'STANDARD'


      and it's still supposed to be cached in the same way as the one above. I have specifically included a where in which a string search contains a new line, so simply finding line endings and replacing them with a space is not going to work here, parsing the request correctly would be much more complicated.



      And even if you do fix that, another query could switch the order of conditions and the query would look like this:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.type = 'STANDARD' AND st.name = 'hello
      world'


      and another request could contain a redundant WHERE argument, like this:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.type = 'STANDARD' AND st.name = 'hello
      world' AND st.stype = 'STANDARD'


      All of those queries are still supposed to return the same result, should be cached in the same way. But handling all of the possible options is pretty much impossible. That's why you cannot simply compare the URL against keys in Redis.






      share|improve this answer


























      • This is a nice answer, but please see the update.

        – nalzok
        2 days ago
















      25














      Basically, abstraction.



      SQL requires your clients to know your exact database structure, which is not good. On top of that, analysing the SQL in order to perform special operations based on the value sent as the input is a really difficult thing to do. There are entire softwares which are pretty much responsible only for that. Do you know what those are? If you have guessed the databases, you are right.



      Thanks to not exposing the SQL directly, you are not limiting the consumer of the API to the internal representation of your database. You easily expose only what you want to expose.



      And since clients of the API depend only on the abstraction, you are free to have as many layers as possible between the API input and the actual database (security, caching, loading data from multiple databases on a single request,...).



      For public services, exposing a database directly is pretty much never the right approach. If you however have a few internal systems, sure, your approach might make sense but even then it might just be easier to connect to application A's database directly from Application B by giving the database credentials to the Application B, rather than trying to come up with a custom HTTP interface for the database SQL language.






      Why can't I just compare the URL (or SQL query) against keys in Redis
      before performing the actual query on the RDBMS?




      Because it's not easy. Even if someone uses a very simple query, such as:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.name = 'hello
      world' AND st.type = 'STANDARD'


      how do you make sure the result is properly cached? This query includes newlines, but someone could just as well write the query in the following way:



      SELECT st.id, jt.name FROM some_table st INNER JOIN join_table jt ON jt.some_table_id = st.id WHERE st.name = 'hello
      world' AND st.type = 'STANDARD'


      and it's still supposed to be cached in the same way as the one above. I have specifically included a where in which a string search contains a new line, so simply finding line endings and replacing them with a space is not going to work here, parsing the request correctly would be much more complicated.



      And even if you do fix that, another query could switch the order of conditions and the query would look like this:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.type = 'STANDARD' AND st.name = 'hello
      world'


      and another request could contain a redundant WHERE argument, like this:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.type = 'STANDARD' AND st.name = 'hello
      world' AND st.stype = 'STANDARD'


      All of those queries are still supposed to return the same result, should be cached in the same way. But handling all of the possible options is pretty much impossible. That's why you cannot simply compare the URL against keys in Redis.






      share|improve this answer


























      • This is a nice answer, but please see the update.

        – nalzok
        2 days ago














      25












      25








      25







      Basically, abstraction.



      SQL requires your clients to know your exact database structure, which is not good. On top of that, analysing the SQL in order to perform special operations based on the value sent as the input is a really difficult thing to do. There are entire softwares which are pretty much responsible only for that. Do you know what those are? If you have guessed the databases, you are right.



      Thanks to not exposing the SQL directly, you are not limiting the consumer of the API to the internal representation of your database. You easily expose only what you want to expose.



      And since clients of the API depend only on the abstraction, you are free to have as many layers as possible between the API input and the actual database (security, caching, loading data from multiple databases on a single request,...).



      For public services, exposing a database directly is pretty much never the right approach. If you however have a few internal systems, sure, your approach might make sense but even then it might just be easier to connect to application A's database directly from Application B by giving the database credentials to the Application B, rather than trying to come up with a custom HTTP interface for the database SQL language.






      Why can't I just compare the URL (or SQL query) against keys in Redis
      before performing the actual query on the RDBMS?




      Because it's not easy. Even if someone uses a very simple query, such as:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.name = 'hello
      world' AND st.type = 'STANDARD'


      how do you make sure the result is properly cached? This query includes newlines, but someone could just as well write the query in the following way:



      SELECT st.id, jt.name FROM some_table st INNER JOIN join_table jt ON jt.some_table_id = st.id WHERE st.name = 'hello
      world' AND st.type = 'STANDARD'


      and it's still supposed to be cached in the same way as the one above. I have specifically included a where in which a string search contains a new line, so simply finding line endings and replacing them with a space is not going to work here, parsing the request correctly would be much more complicated.



      And even if you do fix that, another query could switch the order of conditions and the query would look like this:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.type = 'STANDARD' AND st.name = 'hello
      world'


      and another request could contain a redundant WHERE argument, like this:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.type = 'STANDARD' AND st.name = 'hello
      world' AND st.stype = 'STANDARD'


      All of those queries are still supposed to return the same result, should be cached in the same way. But handling all of the possible options is pretty much impossible. That's why you cannot simply compare the URL against keys in Redis.






      share|improve this answer















      Basically, abstraction.



      SQL requires your clients to know your exact database structure, which is not good. On top of that, analysing the SQL in order to perform special operations based on the value sent as the input is a really difficult thing to do. There are entire softwares which are pretty much responsible only for that. Do you know what those are? If you have guessed the databases, you are right.



      Thanks to not exposing the SQL directly, you are not limiting the consumer of the API to the internal representation of your database. You easily expose only what you want to expose.



      And since clients of the API depend only on the abstraction, you are free to have as many layers as possible between the API input and the actual database (security, caching, loading data from multiple databases on a single request,...).



      For public services, exposing a database directly is pretty much never the right approach. If you however have a few internal systems, sure, your approach might make sense but even then it might just be easier to connect to application A's database directly from Application B by giving the database credentials to the Application B, rather than trying to come up with a custom HTTP interface for the database SQL language.






      Why can't I just compare the URL (or SQL query) against keys in Redis
      before performing the actual query on the RDBMS?




      Because it's not easy. Even if someone uses a very simple query, such as:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.name = 'hello
      world' AND st.type = 'STANDARD'


      how do you make sure the result is properly cached? This query includes newlines, but someone could just as well write the query in the following way:



      SELECT st.id, jt.name FROM some_table st INNER JOIN join_table jt ON jt.some_table_id = st.id WHERE st.name = 'hello
      world' AND st.type = 'STANDARD'


      and it's still supposed to be cached in the same way as the one above. I have specifically included a where in which a string search contains a new line, so simply finding line endings and replacing them with a space is not going to work here, parsing the request correctly would be much more complicated.



      And even if you do fix that, another query could switch the order of conditions and the query would look like this:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.type = 'STANDARD' AND st.name = 'hello
      world'


      and another request could contain a redundant WHERE argument, like this:



      SELECT st.id, jt.name
      FROM some_table st
      INNER JOIN join_table jt ON jt.some_table_id = st.id
      WHERE st.type = 'STANDARD' AND st.name = 'hello
      world' AND st.stype = 'STANDARD'


      All of those queries are still supposed to return the same result, should be cached in the same way. But handling all of the possible options is pretty much impossible. That's why you cannot simply compare the URL against keys in Redis.







      share|improve this answer














      share|improve this answer



      share|improve this answer








      edited Apr 6 at 19:16

























      answered Apr 6 at 12:06









      AndyAndy

      7,69711638




      7,69711638













      • This is a nice answer, but please see the update.

        – nalzok
        2 days ago



















      • This is a nice answer, but please see the update.

        – nalzok
        2 days ago

















      This is a nice answer, but please see the update.

      – nalzok
      2 days ago





      This is a nice answer, but please see the update.

      – nalzok
      2 days ago













      12














      In theory there is no reason you can't expose an SQL interface like this.



      In practice SQL is far too powerful to be effectively limited to the security scope you want to expose.



      Even if you allow only read access, a bad query can still hog resources.



      Other languages such as graphQL are designed to be exposed. They are merely giving users a filter option on what they could already see.



      The benefit of using these languages is that they have gone through all the things you would want to stop users doing in SQL and taken them off the table.






      share|improve this answer





















      • 1





        Thanks for the answer, but could you explain how GraphQL solve the problem of resource draining? A rogue GraphQL query can still say “tell me everything about each movie and their actors“, resulting in a huge graph, and exhausting my DBMS and network.

        – nalzok
        2 days ago











      • But i can write a recursive SQL query that will lock your table and prevent other users from running any queries at all

        – Ewan
        2 days ago






      • 4





        the problem is not so much restricting access to tables, or deleting, but the shear complexity of SQL. will you allow temp table creation? what about executing CLI? loops? transactions? sub selects? cursors? how will you distinguish when the use of these things is acceptable and when its 'bad'

        – Ewan
        2 days ago
















      12














      In theory there is no reason you can't expose an SQL interface like this.



      In practice SQL is far too powerful to be effectively limited to the security scope you want to expose.



      Even if you allow only read access, a bad query can still hog resources.



      Other languages such as graphQL are designed to be exposed. They are merely giving users a filter option on what they could already see.



      The benefit of using these languages is that they have gone through all the things you would want to stop users doing in SQL and taken them off the table.






      share|improve this answer





















      • 1





        Thanks for the answer, but could you explain how GraphQL solve the problem of resource draining? A rogue GraphQL query can still say “tell me everything about each movie and their actors“, resulting in a huge graph, and exhausting my DBMS and network.

        – nalzok
        2 days ago











      • But i can write a recursive SQL query that will lock your table and prevent other users from running any queries at all

        – Ewan
        2 days ago






      • 4





        the problem is not so much restricting access to tables, or deleting, but the shear complexity of SQL. will you allow temp table creation? what about executing CLI? loops? transactions? sub selects? cursors? how will you distinguish when the use of these things is acceptable and when its 'bad'

        – Ewan
        2 days ago














      12












      12








      12







      In theory there is no reason you can't expose an SQL interface like this.



      In practice SQL is far too powerful to be effectively limited to the security scope you want to expose.



      Even if you allow only read access, a bad query can still hog resources.



      Other languages such as graphQL are designed to be exposed. They are merely giving users a filter option on what they could already see.



      The benefit of using these languages is that they have gone through all the things you would want to stop users doing in SQL and taken them off the table.






      share|improve this answer















      In theory there is no reason you can't expose an SQL interface like this.



      In practice SQL is far too powerful to be effectively limited to the security scope you want to expose.



      Even if you allow only read access, a bad query can still hog resources.



      Other languages such as graphQL are designed to be exposed. They are merely giving users a filter option on what they could already see.



      The benefit of using these languages is that they have gone through all the things you would want to stop users doing in SQL and taken them off the table.







      share|improve this answer














      share|improve this answer



      share|improve this answer








      edited Apr 6 at 17:34

























      answered Apr 6 at 13:54









      EwanEwan

      43.6k33698




      43.6k33698








      • 1





        Thanks for the answer, but could you explain how GraphQL solve the problem of resource draining? A rogue GraphQL query can still say “tell me everything about each movie and their actors“, resulting in a huge graph, and exhausting my DBMS and network.

        – nalzok
        2 days ago











      • But i can write a recursive SQL query that will lock your table and prevent other users from running any queries at all

        – Ewan
        2 days ago






      • 4





        the problem is not so much restricting access to tables, or deleting, but the shear complexity of SQL. will you allow temp table creation? what about executing CLI? loops? transactions? sub selects? cursors? how will you distinguish when the use of these things is acceptable and when its 'bad'

        – Ewan
        2 days ago














      • 1





        Thanks for the answer, but could you explain how GraphQL solve the problem of resource draining? A rogue GraphQL query can still say “tell me everything about each movie and their actors“, resulting in a huge graph, and exhausting my DBMS and network.

        – nalzok
        2 days ago











      • But i can write a recursive SQL query that will lock your table and prevent other users from running any queries at all

        – Ewan
        2 days ago






      • 4





        the problem is not so much restricting access to tables, or deleting, but the shear complexity of SQL. will you allow temp table creation? what about executing CLI? loops? transactions? sub selects? cursors? how will you distinguish when the use of these things is acceptable and when its 'bad'

        – Ewan
        2 days ago








      1




      1





      Thanks for the answer, but could you explain how GraphQL solve the problem of resource draining? A rogue GraphQL query can still say “tell me everything about each movie and their actors“, resulting in a huge graph, and exhausting my DBMS and network.

      – nalzok
      2 days ago





      Thanks for the answer, but could you explain how GraphQL solve the problem of resource draining? A rogue GraphQL query can still say “tell me everything about each movie and their actors“, resulting in a huge graph, and exhausting my DBMS and network.

      – nalzok
      2 days ago













      But i can write a recursive SQL query that will lock your table and prevent other users from running any queries at all

      – Ewan
      2 days ago





      But i can write a recursive SQL query that will lock your table and prevent other users from running any queries at all

      – Ewan
      2 days ago




      4




      4





      the problem is not so much restricting access to tables, or deleting, but the shear complexity of SQL. will you allow temp table creation? what about executing CLI? loops? transactions? sub selects? cursors? how will you distinguish when the use of these things is acceptable and when its 'bad'

      – Ewan
      2 days ago





      the problem is not so much restricting access to tables, or deleting, but the shear complexity of SQL. will you allow temp table creation? what about executing CLI? loops? transactions? sub selects? cursors? how will you distinguish when the use of these things is acceptable and when its 'bad'

      – Ewan
      2 days ago











      1














      As others have mentioned, exposing SQL directly in the api is a very bad option. GraphQL, despite it's name, is not an abstraction for SQL, but for any data store or even other services.



      If you are looking for an abstraction that is closer to SQL, you might want to have a look at odata (if you happen to work in .NET backends, though maybe other implementations exist).






      share|improve this answer




























        1














        As others have mentioned, exposing SQL directly in the api is a very bad option. GraphQL, despite it's name, is not an abstraction for SQL, but for any data store or even other services.



        If you are looking for an abstraction that is closer to SQL, you might want to have a look at odata (if you happen to work in .NET backends, though maybe other implementations exist).






        share|improve this answer


























          1












          1








          1







          As others have mentioned, exposing SQL directly in the api is a very bad option. GraphQL, despite it's name, is not an abstraction for SQL, but for any data store or even other services.



          If you are looking for an abstraction that is closer to SQL, you might want to have a look at odata (if you happen to work in .NET backends, though maybe other implementations exist).






          share|improve this answer













          As others have mentioned, exposing SQL directly in the api is a very bad option. GraphQL, despite it's name, is not an abstraction for SQL, but for any data store or even other services.



          If you are looking for an abstraction that is closer to SQL, you might want to have a look at odata (if you happen to work in .NET backends, though maybe other implementations exist).







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Apr 6 at 17:47









          jannikbjannikb

          613




          613























              0














              if you want expose SQL like GraphQL, you will could need something like GraphQL, because you will need hide the important information and select what you want to show in the API, this for security.



              GraphQl and SQL are different things, SQL is the language to query DataBase and GraphQL is only to manage the data from API,in API you will need make yours schemas to show and querys to manage it, etc.



              in any API you will need make those things for simply security, but if you want something that is free data access maybe it would work, you know so many alternatives in the software world






              share|improve this answer




























                0














                if you want expose SQL like GraphQL, you will could need something like GraphQL, because you will need hide the important information and select what you want to show in the API, this for security.



                GraphQl and SQL are different things, SQL is the language to query DataBase and GraphQL is only to manage the data from API,in API you will need make yours schemas to show and querys to manage it, etc.



                in any API you will need make those things for simply security, but if you want something that is free data access maybe it would work, you know so many alternatives in the software world






                share|improve this answer


























                  0












                  0








                  0







                  if you want expose SQL like GraphQL, you will could need something like GraphQL, because you will need hide the important information and select what you want to show in the API, this for security.



                  GraphQl and SQL are different things, SQL is the language to query DataBase and GraphQL is only to manage the data from API,in API you will need make yours schemas to show and querys to manage it, etc.



                  in any API you will need make those things for simply security, but if you want something that is free data access maybe it would work, you know so many alternatives in the software world






                  share|improve this answer













                  if you want expose SQL like GraphQL, you will could need something like GraphQL, because you will need hide the important information and select what you want to show in the API, this for security.



                  GraphQl and SQL are different things, SQL is the language to query DataBase and GraphQL is only to manage the data from API,in API you will need make yours schemas to show and querys to manage it, etc.



                  in any API you will need make those things for simply security, but if you want something that is free data access maybe it would work, you know so many alternatives in the software world







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Apr 6 at 17:29









                  Jorge Félix CazarezJorge Félix Cazarez

                  63




                  63

















                      protected by gnat 2 days ago



                      Thank you for your interest in this question.
                      Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



                      Would you like to answer one of these unanswered questions instead?



                      Popular posts from this blog

                      GameSpot

                      日野市

                      Tu-95轟炸機