Skip to content

Data Access Service Reference Guide

Repository Service

The repository service is for CRUD operations of aggregate objects. Unlike the query and the SQL service, the repository service supports multiple operations, including

  • read, for retrieving objects
  • create, for creating object in database
  • update, for updating object in database in its entirety
  • delete, for deleting object from database
  • save, for creating or updating object in database, and
  • merge, for merging object changes into database.

The repository service comprises:

  • an object
  • a read component, and
  • a write component

The read component is a dynamic query for the object, comprising:

  • an input for read
  • a query for read
  • an input bindings component that maps the query parameters to the input fields, and
  • an output bindings component that maps the query columns to the object fields

The output of the read operation, by definition, is always an array of the repository objects, even for read by id. In this case, it returns an array of a single object if the object exists, and an empty array if not.

The write components comprises:

  • table bindings that map the root object and its child structures to the root and child database tables, respectively, and
  • columns bindings that map the columns of a mapped table to the data fields of the repository object.

File Structure

The following is the file structure of repository service:

myRepositoryObject
    service.json
    object.json
    read/
        input.json
        query.sql
        input-bindings.json
        output-bindings.json
    write/
        tables.json
        my-root-table.table-alias.columns.json
        my-child-table.table-alias.columns.json
    tests/
        testReadMyRepositoryObject.json
        testCreateMyRepositoryObject.json
        testUpdateMyRepositoryObject.json
        testDeleteMyRepositoryObject.json

This file structure is generated when the service is created. TThere is a file for each component of the repository service. The user is expected to complete the object component and all sub-components of the read and write components.

Example

The following is an exemplary repository service for an Order object.

Object

{
    "orderNumber": 23,
    "orderDate": "2021-01-01T00:00:00.000Z",
    "customerNumber": 12,
    "customerName": "John",
    "orderLines": [{
        "orderLineNumber": 123,
        "productCode": "PC",
        "productName": "Computer",
        "quantityOrdered": 10,
        "priceEach": 599.00
    }]
}

The repository object is generally a complex object with nested structure.

Input

{
    "orderNumber": 1,
    "customerNumber": 2,
    "startDate": "2021-01-01T00:00:00.000Z",
    "endDate": "2021-02-01T00:00:00.000Z"
}

The input is a simple object carrying the query parameters for read operation.

Query

select o.orderNumber, o.orderDate,
       _c.customerNumber, _c.customerName,
       od.orderLineNumber, _p.productCode, _p.productName, 
       od.quantityOrdered, od.priceEach
  from classicmodels.orders o
  join classicmodels.customers _c on o.customerNumber = _c.customerNumber
  left join classicmodels.orderdetails od on od.orderNumber = o.orderNumber
  left join classicmodels.products _p on _p.productCode = od.productCode
 where 1 = 1
   and o.orderNumber = :orderNumber
   and o.customerNumber = :customerNumber
   and o.orderDate between :startDate and :endDate
 order by o.orderNumber, od.orderLineNumber

This query is a single SELECT statement for read like the query service. However, it must follow the rules for repository query to be discussed below.

Input Bindings

[
    {
        "parameter": "orderNumber",
        "field": ".orderNumber"
    },
    {
        "parameter": "customerNumber",
        "field": ".customerNumber"
    },
    {
        "parameter": "startDate",
        "field": ".startDate"
    },
    {
        "parameter": "endDate",
        "field": ".endDate"
    }
]

The input bindings map the query parameter to the read input field.

Output Bindings

[
    {
        "column": "orderNumber",
        "field": ".orderNumber"
    },
    {
        "column": "orderDate",
        "field": ".orderDate"
    },
    {
        "column": "customerNumber",
        "field": ".customerNumber"
    },
    {
        "column": "customerName",
        "field": ".customerName"
    },
    {
        "column": "orderLineNumber",
        "field": ".orderLines..orderLineNumber"
    },
    {
        "column": "productCode",
        "field": ".orderLines..productCode"
    },
    {
        "column": "productName",
        "field": ".orderLines..productName"
    },
    {
        "column": "quantityOrdered",
        "field": ".orderLines..quantityOrdered"
    },
    {
        "column": "priceEach",
        "field": ".orderLines..priceEach"
    }
]

The output bindings map the query column to the object fields.

Table Bindings

[
    {
        "name": "classicmodels.orders",
        "alias": "o",
        "object": ".",
        "rootTable": true,
        "mainTable": true,
        "operationIndicator": null,
        "columns": "./classicmodels.orders.columns.json"
    },
    {
        "name": "classicmodels.orderdetails",
        "alias": "od",
        "object": ".orderLines",
        "rootTable": false,
        "mainTable": false,
        "operationIndicator": null,
        "columns": "./classicmodels.orderdetails.columns.json"
    }
]

The following summarizes the properties of the table binding.

Property Description Generated by Service Builder User Editable
name name of table Yes No
alias alias of table Yes No
object path of root or child structure Yes if match found Yes
root table whether the root table Yes No
main table whether a main table Yes Yes
operationIndicator name of object field indicating merge operation No No
columns reference of column binding file Yes No

The table bindings map the root object and its child structures to the database tables. In this example, the root object order is mapped to the classicmodels.orders table, and the child array orderLines is mapped to the classicmodels.orderdetails table. The table bindings specifies what tables shall be inserted with what objects/arrays, when the object is created. The order of inserts is dictated by the order of tables in the table bindings.

Column Bindings

There is a column bindings component for each of the mapped tables, which include the orders and the orderdetails tables in this case. However, only an abbreviated version of the column bindings for the orders table is shown here for brevity. 

[
    {
        "position": 1,
        "column": "orderNumber",
        "field": ".orderNumber",
        "key": true,
        "autoGenerate": false,
        "inputField": ".orderNumber",
        "insertValue": null,
        "updateValue": null,
        "version": false,
        "softDelete": false,
        "dataType": "number",
        "notNull": true,
        "keyEligible": false,
        "versionEligible": true,
        "softDeleteEligible": false
    },
    {
        "position": 2,
        "column": "orderDate",
        "field": ".orderDate",
        "key": false,
        "autoGenerate": false,
        "inputField": null,
        "insertValue": null,
        "updateValue": null,
        "version": false,
        "softDelete": false,
        "dataType": "datetime",
        "notNull": true,
        "keyEligible": false,
        "versionEligible": false,
        "softDeleteEligible": false
    },
    ...
]

The following summarizes the properties of the column binding.

Property Description Generated by Service Builder User Editable
column name of column Yes No
field path of field mapped to the column Yes, if match found Yes
key whether a key column Yes, if primary key defined for table Yes
autoGenerate whether an auto generate column Yes No
inputField path of input field mapped to the key column. Specify if you want the write operations to read back updated objects No Yes
insertValue insert value for the column No Yes
updateValue update value for the column No Yes
version wether a version control column No Yes
softDelete wether a soft delete column No Yes
Meta Properties:
position column position in the table Yes No
dataType target json data type Yes No
notNull whether null value allowed Yes No
keyEligible whether eligible as a key column Yes No
versionEligible whether eligible as a version column Yes No
softDeleteEligible whether eligible as a soft delete column Yes No

The columns bindings map the object field to the columns of a table. It specifies how a row should be inserted and updated.

When insert, the value of a column is from:

  • database if an auto-generate column
  • the insertValue if specified. The insertValue should be a DB expression
  • the object field if mapped and the field exists in the input
  • otherwise, the column is excluded from the insert statement if none of the above is true.

When update, the value of a column is from:

  • the updateValue if specified, the updateValue should be an DB expression
  • the object field if mapped and the field exists in the input
  • otherwise, the column is excluded from the update statement if none of the above is true.

Both update and delete are by the key. The key is always the primary key of the table. However, if in the rare case where no primary key is defined, the user needs to select one or more columns as the key by setting their key properties to true.

The repository service supports optimistic locking with a version column. The version column is designated by setting its version property to true. The version column must be a number column. The versionEligible property indicates if the column is a fit for version control.

The repository service supports soft delete. The soft-delete column is designated by setting its softDelete property to true. The soft-delete column must be a character column. repository service updates this column to 'Y' when delete occurs.

Read-Write Asymmetry

The read and write operations are often asymmetric, meaning that the read operations may read from more tables than the write operations write into.

For example, in the order example above, the read operations read from four tables: orders, orderdetails, customers and products, but the write operations only write into the orders and orderdetails table.

The customers and products tables in this case are read-only, and are called reference table, as they are referenced by other tables.

Root, Child and Reference Table

The root table stores the root object. The child tables store the data that depends on the root object. The reference tables are read-only, with a different life-cycle than the root object. Table bindings are not generated for the reference tables.

In the above example, orders is the root table, orderdetails is a child table, and customers and products are reference tables.

Main Table

The root table is usually the main table, or the top parent table for the aggregate object, referenced by the child tables. However, there are cases where a child table is referenced by the root table. For example, we may have a root table customers referencing the child table addresses. When creating the following Customer object:

{
    "customerNumber": 123,
    "customerName": "John",
    "address": {
        "addressId": 123,
        "street": "123 Main st",
        "city": "Los Angeles"
    }
}

We need to config the addresses table to be the main table, so that a record is inserted into this table before the record for the root table.

Repository Query

The query for repository read operation follows a few rules as listed below:

  • The query should an an ANSI query, with the root table being the only table in the FROM clause, like classicmodels.orders in the example above;
  • The child tables shall be added using ANSI left join, like classicmodels.orderDetails in the example above, in the order that the records are to be inserted when creating the object
  • The reference tables shall be added using ANSI inner or left join as appropriate, like classicmodels.customers and classicmodels.products in the example above;
  • All table should be aliased, like o for the classicmodels.orders table in the example above;
  • The alias for the reference table should start with "_", like _p for the classicmodels.products table in the example above;
  • All table columns from the root and child tables should be prefixed with the respective table alias;
  • The WHERE clause may need to include 1 = 1 as a base condition to support readAll operation, as the query for read is dynamic by definition.

Database views can only be used as reference tables in repository query.

Repository Operations

The repository service is a multi-operation service. The chart below lists the supported operations, and the input and output for each operation:

Operation Input Output
read input array of objects
create object or objects created object or objects
delete object or objects none
update object or objects updated object or objects
save object or objects saved object or objects
merge object or objects updated object or objects

For write operations, the objects can be batched. For delete operation, the object only needs to contain the key fields of the root object. The write operation returns null by default, unless the inputField property is specified for the key columns in the column bindings.

The inputField is the field path to the key field in read input. The repository service retrieves the update object from the database using the readByKey query, which is assumed to be supported by the read component of the service.

Read

The read operation is a query for the object. The input is an instance of the input object; the output is an array of the objects.

The query is dynamic. In the example above, if the input is

{}

it is a query for all objects. If the input is

{ "orderNumber": 123 }

it is a query by order number. If the input is

{ 
    "customerNumber": 123, 
    "startDate": "2021-01-01T00:00:00.000Z",   
    "endDate": "2021-02-01T00:00:00.000Z"
    }

it is a query by customer number and date range.

Create

The create operation creates an instance of the aggregate object, including the root object and its children, in the database. The input is the object to be created, and the output is the object created. However, the input may also be an array of the objects in the service request, and the output will be an array of the objects created in this case.

Delete

The delete operation deletes the aggregate, including the root object and its children, from the database. The input is the object or the array of objects to be deleted, the output is none. The delete is by the key of the root object/table, and it is sufficient to include only the key fields of the object in the service request.

Update

The update operation updates the aggregate, including the root object and its children, in the database. The input is the object to be updated, and the output is the object updated. However, the input may also be an array of the objects, and the output will be an array of the objects updated in this case.

The update is done by the key of the root object/table. The object that is not found and thus not updated will be returned as {} in the output.

In this operation, the old version of the object will be replaced by the given version in its entirety. As part of this process, the child objects of the aggregate may be created, updated or deleted from the database, to sync the stored object with the given object.

Save

The save operation is a create operation if the object does not yet exist or an update operation if the object already exists in the database.

Merge

The merge operation is an experimental feature that merges the changes to an aggregate into the current version in the database. The input is the object that carries the changes, and the output is the merged object. The input may also be an array of objects in the service request, and the output will be an array of merged objects in this case.

The input object should only include the fields that are changed. For arrays, an operation indicator fields should be included with the element to indicate the type of operation to be performed for the element. The valid values include: create, update or delete.

The name of operation indicator field is specified in the table bindings. The operation indicator field itself will not be persisted. Its sole purpose is to indicate the type of change.