Accessing the database through the REST API

The database, tables and rows expose an interface which is compatible with the conventions described in REST interface. The main methods available are described below.

Node type HTTP method Description
Database GET Return a list of all the database tables. See Database Class Script method getData() for the format.
Table GET

Return all the rows in the table, as a JSON array of objects.

This supports two query parameters: filter and view.

Use filter to modify what rows are returned. Build-in filters are:

all Return all the rows (default)
row

Search for a specific row by key. If found, return a one-item array containing the row.

If the row has a single key field, pass it as the value of the key.

Need to reconsider multi-field keys.

boolean

Return rows which have a boolean field set to true. Pass the name of the field in the query parameter boolean, e.g.

database/my_table?filter=boolean&boolean=my_field

The view controls what data is returned. Built-in view are:

data

Return all the fields, but not children fields (default).

Link fields are represented by objects with reference, name and URL.

keys

Return only the key fields

children

Return the data and children fields.

Use the additional parameter keys=true to show key fields in the link objects.

Table PUT

Create or update one or more rows. Pass the rows as an array of JSON objects in the body, using a content type of application/json. Fields that are not passed are removed from the rows.

Table PATCH

Update one or more rows. PATCH is like PUT except that it does not remove fields that have not been passed.

Table GET

Use action=definition to return a definition of the table row. The row definition format is described in Derive Row Definition.

Row GET

This is similar to table GET, except that it returns just the data for the row as an object, rather than an array.

GET supports the view and keys options in exactly the same way as the GET on Table.

Row PUT

Update all the non-key fields in the row. Fields that are not passed are removed from the row.

Row PATCH

Update all the non-key fields in the row. Fields that are not passed are retained on the row.

Row DELETE

Delete the row.

Errors are returned using HTTP conventions.

To format the response JSON, add the query parameter pretty=true.

URL conventions

As well as locating rows using a GET on the table, you can look up individual rows directly by URL if you know the table URL and the row key values.

The rules for this are controlled by the table, and might be modified for some tables with complex requirements, but unless they have been modified the general rules below are used.

  • Convert each key value to its string equivalent.
  • Replace any characters other than A-Z, a-z and 0-9 with _xx_, where xx is the hexadecimal code for the character, without leading zeroes.
  • Concatenate all the key values with __ (a double underscore).
  • If the concatanated key begins with 0-9, prefix it with an underscore.

For example, if a table were keyed on a numeric product_id and a string version_id, with a product id of 123 and a version of "A11.2", we would get:

  • Convert to strings: "123", "A11.2"
  • Replace non alpha characters: "123", "A11_46_2"
  • Concatenate: "123__A11_46_2"
  • Prefix if first character is number: "_123__A11_46_2"

Adding /_123__A11_46_2  to the table URL would then give the row URL.