REST API relationships, pros/cons of fetching modularly (multiple requests) vs in aggregate (single request) with SQL joins
api, postgresql, rest, sql
Solution
Generally 1 API query will be faster than 2, due to the overhead in preparing 2 separate queries, but whether you will avail of the benefit depends on how your API will be consumed.
If your users do not intend to consume the API through the `GET /feedagg` method then there will be no perceived benefit in performance.
The modularity you speak of is the key principal of REST which is to divide your API into logical `Resources`. Pragmatically speaking, stick with the modularity method unless your API will always be consumed the second way in which case do that to realize the performance benefit it provides.
Here is a great resource on pragmatic RESTful API development: http://www.vinaysahni.com/best-practices-for-a-pragmatic-restful-api#ssl
Problem
Usually from what I've seen REST APIs are implemented in a modular way. For example, say we're building an API over a database that holds RSS feeds, then grabbing the feeds and items would entail the following: ``` +-----------+--------------------+ | REST | SQL | +-----------+--------------------+ | GET /feed | SELECT * FROM feed | +-----------+--------------------+ ``` followed by something like either ``` +-------------------------+-----------------------------------------------+ | REST | SQL | +-------------------------+-----------------------------------------------+ | GET /feed/1/item | SELECT * FROM item WHERE feed_id = 1 | | GET /item?feed_id=1,2,3 | SELECT * FROM item WHERE feed_id IN (1, 2, 3) | +-------------------------+-----------------------------------------------+ ``` if you want to do if you wanna do it one at a time or it in aggregate respectively. What I'm wondering is if there's any advantages to making this less modular, and approaching it with JOINs, and aggregates, for example, if you know in advance you'd need an aggregate, you would instead use ``` +--------------+---------------------------------+ | REST | SQL | +--------------+---------------------------------+ | GET /feedagg | SELECT | | | f.*, | | | json_agg(i.*) as items | | | FROM feed f | | | JOIN item i USING (feed_url) | | | GROUP BY f.feed_url | | | ORDER BY f.title ASC | +--------------+---------------------------------+ ``` It's a lot more SQL, but on the other hand, it's only one SQL query and only one API request. I know that these two approaches can coexist since I put them under different routes, but what I'm not clear on is if the second approach is actually better than the first. It's seemingly better because of the fewer request/query count, but I can't find resources detailing anything about this. There's an overwhelming amount of examples regarding the first modular approach, but, in contrast, there's a severe lack of examples online in which people use JOINs + aggregates. I'm not experienced, and I know a lot of factors, including ones that I'm probably not thinking of, are in play. It could be that the performance difference is negligible but regardless, I'd like to get a pro/con breakdown of the two approaches. Could someone clarify this for me?