RESTful URI map to database tables?

Question

When designing a RESTful API, should your URI map to tables (for the most part). I have 2 tables that look like this:

Users

+-----------------------------------+
|id|first_name|last_name|email|role |
+-----------------------------------+
|1 |Jonny     |Walker   |a.com|User |
|2 |Jim       |Beam     |b.com|User |
|3 |Jack      |Daniels  |c.com|Admin|
+-----------------------------------+

Availability

+----------------------------+
|user_id|date      |status   |
+----------------------------+
|1      |2017-06-20|Available|
|1      |2017-06-22|Available|
|1      |2017-06-23|Available|
|2      |2017-06-21|Available|
|3      |2017-06-19|Available|
|3      |2017-06-24|Available|
+----------------------------+

In this case is it better to design the URI like:

www.example.com/users/3/availability/

Or:

www.example.com/users/3
www.example.com/availability/3

When you read it the top makes most sense ("user 3's availability). But it seems that the bottom example would be a better way to separate the availability calendar data from the user data (i.e. first name, last name, email, role).

So my question is should the URI approximately map to the tables like the second URI example?

Answer 1

When designing a RESTful API, should your URI map to tables (for the most part).

No, absolutely not.

URIs do NOT map onto domain objects - that violates encapsulation. Work (ex: issuing commands to the domain model) is a side effect of managing resources. In other words, the resources are part of the anti-corruption layer. You should expect to have many many more resources in your integration domain than you do business objects in your business domain. -- Jim Webber

Answer 2

When designing a RESTful API, should your URI map to tables (for the most part)?

No. In a RESTful API, your URIs should map to (logical) resources.
It can happen that a resource in your application corresponds to (a row in) a single table, but that certainly is not the case when relations and/or more complex resources come into play.

For your example of users and availability, there are two commonly used options (which are not mutually exclusive. You can use both at the same time):

• Make the availability information part of the user resource

  GET /users/3
  
  {
    first_name: "Jack",
    last_name: "Daniels",
    email: "c.com",
    role: "Admin",
    availability: [
      { date: "2017-06-19", status: "Available" },
      { date: "2017-06-24", status: "Available" }
    ],
    links: { 
      self: /users/3
    }
  }
  

• Expose the availability information as a sub-resource under the user resource

  GET /users/3/availability
  
  [
    { date: "2017-06-19", status: "Available" },
    { date: "2017-06-24", status: "Available" }
  ]
  

Answer 3

The id that follows a collection should correspond to a unique resource of that collection. As a developer seeing

www.example.com/availability/3

would be confusing since I would think that this is the availability resource of id 3. It would be better to write it in the form of

www.example.com/availability?user_id=3

as a filter on the availability collection.

As for which to pick I think both are valid, and you can even support both with a single function in the backend since they are both the same filter.

www.example.com/users/3/availability/
www.example.com/availability?user_id=3

Answer 4

In addition to the @VoiceOfUnreason' answer.

REST APIs are meant to be one more layer of the application, not the application itself.

It's an interface for integrations. As interface you want it to be as decoupled as possible, so if the domain changes, the API not necessarily have to change too. In consequence, consumers don't have to be worried about these changes.

If the REST API mimics the domain model at such so low level as data base tables relationships, when changes happens, everyone suffers the consequences. Once the API is released, changing its interface is one of the worse thing to deal with.

That's why, in general, it's good to think in REST APIs as yet another abstraction in the application. So, don't (if possible) expose your implementation details to the world. Rather, do expose representations.

When modelling the URI templates, look for the URI that better informs to the developer what he/she might expect after the requests. URIs don't need to be human readable, but when they are, it makes the developer' job a bit easier.

I say developer, because from the client point of view (app client) URIs are meaningless.

Answer 5

Have your tables in separate schema, call it data, create another schema called api and place there only views that in the beginning are only mirrors of your tables. map your api to those views. now you have your tables decoupled from your api, you can independently change one without affecting the other.

also check out PostgREST the type of api you are building can be automated, you don't need to do it by hand