Data Access Service Reference

Basics

JSON

Plain JSON is chosen over JSON schema for describing the structure of service input, output and object for a couple of reasons:

There is absolutely zero learning requirement.
It is sufficient to describe the structure of the data object and the data type of the data fields.

Data access service (DAS) does not perform data validation. Instead, it leaves data validation to service client.

JSON Structure

DAS supports the following data structures inside the service input, output and object:

Item	Structure
*Input*	value field
	array of values
	object
*Output*	value field
	array of values
	object
	array of objects
*Object*	value field
	array of values
	object
	array of objects

Note: Array of array is not supported.

JSON Data Type

JSON supports three native data types: boolean, number and string. It is extended to support two additional types: datetime and binary. The chart below illustrates how these data types should be encoded in JSON string.

Data Type	Encoding	Examples
boolean	native	true, false
number	native	123, 1.23
string	native	"abc"
datetime	ISO 8601 datetime string	"2019-04-15T15:02 :54.123Z", "2019-04-15T00:00 :00.000Z"
binary	base64 encoded string	"QQ==", "#gghsdh="

The above applies both to the JSON data sent in the service request and response and to the sample JSON used to specify the service input, output and object.

Note: Use "QQ==" to specify a binary data field for service input.

JSON Path

DAS supports a tailed version of JSON path for identifying the data fields and structures of the service input, output and object in various data bindings. Specifically, it uses a dot "." to indicate an object structure and a double-dot ".." to indicate an array structure. Thus for the following sample structure:

{
    "myValueField": "abc",
    "myValueList": ["abc", "xyz"],
    "myObject": {
        "myObjectField": "abc"
    }
    "myArray": [{
        "myArrayField": "abc",
        "myChildArray": [{
            "myChildArrayField": "abc"
        }] 
    }]
}

we have:

Field Name	Field Path
myValueFiled	.myValueField
myValueList	.myValueList
myObjectField	.myObject.myObjectField
myArrayField	.myArray..myArrayField
myChildArrayField	.myArray..myChildArray..myChildArrayField

and

Object Name	Object Path
root	.
myObject	.myObject.
myArray	.myArray..
myChildArray	.myArray..myChildArray..

The field path ends with the field name. The object path ends with a dot ".", if an object, or a double-dot "..", if an array.

Database

DAS currently supports MySQL, PostgreSQL, Oracle and MS SQL Server databases.

MySQL

The chart below lists the MYSQL data types supported by DAS and how they are mapped to JSON data types.

JSON Data Type	MySQL Data Type
*boolean*	boolean
*number*	int
	tinyint
	smallint
	mediumint
	bigint
	float
	double
	decimal
*string*	char
	varchar
	text
	tinytext
	mediumtext
	longtext
*datetime*	date
	datetime
	timestamp
*binary*	blob
	tinyblob
	mediumblob
	longblob

PostgreSQL

The chart below lists the PostgreSQL data types supported by DAS and how they are mapped to JSON data types.

JSON Data Type	PostgreSQL Data Type
*boolean*	boolean
*number*	integer
	smallint
	bigint
	decimal
	numeric
	real
	double precision
	serial
	smallserial
	bigserial
	money
*string*	char
	varchar
	text
*datetime*	timestamp
*binary*	bytea

The date, time, interval and timestamptz types are not supported.

Oracle

The chart below lists the Oracle data types supported by DAS and how they are mapped to JSON data types.

JSON Data Type	Oracle Data Type
*number*	int
	number
	float
	decimal
*string*	char
	varchar2
	nchar
	nvarchar2
	clob
	nclob
*datetime*	datetime
	timestamp
*binary*	blob
	raw

SQL Servers

The chart below lists the SQL Server data types supported by DAS and how they are mapped to JSON data types.

JSON Data Type	SQL Server Data Type
*boolean*	boolean
*number*	tinyint
	smallint
	bigint
	decimal
	numeric
	money
	smallmoney
*string*	char
	varchar
	text
	nchar
	nvarchar
	ntext
*datetime*	date
	datatime
	datatime2
	smalldatatime
*binary*	binary
	nbinary
	image

Binary Data

For query service, binary data read from a binary column is returned as base64 encoded string.

For SQL service, binary data encoded as base64 string is converted into byte array before it is written into a binary column. In order for the service engine to recognize a binary data field, the field should have its valued specified as "QQ==" in the service input specification.

For example, we may have the following as the input specification of a SQL service:

{
    "id": 123,
    "myBinary": "QQ=="
}

For repository service, the service engine recognizes a binary column by the column data type.

Transaction

Database transaction for data access service is implicit and abstracted away from developer. Each service request is a unit of work and comprises a database transaction. To expand transaction scope, multiple command requests may be grouped together and sent in a batch request.

Security

Data access services are protected from SQL injection by default, as all SQL statements are predefined and parametrized. However, enabling substitution variables for a SQL service introduces a controlled risk to this service. To remediate this risk, the service engine

Limits the max length of the value of the substitution variable to 30 characters;
Validate the value of the substitution variable against the SQL key words, such as delete, update, etc., for any potential damages.

The user is recommended to further limit the length of the value of the substitution variable to the smallest number sensible for the application.

Service Request

The data access service is invoked by a HTTP service request for the service. The following is a sample service request for a hypothetic query service:

POST: /namespace/app/mod/getCustomers HTTP/1.1
Content-Type: application/json

{
    "state": "CA",
    "city": "Los Angeles"
}

The http method is always POST. The Content-Type header is application/json. The request body is the service input. The request URL is:

http://host/namespace/application-name/module-name/service-name

for query and SQL services, and

http://host/namespace/application-name/module-name/service-name/operation

for repository service, where operation is read, create, delete, update, save or merge.

The namespace is the workspace for devtime service and deployspace for runtime service.

Service Response

The data access service responds the service request with a service response. The following is a sample service response for a hypothetic query service:

HTTP/1.1 200 OK
Content-Type: application/json

[
    {
        "customerId": 123,
        "customerName": "John",
        "address": {
            "street": "123 main st",
            "city": "Los Angeles",
            "state": "CA"
        }
    },
    {
        "customerId": 456,
        "customerName": "Smith",
        "address": {
            "street": "456 grand ave",
            "city": "Los Angeles",
            "state": "CA"
        }
    }
]

The status is 200 if the service is successful, 500 if a server error, or 400 if a service exception.

For 200 status, the response body is the service output. Otherwise, the response body is an exception message, as in the following hypothetic error response:

HTTP/1.1 400 Bad Request

{
    "name": "InvalidInput",
    "type": "ServiceException",
    "message": "Input is invalid."
}

Batch Request

Service requests may be grouped together and sent as a batch with a batch request, as in the following:

POST: /namespace/app/generic/batch HTTP/1.1
Content-Type: application/json

[
    {
        "name": "query1",
        "service": "/namespace/app/mod/getCustomers",
        "input": {...}
    },
    {
        "name": "query2",
        "service": "/namespace/app/mod/getCountries",
        "input": {...}
    }
    ...
]

The request URL is:

http://host/namespace/application-name/generic/batch

It only contains the application name. The data source associated with this application will be used to execute the batch request.

The body of the batch request is the list of service requests grouped together. The name identifies a service request in the batch request. The service identifies the service to be executed with a format of

namespace/application-name/module-name/service-name[/operation]

The input is the service input to run the service.

The response to the batch request is as follows:

HTTP/1.1 200 OK

[
    {
        "name": "query1",
        "output": {...}
    },
    {
        "name": "query2",
        "output": {...}
    }
    ...
]