5

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?

1
  • 1
    None of those. This question has nothing to do with the RESTful architecture style Jul 7, 2017 at 17:23

5 Answers 5

7

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

7

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" }
    ]
    
3
  • Implementation-wise, is it possible to build that only using SQL? Every solution I've come up with involves me performing a query on the users table and a query on the availability table and then merging the arrays in PHP. I feel like there should be something in SQL to do this without repeating the user data.
    – keelerjr12
    Jul 8, 2017 at 11:59
  • @keelerjr12: I am not an SQL expert and I am not aware of any mechanism in SQL to turn a set of rows into multiple columns. I would probably implement it as two queries and combine the results in the backend code. Jul 8, 2017 at 13:09
  • @keelerjr12 well there are UNION and JOIN commands as well as VIEW / virtual tables that combine data. You could even SELECT into different keys of a JSON structure these days Oct 28, 2022 at 5:54
4

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
1
2

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.

4
  • So what would be a better way to implement? Have a main .php where there is one rewrite rule that goes to that and then this parses it and routes it to the correct function? Or use different folders where the rewrite rule goes to the main .php file in that folder based on the URI and then that executes the correct function. To be it seems just one main.php in the main folder that would parse everything so you don't have a bunch of different files parsing URIs
    – keelerjr12
    Jul 7, 2017 at 17:09
  • 1
    It's ok having different files mapping URIs to functions. The important things is to keep the cohesion. Put together what should be together. For instance, URIs related with users go in one mapping file. Traverse URIs like /users/id/availability also belongs to the user mapping file. However, /availability/id is mapped in some other file. You might think otherwise, but despite the number of the files, having the responsabilities separated will make the maintenance much easier.
    – Laiv
    Jul 7, 2017 at 17:19
  • you are forgetting the databases have this "modern technology" called views which is a way of abstracting the underlying tables (data model) Jul 7, 2017 at 18:35
  • Indeed, views are useful, but I would not abuse of them or you might end up with two data models to maintain. Plus changes on the domain data tables may cause views to change too and hence the API. So no, mapping views is neither the way to go (IMO).
    – Laiv
    Jul 7, 2017 at 18:48
0

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

12
  • I encourage you to review the answer. I don't think you have completely forseen the implications of mapping views as a way to decouple the API from the domian.
    – Laiv
    Jul 7, 2017 at 18:50
  • please explain, can you give an example of such an implication? Jul 7, 2017 at 18:52
  • Modifying any of the tables involved in any of the views.
    – Laiv
    Jul 7, 2017 at 18:53
  • you modify the tables, then you modify the views in order for their output to be the same as with old version of the tables, exactly the same as you would change your code to work the same (expose the same api) with the new tables Jul 7, 2017 at 18:55
  • Change the view to keep them backforward compatibility... What if you can't? What if you have versions? Trying to decouple the API from the implementation details with more implementation details is a solution fated to fail. If it doesn't, it's likely probable that at some point you will have to explain why your database has so many schemas and why the application and the API is so expensive to maintain. The flexibility required to absorb change will come from the code, not from the DataBase. Views will be useful for a little while.
    – Laiv
    Jul 7, 2017 at 19:05

Your Answer

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

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