Fetch()-ing GraphQL data in MLE/JavaScript

GraphQL is a pretty neat technology for querying, changing, and subscribing to data. REST remains a very popular way to exchange messages and other things, but GraphQL earned its place in the developer ecosystem, too. In this post you can read how to use the GraphQL endpoint Oracle Rest Data Services (ORDS) 24.1 provides.

The following environment was used when writing this post:

Oracle Database Free on Linux x86-64
Oracle REST Data Services 24.1

Installing ORDS

Setting up ORDS for GraphQL is beyond the scope of this article, Jeff Smith wrote about GraphQL in ORDS. Head over to his blog and read his post for more details.

The article assumes that you installed ORDS 24.1.0 for GraphQL successfully.

Preparing the schema

As with many other REST operations in ORDS you need to REST-enable a schema. The data source for this GraphQL adventure is provided by the Oracle HR sample schema. You can find it on GitHub if you want to learn more about the tables and their definition. All HR schema objects were installed in freepdb1. Connected to that PDB as the HR user, you need to enable the schema, followed by REST-enabling the employees table. This post shows the use of the Auto-REST feature for convenience to keep it simple. Real applications (read: yours!) must secure the REST endpoints properly; I strongly advise NOT to use Auto-REST in anything other than playground environments.

BEGIN
    ORDS.ENABLE_SCHEMA;

    ORDS.ENABLE_OBJECT(
        P_ENABLED         => TRUE,
        P_SCHEMA          => 'HR',
        P_OBJECT          => 'EMPLOYEES',
        P_OBJECT_TYPE     => 'TABLE',
        P_OBJECT_ALIAS    => 'employees',
        P_AUTO_REST_AUTH  => FALSE
    );
    COMMIT;
END;
/

Testing the REST endpoint

Let’s see if this works. ORDS is hosted on 192.168.121.77, port 8443. The following command will retrieve all records from the employees table, using the REST endpoint:

curl https://192.168.121.77:8443/ords/hr/employees/

By providing an employee ID like 100 you can grab an individual record:

$ curl --silent https://192.168.121.77:8443/ords/hr/employees/100 | jq
{
  "employee_id": 100,
  "first_name": "Steven",
  "last_name": "King",
  "email": "SKING",
  "phone_number": "1.515.555.0100",
  "hire_date": "2013-06-17T00:00:00Z",
  "job_id": "AD_PRES",
  "salary": 24000,
  "commission_pct": null,
  "manager_id": null,
  "department_id": 90,
  "links": [
    {
      "rel": "self",
      "href": "https://192.168.121.77:8443/ords/hr/employees/100"
    },
    {
      "rel": "edit",
      "href": "https://192.168.121.77:8443/ords/hr/employees/100"
    },
    {
      "rel": "describedby",
      "href": "https://192.168.121.77:8443/ords/hr/metadata-catalog/employees/item"
    },
    {
      "rel": "collection",
      "href": "https://192.168.121.77:8443/ords/hr/employees/"
    }
  ]
}

So far, so good. Now let’s look at the GraphQL endpoint (have a look at the documentation/reference for more details) instead. Following the jq command for pretty-printing the output a little bit of sed magic translates the content:

$ curl --silent https://192.168.121.77:8443/ords/hr/_/graphql | jq . | sed -e 's#\\n#\n#g' -e 's#\\"#"#g'
{
  "schemaName": "HR",
  "description": "the SDL representation of the 'HR' GraphQL Schema",
  "SDL": "type Query {
  """Generic resolver for EMPLOYEES type."""
  employees(primaryKey: JSON, where: JSON, sort: JSON, limit: Int, offset: Int): [EMPLOYEES]
}

"""
The 'Date' scalar type represents date values as specified by the ISO 8601 format in UTC 
time zone (YYYY-MM-DDThh:mm:ssZ).
"""
scalar Date

"""
The `Float` scalar type represents signed double-precision fractional values as specified by
[IEEE 754](https://en.wikipedia.org/wiki/IEEE_floating_point).
"""
scalar Float

"""
The `Int` scalar type represents non-fractional signed whole numeric values. Int can represent 
values between -(2^31) and 2^31 - 1.
"""
scalar Int

"""
The `JSON` scalar type represents JSON values as specified by [ECMA-404]
(http://www.ecma-international.org/publications/files/ECMA-ST/ECMA-404.pdf).
"""
scalar JSON

"""
The `String` scalar type represents textual data, represented as UTF-8 character sequences.
The String type is most often used by GraphQL to represent free-form human-readable text.
"""
scalar String

type EMPLOYEES {
  employee_id: Int!
  manager_id: Int
  phone_number: String
  commission_pct: Float
  department_id: Int
  salary: Float
  first_name: String
  email: String!
  job_id: String!
  hire_date: Date!
  last_name: String!

 """
 The relationship between the EMPLOYEES type and the EMPLOYEES type on 
 EMPLOYEES.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID
 """
 manager_id_employees(
  primaryKey: JSON, where: JSON, sort: JSON, limit: Int, offset: Int
 ): [EMPLOYEES]

 """
 The relationship between the EMPLOYEES type and the EMPLOYEES type on 
 EMPLOYEES.EMPLOYEE_ID = EMPLOYEES.MANAGER_ID
 """
 employees_manager_id(
  primaryKey: JSON, where: JSON, sort: JSON, limit: Int, offset: Int
 ): [EMPLOYEES]
 }"
}

As you can see, ORDS provides the goods. Let’s query this endpoint in MLE/JavaScript.

Simple GraphQL Query using fetch()

The first step has been completed: a GraphQL endpoint has been defined for the HR schema. This endpoint is now waiting to be consumed. This is done from a different schema, containing the MLE module. To keep the 2 schemas separate the consumer’s schema is named EMILY.

Connected as EMILY you create an inline JavaScript function to interrogate the GraphQL endpoint. The GraphQL query is to be provided as input to the function, which returns JSON.

create or replace function graphql_fetch(
  "query" JSON
) return json 
as mle language javascript
{{
    await import ('mle-js-fetch')

    const endpoint = 'https://192.168.121.77:8443/ords/hr/_/graphql'

    const response = await fetch(
        endpoint, {
            method: "POST",
            headers: {
                "Content-Type": "application/json",
                Accept: "application/json",
            },
            body: JSON.stringify(query),
    })

    const data = await response.json()
    return data
}};
/

Please note that this is the minimally viable prototype; actual code should check if the errors object is returned instead of data, indicating a problem, and act accordingly. If you are unsure where the endpoint information is coming from or how to use the fetch API please refer to the ORDS and MLE documentation respectively.

Let’s put the function to good use, shall we? As a first test let’s fetch the employee with ID 100. The raw GraphQL query looks like this:

{
  employees(primaryKey: {employee_id: 100}) {
    first_name
    last_name
    email
  }
}

Wrapping the query into the required format, it is possible to test the previously created function and GraphQL endpoint:

SQL> set serveroutput on
SQL> r
  1  declare
  2      l_graphQL_query     json;
  3      l_graphQL_result    json;
  4  begin
  5      l_graphQL_query := JSON('{ query: "{ employees(primaryKey:{employee_id: 100})  { first_name, last_name, email } }" }');
  6
  7      l_graphQL_result := graphql_fetch(l_graphQL_query);
  8
  9      dbms_output.put_line(
 10          json_serialize(
 11              l_graphQL_result
 12              pretty
 13          )
 14      );
 15* end;
{
  "data" :
  {
    "employees" :
    [
      {
        "first_name": "Steven",
        "last_name" : "King",
        "email" : "SKING"
      }
    ]
  }
}

PL/SQL procedure successfully completed

Summary

This did the job! As you can see, invoking a GraphQL endpoint using the fetch() API is straightforward.

Fetch()-ing GraphQL data in MLE/JavaScript

Installing ORDS

Preparing the schema

Testing the REST endpoint

Simple GraphQL Query using fetch()

Summary

Martin Bach

How to Configure Universal Connection Pool in Hibernate Applications

Where is the Complexity? Part 3

Fetch()-ing GraphQL data in MLE/JavaScript

Installing ORDS

Preparing the schema

Testing the REST endpoint

Simple GraphQL Query using fetch()

Summary

Authors

Martin Bach

How to Configure Universal Connection Pool in Hibernate Applications

Where is the Complexity? Part 3