The Datably API lets you programmatically access all Datably features directly via a programming language such as JavaScript. It is based on PHP-CRUD-API, the TreeQL reference implementation in PHP.
Show Technical Info - check this option in the Database stack to display the API Path in Preview.
These limitation and constrains apply:
- Primary keys should either be auto-increment (from 1 to 2^53) or UUID
- Composite primary and composite foreign keys are not supported
- Complex writes (transactions) are not supported
- Complex queries calling functions (like "concat" or "sum") are not supported
- Database must support and define foreign key constraints
- SQLite cannot have bigint typed auto incrementing primary keys
- SQLite does not support altering table columns (structure)
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.
It is loosely based on the REST standard and also inspired by json:api.
The example "posts" table has only a a few fields:
The CRUD + List operations below target this table.
If you want to create a record the request can be written in URL format as:
You have to send a body containing:
And it will return the value of the primary key of the newly created record:
To read a record from this table the request can be written in URL format as:
Where "1" is the value of the primary key of the record that you want to read. It will return:
On read operations you may apply joins.
To update a record in this table the request can be written in URL format as:
Where "1" is the value of the primary key of the record that you want to update. Send as a body:
This adjusts the title of the post. And the return value is the number of rows that are set:
If you want to delete a record from this table the request can be written in URL format as:
And it will return the number of deleted rows:
To list records from this table the request can be written in URL format as:
This will return:
On list operations you may apply filters and joins.
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:
Examples:
- "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)
Examples:
Output:
Filters can be a by applied by repeating the "filter" parameter in the filter string, for example:
This will request all records "where id > 1 and id < 3". If you wanted "where id = 2 or id = 4" you should write:
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.
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:
Output:
With the "order" parameter you can sort. By default the sort is in ascending order, but by specifying "desc" this can be reversed:
Output:
NB: You may sort on multiple fields by using multiple "order" parameters. You can not order on "joined" columns.
