Creating APIs‎ > ‎

Customize your API

You can customize your API by defining custom multi-table resources. These return responses from multiple tables (for example, Customers, Orders, and Payments), possibly from multiple databases. 

Custom resources serve the following purposes:

  • Database abstraction layer. REST resources are loosely akin to database views, where you can define named projections, filters, and alias names. The database abstraction layer protects your application for underlying changes in the database.
You can also protect your application from schema changes in the database by giving aliases to tables and columns in the context of a resource (of type Normal).
  • Minimize network latency. Beyond view-like functionality, you can define sub-resources for related parent/child data. For example, you might define a customer that includes its purchase orders and line items. The data is included in the JSON that is returned from issued REST requests.
  • Convenient programming model. JSON results are returned as a document model with nesting for sub-resources - this is often preferable for client applications.
  • Integration. Sub-resources can come from different databases, including non SQL sources such as REST, Mongo, and ERP systems.

For more information about REST integration examples, see REST Integration Examples

Sources for Resources

Your API can include the following kinds of resources, or endpoints, in your API:

Schema

When you create an API and connect API Creator to a database, API Creator scans the schema to create endpoints for each of the following:

Base Table Resources

Base table resources are resources that API Creator creates for each base table.
Rows - JavaScript Object Model

When you create an API and connect API Creator to your database, API Creator builds a JavaScript object model from your schema. API Creator automatically creates a row object type (class) for each base table. The row object types provide basic services for the following:

  • Persistence. Services to read and write rows.
  • Accessors. Services to access attributes and related row objects.
  • Logic execution. When rows are updated, the updates automatically invoke the relevant reactive logic and events you have defined.
For more information about row object types, see Row Objects.

View Resources

View resources are resources that API Creator automatically creates for each view table. Use view resources to access complex query processing.

For more information about view resources, including automatic discovery, see View Resources.

Stored Procedure Resources

Stored procedure resources are resources that API Creator automatically creates during schema discovery for each stored procedure. The resources are created as REST endpoints. Use stored procedure resources to leverage the business logic already developed in your database.

For more information about stored procedure resources, including automatic discovery, see Stored Procedure Resources.

You can protect these resources using security.

For more information about how to protect resources, see Security.

System Resources

System resources are automatically-created metadata services you can use to obtain the list of tables and resources and their attributes.

The following code snippet shows a list of the system REST resources:

/@tables, /@tables/{tablename}, /@resources, /@resources?projectId=1000, /@authentication, /@procedures, /@docs

For more information about how to use these system REST endpoints, see System REST endpoints.

Custom Resources

You can shape your API by defining custom resources. These resources can include related data (joined tables), alias attributes, and defined filters.

Define Custom Multi-table Resources

After you define your custom multi-table resource, you can select the attributes to be returned by your resource.
  1. With your API open, go to the Create, Resources, Resource tab and create a new custom resource by clicking New Resource.
  2. Complete the following fields and then save your changes:
    Resource type
    The resource type.
    Values:
    • Normal. (The most common case) A resource of type Normal automates SQL handling. You can integrate other data sources and control SQL by defining the resource type. You can give aliases to tables and columns in the context of a resource (of type Normal). You can also specify joins and filters by joining together one or more table. Normal resources are linked to existing base SQL tables.
    For more information:
      • About how to give an alias to a table or column, see the "Select the Attributes to be Returned by your Resource" section.
      • About how to specify joins and filters, see 
    • Free SQL. Free SQL resources are resources where you manually specify the SQL to execute.
    For more information about defining Free SQL resource types, see Define Free SQL Resource Types.

    Tip! Pirate Syntax. Why just talk like a pirate, when you can also code like one? You can use the well-recognized arghhh spelling of arg in API Creator.

    • JavaScript. (Advanced Users) Supply server-side JavaScript that is executed whenever the resource is accessed and returns JSON. Your JavaScript code is responsible for retrieving data and for formatting it into JSON. The most common use for this resource type is to make a call to an outside REST services and connect to multiple servers of any type. For example, you might want to aggregate REST/JSON services.
    For more information about aggregating REST/JSON services, see Define JavaScript Resource Types.
    • MongoDB. MongoDB resources are resource you can connect to a specific MongoDB server, database, and collection.
    For more information about integrating with MongoDB, see MongoDB Integration.
    Default: Normal
    Table
    The base table name. If you rename the table, fix the base table name. Renaming the base table does not affect client applications.
    Resource name
    The unique name within the root resource. This is the container name with JSON. The name must contain alphanumerics or the underscore (_) special character.
    Is a collection
    Defines whether the resource is expected to be a single row at most or potentially more than one row. That is, it defines whether the table for the resource is a child table of the containing resource's table. Determines whether the JSON is an array (cleared) or object (selected). When cleared, an exception is generated if API Creator detects multiple rows. This field is typically only used for parent sub-resources.

    For more information, see the "Parent Sub-resources" section.

    Best practice: Define top-level resources as collections. Sub-resources are collections if there can be many rows for a single row in the containing resource. If a sub-resource is for a table that is a parent table of the containing resource's table, then do not define it as a collection, since at most only one row can be expected.

    Combined
    Defines the resource a resource whose attributes are combined and included in a containing resource. This checkbox displays only for sub-resources.
    Default: Cleared
    For more information about combined resources, including this checkbox, see 
    Combined Resources.
    Join
    A parameterized filter to retrieve results based on the containing resource. Use brackets ([]) to refer to containing resource attributes. The terms within the brackets must follow the "=" sign.
    Examples:
    • To retrieve child rows for a containing parent, use the following:
    fk = [pk]
    • To retrieve parent for a containing child, use the following
    pk = [fk]
  3. Create a sub-resource by completing the following:
    1. Click New Level.
      The Sub Resource Helper window opens.
    2. Select a related table from the list and then click Create Resource.
      Sub-resources for a tree-like document model are created.
    3. The following image shows the CustomerBusinessObject custom REST resource of type Normal and the nesting of related tables on the Create, Resources, Resource tab:

      A
       list of related objects based on your schema's foreign keys is presented. If your schema does not have foreign keys, you can define virtual foreign keys.

      For more information about foreign keys and how to define virtual foreign keys, see Integrate Multiple Databases.

      For more information about creating sub-resources, see the "Create sub-resources" section.
Your custom multi-table resource is defined.

Define Document-Oriented Resources

Multi-table resources return results in a single response (for example, Customers, Orders, and Payments). You will often want to define named resources that:
  • Deliver "document-oriented" multi-table results to clients, instead of flat relational tables. These results can be more convenient to client code and reduce latency by delivering all the required data in one transmission (with pagination for large results) instead of in multiple server calls.
  • Protect your application from underlying schema changes in the database by provisioning resource/attribute alias names. You can select and alias the attributes so that your API is not simply a direct reflection of the schema.

Define the Resource Type

You define the resource type using the Resource type field on the Create, Resources, Resource page.

For more information about defining the resource type, see the "Define Custom Multi-table Resources" section.

Resource Details

You can apply filters and sorts to each level of the endpoint.
  1. With your API open and your resource selected, click the Details tab.
    The following image shows the fields on this tab:
  2. Complete the following fields and then save your changes:

Filter

Specify additional filtering on the resource, for example, to restrict the rows that the resource returns.

For SQL resources of type Normal, enter a fragment of a WHERE expression into the Filter field. For example:

Part.price > 1000 and paid = 'Y'

The fragments you enter are merged into a WHERE SQL clause. You are not restricted to resource attributes. Use base table column names (not resource attributes aliases). You can filter on parent sub-resource attributes using qualified attribute names.

For MongoDB resources, enter Mongo syntax in the Filter field. For example:

{"$and": [{"$lt": {"amount_total": 1000}}, {"paid": "Y"}]}

Order

Specify row ordering on the resource, for example, to sort customers by balance. Use base table column names.

The filters and sorts are applied to each level of the endpoint.

Select the Attributes to be Returned by your Resource

You optionally can shape the response attributes by choosing and renaming columns. By default, resource attributes have the same name as the corresponding columns, but you can change that and give each attribute a name that is better suited to your purposes. This is akin to giving an alias to the column in the context of that specific resource.

When you create a resource of type Normal, API Creator returns all base table columns by default for that resource. You can override this default by selecting the columns you want the resource to return. API Creator always returns the primary key column, whether you select it or not.

  1. With your API open, go to the Create, Resources, Attributes tab.
    The following image shows this tab:
  2. Select the resource for which you want to return attributes, complete the following fields, and then save your changes:
Columns
Clear the checkbox for the base table columns you do not want the resource to return.
Default: All columns are selected.
Attribute name
Select which attributes your resource returns (subject to security) and override the default name. Each resource attribute is identified with an (alias) name, and includes a column_name and format.
Optional: Yes
Note:
If you do not select attributes, API Creator interprets this as "all attributes in table" and applies table columns you subsequently add to this resource.

The attributes for that resource are selected and saved.

Filter and Order

You can filter results (in addition to security) and define a comma-separated list of sort fields, with an optional asc/desc indicator.

For more information about how to do this, see the "Resource Details" section.

Create Sub-resources

Sub-resources are shown indented under each level. You can add custom child resources using JavaScript code.

  1. Create an API by connecting to a database called Demo.
  2. Create a custom resource using the Customers table from the Demo database.
  3. Click New Level.
    The Sub Resource Helper dialog opens displaying related tables. If the level you selected is a base table, suggestions are provided based on relationship roles. The following image shows this dialog:

  4. Choose orders from a different database and then click Create Resource.
    For more information about how your API can integrate multiple databases, see Multiple Databases.
The sub-resource is created.

Each resource attribute is identified with an (alias) name and includes a column_name and format.

Parent Sub-resources

In the previous image, Product is a sub-resource of Lineitem. Even though Product is a parent (one side of a one-to-many), it is defined as a sub-resource. That means you get JSON, like the following snippet illustrates:

        "order_number": 1,
        "amount_total": 35,
        "paid": false,
        "notes": "This is a small order",
        "customer_name": "Alpha and Sons",
        "links": [],
        "Lineitems": [
          {
            "@metadata": {
              "href": "http://localhost:8080/APICreator/rest/demo1/OneCustomer.Orders.Lineitems/1",
              "checksum": "69a06521a60842973ca3a27e50520851"
            },
            "lineitem_id": 1,
            "product_number": 1,
            "order_number": 1,
            "qty_ordered": 1,
            "product_price": 10,
            "amount": 10,
            "links": [],
            "Product": {
              "@metadata": {
                "href": "http://localhost:8080/APICreator/rest/demo1/OneCustomer.Orders.Lineitems.Product/1",
                "checksum": "0efd354caacf68997d3a4685783bf740"
              },
              "product_number": 1,
              "name": "Hammer",
              "price": 10
            }
          },

This is a common pattern in defining resources over a junction table. Internally, Live API Creator collects the parent sub-resources and computes the join that is sent to the database management system. The automated join processing extends to multiple and nested parents. Parent sub-resources are the preferred method for retrieving parent data. You can obtain parent data for retrieval without introducing business logic, such as formulas.

For more information:

Declare Combined Sub-Resource Attributes into a Containing Resource

You can declare combined sub-resource attributes into the containing resource. The following code snippet shows an example:

"order_number": 1,
        "amount_total": 35,
        "paid": false,
        "notes": "This is a small order",
        "customer_name": "Alpha and Sons",
        "links": [],
        "Lineitems": [
          {
            "@metadata": {
              "href": "http://localhost:8080/APICreator/rest/demo1/OneCustomer.Orders.Lineitems/1",
              "checksum": "69a06521a60842973ca3a27e50520851"
            },
            "lineitem_id": 1,
            "product_number": 1,
            "order_number": 1,
            "qty_ordered": 1,
            "product_price": 10,
            "amount": 10
                   "name": "Hammer"...

For more information about combined sub-resources, see Combined Resources.

Inject Computed Attributes and Discard Rows

You can inject computed attributes and discard rows by:
  • Providing resource row events.
For more information about how to provide resource row events, including the variables you can define for the JavaScript code, see Resource Row Events.
  • Define non-persistent attributes.

Note: Non-persistent attributes show up in your API documentation.

For more information about how to define non-persistent attributes, see Non-persistent Attributes.

Move Resources between API Projects Resource Import/Export

For more information about importing and exporting API definitions, see Import and Export API Definitions.

Override Implicit Resources

You can define a resource with the same name as the base table. This serves the following functions:

  • Database abstraction. Defining a resource with the same name as the base table effectively hides the base table, perhaps to provide the database abstraction functionality noted for REST retrieval/update.
  • Refresh. The update APIs optionally return all the resources affected by your update. The resource names for these are your base tables. You can provide abstraction for refresh, just as for REST retrieval/update, by overriding resources.

Extend Resources

You can further control resource processing using the following extensibility services:
  • Control the returned response values with resource row events.

For more information about extending resources using resource row events, see Resource Row Events.

  • Define sub-resources (and obtain those rows using JavaScript) using JavaScript resource types.

For more information about extending resources using JavaScript resource types, see Define JavaScript Resource Types.