Creating APIs‎ > ‎Integration‎ > ‎

Integrate Systems and Data

RESTful servers are a compelling architecture because they are network aware. They are a great way to connect mobile devices and Web applications.

They also provide Web services, which is a great way to perform data integration functions. These Web services exchange data in real time. This is in contrast to previous batch-oriented approaches, which relied upon Extract, Transfer, and Load (ETL). The following diagram illustrates real-time integration:

You can integrate data, ranging from integrating retrieval over multiple data sources to synchronizing updates using Live API Creator integration services. This page outlines these services and illustrates examples of the integration.

Common Patterns for Data Integration

The following integration services address common patterns for data integration, as illustrated in the following diagram:

Custom Resources

You can enable systems integration using custom resources. Use custom resources for incoming and outgoing requests. They provide declarative services for name mapping and nested objects.

For more information:
  • About custom resources, including update processing by way of the JavaScript object model, see Customize your API.
  • About using custom resources as transformation definitions (for example, in event handlers), including the logicContext object's transformCurrentRow method, see The logicContext Object.

Resources for Name Mapping

You can integrate the same data from different systems (for example, CustomerId vs AccountNumber) and receive or send data by providing name mapping, or transformations. You alias the names of tables and columns used to construct a custom resource, or endpoint. You can return GETs in this format and "de-alias" the resources so that they can share business logic by using update processing (PUT, POST, and DELETE).

A common pattern for enabling systems integration is to provide WebHooks. You can register custom resources as the handler for such posts, where the name mapping matches the WebHook 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 these 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 incoming requests (step 1 in the previous diagram). However, sometimes you must transform the data or poll for the data.

Custom Endpoints - Physical Transforms

If the payload data is in a format not easily matched by a custom resource, you can define a custom endpoint that can:
  • Access the request data and reformat it in a suitable manner.
  • Use a REST utility 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, poll for the data and then RestPost the result. Polling can take many forms. Some systems can poll for the data ("call me every minute, and I'll tell you what has changed"), whereas others require you to read the data periodically.

For periodic polling, issue the reads by "waking up". One way to do this is by setting up a cron job that inserts a row into a cron_table and then providing logic (for example, an event) on cron_table insert to read the data, and RestPost.

JSON Meta Data Logic

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 de-reference natural keys into IDs by performing lookup operations.

For more information:

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 (for example, paying an order reduces the customer balance).

You can create row events that API Creator executes during GET operations. API Creator automatically invokes event logic as it processes updated requests. Row and update events are often used to invoke the APIs.

For more information:

Request Objects (the Request Pattern)

Received data often maps (typically by way of a custom resource) to database tables whose logic 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 the following advantages:
  • You can place logic on the request table to process the request (for example, use REST Utilities to integrate with other systems, described in the next section)
  • The request table inherently keeps a record of requests, for auditing.
The following is a typical pattern:
  1. Store the raw JSON using the request table.
  2. Move the request table into domain objects. This may fail if, for example, the data is garbled and the data cannot be converted (for example, alpha characters instead of numbers). You can trace back to the original submitted data using the request object. The data might convert properly but be invalid (for example, 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.
For more information:

Persistence-based Integration

It is 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 consolidate multiple messages using additional logic. For example, you might accrue multiple requests by employing managed parent. In such cases, the managed parent object has logic that determines when to issue the outbound request.

For more information about managed parent, see Managed Parent Rule Type.

    REST Utilities to invoke APIs from other systems

    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 these calls from reactive logic, or row events.

    The REST utilities include:
    • SysUtility.restGet(url, params, settings)
    This method 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, but 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 Business to Business Example, we create an instance of the SupplierAlert custom resource (to match the API agreement with our supplier Pavlov), as follows:

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

    Custom Resources as Transformation Definitions

    System integration often requires that you Post requests to other systems. This posted data may require transformation logic:

    • Choose only specific attributes (for example, not send Employee Salary information).
    • Alias their names (for example, use agreed-upon names rather than schema names).
    • Include related data (for example, an Order with its Order Details).

    This kind of transformation logic is tedious to code and is not transparent to business users and API partners.

    You can integrate the same data from different systems (for example, CustomerId vs AccountNumber) and receive or send data by providing name mapping, or transformations. Using custom resources as transformation definitions satisfies all of the requirements. Custom resources also provide Swagger documentation that is available to business partners. The logicContext object's transformCurrentRow method is typically called in a table event to transform a table row to a resource instance.  This provides transformation logic to select/rename attributes and related objects.

    For more information:

    1. Define a custom resource that defines your transformation logic.
    2. Test your custom resource in the REST Lab.
    3. Obtain the resource instance using the following transformCurrentRow method, typically in a table event:
      The B2B sample includes the following Orders event logic:
      var shipper = row.FK_Orders_Shippers;
      if (shipper !== null) { // resource defines transform: cols, aliases, related objects
        var transform = logicContext.transformCurrentRow("ShipperAPIDef");
        B2B.sendToWebHook(transform, shipper.webHookURL);
    Note: The Orders event logic is available with code completion.

    Common System Integration Scenarios

    The following are common system integration scenarios. 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

    In the following example, 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 only a resource definition. No code changes were required on either the application server or each database.

    Mongo Integration

    You can integrate Mongo and SQL data into a common mashup API. Direct Mongo support is described on this page.

    Mashups with general RESTful Servers

    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. The following diagram illustrates this requirement:
    You can specify JavaScript sub-resources that can materialize sub-resources by invoking a RESTful service. In this example, the called RESTful service is another API Creator instance (from Demo), illustrating you can use API Creator as both a client (per the previous code) 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.
    For more information about creating JavaScript sub-resources, see Customize your API.

    Time-triggered Integration

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


    A common pattern for enabling systems integration is to provide WebHooks, where 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 (for example, specify URL for post when event occurs).

    You can:

    1. Define custom resources to match the WebHook format.
    2. Register them as WebHook listeners.
    3. Process posts.
      For more information about processing the posts, see the "Update-triggered integration" section on this page and also
      illustrated in the Business to Business Example.

    Custom Endpoints can address authorization, formatting

    WebHooks might require different authentication techniques than API Creator or deliver JSON in expected formats. You can address both issues by providing a custom endpoint.

    For more information about creating custom endpoints, see Custom Endpoints.

    Update-triggered Integration

    Updates (PUT, POST, DELETE) can trigger integration. You can use update events to address these.

    Example: 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, as shown in the following diagram:
    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. You can post data as either an insert or as a merge as an update using the MERGE_INSERT metadata action tag, or upsert.

    For more information about the MERGE_INSERT metadata action tag, see Merge Insert Metadata Action.

    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, as shown in the following diagram:

    You can approach this by doing the following:
    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 (for example, as a post). The following image shows the commit event in API Creator:

    Retrieve 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 (for example, sums and counts).
    4. Use the resultant local data in the current transaction (for example, for validation).
    The following diagram illustrates the requirement:
     A reasonable approach is to:
    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.

    Integrate External Systems, without Required Persistence

    You can integrate two systems that are not open to modification, available only by way of existing APIs. This type of integration may not necessarily require persistence. You can use the following alternative approaches:
    • Introduce persistence by using the request pattern.
    • Use JavaScript resources

    Introduce Persistence (for example, for system monitoring)

    While you may not require persistence, you might want this so that you can log requests and their results. This can assist in system monitoring. You can introduce persistence by using the request pattern, as follows:

    1. Create a request table that receives in the incoming Post.
      This Post might be a web hook from a remote system, or a cron job that polls for changes. The request table may have few attributes, for example, date and time, a completion status, and progress indicator fields.
    2. As required, declare rules on the request table that log date and time.
    3. Create an event on the request table that processes the request.
      This event might use restGet to read other systems, and restPost to write. You will commonly need to access data in the Get result to create the put/post request data using JSON parse and stringify.
      Best practice: Update values in the request object to indicate processing progress. This can assist in problem diagnosis.
    4. Consider what happens when the request fails.
      You may, for example, wish to set a field in the request table and use a request (response) event to control the response message.
    For more information:
    • About the request pattern, see Logic Patterns.
    • About how to invoke other RESTful services from business logic or row events using the restGet/restPost methods, see The SysUtility Object.
    • On JSON parse and stringify, see JavaScript (API Creator and JavaScript).
    • On Request (Response) events, see Request Events.

    Use JavaScript Resources

    You can define JavaScript resources and specify similar code as you would in the event.

    Use Custom Endpoints

    Custom endpoints offer similar functionality, but are not subject to authentication and provide options to return response data in different formats.

    Integrated Example

    You can integrate multiple systems together using JavaScript events. JavaScript events can call local and remote REST services. The following video illustrates integration:

    The following diagram illustrates this example:

    This diagram illustrates:
    1. Partner request. A Partner sends an Order to us, which is a RESTful POST.
    2. Compliance message. API Creator processes the Order, 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 response is in lieu of our normal transaction summary, which is intended for client refresh processing.

    For more information about normal transaction summary, including refresh information, see PUT.

    You can process the entire transaction with several rules and the following Live API JavaScript, which is about 20 lines of JavaScript using Live API, operating in conjunction with live logic. API Creator provides all the other REST/ SQL handling. It is 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. API Creator automatically partitions logic to the API Server for data access and security/integrity enforcement and so that API Server can properly share the logic. Row-level security is delegated to the DBMS for optimization.

    Partner Request

    The partner request is a simple RESTful request, such as POSTing an order and two 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

    For more information, see the Logic Sample Database.

    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 on the Manage, Rules, event page:

    For simplicity, this event logs the message instead of sending it; look for a log entry like (ensure that your API keys debug settings 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 order ready logic; the event is triggered as a consequence of the adjustment to the Product.totalQtyOrdered.

    For more information:

    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, which are 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. The following image shows how to define a request event in the Manage, Request Events page:

      The result is shown in the following image. The response contains the resource result, without the transaction summary. This image shows the Execute, REST Lab, Request tab:
    For more information about defining a request event, see Event Handlers.

    You can see the accountSummary field (added in the last line, in the previous image) in the response JSON.