Integrate directly with an external database

Appfarm supports integration towards external APIs (REST, GraphQL or SOAP) by using the Web Request action node (when the push or pull is initiated by Appfarm) or with Service Endpoints (when a third-party system initiates the push or pull towards Appfarm).

From an architectural perspective, you should always build an API on top of a database when the database is owned by an external system or if you are building a data platform. However, in some cases, you might want to access the database directly when no API is in place. Examples:

• You are hosting data internally on a database platform (Such as Azure SQL, Oracle, SQL Server, PostgreSQL, MongoDB, IBM DB2), such as when building a data platform or hosting a data warehouse. But there is no web-based API for accessing these data.

• You have access to the database of an old legacy system, without any APIs for accessing these data.

Appfarm does not have built-in database connectors. However, third-party tools exist for this purpose, enabling REST or GraphQL Interfaces on top of an existing database. We suggest one simple tool below for this case, but please note that other lightweight tools as well as more powerful Integration Platforms (for automation and more advanced integrations) also exist. Some database providers offer built-in support for enabling REST or GraphQL interfaces for accessing the database as well.

Hasura

Hasura is an open-source tool that may be hosted yourself (for free) or hosted in the cloud (subscription, see https://hasura.io/pricing/). It is easy to set up. By setting up Hasura, you let Hasura connect to your databases, and it will provide a REST or GraphQL endpoint on top of the databases.

This means, that Appfarm may use the Web Request action node either to

• Send SQL Statements to the Hasura REST endpoints

• Send GraphQL queries or mutations to the Hasura GraphQL endpoints

Both of these options give you data back in JSON format, allowing you to map the response to your Appfarm Data Sources using the Result Mapping of the Web Request action node.

Getting data from Azure SQL with Hasura

This is a short step-by-step guide using the Hasura Free version (hosted in Hasura cloud) to retrieve data from a table in an Azure SQL database.

Create a new Hasura Project

Sign up at https://cloud.hasura.io/. Navigate to Projects -> Create new Project. Select your preferences, similar to this

Allow Hasura to communicate with Azure - IP whitelisting

In Hasura, click the cog wheel icon on the Project to see the settings. Note down the IP-address, as you will need to enter it in Azure.

In Azure, locate the SQL Server resource, and click it. On the left menu, go to Security -> Networking and add a new Firewall Rule. Give it a proper name, and enter the IP address of the Hasura project.

Allowing Hasura through the Azure firewall

Connect the Azure database to Hasura

In Hasura, click Launch Console for you project. Navigate to the Data menu. Having the Data Manager selected in the left menu - click Connect Database. Select MS SQL Server and click Connect Existing Database. Give it a proper name and enter the Database URL: Driver={ODBC Driver 17 for SQL Server};Server=<SQL Server database address>;Database=<database name>;UID=<SQL Server user>;PWD=<SQL server password>;

• Server: You may find this address in Azure when navigating to the Azure SQL Server instance. It is the "Server name" property found on the front page

• Database: This is the name of the database in Azure SQL

• UID: This is the username of an SQL Server user with access to the database.

• PWD: This is the password that UID.

Click Connect Database.

Now, in the left menu, locate your database in the Datebases menu. Click <your-database-name> -> dbo. In the details window, click Track All (or just Track on the relevant tables)

See examples below.

Setting up the connection string to the Azure SQL database in Hasura

Test GraphQL queries in Hasura

To verify the connection, navigate to the API menu in Hasura. You should see your tables in the explorer to the left. In the below screenshot, we have just a single table "Employees" in Azure SQL. Using the explorer, expand the Employees entry and select the columns your want to get from Azure SQL, and click the Play icon:

The important part for now is the query in the center panel above. You do not need a name for the query. Here's a few example queries:

To get all Employees:

query {
  Employees {
    EmailAddress
    PhoneNumber
    EmployeeID
    FirstName
    LastName
  }
}

To get a filtered set of Employees (note the where keyword here, and operators with a underscore prefix, as opposed to the filter keyword used when using GraphQL in Appfarm ):

query {
  Employees(where: {FirstName: {_eq: "John"}}) {
    EmailAddress
    PhoneNumber
    EmployeeID
    FirstName
    LastName
  }
}

Connect to Hasura from Appfarm using a Web Request

Now, we are ready to query the GraphQL endpoints in Hasura from Appfarm, using the Web Request action node.

1. First, still in Hasura, go to the project front page, copy the Admin Secret, and save it in Appfarm Create (as a Secret).

2. In Appfarm Create, you will need an Object Class for representing Employees. Create the Object Class (if you have not done it already) and add the Object Class Properties. With reference to the above Employees example, you may add Employee ID (integer), Email (string), First name (string), Last name (string) and Phone (string). We recommend using the same Node Name for these Object Class Properties as the name of the columns/graphQL properties in Hasura.

3. Add Employees as a Runtime Only Data Source in your App.

4. Add a Web Request to an Action.

   1. URL: The GraphQL Endpoint found in the API menu in Hasura (see screenshot above)
   2. Request Headers: Add x-haura-admin-secret, and select the secret stored in 1) as value

   3. Authorization: No Auth

   4. Method: POST

   5. Body: Use the function editor to return a GraphQL query. See Example Queries below.

   6. Result Mapping: RootPath is data.<tablename> (example: data.Employees). Select the Employees data source of step 2) as Data Source. Enter the column names manually, or click Automap Node Names if the Node Names in Appfarm match with the response from Hasura GraphQL.

Example Query - All Employees

With reference to the Function editor, you may construct the following function. Here, we return all 5 properties for all Employees.

let myQuery = `{
  Employees {
    EmployeeID
    FirstName
    LastName
    EmailAddress
    PhoneNumber
  }
}`

return {query: myQuery}

Example Query - Filter Employees

With reference to the Function editor, you may construct the following function. Here, we return 3 properties for a specific Employee - more precise - the Employee with a given phone number.

let myQuery = `{
  Employees(where: {
    PhoneNumber: {_eq: "555-5678"}
  }) {
    EmailAddress
    PhoneNumber
  }
}`

return {query: myQuery}