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?

None of those. This question has nothing to do with the RESTful architecture style — K. Alan Bates, Jul 07 '17 at 17:23

score 7 · Answer 1 · answered Jul 07 '17 at 06:41

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" }
]

answered Jul 07 '17 at 06:41

Bart van Ingen Schenau

71,712
20
110
179

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 08 '17 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. – Bart van Ingen Schenau Jul 08 '17 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 – That Realtor Programmer Guy Oct 28 '22 at 05:54

score 7 · Accepted Answer · answered Jul 07 '17 at 10:56

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

score 4 · Answer 3 · answered Jul 07 '17 at 02:23

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

Since `availability` is a collection resource, your path component should have a trailing slash, even with the query component (see https://cdivilly.wordpress.com/2014/03/11/why-trailing-slashes-on-uris-are-important/). — Géry Ogam, May 01 '19 at 13:08

Laiv · Answer 4 · 2017-07-07T18:08:36.293

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.

edited Jul 07 '17 at 18:08

answered Jul 07 '17 at 17:01

Laiv

14,283
1
31
69

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 07 '17 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 07 '17 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) – Ruslan Talpa Jul 07 '17 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 07 '17 at 18:48

score 0 · Answer 5 · answered Jul 07 '17 at 18:38

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

answered Jul 07 '17 at 18:38

Ruslan Talpa

109

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 07 '17 at 18:50
please explain, can you give an example of such an implication? – Ruslan Talpa Jul 07 '17 at 18:52
Modifying any of the tables involved in any of the views. – Laiv Jul 07 '17 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 – Ruslan Talpa Jul 07 '17 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 07 '17 at 19:05
Not saying that now your code has N data models and countless mappings to the db. – Laiv Jul 07 '17 at 19:06
you may believe me or not, but this works, i've done it, so have lots of other people using postgrest and the supposedly scary stuff that you say will happen never happened. – Ruslan Talpa Jul 07 '17 at 19:18
I have It done too. That's why I told you to re-think the implications. We both just went throught different experiences. – Laiv Jul 07 '17 at 19:24
I upvoted your answer but I think you should take Laiv's feedback into account to improve it. As of now it's not very balanced (e.g. OP may not be familiar with your tools and neither am I). @Laiv: can you expand on what is required to keep up with the changes that can't be done? why it's harder than another method? (I mean, backwards compatibility is a nightmare, even if you build your system with support for it in the first place). – Jul 08 '17 at 17:06
Little example that happened to me. One of the tables is no longer available. What if one of the table or many of them are no longer in the schema and the data is provided by another sort of datasource that is out of your control? Would you tie the API to the new datasource like you did with the view? Or will you build a facade to abstract the datasource from the API? Data models and the data **change** often, and you will never forsee the nature of the changes. If you tie your code to something prone to change, your code becomes britle. – Laiv Jul 08 '17 at 17:26
This is solving problems you don't have yet. PostgreSQL is more then just tables and views. For your specific example, on the chance that it "might happen". You have FDW at your disposal to pull in data from other sources and you have a choice of scripting languages (and even C) to implement your custom logic and run it in the db context, assuming you ever hit those corner cases. – Ruslan Talpa Jul 09 '17 at 06:56
How about querying Twitter from SQL. https://github.com/umitanuki/twitter_fdw This is just a small example of the capabilities of PostgreSQL as an application server (not saying you should put EVERYTHING in it, just don't overcomplicate on the account of something that might happen in the future) – Ruslan Talpa Jul 09 '17 at 07:11

RESTful URI map to database tables?

5 Answers5