database-element.md

Create a Database Element BETA

Element Builder gives you the ability to create custom integrations to cloud services using the Cloud Elements API Management Platform. We are currently testing our BETA version. Currently only endpoints with a REST API can be built using Element Builder. Please contact us if you are interested in participating in our round of BETA testing.

This guide will demonstrate how to build a Database Element only. Please view the Element Builder Documentation for more information on how to build Elements for endpoints with a REST API.

PostGreSQL will be used for this demonstration.

• Element Builder Tabs
  • Info Tab
  • Configuration Tab
  • Instance Tab
  • Resources Tab
  • ADDING COMPLEX QUERIES: GET /customersDetails
  • Models Tab
  • Events Tab
  • Bulk Tab
  • Documentation Tab
• Admin Dashboard
  • Import our Sample Element
• Support

In order to create a PostgreSQL Database Element, the following steps are required:

1. Create a Database
2. Obtain the following for creating a connection to that Database:

• Database Host: e.g. localhost:5432
• Database Name
• Database Username
• Database Password

The PostgreSQL Element leverages the tables contained within your PostgreSQL database and transforms them into a collection of resources. RESTful methods (POST, GET, PATCH, DELETE) are used to interact with these resources (accounts, contacts, files). The columns in the table become the modeling attributes used to send payloads with each API call. For example a database with the following columns of data:

name	phone	address
Jon Doe	333-333-3333	123 Main

transforms to this JSON body:

{
  "name": "Jon Doe",
  "phone": 333-333-3333,
  "address": "123 Main"
}

If the table contains a primary key, the Retrieve, Update, and Delete by ID APIs can be generated. If a table does not have a primary key or contains multiple primary keys, the Retrieve, Update, and Delete by ID APIs cannot be generated.

New Database Element

Element Builder Tabs

Log in to the Cloud Elements API Manager

Click “Elements Builder”

Click “Build Element”

Info Tab

Select a Hub e.g. DB Hubs map resources from a collection of endpoints (What we call Elements) into a uniform set of APIs. Add a new hub from the dropdown menu to create a new collection if our defaults do not meet your needs.

Name of the new Element, e.g. MyPostGreSQL Element

Add a description for the new Element. e.g. Add a MyPostGreSQL Instance to connect your existing MyPostGreSQL Database to the Database Hub, allowing you to manage your objects across multiple Database Elements. You will need your PostgreSQL account information to add an instance.

Add a URL to an image or logo to represent the new Element.

Select the type of authentication the endpoint expects. The four types supported by Cloud Elements are Basic, OAuth 1, OAuth 2, and Custom. When building Database Elements, select the Custom auth type. Custom authentication is completely user defined, such as a username and password exchanged for an API key with some type of expiration involved.

Select JDBC protocol

Click “Next”

Configuration Tab

By selecting JDBC protocol, this will load the default values needed to configure the Element. Select the appropriate JDBC URL. In our example: jdbc:postgresql://{db.host}/{db.name}

Select the page size, e.g. 100 objects per page.

Select the paging type. Page is the default, but Offset is available to view portions of data, as well as, Cursor to move through large data sets.

Select the Accept Header and the Content Type Header Cloud Elements supports two types: JSON and XML. Please select the type the endpoint expects. In our example, Cloud Elements expects JSON payloads.

Under the Add Configuration panel > Key: jdbc.driver.classname > click Edit

In the 'Default Value' Field, input: org.postgresql.Driver

Click Done

Click “Next”

Configurations, Parameters, and Hooks will not be covered in this guide as the defaults have been set.

However, here are some definitions:

• Configuration: A value which is stored when a connection to the endpoint is created. This value is populated via user input and will be used when invoking the API, e.g. an api_key and api_secret.
• Parameter: A globally defined value that will apply to all API calls. Parameters can be sent as a header, path, query, body.
• Hooks: Pre-Hook: Action you wish to execute prior to sending API calls e.g. manipulating or adding data (query, header, path, body, configuration). Post-Hook: Modify the response data (body, header, configuration) on the return call from the endpoint.

Instance Tab

Test the connection to the endpoint by creating an instance.

Input a name for the instance, e.g. MyPostGreSQL Element Instance 1

• Database Host: e.g. localhost:5432
• Database Username
• Database Password
• Database Name

Click “Create Instance”

Resources Tab

Add entities/resources to your Element within the Resources Tab. Resources will need an HTTP verb like POST, GET, PATCH, PUT, or DELETE associated with it. If the Element being created can be identified with one of Cloud Elements current hubs, pre-built resources can be used to get started in mapping out resources.

ADDING RESOURCE: GET /customers

For this example, the GET /customers resource will be added to our PostgreSQL Element.

Click “+ Add” > API

Input the Path: /customers

Input the Vendor Path: select * from customer offset :offset limit :limit – this can be any valid SQL query

Input the Method: GET

Input the Description – what your users will see when they view the API documentation, e.g. "Retrieve a list of customers"

Click “Add Parameter” > Page

Input the Parameter Fields:

• Name: page
• Vendor Name: offset
• Type: Query
• Vendor Type: Query
• Data Type: string
• Vendor Data Type: string
• Parameter Source: Request
• Is it Required: NO
• Description: The text of your status update

Click “Done”

Click “Add Parameter” > pageSize

Input the Parameter Fields:

• Name: page
• Vendor Name: offset
• Type: Query
• Vendor Type: Query
• Data Type: string
• Vendor Data Type: string
• Parameter Source: Request
• Is it Required: NO
• Description: The number of resources to return in a given page

Click “Done”

Click “Try it out!”

This will open up the API Documentation sliding Panel.

NOTE: The default pageSize will be 50 if not populated with a number of your choice. Click the Try it out button to test the call.

View the response on the left hand side panel.

View the vendor request and response on the right hand side panel. This is very useful when debugging and API call. This guide will not go into debugging, however you can view tips debugging tips in the general Element Builder documentation. Click the 'X' in the corner to close the sliding panel.

ADDING RESOURCE: POST /customers

Click “+ Add” > API

Input the Path: /customers

Input the Vendor Path: insert into customer ( first_name, last_name, email, mobile_phone ) values ( :first_name, :last_name, :email, :mobile_phone ) returning first_name, last_name, email, mobile_phone, customer_id, created_dt – this can be any valid SQL query

Input the Method: POST

Input the Description – what your users will see when they view the API documentation, e.g. "Create a customer"

Click “Add Parameter” > Blank

Input the Parameter Fields:

• Name: body
• Vendor Name: body
• Type: BODY
• Vendor Type: QUERY
• Data Type: customers
• Vendor Data Type: customers
• Parameter Source: Request
• Is it Required: No
• Description: The customer body

Click “Done”

Click “Try it out!”

This will open up the API Documentation sliding Panel.

Input the following customer JSON body:

{
  "last_name": "Snow",
  "first_name": "Jon",
  "email": "jonsnow@got.com"
}

Click the Try it out button to test the call.

View the response on the left hand side panel.

{
  "created_dt": 1464096203573,
  "last_name": "Snow",
  "customer_id": 102,
  "first_name": "Jon",
  "email": "jonsnow@got.com"
}

View the vendor request and response on the right hand side panel. This is very useful when debugging and API call. NOTE: Since we did not populate all customer values, i.e. mobile_phone, the value will be captured as null Click the 'X' in the corner to close the sliding panel.

ADDING RESOURCE: GET /customers/{id}

Click “+ Add” > API

Input the Path: /customers/{id}

Input the Vendor Path: select * from customer where customer_id = :id – this can be any valid SQL query

Input the Method: GET

Input the Description – what your users will see when they view the API documentation, e.g. 'Get a customer by ID'

Click “Add Parameter” > Id

Input the Parameter Fields:

• Name: id
• Vendor Name: id
• Type: PATH
• Vendor Type: QUERY
• Data Type: number
• Vendor Data Type: number
• Parameter Source: Request
• Is it Required: Yes
• Description: The ID of the customer

Click “Done”

Click “Try it out!”

This will open up the API Documentation sliding Panel.

Input the ID of the customer. In our example: 102

Click the Try it out button to test the call.

View the response on the left hand side panel.

View the vendor request and response on the right hand side panel. This is very useful when debugging and API call. Click the 'X' in the corner to close the sliding panel.

ADDING RESOURCE: PUT /customers/{id}

Click “+ Add” > API

Input the Path: /customers/{id}

Input the Vendor Path: update customer set first_name = :first_name, last_name = :last_name, email= :email, mobile_phone =:mobile_phone where customer_id = :customer_id returning first_name, last_name, email, mobile_phone, customer_id; – this can be any valid SQL query

Input the Method: PUT

Input the Description – what your users will see when they view the API documentation, e.g. "Update a customer"

Click “Add Parameter” > Blank

Input the Parameter Fields:

• Name: body
• Vendor Name: body
• Type: BODY
• Vendor Type: QUERY
• Data Type: customers
• Vendor Data Type: customers
• Parameter Source: Request
• Is it Required: No
• Description: The customer object

Click “Done”

Click “Add Parameter” > Id

Input the Parameter Fields:

• Name: id
• Vendor Name: customer_id
• Type: PATH
• Vendor Type: QUERY
• Data Type: number
• Vendor Data Type: number
• Parameter Source: Request
• Is it Required: Yes
• Description: The customer ID

Click “Done”

Click “Try it out!”

This will open up the API Documentation sliding Panel.

Input the ID of the customer. In our example: 102 Input the customer JSON to update the object:

{
  "last_name": "Snow",
  "first_name": "Jon",
  "email": "jonsnow@got.com",
  "mobile_phone": "303-333-3333"
}

Click the Try it out button to test the call.

View the response on the left hand side panel.

View the vendor request and response on the right hand side panel. This is very useful when debugging and API call. Click the 'X' in the corner to close the sliding panel.

ADDING RESOURCE: DELETE /customers/{id}

Click “+ Add” > API

Input the Path: /customers/{id}

Input the Vendor Path: select * from customer where customer_id = :id – this can be any valid SQL query

Input the Method: DELETE

Input the Description – what your users will see when they view the API documentation, e.g. 'Delete a customer by ID'

Click “Add Parameter” > Id

Input the Parameter Fields:

• Name: id
• Vendor Name: id
• Type: PATH
• Vendor Type: QUERY
• Data Type: number
• Vendor Data Type: number
• Parameter Source: Request
• Is it Required: Yes
• Description: The ID of the customer

Click “Done”

Click “Try it out!”

This will open up the API Documentation sliding Panel.

Input the ID of the customer. In our example: 102

Click the Try it out button to test the call.

View the response on the left hand side panel.

View the vendor request and response on the right hand side panel. This is very useful when debugging and API call. Click the 'X' in the corner to close the sliding panel.

ADDING RESOURCE: POST /addresses

Click “+ Add” > API

Input the Path: /addresses

Input the Vendor Path: insert into address ( street1, street2, state, postal_code, country, customer_id ) values ( :street1, :street2, :state, :postal_code, :country, :customer_id ) returning street1, street2, state, postal_code, country, customer_id, address_id, created_dt – this can be any valid SQL query

Input the Method: POST

Input the Description – what your users will see when they view the API documentation, e.g. "Create an address"

Click “Add Parameter” > Blank

Input the Parameter Fields:

• Name: body
• Vendor Name: body
• Type: BODY
• Vendor Type: QUERY
• Data Type: addresses
• Vendor Data Type: addresses
• Parameter Source: Request
• Is it Required: No
• Description: The address body

Click “Done”

Click “Try it out!”

This will open up the API Documentation sliding Panel.

Input the following customer JSON body:

{
  "street1": "123 Main",
  "street2": "PO Box 123",
  "state": "CO",
  "country": "USA",
  "customer_id": 104
}

Click the Try it out button to test the call.

View the response on the left hand side panel.

{
  "country": "USA",
  "created_dt": 1464111347732,
  "address_id": 109,
  "street1": "123 Main",
  "street2": "PO Box 123",
  "state": "CO",
  "customer_id": 104
}

View the vendor request and response on the right hand side panel. This is very useful when debugging and API call. NOTE: Since we did not populate all customer values, i.e. postal_code, the value will be captured as null Click the 'X' in the corner to close the sliding panel.

ADDING COMPLEX QUERIES: GET /customersDetails

Click “+ Add” > API

Input the Path: /customersDetails

Input the Vendor Path: select c.*, a.* from customer c inner join address a on a.customer_id = c.customer_id {where} offset :offset limit :limit – this can be any valid SQL query NOTE The {where} indicates a place holder. This will replace the placeholder with an empty value so the script works correctly on our side. The where is a standard Cloud Elements placeholder. If a where does not exist, this will satisfy that requirement.

Input the Method: GET

Input the Description – what your users will see when they view the API documentation, e.g. 'Retrieve a list of customer details including address'

Click “Add Parameter” > Page

Input the Parameter Fields:

• Name: page
• Vendor Name: offset
• Type: QUERY
• Vendor Type: QUERY
• Data Type: string
• Vendor Data Type: string
• Parameter Source: Request
• Is it Required: NO
• Description: The text of your status update

Click “Done”

Click “Add Parameter” > pageSize

Input the Parameter Fields:

• Name: page
• Vendor Name: offset
• Type: QUERY
• Vendor Type: QUERY
• Data Type: string
• Vendor Data Type: string
• Parameter Source: Request
• Is it Required: NO
• Description: The number of resources to return in a given page

Click “Done”

Click “Add Parameter” > Where

Input the Parameter Fields:

• Name: where
• Vendor Name: where
• Type: QUERY
• Vendor Type: PATH
• Data Type: string
• Vendor Data Type: string
• Parameter Source: Request
• Is it Required: NO
• Description: The CEQL search expression

Click “Done”

Click “Try it out!”

This will open up the API Documentation sliding Panel.

Input the search expression in the where field: last_name='Snow'

Click the Try it out button to test the call.

View the complex query response on the left hand side panel.

View the vendor request and response on the right hand side panel. This is very useful when debugging and API call. Click the 'X' in the corner to close the sliding panel.

Models Tab

Below is an excerpt from our general Element Builder documentation. It will cover Model functionality

The Models Tab is an additional feature with some optional functionality to make resource creation more streamlined.

NOTE: This functionality is only available for endpoints with JSON payloads. SOAP endpoints or endpoints with XML payloads are not currently supported.

The Freshdesk endpoint will be referenced in this portion of the documentation.

The Models Tab allows the user to build resource models via customization, as well as, an import function.

We will take a look at the Freshdesk Company endpoint which we will map to the Account resource.

A review of the Freshdesk API documentation is needed prior to making any changes to the resource. The docs can be found here: https://developer.freshdesk.com/api/#view_company

MODEL CUSTOMIZATION

Click on the Models Tab. If you have any unsaved progress, an alert will appear asking if you want to save your changes.

The current list of API resources will render.

Select a model to edit. In our example, we will be viewing the GET /accounts resource.

Looking at the “updated_at” field, we can see the value is a date.

In our model, the data type is set to string.

This can be modified within the code editor.

Change “type” to “date”

Click “Save Model” to update the resource

IMPORT MODELS

To import a model, head to the endpoint API documentation. Navigate to the desired resource and locate the example response.

Copy the response payload.

Select the resource in the list: GET /accounts

Click “Generate from Payload”

Paste in the model

Click “Generate Model”

The model is now built. If you wish to make any changes, they can be made right in the code editor panel. Just remember to save your changes.

Events Tab

Events are currently not supported for Database Elements. We'll keep you posted on this feature.

Bulk Tab

Below is an excerpt from our general Element Builder documentation. It will cover Bulk functionality

Cloud Elements supports bulk download of objects in JSON format if the endpoint supports filter by date.

The following fields are required to enable bulk download:

• Bulk Query Time Format: Select a time format from the dropdown list. The time format must match the endpoint’s supported time format for queries.
• Bulk Query Field Name: Provide an object and an operator in the Bulk Query Field Name, e.g. where orders >= “{time_format}”. This field may be left blank if you wish to include bulk download on all objects.
• Bulk Query Operator: The Bulk Query Operator can be set to “=” or “>=”.

Let’s take a look at the FreshBooks API documentation: https://www.freshbooks.com/developers/docs/expenses#expense.list

FreshBooks has a query API – expense.list which supports the date_from field:

This information will be used to create our bulk query.

First let’s take a look at the expense object:

{
    "date": "2016-02-11 00:00:00",
    "amount": 200,
    "compound_tax": 0,
    "client_id": 0,
    "folder": "active",
    "category_id": 123456,
    "project_id": 0,
    "vendor": "Acme",
    "staff_id": 1,
    "expense_id": "00001234567",
   ///////////////////////////////////
   /"updated": "2016-02-11 12:40:51",/
   ///////////////////////////////////
    "status": 0,
    "has_receipt": 0
  }

FreshBooks supports an updated field. However, they do not support a created_at field. Since the created_at field is not supported, bulk querying will occur on objects updated on or after a certain day and time.

Using the following information:

• expense.list query parameter date_from
• updated field within the expense object

The bulk query can be constructed.

The Bulk Query Field Name will be blank so all objects can be queried

Select the Bulk Query Date Format, the format the endpoint expects. This can be found in the endpoint API documentation.

Input a Bulk Query Operator. The Bulk Query Operator can be set to = or >= In our example, it will be set to = since we are querying date_from.

Click “Next”

Documentation Tab

Below is an excerpt from our general Element Builder documentation. It will cover Documentation functionality

View the newly created API documentation for Element. Try out the API calls right from Element Builder.

View the new Element documentation by selecting an instance from the dropdown: “Change Instances”.

If an instance has not been created, click “Add Instance”.

Try It Out: Click on a resource, e.g. GET /users

Input the query parameter: q=’jonsmith’

Click “Try it out!”

View the response.

Click “Done”

Admin Dashboard

Within the in Admin Dashboard, the following functions are available to exercise on all Elements created using Element Builder:

• Edit an Element: change a URL, add a resource, edit configurations
• Deactivate: deactivates an Element, temporarily removing it from the catalog
• Delete: deletes an Element, permanently removing it from the catalog
• Export: exports Element JSON, this JSON can be imported at a later time

To access the Element admin functions, click on the gear.

To import Element JSON, click “Import Element” and follow the prompts. All functionality will be available based on the last state of the Element prior to export.

Import our Sample Element

Below is the JSON needed to create our sample DB Element.

[Download][1]

[1]:{{ site.url }}/download/sampleDbElement.json the file and import the Element from the Admin Dashboard

Support

Element Builder is currently in BETA. We would love to hear about enhancements or concerns regarding the application. Please don’t hesitate to get in touch.

Need some help? Don’t hesitate to reach out to Cloud Elements Support with any questions or concerns.

The Cloud Elements Team