Data Integration

RESTful servers are a compelling architecture because they are network aware. They are a great way to connect mobile devices and Web apps. They also provide Web Services: a great way to perform data integration functions.

Such Web Services exchange data in real time. This is in contrast to previous batch-oriented approaches, which relied upon Extract, Transfer, and Load (ETL).
Live API Creator includes services that facilitate data integration, ranging from integrating retrieval over multiple data sources to synchronizing updates. This page outlines these services and illustrates several examples.

Key Services

The following key services are useful in addressing common patterns for data integration:

Custom Resources

Custom resources are a key service for integration, both for incoming and outgoing requests, providing declarative services for name mapping and nested objects.

Resources for Name Mapping

One of the key requirements of data integration is Name Mapping, or transformations. Different systems name the same data differently (for example, CustomerId vs AccountNumber). Such mapping is required for receiving or sending data.

You alias the names of tables and columns used to construct a resource end point using custom resources. Live API Creator provides the services to return GETs in this format, and update processing (PUT, POST, and DELETE) to "de-alias" the resources so that they can share business logic.

A common pattern here is Web Hooks. You can register custom resources as the handler for such posts, where the name mapping matches the Web Hook definition.

Multi-Table Resources

Systems typically need to exchange a set of related data, often referred to as a business object (for example, Order Header + Line Items + Shipment details). You define such objects, including multiple databases, by creating automated SQL-based sub-resources.

You can also create sub-resources based on other data source types, such as Mongo, JavaScript APIs (for example, to other RESTful servers), and custom SQL.

For more information:

Alternate entry points

In many cases, Custom Resources can directly process in-coming requests (step 1 in the diagram above). However, sometimes you must transform the data, or poll for it.

Custom Endpoints - Physical Tranforms

If the payload data is in a format not easily matched by a Custom Resource, you can define a Custom Endpoint:
  1. Access the request data, and reformat it in a suitable manner
  2. Use a Rest Utility (described below) to RestPost the reformatted request data to a Custom Resource.


Reactive logic "fires" only in response to requests. It requires a "triggering event". If there is no initiating call, you need to poll for the data and then RestPost the result.

Polling can take many forms. Some systems provide support for polling ("call me every minute, and I'll tell you what has changed"), whereas others require you to read the data periodically.

For periodic polling, you need to "wake up" to issue the reads. One way to do this:
  1. Set up a cron job that inserts a row into a cron_table.
  2. Provide logic (e.g., an event) on cron_table insert to read the data, and RestPost.

JSON Meta Data Logic, e.g., Merge Insert

You can designate that post operations perform merge operations, such as when receiving data from an external system that may or may not already exist locally. Or, you can perform lookup operations to de-reference Natural Keys into IDs.

For more information about merge operations, see Merge Insert. Lookup and Merge Insert are part of Complex Transaction support.

Business Logic

Business Logic is generally regarded as the code you write that retrieves data, and processes updates. This involves not only the SQL, but also change propagation (e.g., paying an order reduces the customer balance).
API Creator provides row events that are triggered during GET operations and various forms of Update Logic including update events. These events are often used to invoke the APIs.

For more information:

Request Objects (the Request Pattern)

Received data often maps (typically via a Custom Resource) to database tables whose logic Live API Creator manages, but this is not always the case. A good practice is to create a Request Table that stores the posted data. This has two advantages:
  1. You can place logic on the Request Table to process the request (e.g., use REST Utilities to integrate with other systems, described in the next section)
  2. The Request Table inherently keeps a record of requests, for auditing.
A pattern we have seen often is to use the Request Table to store the raw JSON, and then:
  1. Move it into domain objects. This may fail if, for example, the data is garbled and the data cannot even be converted (e.g., alpha characters instead of numbers). Request Objects allow you to trace back to the original submitted data.
  2. The data might convert properly, but be invalid (e.g., it's a number, but it's too high). In that case:
    1. Store the data as received, but mark it as Not Ready.
    2. Create a manual process to repair the data (perhaps send an email).
    3. When the data passes your logic, set the Ready flag to ok, and use this to trigger the integration logic.

Persistence-based integration

It's easy to imagine integration as simply message formatting and routing.  It is often more complicated, requiring state.

For example, generating an outbound message may require the consolidation of multiple inbound messages.  Not only may this require the Request Pattern, but you may often find it necessary to use additional logic to consolidate multiple messages.  

For example, you might employ Managed Parent to accrue multiple requests.  In such cases, the Managed Parent object has logic that determines when to issue the outbound request.

    REST Utilities

    When mapping to integration systems that are managed by existing business logic, you should not access the data directly. This bypasses business logic (much like accessing Live API Creator-managed data by direct SQL). A better approach is to use RESTful APIs, if they exist. You can invoke RESTful services in other API servers (not necessarily API Creator servers) by enabling the REST utilities. You can issue such calls from reactive logic, or row events.

    The REST Utilities include:
    • SysUtility.restGet(url, params, settings)
    returns a Stringified version of the result of the GET to the specified url.
    • SysUtility.restPut(url, params, settings, requestData)
    returns a Stringified version of the response of a PUT to the specified url using the requestData (perhaps obtained through SysUtility.restGet).
    • SysUtility.restPost(url, params, settings, requestData)
    returns a Stringified version of the response of a POST to the specified url using the requestData (perhaps obtained through SysUtility.restGet).

    For example, in the Business to Business example:
    var settings = { headers: { Authorization: "CALiveAPICreator supplier:1" }};
    var pavlov_response =  SysUtility.restPost(url, null, settings,

    • SysUtility.restDelete(url, params, settings)
    returns a Stringified version of the response of a DELETE to the specified url.

    Settings is an option JavaScript object. Only an id "headers" is examined. Any other ids are ignored.
    var settings = {
        "headers" : {
           "Authorization" : "Basic base64string",
           "Cache-Control" : "no-cache"

    For more info see Wikipedia Entry to for HTTP Headers.
    • SysUtility.getResource(resourceName, options)
    returns a Stringified version of the response to ResourceName (a Custom Resource created in your project).

    Note: unlike the services above, this operates on your Resources in your current project.

    options typically specifies your filter criteria, for example:

    var options = {sysfilter: "equal(OrderID:" + row.OrderID + ")"
                "equal(CompanyName: '" + "Pavlova, Ltd." + "')" };
    options can also specify pagesize, offset and chunksize. These are normally defaulted (so not required, shown here in case you need them):

    var details = {
        filter: "name like 'ABC%',
        order: "name asc",
        pagesize: 20,
        offset: 0,
        chunksize: 10

    For example, in the B2B Sample, we create a instance of the SupplierAlert resource (to match the API agreement with our Supplier Pavlov), as follows:

    var supplierReport_response = SysUtility.getResource("SupplierAlert", options);


    Here are some common scenarios we have seen. The teal node denotes API Creator, the other nodes are other systems, which may be Enterprise Service Bus products, other API Creator Servers, NoSQL machines, etc.
    GET-triggered Integration
    The simplest examples are triggered by GET requests.

    Restify a SQL Database to a desired format

    Here, a partner's application server needed to deal with many different databases, each with their own similar but unique schema. The app server coded wanted identical JSON for each, so adding a new database would not require any code changes.
    Their solution was to declare an API Creator Server for each database, where Resources were defined on each Schema to match a canonical format. This required no code changes on either the app server or each database - a resource definition was all that was required.

    Mongo Integration

    You may need to integrate Mongo and SQL data into a common mashup API. Direct Mongo support is described here.
    The next section addresses mashups with general RESTful servers.

    get mashup

    A common requirement is to enable a client to issue a get where the results return data from both a local database, as well as another RESTful service:
    You can specify JavaScript SubResources that can materialize subresources by invoking a RESTful service:
    In this particular example, the called RESTful service is another API Creator instance (from Demo), illustrating you can use API Creator as both a client (per the code above) and as a server (the called instance). There are no restrictions. The called service can be any RESTful server, such as Mongo and Enterprise Data Sources.
    You can compute data and add new attributes using row events.

    Time-triggered Integration

    The following upsert example illustrates a "push" from a related system. At time, you need to poll and "pull" the data you need, periodically. One common approach is to create a Cron job (outside API Creator), and issue RESTful requests to API Creator.

    Web Hooks

    A common pattern for enabling systems integration is to provide Web Hooks:
    • The originating system provides a mechanism to register listeners to various update actions

    • Such registrations often provide options to implement the listeners as a Post request (i.e., specify URL for post when event occurs)
    You can define Resources to match the Web Hook format, register them as Web Hook Listeners, and process posts as described below in Update-triggered integration, also illustrated in the B2B.

    Custom Endpoints can address authorization, formatting

    Web Hooks might require different Authentication techniques than API Creator, or deliver JSON in expected formats.  You can address both issues by providing a Custom Endpoint.

    Update-triggered integration

    It is also common that updates (put, post, delete) might trigger integration. You can use reactive logic update events to address these.

    upsert External Request

    Your RESTful service may need to process data submitted by another service, perhaps a Master Data node. It may also be required that the Master dictate the names of the submitted objects/attributes:
    You can define resources whose aliased names match the partner system.
    This scenario is also common in a Master Data Management configuration, where the Master may send changes to subscribing systems. It is often the case that the submitted data may - or may not already exist in your site. API Creator therefore supports the Merge Insert functionality, where the posted data may be regarded as either an insert or is merged in as an update.

    post to External System

    Your service may need to publish JSON changes to other systems, in the form of multi-table business objects whose names match the target system.

    A good way to approach this is:
    1. Define a Resource whose aliased table/attribute names match the target (cust in the following example).
    2. Provide an update event that invokes getResource to create a JSON string for this resource, and send it to the target (e.g. as a post).

    get External Data for current transaction

    Your business logic may need to:
    1. Obtain data from an external system in order to process a transaction.
    2. Transform the external data into a local names.
    3. Store the external data, including transformations / derivations (e.g., sums, counts).
    4. Use the resultant local data in the current transaction (e.g., for validation).
     A reasonable approach would be:
    1. Define a resource R whose alias names match the external system. 
    2. Use restCaller to get the external data.
    3. Use resourcePost to post this data into R.

    Integrated Example

    JavaScript events can call local and remote REST services. This enables you to integrate multiple systems together. In the example below:
    1. Partner Request: a Partner sends an Order to us - a RESTful POST.
    2. Compliance Message: we process it, where part of the business logic detects that the order includes a controlled substance which requires we send a Controlled Substance Message to a Regulatory Agency.
    3. Partner Response: the Return Response to the Partner is an Account Summary. This is in lieu of our normal transaction summary, which is intended for client refresh processing.

    Live API, operating in conjunction with Live Logic, enables us to process this entire transaction with several rules and the Live API JavaScript below - about 20 lines of JavaScript. API Creator provides all the other REST/ SQL handling.
    It would also be invoked as a consequence of an interactively entered order.
    The logic is not coded into some button's controller in a mobile/web app. The logic is automatically partitioned for re-use. In many Web apps, logic is "buried in the buttons", and therefore cannot be shared by mobile apps, called as a service by other systems/partners. Logic for data access and security/integrity enforcement is automatically partitioned to the API Server, so that it can be properly shared. Row-level security is delegated to the DBMS for optimization.

    Partner Request

    This is a simple RESTful request, such as POSTing an order and 2 items. Here, we POST the following JSON in the Logic Sample database to REST end-point cust.orders_c:

     "customer": "Max Air",
     "IsReady": true,
     "items_o": [
        "ProductName": "Dynamite",
        "QuantityOrdered": 1
        "ProductName": "Hammer",
        "QuantityOrdered": 1
    In later versions of Logic Sample, you can also post this to PartnerOrder:

     "customer": "Max Air",
     "isReady": true,
     "LineItems": [
        "productName": "Dynamite",
        "quantityOrdered": 1
        "productName": "Hammer",
        "quantityOrdered": 1

    Compliance Message

    Certain substances are controlled, and require regulatory reporting. We can define an event on the products table using API Creator. The following image shows an event in API Creator:

    For simplicity, this logs the message instead of sending it; look for a log entry like (ensure your API Keys debug setting are correct):
    ***sending message

    This logic is invoked by the Logic Engine as a consequence of processing the order, as follows. Since we are POSTing the order as IsReady=true, this invokes the Make Ready logic; the event is triggered as a consequence of the adjustment to the Product.totalQtyOrdered.

    Partner Response

    The normal response JSON is the transaction summary. But in this case, we want to reformat the response per our understanding of our partners' message formats - object and attribute names.
    1. Define a RESTful resource that maps our tables onto their names, using Resource aliases to provide the proper names. 
    2. Define a Request Event, where we can override the normal response, like this:
    You can see the accountSummary field (added in the last line, above) in the response JSON, shown in the first screen shot above.