Learn about TreeQL, how it works, and how to use it in this article. Looking for sample implementations or starters for a TreeQL service? There are code samples and implementations of TreeQL in many different languages.

TreeQL allows you to create a “tree” of JSON objects based on your SQL database structure (relations) and your query.

It is loosely based on the REST standard and also inspired by json:api.

CRUD + List

The example posts table has only a a few fields:

posts  
=======
id     
title  
content
created

The CRUD + List operations below act on this table.

Create

If you want to create a record the request can be written in URL format as:

POST /records/posts

You have to send a body containing:

{
    "title": "Black is the new red",
    "content": "This is the second post.",
    "created": "2018-03-06T21:34:01Z"
}

And it will return the value of the primary key of the newly created record:

2

Read

To read a record from this table the request can be written in URL format as:

GET /records/posts/1

Where “1” is the value of the primary key of the record that you want to read. It will return:

{
    "id": 1
    "title": "Hello world!",
    "content": "Welcome to the first post.",
    "created": "2018-03-05T20:12:56Z"
}

On read operations you may apply joins.

Update

To update a record in this table the request can be written in URL format as:

PUT /records/posts/1

Where “1” is the value of the primary key of the record that you want to update. Send as a body:

{
    "title": "Adjusted title!"
}

This adjusts the title of the post. And the return value is the number of rows that are set:

1

Delete

If you want to delete a record from this table the request can be written in URL format as:

DELETE /records/posts/1

And it will return the number of deleted rows:

1

List

To list records from this table the request can be written in URL format as:

GET /records/posts

It will return:

{
    "records":[
        {
            "id": 1,
            "title": "Hello world!",
            "content": "Welcome to the first post.",
            "created": "2018-03-05T20:12:56Z"
        }
    ]
}

On list operations you may apply filters and joins.

Filters

Filters provide search functionality, on list calls, using the “filter” parameter. You need to specify the column name, a comma, the match type, another commma and the value you want to filter on. These are supported match types:

  • “cs”: contain string (string contains value)
  • “sw”: start with (string starts with value)
  • “ew”: end with (string end with value)
  • “eq”: equal (string or number matches exactly)
  • “lt”: lower than (number is lower than value)
  • “le”: lower or equal (number is lower than or equal to value)
  • “ge”: greater or equal (number is higher than or equal to value)
  • “gt”: greater than (number is higher than value)
  • “bt”: between (number is between two comma separated values)
  • “in”: in (number or string is in comma separated list of values)
  • “is”: is null (field contains “NULL” value)

You can negate all filters by prepending a “n” character, so that “eq” becomes “neq”. Examples of filter usage are:

GET /records/categories?filter=name,eq,Internet
GET /records/categories?filter=name,sw,Inter
GET /records/categories?filter=id,le,1
GET /records/categories?filter=id,ngt,2
GET /records/categories?filter=id,bt,1,1

Output:

{
    "records":[
        {
            "id": 1
            "name": "Internet"
        }
    ]
}

In the next section we dive deeper into how you can apply multiple filters on a single list call.

Multiple filters

Filters can be a by applied by repeating the “filter” parameter in the URL. For example the following URL:

GET /records/categories?filter=id,gt,1&filter=id,lt,3

will request all categories “where id > 1 and id < 3”. If you wanted “where id = 2 or id = 4” you should write:

GET /records/categories?filter1=id,eq,2&filter2=id,eq,4

As you see we added a number to the “filter” parameter to indicate that “OR” instead of “AND” should be applied. Note that you can also repeat “filter1” and create an “AND” within an “OR”. Since you can also go one level deeper by adding a letter (a-f) you can create almost any reasonably complex condition tree.

NB: You can only filter on the requested table (not on it’s included) and filters are only applied on list calls.

Column selection

By default all columns are selected. With the “include” parameter you can select specific columns. You may use a dot to separate the table name from the column name. Multiple columns should be comma separated. An asterisk (“*”) may be used as a wildcard to indicate “all columns”. Similar to “include” you may use the “exclude” parameter to remove certain columns:

GET /records/categories/1?include=name
GET /records/categories/1?include=categories.name
GET /records/categories/1?exclude=categories.id

Output:

    {
        "name": "Internet"
    }

NB: Columns that are used to include related entities are automatically added and cannot be left out of the output.

Ordering

With the “order” parameter you can sort. By default the sort is in ascending order, but by specifying “desc” this can be reversed:

GET /records/categories?order=name,desc
GET /records/categories?order=id,desc&order=name

Output:

    {
        "records":[
            {
                "id": 3
                "name": "Web development"
            },
            {
                "id": 1
                "name": "Internet"
            }
        ]
    }

NB: You may sort on multiple fields by using multiple “order” parameters. You can not order on “joined” columns.

Limit size

The “size” parameter limits the number of returned records. This can be used for top N lists together with the “order” parameter (use descending order).

GET /records/categories?order=id,desc&size=1

Output:

    {
        "records":[
            {
                "id": 3
                "name": "Web development"
            }
        ]
    }

NB: If you also want to know to the total number of records you may want to use the “page” parameter.

Pagination

The “page” parameter holds the requested page. The default page size is 20, but can be adjusted (e.g. to 50).

GET /records/categories?order=id&page=1
GET /records/categories?order=id&page=1,50

Output:

    {
        "records":[
            {
                "id": 1
                "name": "Internet"
            },
            {
                "id": 3
                "name": "Web development"
            }
        ],
        "results": 2
    }

NB: Since pages that are not ordered cannot be paginated, pages will be ordered by primary key.

Joins

Let’s say that you have a posts table that has comments (made by users) and the posts can have tags.

posts    comments  users     post_tags  tags
=======  ========  =======   =========  ======= 
id       id        id        id         id
title    post_id   username  post_id    name
content  user_id   phone     tag_id
created  message

When you want to list posts with their comments users and tags you can ask for two “tree” paths:

posts -> comments  -> users
posts -> post_tags -> tags

These paths have the same root and this request can be written in URL format as:

GET /records/posts?join=comments,users&join=tags

Here you are allowed to leave out the intermediate table that binds posts to tags. In this example you see all three table relation types (hasMany, belongsTo and hasAndBelongsToMany) in effect:

  • “post” has many “comments”
  • “comment” belongs to “user”
  • “post” has and belongs to many “tags”

This may lead to the following JSON data:

{
    "records":[
        {
            "id": 1,
            "title": "Hello world!",
            "content": "Welcome to the first post.",
            "created": "2018-03-05T20:12:56Z",
            "comments": [
                {
                    id: 1,
                    post_id: 1,
                    user_id: {
                        id: 1,
                        username: "mevdschee",
                        phone: null,
                    },
                    message: "Hi!"
                },
                {
                    id: 2,
                    post_id: 1,
                    user_id: {
                        id: 1,
                        username: "mevdschee",
                        phone: null,
                    },
                    message: "Hi again!"
                }
            ],
            "tags": []
        },
        {
            "id": 2,
            "title": "Black is the new red",
            "content": "This is the second post.",
            "created": "2018-03-06T21:34:01Z",
            "comments": [],
            "tags": [
                {
                    id: 1,
                    message: "Funny"
                },
                {
                    id: 2,
                    message: "Informational"
                }
            ]
        }
    ]
}

You see that the “belongsTo” relationships are detected and the foreign key value is replaced by the referenced object. In case of “hasMany” and “hasAndBelongsToMany” the table name is used a new property on the object.

Batch operations

When you want to create, read, update or delete you may specify multiple primary key values in the URL. You also need to send an array instead of an object in the request body for create and update.

To read a record from this table the request can be written in URL format as:

GET /records/posts/1,2

The result may be:

[
        {
            "id": 1,
            "title": "Hello world!",
            "content": "Welcome to the first post.",
            "created": "2018-03-05T20:12:56Z"
        },
        {
            "id": 2,
            "title": "Black is the new red",
            "content": "This is the second post.",
            "created": "2018-03-06T21:34:01Z"
        }
]

Similarly when you want to do a batch update the request in URL format is written as:

PUT /records/posts/1,2

Where “1” and “2” are the values of the primary keys of the records that you want to update. The body should contain the same number of objects as there are primary keys in the URL:

[   
    {
        "title": "Adjusted title for ID 1"
    },
    {
        "title": "Adjusted title for ID 2"
    }        
]

This adjusts the titles of the posts. And the return values are the number of rows that are set:

1,1

Which means that there were two update operations and each of them had set one row. Batch operations use database transactions, so they either all succeed or all fail (successful ones get roled back).

Spatial support

For spatial support there is an extra set of filters that can be applied on geometry columns and that starting with an “s”:

  • “sco”: spatial contains (geometry contains another)
  • “scr”: spatial crosses (geometry crosses another)
  • “sdi”: spatial disjoint (geometry is disjoint from another)
  • “seq”: spatial equal (geometry is equal to another)
  • “sin”: spatial intersects (geometry intersects another)
  • “sov”: spatial overlaps (geometry overlaps another)
  • “sto”: spatial touches (geometry touches another)
  • “swi”: spatial within (geometry is within another)
  • “sic”: spatial is closed (geometry is closed and simple)
  • “sis”: spatial is simple (geometry is simple)
  • “siv”: spatial is valid (geometry is valid)

These filters are based on OGC standards and so is the WKT specification in which the geometry columns are represented.

Authentication

Authentication is done by means of sending a “Authorization” header. It identifies the user and stores this in the session context. This variable can be used in the authorization handlers to decide wether or not sombeody should have read or write access to certain tables, columns or records. Currently there are two types of authentication supported: “Basic” and “JWT”. This functionality is enabled by adding the ‘basicAuth’ and/or ‘jwtAuth’ middleware.

Basic authentication

The Basic type supports a file that holds the users and their (hashed) passwords separated by a colon (‘:’). When the passwords are entered in plain text they fill be automatically hashed. The authenticated username will be stored in the “username” variable of the session context. You need to send an “Authorization” header containing a base64 url encoded and colon separated username and password after the word “Basic”.

Authorization: Basic dXNlcm5hbWUxOnBhc3N3b3JkMQ

This example sends the string “username1:password1”.

JWT authentication

The JWT type requires another (SSO/Identity) server to sign a token that contains claims. Both servers share a secret so that they can either sign or verify that the signature is valid. Claims are stored in the “claims” variable of the session context. You need to send an “Authorization” header containing a base64 url encoded and dot separated token header, body and signature after the word “Bearer” (read more about JWT here).

Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWUsImlhdCI6IjE1MzgyMDc2MDUiLCJleHAiOjE1MzgyMDc2MzV9.Z5px_GT15TRKhJCTHhDt5Z6K6LRDSFnLj8U5ok9l7gw

This example sends the signed claims:

{
  "sub": "1234567890",
  "name": "John Doe",
  "admin": true,
  "iat": "1538207605",
  "exp": 1538207635
}

NB: The JWT implementation only supports the hash based algorithms HS256, HS384 and HS512.

Authorizing operations

The Authorization model acts on “operations”. The most important ones are listed here:

method path                  - operation - description
----------------------------------------------------------------------------------------
GET    /records/{table}      - list      - lists records
POST   /records/{table}      - create    - creates records
GET    /records/{table}/{id} - read      - reads a record by primary key
PUT    /records/{table}/{id} - update    - updates columns of a record by primary key
DELETE /records/{table}/{id} - delete    - deletes a record by primary key
PATCH  /records/{table}/{id} - increment - increments columns of a record by primary key

The “/openapi” endpoint will only show what is allowed in your session. It also has a special “document” operation to allow you to hide tables and columns from the documentation.

For endpoints that start with “/columns” there are the operations “reflect” and “remodel”. These operations can display or change the definition of the database, table or column. This functionality is disabled by default and for good reason (be careful!). Add the “columns” controller in the configuration to enable this functionality.

Authorizing tables, columns and records

By default all tables and columns are accessible. If you want to restrict access to some tables you may add the ‘authorization’ middleware and define a ‘authorization.tableHandler’ function that returns ‘false’ for these tables.

'authorization.tableHandler' => function ($operation, $tableName) {
    return $tableName != 'license_keys';
},

The above example will restrict access to the table ‘license_keys’ for all operations.

'authorization.columnHandler' => function ($operation, $tableName, $columnName) {
    return !($tableName == 'users' && $columnName == 'password');
},

The above example will restrict access to the ‘password’ field of the ‘users’ table for all operations.

'authorization.recordHandler' => function ($operation, $tableName) {
    return ($tableName == 'users') ? 'filter=username,neq,admin' : '';
},

The above example will disallow access to user records where the username is ‘admin’. This construct adds a filter to every executed query.

NB: You need to handle the creation of invalid records with a validation (or sanitation) handler.

Sanitizing input

By default all input is accepted and sent to the database. If you want to strip (certain) HTML tags before storing you may add the ‘sanitation’ middleware and define a ‘sanitation.handler’ function that returns the adjusted value.

'sanitation.handler' => function ($operation, $tableName, $column, $value) {
    return is_string($value) ? strip_tags($value) : $value;
},

The above example will strip all HTML tags from strings in the input.

Validating input

By default all input is accepted. If you want to validate the input, you may add the ‘validation’ middleware and define a ‘validation.handler’ function that returns a boolean indicating whether or not the value is valid.

'validation.handler' => function ($operation, $tableName, $column, $value, $context) {
    return ($column['name'] == 'post_id' && !is_numeric($value)) ? 'must be numeric' : true;
},

When you edit a comment with id 4 using:

PUT /records/comments/4

And you send as a body:

{"post_id":"two"}

Then the server will return a ‘422’ HTTP status code and nice error message:

{
    "code": 1013,
    "message": "Input validation failed for 'comments'",
    "details": {
        "post_id":"must be numeric"
    }
}

You can parse this output to make form fields show up with a red border and their appropriate error message.

Multi-tenancy support

You may use the “multiTenancy” middleware when you have a multi-tenant database. If your tenants are identified by the “customer_id” column you can use the following handler:

'multiTenancy.handler' => function ($operation, $tableName) {
    return ['customer_id' => 12];
},

This construct adds a filter requiring “customer_id” to be “12” to every operation (except for “create”). It also sets the column “customer_id” on “create” to “12” and removes the column from any other write operation.

Prevent database scraping

You may use the “joinLimits” and “pageLimits” middleware to prevent database scraping. The “joinLimits” middleware limits the table depth, number of tables and number of records returned in a join operation. If you want to allow 5 direct direct joins with a maximum of 25 records each, you can specify:

'joinLimits.depth' => 1,
'joinLimits.tables' => 5,
'joinLimits.records' => 25,

The “pageLimits” middleware limits the page number and the number records returned from a list operation. If you want to allow no more than 10 pages with a maximum of 25 records each, you can specify:

'pageLimits.pages' => 10,
'pageLimits.records' => 25,

NB: The maximum number of records is also applied when there is no page number specified in the request.

Customization handlers

You may use the “customization” middleware to modify request and response and implement any other functionality.

'customization.beforeHandler' => function ($operation, $tableName, $request, $environment) {
    $environment->start = microtime(true);
},
'customization.afterHandler' => function ($operation, $tableName, $response, $environment) {
    $response->addHeader('X-Time-Taken', microtime(true) - $environment->start);
},

The above example will add a header “X-Time-Taken” with the number of seconds the API call has taken.

File uploads

File uploads are supported through the FileReader API.

OpenAPI specification

On the “/openapi” end-point the OpenAPI 3.0 (formerly called “Swagger”) specification is served. It is a machine readable instant documentation of your API. To learn more, check out these links:

Errors

The following errors may be reported:

Error HTTP response code Message
1000 404 Not found Route not found
1001 404 Not found Table not found
1002 422 Unprocessable entity Argument count mismatch
1003 404 Not found Record not found
1004 403 Forbidden Origin is forbidden
1005 404 Not found Column not found
1006 409 Conflict Table already exists
1007 409 Conflict Column already exists
1008 422 Unprocessable entity Cannot read HTTP message
1009 409 Conflict Duplicate key exception
1010 409 Conflict Data integrity violation
1011 401 Unauthorized Authentication required
1012 403 Forbidden Authentication failed
1013 422 Unprocessable entity Input validation failed
1014 403 Forbidden Operation forbidden
1015 405 Method not allowed Operation not supported
1016 403 Forbidden Temporary or permanently blocked
1017 403 Forbidden Bad or missing XSRF token
1018 403 Forbidden Only AJAX requests allowed
1019 403 Forbidden Pagination Forbidden
9999 500 Internal server error Unknown error

The following JSON structure is used:

{
    "code":1002,
    "message":"Argument count mismatch in '1'"
}

NB: Any non-error response will have status: 200 OK