Tell me more ×
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.

Question and Context

I am currently working on a project where the main question is, how do we insert an API between the database and end-users who may develop applications/scripts of their own that will work with or on top of the database?

The database type I am working with is Postgresql, and this project is in-house, so the end-users are users in other development groups.

Aims

What I am looking to satisfy are the following:

  • Avoid a wide-interface.
  • Require authentication from the application that is to use the API - provide end-user a single unique token or key to access the application. No user/password management.
    • Have the option to make it open - no authentication required.
  • Allow users to create and read records.
  • Restrict users from deleting and updating existing records.
  • (Optional) Track who is using the API - number of requests, records, etc.
  • (Optional) Track amount of time spent completing a request.

Some Thoughts

My initial thought is to establish a RESTful API by building my own servlet to handle the requests, and of course, customize it to meet my aims. However, I have a feeling there are already many versions of this wheel out there so I would like to avoid reinventing it.

An additional question to this would be, what is this kind of API/layer called (aside from DAL)?

Update

How would the advantages of putting a common API in place be if there are multiple databases of varying types e.g. postgres, ms sql, oracle, etc.?

share|improve this question

2 Answers

up vote 4 down vote accepted

My initial thought is to establish a RESTful API by building my own servlet to handle the requests, and of course, customize it to meet my aims. However, I have a feeling there are already many versions of this wheel out there so I would like to avoid reinventing it.

There probably are but REST APIs are like Database ORMs, they can potentially speed up development a bit but they come with overhead and most of the work will still be focused in defining your interfaces.

Your best bet is to pick a low-level server platform that allows you to control routing, requests, and responses directly (ie URIs + GET/POST/PUT/DELETE).

In fact, if you take some time to read up on the HATEOAS approach to REST APIs you will start to realize how simple and powerful REST can be if fully utilized. Resist the urge to use hacky Ruby-style REST anti-patterns.

Update:

Here's a good link to some good REST anti-pattern examples.

By Ruby-style REST anti-patterns I'm referring to unnecessarily creating URIs for specific actions.

For the URI:

// a URI representing a post
example.org/post/456
// another URI representing a post edit
example.org/post/456/edit

As opposed to using POST (ie create), PUT (ie update), DELETE (ie remove).

Another example is the the blog anti-pattern:

// filter by date
example.com/posts/2012/12/1/
// load by ID
example.com/posts/id/456
// load by title
example.com/posts/title/example-post-title

Basically, what this is searching and returning the first instance that matches the date parameters. The RESTful way to do a filter is by using a querystring on the URI.

// the post uri represents a post
example.com/post/456
// the posts uri represents a filtered list of posts
// think of a filtered ATOM feed
example.com/posts?date=12/12/1
example.com/posts?title=example-post-title

Ruby was one of the first widespread platforms that enabled custom/advanced routing schemes without obscure configurations like apache mod_rewrite. Unfortunately, at the time a lot of people started considering custom routing schemes RESTful when then don't really follow the definition.

The idea is that a there should be a single URI representing each resource. Resources are created via POST, updated via PUT, read/filtered via GET, etc... Funneling everything through GET/POST and/or creating URIs to represent actions on other URIs are common but not ideal.

Transforms can be handled using MIME-Types:

example.org/post/456+json

Note: Also, don't forget to send the response with the correct Content-Type in the header so the client knows how to handle it. In this case the Content-Type would be 'application/json'.

Authentication can be very simple if you don't need a complicated ACL. Just create a Controller base class where POST/PUT/DELETE requests all require authentication. GET requests are usually read-only.

The differences may seem trivial but imagine REST as a class interface. Everything that inherits it is expected to act in a manner that complies with the interface definition. Breaking the pattern means breaking the interface contract at which point the abstraction leaks. Every anti-pattern you implement will then need to be re-implemented on every client and the interface becomes implementation-specific.


Optional:

Analytics Tracking:

Tracking is just as easy. Setup a URI for the tracker that's setup to accept POST requests. When you send the response to the user, send a second response to the tracker URI with the user's agent info in the body.

// POST tracking info to this URI with user agent info in the body
example.org/tracking/

Request Time Tracking:

This really depends. Do you want the time from the request is received by the server and a response is sent? If that's the case, just mark Date.now() at the start of the controller, do it again after the response is sent, and compare the two.

share|improve this answer
What would be an example of a hacky Ruby-style REST anti-pattern? Just need a reference to make sure I'm on the right track. +1 for the HATEOAS reference. – hulkmeister Jan 9 at 2:24
1  
@hulkmeister Sorry about the delayed response, I answered your question and added a bunch more useful info. I would post examples of controllers but implementations can be wildly different across the wide variety of languages/platforms/frameworks used on the server-side. So far the GoogleAppEngine (python) webapp2 has the cleanest implementation I have seen yet unfortunately it's hard to find good examples online. – Evan Plaice Jan 11 at 4:42

This may sound flippant but its not. Why not use SQL.

It covers all the bases in your requirements.

Avoid a wide-interface.
       -- SQL is wide by default but there are some tricks you can use to narrow it down.
Require authentication from the application that is to use the API.
       -- Postgres comes with strong authentication and fine grained security.
Allow users to create and read records.
       -- Just "GRANT" them the privilege.
Restrict users from deleting and updating existing records.
       -- Do not grant them the privilege.
(Optional) Track who is using the API - number of requests, records, etc.
       -- Postgres will log most of this.
(Optional) Track amount of time spent completing a request.
       -- Possible with some low level trickery.

If you do go down this route I would strongly recommend you expose only views to your external users. In fact you should consider views as an Interface definition, which would allow you to modify the underlying implementation without affecting you users.

share|improve this answer
No, it does not sound flippant; I appreciate the answer very much. This is something I have considered, but I don't want to have to manage the users, and for appearance sake, don't want the end-user to understand that there are "users" to be made with every application. I'll update the specs list. – hulkmeister Jan 9 at 3:30
I am mulling this over, aside from the development time to put an API into place, what are the advantages of doing it this way? – hulkmeister Jan 9 at 3:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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