Reduce number of SQL queries with falcor-router references

Question

Suppose I have two falcor-routes

route: 'users[{ranges}]'

and

route: 'UserById[{integers:ids}]["name","email"]'

whereas users returns references to the UserById-route. If I then fire a query

get('users[0..10]["name","email"]')

against this, the router will first evaluate the users[0..10] part which will perform a SELECT id FROM users LIMIT 10 on the database and return the corresponding ids. The router will then use these ids together with the specific route to fill the actual values. Without akward caching on the UserService-implementation (which would need to be repeated for any similar case, e.g. addresses, cost-types and the like) this would produce at least two queries against my persistence backend, wheere a more traditional approach using a single RESTful endpoint

GET /users/?offset=0&limit=10

would most probably be content with a single one.

Is there a general best practices approach how to optimize database queries in this scenario, e.g. by some clever cache between the router and the backend services? Returning the full information already in the users route of the model is a no-go since our users might have graph links to friends which are users themselves.

Answer 1

Wouldn't having both a generic users-by-index route, and a username-and-email-by-index route allow you to handle the get('users[0..10]["name","email"]') query with a single DB request?

E.g.

route: 'users[{ranges}]'

and

route: 'users[{ranges}]["name", "email"]'

The second route, because it is more specific, would return everything you need for the get('users[0..10]["name","email"]') query w/ only one DB request, while user queries on any additional fields (beyond name and email) would fall through to the first route.

Answer 2

This question is answered here by Jafar Hussain quoting the documentation:

The Router is appropriate as an abstraction over a service layer or REST API. Using a Router over these types of APIs provides just enough flexibility to avoid client round-trips without introducing heavy-weight abstractions. Service-oriented architectures are common in systems that are designed for scalability. These systems typically store data in different data sources and expose them through a variety of different services. For example Netflix uses a Router in front of its Microservice architecture.

It is rarely ideal to use a Router to directly access a single SQL Database. Application’s that use a single SQL store often attempt to build a single SQL Query for every server request. Router’s work by splitting up requests up for different sections of the JSON Graph document into separate handlers and sending individual requests to services to retrieve the requested data. As a consequence Router’s rarely have sufficient context to produce a single optimized SQL query. We are currently exploring different options for supporting this type of data access pattern in Falcor in future.

What I'm exploring myself right now is using a router to collect partial database queries, then crawling the JSON Graph result to merge those partial queries, execute them as one against the database, then crawling the graph again to place values at the right paths. It is definitely possible using ArangoDB and AQL's LET statements, though it is a bit tricky. I don't know about SQL.

If you are looking for a simpler solution, you can cache the index-to-id mapping to try and minimize database access. James Conkling's answer is also good.