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.


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)?


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.?

3 Answers 3


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.


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
// another URI representing a post 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
// load by ID
// load by 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
// the posts uri represents a filtered list of posts
// think of a filtered ATOM feed

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:


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.


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

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.

  • 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. Commented Jan 9, 2013 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. Commented Jan 11, 2013 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.

  • 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. Commented Jan 9, 2013 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? Commented Jan 9, 2013 at 3:37


I'm late to the question, but I'll echo what James Anderson said: implementing some kind of data warehouse may be a better option.

Go through your existing databases and model the information in a consistent way that all your groups can use. (You were already planning some of this work when creating your API. Change your thinking from Objects to Relations.)

Think from an entire organization perspective, with current and past software. You may have entity/data types that have certain elements where one system is master of one part of the entity and another is master over other aspects. (e.g. The Student system is master over all student records but transportation system knows all the buses they may ride).

Model all the data to fit into the same database, with as close to the native format for information for that data elements master system (e.g. Student system has 1 field for a bus number, but transportation categorizes 4 different buses for a student)

Create views that can be used for reading the data. Keeping the data in a separate database makes updates and deletions not an option. You're fully in control of which users have access to what.

I don't have a simple answer for allowing users to create new records or tracking usage and response time. For creating records, maybe some combination of creating rules or triggers to update the origin DB using dblink or postgres_fdw could accomplish what you want. Analytics would consist of watching the logs.

Other things to Consider - Do all of your dev groups know how to work with a REST based resource (or whatever your API is going to use)? - Can you pull data into excel with your API? There is a huge ecosystem built around reports, data extraction, business intelligence using SQL based tools.



Based on your Update, I think I have a similar problem to yours in my organization and was about to post a separate question about it. I was leaning towards writing a bunch of APIs using Apache Thrift based services that have the "knowledge" of how to query different data elements the right way. My reasoning was:

  • Scripts wouldn't need to fight with DB drivers since they'd use the service. Especially important if I wanted to try out a different language that didn't have the DB driver implemented. (NodeJS and Go work with Thrift, I can't get any Oracle drivers to work on Windows)
  • I could have a standard way of pulling data elements from multiple vendor versions of our primary line of business application (2 software conversions with a 6 month implementation time in the time span of 4 years makes it hard to query historical data)

My answer reflects why I am settling on the SQL based approach. The API would be phase 2 of the project.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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