Explore our sites

Stack Exchange
tour help
Take the tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 0 down vote favorite

What are the pros and cons of exposing an SQL API instead of a REST API?

Let's assume that this would be a private API – the API developer and user are on the same team.

Probably the biggest problem would be security and validation. I can see two possible approaches:

  1. Defining a set of allowed SQL query templates.
  2. Doing some clever analysis of every incoming SQL query.
share|improve this question
6  
First: what are your goals? Second: Why doesn't REST API fulfill them? –  exizt Oct 5 at 13:32
 
@exizt 1. Tightly coupled mobile client + server backend. 2. Advantages of SQL over REST: transactions and flexibility (I don't have to create a new API endopoint for every non-trivial db query) –  fhucho Oct 5 at 13:45
2  
How is SQL in any way an "API"? You're just granting access to the database and putting some restrictions on what queries can be executed (basically, you're breaking SQL). REST has as much to do with SQL as apples have to do with electric guitars. How do you map POST, HEAD, OPTIONS, or even the general notion of a "resource" into SQL? –  Aaronaught Oct 5 at 15:05
 
Yql pulls it off nicely, but that's the only instance I'm aware of. Unless you are actually building SQL as the data access layer to a highly complex data source, this will either be extremely hard to do properly, or its pointing to your database and is worth staying away from. –  Adrian Schneider Oct 5 at 15:29
1  
YQL isn't a technology you can use. Yahoo created their own "SQL-like" language. You'd need to write your own parser, AST, and compiler. Creating a language is not trivial - it's orders of magnitude more complex than a REST API. If all you want is to encapsulate queries, look at OData instead. –  Aaronaught Oct 5 at 15:57
show 6 more comments

closed as too broad by gnat, Kilian Foth, gbjbaanb, MichaelT, Bart van Ingen Schenau Oct 8 at 10:27

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

up vote 4 down vote accepted

Security

What if somebody submits a SQL query like:

DELETE FROM comments

Oh, that's not good. We don't want to be able to delete all the comments. Maybe we'll have a list of approved SQL queries, like:

DELETE FROM comments WHERE id = ?;

Ok, but how do we know that comment belonged to the user? We need to add checks to make sure the user has permission to delete the comment. Maybe we also want to prevent deleting comments after five minutes or so.

This SQL api is getting more and more complicated. In fact, its becoming pretty much that REST api you were attempting to avoid creating. In order to make SQL queries work as an API, you are going to have to do all the work involved in a REST api. The only difference is that you have to deal with the complexities of incoming SQL.

Implementation Details

If you implement a REST api, you make promises about the internal implementations of anything. Comments could be stored in a table, or in a blob column on the post table, or in files, or in a NoSQL database. From the perspective of the API, it doesn't matter. By deliberately not giving access to the SQL, you prevent the frontend that uses the API from having to be rewritten if you make changes to how the comments are stored.

Altogether, trying to use SQL as an api won't be simpler than making a REST api. It will end up being much more complicated. And it will make it very hard to change the internal mechanisms of the API implementation.

share|improve this answer
 
Great answer. I'd swear I read something similar a while ago. Can't remember where it was. –  JensG Oct 6 at 20:57
add comment
up vote -1 down vote

Separate interface from implementation. This allows you to change the implementation as it becomes necessary due to changes in your requirements. One way to think about this is as an extension of encapsulation.

In this case the implementation is what is happening on the far side of the REST API from the requestor. Whatever that is, encapsulate it to insulate the requestor from changes to it.

The interface is the REST API; expose only the "business" and not the fact that to satisfy some business need you must select columns from one or more tables.

Eventually, changes occur. You stop using an RDMBS and start using something else. Your data model changes. Don't force your requestors to change if you can at all help it.

share|improve this answer
1  
Care to explain your answer? What is the "interface" and what is the "implementation"? What is being separated or supposed to be separated? –  Aaronaught Oct 5 at 15:49
 
What about a tightly coupled client / server (possibly developed by a single person)? –  fhucho Oct 5 at 16:03
 
Interface is the publicly visible API, implementation is everything behind (which you typically don't see). That's not so hard to understand, I think. –  JensG Oct 5 at 17:03
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.