Best practice to pass query conditions in ajax request

Question

I'm writing a REST api in node js that will execute a sql query and send the results; in the request I need to send the WHERE conditions; ex:

GET   127.0.0.1:5007/users            //gets the list of users

GET   127.0.0.1:5007/users   
      id = 1                          //gets the user with id 1

Right now the conditions are passed from the client to the rest api in the request's headers. In the API I'm using sequelize, an ORM that needs to receive WHERE conditions in a particular form (an object); ex: having the condition:

(x=1 AND (y=2 OR z=3)) OR (x=3 AND y=1)

this needs to be formatted as a nested object:

                      -- x=1
              -- AND -|         -- y=2 
              |       -- OR ----|
              |                 -- z=3
       -- OR -|
              |
              |       -- x=3
              -- AND -|
                      -- y=1

so the object would be:

Sequelize.or (
    Sequelize.and (
        {x=1},
        Sequelize.or(
            {y=2},
            {z=3}
        )
    ),
    Sequelize.and (
        {x=3},
        {y=1}
    )
)

Now I'm trying to pass a simple string (like "(x=1 AND (y=2 OR z=3)) OR (x=3 AND y=1)"), but then I will need a function on the server that can convert the string in the needed object (this method in my opinion has the advantage that the developer writing the client, can pass the where conditions in a simple way, like using sql, and this method is also indipendent from the used ORM, with no need to change the client if we need to change the server or use a different ORM);

The function to read and convert the conditions' string into an object is giving me headache (I'm trying to write one without success, so if you have some examples about how to do something like this...)

What I would like to get is a route capable of executing almost any kind of sql query and give the results:

now I have a different route for everything:

127.0.0.1:5007/users  //to get all users
127.0.0.1:5007/users/1    //to get a single user
127.0.0.1:5007/lastusers  //to get user registered in the last month

and so on for the other tables i need to query (one route for every kind of request I need in the client); instead I would like to have only one route, something like:

127.0.0.1:5007/request

(when calling this route I will pass the table name and the conditions' string)

Do you think this solution would be a good solution or you generally use other ways to handle this kind of things? Do you have any idea on how to write a function to convert the conditions' string into the desired object?

Any suggestion would be appreciated ;)

score 1 · Accepted Answer · answered Aug 29 '14 at 02:23

I would strongly advise you not to expose any part of your database model to your clients. Doing so means you can't change anything you expose without the risk of breaking the clients. One suggestion as far as what you've supplied is that you can and should use query parameters to cut down on the number of endpoints you've got.

GET /users  //to get all users
GET /users?registeredInPastDays=30  //to get user registered in the last month
GET /users/1    //to get a single user

Obviously "registeredInPastDays" should be renamed to something less clumsy .. it's just an example.

As far as the conditions string, there ought to be plenty of parsers available online. The grammar looks very straightforward.

I agree; building a single maxi route that execute all kind of query was supposed to be a method to reduce the API endpoints to 1 (except for other special routes); I'm looking for js expression parsers; if you have already used some of them can you tell which one is your favorite? — Cereal Killer, Aug 29 '14 at 15:08

score 0 · Answer 2 · answered Aug 29 '14 at 06:49

IMHO the main disadvantage of your solution is that you are creating just another API for quering data. Why create sthm from scratch if it is already created? You should use existing mature query API and focus on your business logic rather then inventing sthm new.

For example, you can take query syntax from Odata. Many people have been developing that standard for a long time. They have already considered different use cases and obstacles for query API.

score 0 · Answer 3 · edited May 23 '17 at 12:27

Resources are located with a URI. You can use or mix three ways to address them:

Hierarchically with a sequence of path segments:

/users/john/posts/4711
Non hierarchically with query parameters:

/users/john/posts?minVotes=10&minViews=1000&tags=java
With matrix parameters which affect only one path segment:

/users;country=ukraine/posts

This is normally sufficient enough but it has limitations like the maximum length. In your case a problem is that you can't easily describe and and or conjunctions with query parameters. But you can use a custom or standard query syntax. For instance if you want to find all cars or vehicles from Ford except the Capri with a price between $10000 and $20000 Google uses the search parameter

q=cars+OR+vehicles+%22ford%22+-capri+%2410000..%2420000

(the %22 is a escaped ", the %24 a escaped $).

If this does not work for your case and you want to pass data outside of the URI the format is just a matter of your taste. Adding a custom header like X-Filter may be a valid approach. I would tend to use a POST. Although you just want to query data this is still RESTful if you treat your request as the creation of a search result resource:

POST /search HTTP/1.1

your query-data

Your server should return the newly created resource in the Location header:

HTTP/1.1 201 Created
Location: /search/3

The result can still be cached and you can bookmark it or send the link. The downside is that you need an additional POST.

Best practice to pass query conditions in ajax request

3 Answers3