An API which allows users to connect directly to the database

Question

I've worked with many APIs and it's never usually an easy task. Messing about with POST requests and then trying to handle the XML is a pain. And I thought wouldn't it be easier for both user and developer if they could just directly interact with the database.

Is it possible to create a user which API users would connect to then assign that certain privilages? For example they would only be able to select from particular tables and columns. And basically make it so they can't do anything malicious or anything you don't want.

I realise that there is a lot more than just taking data so there would be certain limitations there however selecting is probably what goes on the most when it comes to API usage.

Is this a practical idea? Is it secure? I'm really not sure, I'm the furthest thing from a professional here, it's just an idea.

Answer 1

Yes, almost all databases allow you to create users with only select access to a specific schema. I've used this to give advanced Excel users ODBC access without worrying that they will mess anything up. Use very sparingly--it has always created maintenance difficulty because people end up using parts of your schema in ways that you didn't intend (or had plans to replace).

Answer 2

You could set up a RESTful API that can speak directly to a mySQL database like PHPRestSQL. It can do all the dirty work for you, but you would have full freedom in implementing new functions or restrictions.

Answer 3

You can connect Access to any database - Oracle, for example.

However, it's not necessarily a good idea - for security and data integrity reasons.

Answer 4

What do you mean exactly by API, which API are you talking about?

This sounds more like a design decision. If I understand correctly, you want to interact with the User layer and Database / Persistence layer of an application. In general this is a bad idea. First it really reduces code reuse. This may not be a concern at your point in the development but it is a good idea to learn best practices. The layers I usually follow are:

Model-View-Controller Service Persistence Model / Domain

You can see here that MVC (user interface) is separated from the model by at least two layers. This is usually more secure, and promotes code reuse.

Answer 5

Yes you can do this with any client / server database system (if it is a database server there must be a way to connect to it.)

It is not done much because of a number of issues.

• Maintenance is hard
• Security is worse
• In general there is no benefit.

Basically it causes headaches and does not really provide anything which is good.

Answer 6

The two most important counter-questions are:

1) Is the underlying DB already determined, or can you choose one?

2) What sort of DB operations do your users really need to perform? If "select" is really enough then yes, it probably does make sense to expose the data via a "read only" web service. But if you want to update, delete, make stored procedure calls, etc. then you're going to need something like SQL and it's way hard to build a web services API for that.

If the answer to counter-question 1 is "I can choose", then take a look at CouchDB, which already has a RESTful API (http://wiki.apache.org/couchdb/HTTP_REST_AP) built for it.