Database REST API

Version: 1.0
Initial release: 2015-07-07
Latest updated: 2015-07-29


Author

Leandro Silva


License

Copyright © 2015 Leandro Silva
This projected is licensed under the terms of the MIT license.


Server Requirements

PHP >= 5.3.3


Quick Links


Description

This RESTful service allows users to perform CRUD operations on whitelisted tables of a MySQL database.


URI

api/[version]/[table]?[queries]


Queries


Request Headers

X-Pretty-Print While this is defaults to true, to receive a response that is not pretty-printed, send your request with the ‘Pretty-Print’ header set to false (X-Pretty-Print: 0).

If-None-Match Use an ETag to check if your request has been modified. An ETag is returned with a successful GET request on a record. Note that it is not returned when requesting the API version (api/v1) nor when a response has no content (status code 204). Learn more on how to use an ETags here.

Accept-Encoding This API supports the gzip compression option.


Authentication

This API uses Digest Authentication, so never send your API secret over the wire. RFC2617

To simplify authentication, I’ve created some helper files for PHP, shell script, and JavaScript (with browser and Node support). Reach out if you would like me to share them with you.

Note that unless you’re using this API over a secure connection (SSL/TLS), your request and response body can be easily retrieved.


Notes

In an effort to maintain database integrity, this API is strict on updating and deleting records; it only allows one record to be updated/deleted per request.

This API expects your table to have an id column (as “id” or “ID”). If an id column is present, an ‘apiPath’ property (a URI that represents its row) will be returned with each result. The id is assumed to be a unique value.

Except for successful GET requests (GETs that return a 200 status code), all responses include a ‘status’ property.

By default, numeric queries will be converted into numbers. The below will search for the number 1.0:
api/v1/my_table?where(version)=1.0

To explicitly search for a string, wrap your value in single quotes. The below example searches for the string 1.0:
api/v1/my_table?where(version)='1.0'

Unless explicitly stringified (see above), boolean queries will be converted into proper booleans:
api/v1/my_table?where(male)=true

If you receive unexpected results from a search, use the debug query parameter to confirm your statement is being properly created.


Inline Clauses

As a shorthand, you can express inline ‘OR’ and ‘AND’ clauses in query values by using semicolon and/or comma delimiters respectively. Inline clauses are smart and will match the clause type where the delimiter was used. For example, a where(id)=1;2;3 query will use an inline ‘OR’ equals clause whereas a where-not(id)=1;2;3 query will use an inline ‘OR’ not equals clause. See below.

You can express an inline ‘OR’ type clause in a query value by using a semicolon (";"):
api/my_table?where(user)=foo;bar - is the same as - api/my_table?where(user)=foo&or(user)=bar

To negate an inline ‘OR’ type clause, wrap your value in single quotes. In other words, to use a semicolon in a search:
api/my_table?where(user)='foo;bar'

You can express an inline ‘AND’ type clause in a query value by using a comma (","). Note that since the comma is used in a where-not query parameter, the inline ‘AND’ clause becomes an ‘AND’ not clause:
api/my_table?where-not(user)=foo,bar - is the same as - api/my_table?where-not(user)=foo&and-not(user)=bar

To negate an inline ‘AND’ type clause, wrap your value in single quotes. In other words, to use a comma in a search:
api/my_table?where-not(user)='foo,bar'


SQL Aliases

In order to use aliases, separate your field/function name and its alias with a tilde ("~"). Note that in order to run the same SQL function (via fn) multiple times, you must use aliases. See below.

The query api/v1/people_table?limit=1&fields=id,user_age will return:

{
    "id": 1,
    "user_age": 18,
    "apiPath": "api/v1/people_table/where(id)=1"
}

While the same query with aliases (api/v1/people_table?limit=1&fields=id~personId,user_age~age) will return:

{
    "personId": 1,
    "age": 18,
    "apiPath": "api/v1/people_table/where(id)=1"
}

The query api/v1/people_table?fn(avg)=height&fn(avg)=age&fn(sum)=age will return:
*Note that the height average gets overwritten by the age average since they share the same property name (avg).

{
    "avg": 29,
    "sum": 97,
}

While the same query with aliases (api/v1/people_table?fn(avg~avgHeight)=height&fn(avg~avgAge)=age&fn(sum~totalAges)=age) will return:

{
    "avgHeight": 5.9,
    "avgAge": 29,
    "totalAges": 97,
}

Samples

api

api/v1

api/v1/my_table

api/v1/my_table?fields=first&limit=null

api/v1/my_table?where(id)=3

api/v1/my_table?where(first)=John&fields=first,last

api/v1/my_table?where(first)=John&fields=first,last&limit=1&offset=2

api/v1/my_table?fn(count)=first

api/v1/my_table?where(first)=John&and(last)=Doe;Schmidt&fields=first,last


Examples

Select CU numbers from the CU table where the type is ‘SPR’ and the ‘show_in_library’ value is set to anything but true.
api/cu?where(type)=SPR&and-not(show_in_lib)=true&fields=cu_num

Select CU numbers from the CU table where the type is ‘SPR’ or the ‘show_in_library’ value is set to true.
api/cu?where(type)=SPR&or(show_in_lib)=true&fields=cu_num

Select CU numbers from the CU table where the type is ‘SPR’ or ‘CTV.’
api/cu?where(type)=SPR;CTV&fields=cu_num

Select ‘stateDMA’ and ‘09/15/14’ records from the booked-amount table where the ‘stateDMA’ has the value 504 in it.
api/ab_booked_amount?where-like(stateDMA)=%504%&fields=09/15/14,stateDMA

Count the data from the gb table where records are dated between ‘2014-01-01’ and ‘2014-12-31’.
api/gb?where-gte(timestamp)=2014-01-01&and-lte(timestamp)=2014-12-31&fn(count)=*

Return 20 ‘start-date’ records from the workflow-benchmarks table where the start date is ‘11/15/2014,’ the end date is ‘01/24/2015,’ and the calculation mode is ‘desktop.’
api/ab_workflowBenchmarks?where(calcMode)=desktop&and(dateStart)=11/15/2014&and(dateEnd)=01/24/2015=&fields=dateStart&limit=20

Return all records from the CU table where the ‘member’ name begins with ‘lsilva’ and the ‘type’ does not have terms like ‘Pre,’ ‘PR,’ and ‘iRoll.’
api/v1/cu?limit=null&where-like(member)=lsilva%&and-notlike(type)=%Pre%,PR%,%iRoll%

Describe the users table.
api/users?describe

Return multiple averages from the people table. Note that in order to run the same function multiple times, you must give each subsequent function name an alias.
api/v1/people?fn(avg)=age&fn(avg~avgHeight)=height&fn(avg~avgWeight)=weight

Count the distinct ‘age’ records from the people table.
api/v1/people?fn(count)=distinct age

Return a list of unique ‘type’ records along with a count of rows in each type.
api/v1/cu?limit=null&fields=type,count(type)~count&group=type


Quick Start

  1. Visit /api to see available versions.
  2. From there, select /api/v# to see available tables.
  3. Select a table to query and begin (api/v#/my_table).

Changelog

2015-07-29

2015-07-20

2015-07-10

2015-07-07