SQL to anything: documentation

s2x converts SQL functions calls to a variety of formats (JSON, XML, csv, Excel) and returns result sets over HTTP.

SQL function names and parameters are passed inside HTTP requests.

Multiple calls can be chained into one HTTP request to avoid database round-trips.

Endpoints

s2x provides the following endpoints that accept HTTP requests and each return a format type:

Create a SQL function in your database:

CREATE FUNCTION listProductNames() RETURNS SETOF varchar AS $$
   SELECT name FROM Products;
$$ LANGUAGE 'SQL';

Access the result set by calling a format-specific endpoint:

curl http://s2x.co/json?listProducts
{"listProducts":[
  {"uid":"9jw7OQy=SbuaiqxUb1TPGg","name":"Snow blower","price":"149.99"},
  {"uid":"50Ls1cj8QeGLfLiRBnQBCg","name":"Cordless drill","price":"17.99"}
]}

curl http://s2x.co/xml?listProducts
<sql2xml>
   <function name="listProducts" type="set"><set>
      <record>
         <value name="uid">9jw7OQy=SbuaiqxUb1TPGg</value>
         <value name="name">Snow blower</value>
         <value name="price">149.99</value>
      </record>
      <record>
         <value name="uid">50Ls1cj8QeGLfLiRBnQBCg</value>
         <value name="name">Cordless drill</value>
         <value name="price">17.99</value>
      </record>
   </set></function>
</sql2xml>

curl http://s2x.co/csv?listProducts
"uid","name","price"
"9jw7OQy=SbuaiqxUb1TPGg","Snow blower","149.99"
"50Ls1cj8QeGLfLiRBnQBCg","Cordless drill","17.99"

curl http://s2x.co/xlsx?listProducts

Calls

s2x calls are made by specifying the name of the s2x endpoint in the base url (call "to endpoint") or the file name extension of the document part of the base url (call "by file name extension").

Calls to endpoint

Create a URL by combining the following:

  1. Host name (e.g. http://s2x.co)
  2. s2x endpoint as the document name (e.g. json)
  3. SQL function name as the parameter name (e.g. listProductNames)
curl http://s2x.co/json?listProductNames
{"listProductNames":[{"name":"Snow blower"},{"name":"Cordless drill"}]}

Arguments to the function call can be passed as additional parameters (see Parameters below).

Multiple function calls can be chained together (see Chaining function calls below).

Calls by file name extension

Create a URL by combining the following:

  1. Host name (e.g. http://s2x.co)
  2. SQL function name as the file name part of the document name (e.g. listProductNames)
  3. s2x endpoint as the file name extension (e.g. xml)
curl http://s2x.co/listProductNames.xml
<sql2xml>
  <function name="listProducts" type="set"><set>
     <record>
       <value name="name">Snow blower</value>
     </record>
     <record>
       <value name="name">Cordless drill</value>
     </record>
  </set></function>
</sql2xml>

Arguments to the function call can be passed as additional parameters (see Parameters below).

Calls by file name extension only support the default result type (set for JSON and XML and table for csv and xlsx).

Calls by file name extension are implemented using mod_rewrite. If mod_rewrite is not installed or not enabled, only calls to the endpoint will work (e.g. only json?listProducts and not listProducts.json).

Result types

Databases generally return results as rows of data.

The s2x keywords set, s, table, tab, t, record, rec, r, value, val, v as the name of a HTTP parameter introduce the desired type of a function call.

set / s (default)

curl http://s2x.co/json?set=listProducts
curl http://s2x.co/json?s=listProducts

The result is an array of objects (records).

{"listProducts":[
  {"uid":"9jw7OQy=SbuaiqxUb1TPGg","name":"Snow blower","price":"149.99"},
  {"uid":"50Ls1cj8QeGLfLiRBnQBCg","name":"Cordless drill","price":"17.99"}
]}

set is the default result type for JSON and XML. If you do not specify a result type, it will be returned as set.

curl http://s2x.co/json?listProducts
curl http://s2x.co/listProducts.json

table / tab / t

curl http://s2x.co/json?tab=listProducts

The result is an array of arrays (rows). The first row (array) represents the column names.

{"listProducts":[
  ["uid","name","price"],
  ["9jw7OQy=SbuaiqxUb1TPGg","Snow blower","149.99"],
  ["50Ls1cj8QeGLfLiRBnQBCg","Cordless drill","17.99"]
]}

table is the only result type supported by the csv and xlsx endpoints. If you specify a different type for a call, it will be ignored.

curl http://s2x.co/listProducts.csv
curl http://s2x.co/listProducts.xlsx

record / rec / r

curl http://s2x.co/json?r=getRecommendedProduct

The result is a record.

{"getRecommendedProduct":{
  "uid":"9jw7OQy=SbuaiqxUb1TPGg",
  "name":"Snow blower",
  "price":"149.99"
}}

Returns an error if the result does not have exactly one row.

curl http://s2x.co/json?rec=listProductNames
{"listProductNames":{"error":"returning SET ( rows=3 ) in RECORD context"}}

value / val / v

curl http://s2x.co/json?value=getBestPrice

The result is a primitive type value.

{"getBestPrice":4.69}

Returns an error if the result has more than one row and/or more than one column.

curl http://s2x.co/json?val=listProducts
{"listProducts":{"error":"returning SET ( rows=3, columns=3 ) in VALUE context"}

Parameters

Arguments to a function call are passed as parameters of the HTTP request. Named parameters (name=value) are matched to the named argument in the function definition. Anonymous parameters are processed in sequence.

Named parameters

Named parameters are passed as name=value pairs. The database matches the name with the named argument in the function definition.

CREATE FUNCTION findUser( last_name varchar, country varchar )
RETURNS SETOF Person AS $$
   SELECT * FROM Person Person.last_name = $1 and Person.country = $2;
$$ LANGUAGE 'SQL';
curl "http://s2x.co/json?findUser&last_name=Harrington&country=USA"
{"findUser":[
  {"first_name":"Colton","last_name":"Harrington","country":"USA"},
  {"first_name":"Velma","last_name":"Harrington","country":"USA"}
]}

Named parameters are silently ignored when using a version of PostgreSQL earlier than 9.0. In this case, s2x processes them as anonymous parameters in the sequence in which they were sent.

Anonymous parameters

Anonymous parameters are passed as the value. The function call consumes them in the order of argument declaration (positional notation).

curl "http://s2x.co/json?findUser&Crawford&USA"
{"findUser":[
  {"first_name":"Joseph","last_name":"Crawford","country":"USA"},
  {"first_name":"Kelsey","last_name":"Crawford","country":"USA"}
]}

Chaining function calls

Multiple calls can be chained in a single HTTP request.

curl "http://s2x.co/json?rec=getProduct&50Ls1cj8QeGLfLiRBnQBCg&val=getBestPrice&set=listProductNames"
{
  "getProduct":{
     "uid":"50Ls1cj8QeGLfLiRBnQBCg",
     "name":"Cordless drill",
     "price":"17.99"
  },
  "getBestPrice":"4.69",
  "listProductNames":[
     {"name":"Snow blower"},
     {"name":"Cordless drill"}
]}

All functions other than the first one need an explicit qualifier (set/table/record/value), otherwise s2x will treat them as parameters, with effects that may surprise.

curl "http://s2x.co/json?getProduct&50Ls1cj8QeGLfLiRBnQBCg&getBestPrice"
{"getProduct":{"error":"function getproduct(unknown, unknown) does not exist"}}

Function calls will be made in the order they appear in the HTTP request. The first call that results in an error will stop subsequent calls from being made. The results of the previous calls will be returned.

Calling the same function multiple times in the same chain will work as expected, but a JSON result set may not be parsed properly by the client environment.

HTTP calls

GET

curl "http://s2x.co/json?rec=getProduct&9jw7OQy%3DSbuaiqxUb1TPGg"

All parameters need to be URL (percent) encoded. The following will fail with surprising results:

curl "http://s2x.co/json?rec=getProduct&jw7OQy=SbuaiqxUb1TPGg"
{"getProduct":"error":"ERROR:  function getproduct(jw7oqy := unknown) does not exist"}

s2x does not enforce the safe/idempotent recommendation for GET requests.

POST

s2x accepts URL encoded POST (application/x-www-form-urlencoded). All parameters need to be URL (percent) encoded Note %3D encoding '=' below.

curl -d rec=getProduct \
  -d uid=9jw7OQy%3DSbuaiqxUb1TPGg \
  -d val=getBestPrice \
  -d set=listProductNames \
  http://s2x.co/json

Multipart POST (multipart/form-data) is also accepted. Values don't need any encoding.

curl -F rec=getProduct \
  -F uid=9jw7OQy=SbuaiqxUb1TPGg \
  -F val=getBestPrice \
  -F set=listProductNames \
  http://s2x.co/json

For POST, all functions need an explicit type qualifier and all parameters need a name. Positional calls and anonymous parameters are not supported.

Only POST calls to endpoint are supported. POST calls by file name extension are not supported.

JSONP

s2x supports JSONP, for example, by using jQuery.ajax.

$.ajax({
  url:"json",
  dataType:"jsonp",
  jsonpCallback:"callbackFunction",
  cache:true,
  data:{"value":"getBestPrice"}
});

The response wraps the JSON content as an argument in a callback function:

callbackFunction({"getBestPrice":4.69});

In the example above, unless specifying cache=true, jQuery inserts a "cache buster" parameter in the request _:timestamp that will result in a s2x parse error.

Errors

Errors are reported back as fields of the JSON reponse.

Parsing or overall communication errors are reported as part of the response.

The following request fails when trying to call a function with a name starting with digits (123table). The error message also contains the portion of the input that has been parsed so far.

curl "http://s2x.co/json?getProduct&uid=50Ls1cj8QeGLfLiRBnQBCg&table=123table"
{
   "error":"Sorry. Cannot understand request."},
   "sofar":{"getProduct":{ "code":"50Ls1cj8QeGLfLiRBnQBCg"}}
}

Errors associated with a specific call will be returned as a field of that call.

The first call that results in an error will stop subsequent calls from being made. The results of the previous calls will be returned.

curl "http://s2x.co/json?v=getBestPrice&v=listProducts&r=getProduct&50Ls1cj8QeGLfLiRBnQBCg"
{
"getBestPrice":1203,
"listProducts":{
  "error":"returning SET ( rows=3, columns=3 ) in VALUE context"
}}

s2x traps exceptions raised by the database functions and treats them as errors. Use RAISE EXCEPTION in your database function to trigger an error.

IF NOT FOUND THEN
  RAISE EXCEPTION 'product not found';
END IF;
curl "http://s2x.co/json?v=getProduct&wrong_code"
{"getProduct":{"error":"ERROR:  product not found"}}

Exceptions raised by the database (e.g. unique violation) will also be passed through as s2x errors. These error messages are often cryptic. You may consider writing your code to avoid (trap) them explicitly.

Feature backlog