Docs‎ > ‎API Creator‎ > ‎Create your API Project‎ > ‎

Define Custom REST Resources

Explicitly defined custom resources, or endpoints, 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 defining aliases for your tables and columns.
  • 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.
The following image shows a custom REST resource of type Normal on the Create, Resources, Resource tab:

The following image shows the OrderAudits custom REST resource:

Sources for Resources

Your server includes the following kinds of resources:

  • Base table resources. Automatically-created base tables for the active database. They are the REST resources that support GET, PUT, POST and DELETE.
  • Custom resources. You create custom resource that include related data (joined tables), project/alias attributes, defined filters.
  • View resources. Automatically created for each view table.
  • Stored procedure resources. Automatically created for each stored procedure.
  • Logic administration resources. Automatically-created meta data services to obtain the list of tables and resources and their attributes.
For more information:
  • About resource behavior, including pagination, optimistic locking, active logic enforcement, and update refresh, see Architecture.
  • About REST API support, see Language Examples.
Live API Creator automatically builds resources from the following sources:
  • Schema resources. Your base tables, view tables, and stored procedures become RESTful resource endpoints (subject to security). The following image shows an example of schema resources on the Integrate, Schema, Tables tab:
  • Logic Administration Resources. Live API Creator provides meta data so your software can discover the resources, for example /@tables, /@tables/{tablename}, /@resources, /@resources?projectId=1000, /@authentication, /@procedures, /@docs.

    For more information about the system REST endpoints, see System REST endpoints.

Define a Table as a Child Table

You can define a resource to be more than one row, that is, whether the table for the resource is a "child" table of the containing resource's table. You do this by selecting the Is a collection checkbox. As a 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's table is a "parent" table of the containing resource's table, then do not define it as a collection since at most only one row is expected.

Expose Base Table as Resources

Your your project can get underway instantly by exposing your base tables as resources (subject to security). You can then introduce custom resources as client development proceeds.

Rows - JavaScript Object Model

When you create an API project and connect to your database (step A), Live API Creator creates a JavaScript Object Model. API Creator defines a row object type (class) for each base table. You can think of these as Object-Relational Mapping (ORM) objects: they provide access to attributes, related objects, and persistence. They also expose update events you can handle in JavaScript.

The following diagram illustrates the row object type (class) for each base table denoted as the JavaScript Object Model:



Instances of these objects are created during update operations, and passed as context to your logic, for example, events, derivations, and validations. Unlike anti-pattern Anemic Data Objects, think of these objects as "Smart Data Objects." They encapsulate and actively enforce the business logic you define through events, derivations, and validations. Logic invocation is automatic; it's always enforced and you do not have remember to call it.

For more information:

Row Object Behavior

Row objects have the following behaviors:

  • Attributes - Rows have attributes for every column. You can refer to a Order's amount_total using: row.amount_total.
  • Role Accessors - Rows provide role accessors. You can refer to an Orders' row.customer.balance, or obtain a collection of row.lineitems (a collection of lineitem rows).

For more information about role accessors, such as accessing related parent/child data, see LogicContext object.

  • Expression Help - The JavaScript code editor provides easy "code completion" access to row attributes and role accessors.
  • toString - This method is provided to show attribute values for logging and debugging.
For more information about logging and debugging, Debug your API Project.
  • Persistence - Read, insert, update, and delete verbs save changed rows, with logic enforcement.
  • metaData - Returns the description of the object, useful for extension services seeking to deal with rows generically.
  • Logic Aware - Rows enforce reactive logic, and server-side JavaScript logic events.

For more information:

  • Resource Row Events - A resource can optionally have Resource Row Events, which allows for programmatic manipulation of the resource results during retrieval, for example, to inject new columns and to filter out row.

Override Implicit Resources

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

  • Database Abstraction. This effectively hides the base table, perhaps to provide the database abstraction functionality noted above 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.

View Resources

View resources are automatically created for each view table. For more information about view resources, including automatic discovery, see View Resources.

Stored Procedure Resources

Stored procedure resources are automatically created for each Stored Procedure. For more information about stored procedure resources, including automatic discovery, see Stored Procedure Resources.

Logic Administration Resources

Logic Administration Resources are automatically provided by the system. They provide meta data information, such as the list of tables and resources.

For more information about logic administration resources, see System REST endpoints.

Define Custom Resources for Multi-Tabled "Document Model" Data Access

Customize your API by defining multi-table resources that 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 can be more convenient to client code, and reduce latency by delivering all the required data in one transmission (with pagination for large results) thus eliminating multiple server calls).
  • Provisions for resource/attribute alias names protect your application from underlying schema changes in the database. Select and alias the attributes so that your API is not simply a direct reflection of the schema.
The following is an example of a multi-table resource for aliasing attributes on any selected column attribute:

Resource Details

You can apply filters and sorts to each level of the endpoint using the Details tab for base table (normal).

Resource Attributes

Optionally, you can select the attributes to be returned for your resource. If you do not select attributes, the system interprets this as "all attributes in table." This means that table columns added in the future apply to this resource.

You can also explicitly select which attributes are returned (subject to security), and override the default name, as shown in the following image:

You can click Select All, but unlike selecting no attributes, future table columns are not added. This provides an effective way to "lock down" a resource to the exact set of attributes desired, unaffected by subsequent changes to the table.

The following table describes the resource properties:
Property Description Notes
Filter [Details Tab]Filters results (in addition to security).

Merged into WHERE sql clause.
Use base table column names (not the Resource attributes aliases.)

You can filter on parent sub-resource attributes using qualified attribute names. For example, to filter LineItems to expensive Parts, use:
Part.price > 1000

You are not restricted to resource attributes.
Order [Details Tab]Comma-separated list of sort fields, with optional asc/desc indicator.Use base table column names.

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

Create Sub-resources

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

Select the level and 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:

The following is an example of creating a sub-resource:
  1. Create an project (API) by connecting to a database called Demo.
  2. Create a custom resource using the Customers table from the Demo database.
  3. Create a sub-resource by clicking New Level.
    The Sub Resource Helper dialog displays related tables.
  4. Choose orders from a different database.
    For more information about how your API can integrate multiple databases, see Multiple Databases.
The following table describes the sub-resource properties:

PropertyDescription Notes
Table The base table name. If you rename the table, fix the base table name. All client applications are unaffected.
Resource name Unique name within Root Resource The resource name is the container name with JSON. The name must contain alphanumerics or the underscore (_) special character.
Is a collection Selected if more than one row can result. Determines whether the JSON is an array or object. An exception is generated if you clear this field and the system detects multiple rows.

This is typically only used for Parent Sub Resources. For more information, see the section "Parent Sub Resources".
CombinedFor more information about combined resources, see Combined Resources. 
Join Parameterized filter to retrieve results based on containing Resource.  Use [] to refer to containing resource attributes, e.g.:
  • fk = [pk] to retrieve child rows for a containing parent

  • pk = [fk] to retrieve parent for a containing child
Bracket terms must follow the "=" sign, as indicated in the previous examples.
 Description Comments 

Each Resource Attribute is identified with an (alias) name, and includes a column_name and format.

Parent Sub Resources

In the previous diagram, 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 snippet shown below:

        "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 very common pattern in defining Resources over a Junction Table. Internally, the system collect such Parent Sub Resources and computes the join that is sent to the DBMS as a single command. Such automated join processing extends to multiple and nested parents. Parent sub-resources are the preferred method for retrieving parent data. You can obtain such data for retrieval without introducing business logic, such as formulas.

Combined Sub Resource

You can also declare Combined (Sub) Resources attributes into the containing Resource.

"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"...

Resource Row Events

You can provide Resource Row Events to inject computed attributes, and discard rows.

Resources Types

Normal resource types automate SQL handling. Additional resource types provide flexibility to integrate other data sources, control SQL, etc. 

Normal

(The most common case) Join together one or more table, allowing you to alias the table and columns, and specify joins and filters. Normal resources are linked to existing base SQL tables.

Specify the SQL to Execute

Use Free SQL resource type to manually specify the SQL to execute. For more information about defining Free SQL resource types, see Define Free SQL Resource Types.
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.

Supply Server-Side JavaScript to Return JSON

(Advanced Users) Use JavaScript resource type to write server-side JavaScript code that is executed whenever the resource is accessed. 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 Create JavaScript Resource Types.

Connect to a MongoDB Server

Use MongoDB resource type to connect to a specific MongoDB server, database, and collection. This connection type returns filtered requests in JSON format using pipeline syntax. This can be a stand-alone top resource on joined (linked) to SQL using the pipeline syntax ({customer_name: "<name>"} - the <name> represents the parent SQL attribute being passed into the JOIN).

For more information about integrating with MongoDB, see MongoDB Integration.

Move Resources between API Projects Resource Import/Export

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