Java – How to translate SQL query into REST API requests

javaplanningrestsqltranslate

Let's say I have a machine-readable description (such as in WADL, Swagger or RAML) of a REST API that provides interface to a database.

My users submit queries about underlying database in form of SQL or similar query language. However, I cannot access the database directly, only through the REST API.

What approach would you choose to build (preferably semi-automatically from description) a system that translates such SQL queries into a sequence of requests to given REST API?

How would you represent such problem? Are there any algorithms, theoretical frameworks or tools that can help?

The REST API supports:

  • read-only operations, i.e. only SELECT SQL queries
  • XPartial projections (e.g. /persons?fields=firstname,lastname)
  • RSQL constraints (/persons?query=firstname==John;department.code==42, see another examples).

Some references between tables (foreign keys) are represented as attributes in the REST API (such as Person.department in above constraints example), but some references are represented as subresource (e.g. /persons/{userName}/projects). This means that some queries requires to devise "a plan" to be answered in terms of REST requests.

E.g.: Query for "names of active projects of Chuck Norris" would translate to:

  1. /persons?query=firstname==Chuck;lastname==Norris
  2. Get userName from result
  3. /projects/{userName}?fields=name&query=state==ACTIVE

Best Answer

Technically the query language is just another standard the REST service should understand. It does not necessary map to query params. For example by Hydra it is possible to define a single query param which contains for example an SQL. All you need is a vocab in which you can describe SQLs, and ofc a client which can build SQL and a server which can understand SQL. The common vocab is the contract between the client and the server in this case. Some talk about this. So you don't have to create an own query language, you can reuse any existing query language...

WADL and other REST description languages are a bad start, because REST have an uniform interface / HATEOAS constraint which states that the clients have to use hyperlinks in order to change their state. The clients should decide which hyperlink to chose by checking their semantics. The link metadata (for example link relation) contains that semantics... By languages like WADL there are no links, link metadata, or semantics, and so the REST client cannot be reused, since is not decoupled from the REST service. :S

Related Topic