Basics

sandman2 exposes each table in your database as a resource in a RESTful API service. You access and manipulate resources by sending HTTP requests with various methods (e.g. GET, POST, DELETE, etc.). Here’s a quick list of common tasks and the HTTP method to use to complete them:

Retrieve all records in a table

In REST-terms, a table in your database represents a resource. A group of resources (i.e. data returned from a SELECT * FROM foo statement) is called a collection. To retrieve a collection of resources from your API, make a HTTP GET call to the resource’s base URL with a trailing /. The / indicates that this URL represents a collection rather than a single resource. By default, a resource’s base URL is set to /<table_name_in_lowercase>/. If you had an Artist table in your database, you would use the following curl command to retrieve the collection of all Artist resources:

$ curl http://127.0.0.1:5000/artist/

The implied HTTP method in this case is GET. The response would be a JSON list of all the artist resources in the database.

Possible HTTP status codes for response

  • 200 OK if the resource is found
  • 404 Not Found if the resource can’t be found

Retrieve a single row

To retrieve a single resource (i.e. row in your database), use the GET method while specifying the value of the resource’s primary key field. If our Artist table used the column ArtistId (an integer) as a primary key, we could retrieve a single resource like so:

$ curl http://127.0.0.1:5000/artist/3

Again, the implied HTTP method is GET.

Possible HTTP status codes for response

  • 200 OK if the resource is found
  • 404 Not Found if the resource can’t be found

Add a new row to a table

To add a resource to an existing collection, use the HTTP POST method on the collection’s URL (e.g. /artist/ in our example). All required fields should be sent as JSON data, and the Content-type header should be set to application/json. Here’s how we would create a new artist resource:

$ curl -X POST -d '{"Name": "Jeff Knupp"}' -H "Content-Type: application/json" http://127.0.0.1:5000/artist/

In this case, the primary key field (ArtistId) was not sent, since it is an auto-incremented integer. The response shows the assigned ArtistId:

{
    "ArtistId": 276,
    "Name": "Jeff Knupp"
}

We know based on our knowledge of how sandman2 works that this new resource can be retrieved at /artist/276 (i.e. <resource name>/<primary key value>). If we didn’t know how sandman2 worked, however, how would we know where the new resource was located? The Link HTTP response header always indicates the location a resource can be reached at, among other things.

Possible HTTP status codes for response

  • 201 Created if a new resource is properly created
  • 400 Bad Request if the request is malformed or missing data

Error conditions

If we send a field not in the record’s definition, we are alerted by sandman2 in the HTTP response:

$ curl -X POST -d '{"Name": "Jeff Knupp2", "Age": 32}' -H "Content-Type: application/json" http://127.0.0.1:5000/artist
{
    "message": "Unknown field [Age]"
}

Similarly, if we miss a required field, sandman2 helpfully lets us know which field(s) we missed. Imagine we had an Album table that contains albums for each artist. Each row has the album’s title in the Title column and the associated Artist’s ArtistId in the ArtistId column. If we try to create a new album with only a Title set, the following is returned:

$ curl -X POST -d '{"Title": "For Those About To Rock We Salute You"}' -H "Content-Type: application/json" http://127.0.0.1:5000/album
{
    "message": "[ArtistId] required"
}

Delete a single row from a table

To remove a resource from a collection, use the HTTP DELETE method while specifying a value for the primary key field:

$ curl -X DELETE http://127.0.0.1:5000/artist/276

Possible HTTP status codes for response

  • 204 No Content if the resource was found and deleted
  • 404 Not Found if the resource could not be found

Update an existing row

To update a row using the so-called “delta”, i.e. just the fields that must be changed, send an HTTP PATCH request to the service. To change the ArtistId associated with an album, you could send the following PATCH request:

$ curl -X PATCH -d '{"ArtistId": 3}' -H "Content-Type: application/json" http://127.0.0.1:5000/album/6

This updates the Album with ID 6 to refer to the Artist with ID 3.

Possible HTTP status codes for response

  • 200 OK if the resource was found and updated
  • 400 Bad Request if the request is malformed or missing data
  • 404 Not Found if the resource could not be found

“Upsert” a row in a table

Some database engines support an “upsert” action where a full row is provided, including a value for the primary key. If no record with that primary key exists, the row is inserted as normal. If there is an existing row with the same primary key value, the operation is changed to an “update”, and the existing row is updated with the new values.

The HTTP PUT method works in much the same way. A full copy of a resource is sent in the request. The primary key value is determined by the URL the request is sent to (i.e. a PUT to /artist/3 implies an ArtistId of 3). Any existing resource is overwritten with the new values.

An important property of the HTTP PUT is idempotency. An idempotent operation always gives the same result, regardless of how many times or in which order it is applied. You can always be sure of the state of a resource after a successful PUT request.

Possible HTTP status codes for response

  • 200 OK if the resource was found and updated
  • 201 Created if the resource was not found and a new resource was created
  • 400 Bad Request if the request is malformed or missing data

Searching, Filtering, and Sorting

Filtering

One can filter the resources that will be returned from a GET request for a collection. Filtering is done on the value of one or more of the fields in a resource. For example, suppose we had a Person resource with first_name, last_name, and age fields. To request only the resources where the person’s first_name is “Jeff”, you would make an HTTP GET request to the following URL:

/person/?first_name=Jeff

Notice that person is followed by a /, indicating it is a collection and not a resource. first_name is simply set as a URL parameter with the value set to “Jeff”.

Combining filters

When more than one filter is specified on a request, the filters are combined and taken to be a set of clauses joined by AND. That is, a resource must match all filters to be returned, not just one. This behavior is useful for further refining results:

/person/?first_name=Jeff&last_name=Knupp

A GET request to this URL will return only the resources where first_name is “Jeff” and last_name is “Knupp”. We could also specify an age to filter on, though only exact matches (e.g. age=33 vs age<40) are currently supported for non-text fields.

Filtering text fields with pattern matching

Suppose we want Person resources not with a specific first_name, but where the first_name begins with J. We can specify a “like” parameter (named after the SQL LIKE keyword) by sending a GET request to:

/person/?%name=J%%

(Note that the % character must be URL-escaped)

The double % s mean “match any series of characters”, so our filter is “first_name starting with J and followed by any series of characters.”

Sorting

The last type of operation that controls how/which resources are returned from a collection is sorting. A collection can be sorted by any field on the resource