TreeQL and PathQL compared

02 Nov 2019 - by 'Maurits van der Schee'

TreeQL (see: TreeQL.org) and PathQL (see: PathQL.org) are two API query languages that I have designed and implemented. These implementations can save you time implementing your queries and CRUD operations on database models as REST API's in (administrative) business applications.

TreeQL design philosophy

In one sentence:

TreeQL is a feature-rich REST protocol for exposing database tables as resources over the web using nested JSON.

It does:

.. follow the REST protocol closely
.. have a limited (spatial) filtering language
.. nesting based on foreign key relations
.. hide the underlying SQL dialect
.. no real joins, only sub-selects
.. support application authorization

Example query to get the content of post 1 with the messages in the comments:

GET /records/posts/1?join=comments&include=posts.content,comments.message

TreeQL has a limited functionality, but is seen as a "real" REST API.

PathQL design philosophy

In one sentence:

PathQL is a simple protocol to talk SQL to your database system over the web and get nested JSON results back.

It does:

.. not follow the REST protocol
.. use SQL as filtering language
.. nesting based on JSONPath in column aliases
.. expose the underlying SQL dialect
.. support all SQL constructs
.. support database authorization only

Example POST value to get the content of post 1 with the messages in the comments:

select posts.content as "$.content", comments.message as "$.comments[].message" 
from posts, comments where comments.post_id = posts.id and posts.id = 1

Resulting in the following nested JSON:

{"content":"blog started","comments":[{"message":"great"},{"message":"fantastic"}]}

PathQL relies on standards, has unlimited functionality, but it is not a "real" REST API.

Security considerations

With TreeQL your clients cannot execute arbitrary SQL, but with PathQL they can. This means that PathQL has one layer of defense less and that the attack surface is at the database level. That's why I would not advice to use PathQL unless you have a good DBA to secure, limit, isolate and monitor the database usage. On the other hand the PathQL implementation is a lot thinner than the TreeQL implementation, making it easier to audit.

Links

PS: Liked this article? Please share it on Facebook, Twitter or LinkedIn.

TQ
dev.com