Build JSON API Responses With Postgres CTEs

2017 Apr30

P

agination is a recurring problem that developers have to deal with when implementing data access layers for APIs. It can be particularly tricky with the more traditional RDMS like MySQL or Postgresql. For example, let's say we had an API endpoint that allowed consumers to search a data base of moves. We could search by title, director, starring actors, etc. Our data base has millions of movies, and we know we don't want to return all all the potential matches for every search request. We only want to return the top 25 or so records and indicate in the response that there are more results to query for:

{
  meta: {
    total: 12000
  , limit: 25
  , next: <URL TO NEXT PAGE&

Read More
filed under:  sql postgres node.js