GraphQL is a pretty neat technology for querying, changing, and subscribing to data. Since I got to grips with it (admittedly, it’s in the early days), I’m in love: the added functionality it provides over plain REST (Representational State Transfer) is quite appealing. 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 I’ll show you how to use the GraphQL endpoint Oracle Rest Data Services 24.1 provides.
My environment
I used the following components to write this post:
- Oracle Database Free on Linux x86-64
- Oracle REST Data Services 24.1.0
Installing ORDS
Setting up ORDS is beyond the scope of this article, Jeff Smith wrote about GraphQL in ORDS and I’d like to invite you to read his post for more details.
I am assuming 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 my 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. I installed the HR schema objects in freepdb1. Connected to that PDB as the HR user, I enable the schema, followed by auto-REST enabling the employees table. I use auto-REST for convenience in this blog post 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:
curl https://192.168.121.77:8443/ords/hr/employees/
By providing an employee ID like 100 you can grab an individual record:
$ curl -s 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):
$ 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 a good starting point :) Let’s query this endpoint in MLE.
Simple GraphQL Query using fetch()
The first step has been completed: a GraphQL endpoint has been defined in the HR schema. This endpoint is now waiting to be consumed. I created another schema in the database named EMILY to demonstrate how you can do that in MLE/JavaScript using the fetch() call. Connected as EMILY I can create an inline JavaScript function to interrogate the GraphQL endpoint. To remain flexible I allow the user to pass a valid GraphQL query string to the function and return the resulting JSON.
create or replace function graphql_fetch(
"query" JSON
) return json
as mle language javascript
q'~
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? I want to fetch the employee with ID 100. The “raw” GraphQL query is this:
{
employees(primaryKey: {employee_id: 100}) {
first_name
last_name
email
}
}
Wrapping the query into the required format, it is possible to test the 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
This did the job! As you can see, invoking a GraphQL endpoint using the fetch() API is straightforward. In the next post, I’ll show you how to create a GraphQL schema and use graphql-js to interrogate the database.
Martins Blog 