GraphQL Query Examples with Oracle & ORDS 23.3

We made a pretty big announcement that GraphQL Query support was coming to Oracle REST Data Services 2 weeks ago, here is that post.

Requirements to make this ‘go’

ORDS 23.3 or higher
GraalVM 17 + JavaScript support
ORDS enabled schema and 1 or more enabled tables and views

GraphiQL or API Client?

The GraphiQL interface is probably pretty familiar to those that have used GraphQL before, and it’s behavior for Oracle Database should be pretty familiar.

ORDS Endpoint for GraphiQL:
http://server:port/ords/schema/_/graphiql/

GraphiQL: Fetches the schema auto-complete your query requests.

But developers using API tools might also be pretty familiar with something like Insomnia. It’s an open source project and also supports GraphQL!

If you’re already using it for REST APIs, it’ll handle GraphQL as well.

I’m going to show just a few examples of querying our data in more detail, and I’ll be using Insomnia.

Walking Examples from the Docs

The ORDS Examples for GraphQL requests can be found here.

They assume you have an HR schema, that’s ORDS enabled and also has the following tables enabled –

employees
departments
locations
jobs
countries

With access to these data structures granted to the ORDS mid-tier, developers can now start exercising those endpoints, in this case the GraphQL query endpoint.

ORDS Endpoint for GraphQL Requests:
POST http://server:port/ords/schema/_/graphql

Getting data from a single resource, table

How about our EMPLOYEES, their names and money details.

I ask for 5 attributes, my response ONLY has those 5 attributes

The request looks like this –

query Employees {
  employees {
    employee_id
    first_name
    last_name
    commission_pct
    salary
  }
}

There’s not much of interest here other than the fact that this is a simple GraphQL query on our EMPLOYEES table.

Getting data from a single resource, filtered

Maybe we don’t want ALL the employees, maybe we just want those employees that, have a JOB_ID of ‘IT_PROG’

query{
  employees (where : {job_id : {eq : "IT_PROG"}}){
    employee_id
    first_name
    last_name
    salary
    job_id
    }
  }

Insomnia fetches the schema for us automatically and can also provide assistance.

Getting data from a single resource, filtered & sorted

Let’s get that same list of employees, but now let’s sort it by FIRST_NAME descending order, and we’ll get PU_CLERK’s this time.

query {
	employees(
		where: { job_id: { eq: "PU_CLERK" } }
		sort: [{ first_name: "desc" }]
	) {
		employee_id
		first_name
		last_name
		salary
		job_id
	}
}

Now let’s do some JOINs

We’ll JOIN employees to itself, via the circular reference for the manager attribute pointing back to the employees table on the employee ID field.

This one starts our normal, we wants stuff from employees, but then after ‘salary,’ we have ‘manager_id_employees.’ This tells the GraphQL query engine that we’re going to traverse the employee record to another employee record, and for THAT employee, we want their name and JOB_ID.

query Employees {
    employees {
        employee_id
        first_name
        last_name
        salary
	manager_id_employees {
            first_name
            last_name
            job_id
        }
    }
}

The relationship is known via the database’s FOREIGN KEY CONTRAINTS (references) clause.

These known relationships are listed here in Insomnia or here in the GraphiQL app.

In Graphiql, toggle the ‘Documentation Explorer’ button, and in Insomnia hit ‘Show Documentation’ on the schema item in the toolbar.

Let’s join some tables and filter and sort

Ok for the City of Seattle, I want a list of departments, sorted in ascending order, and the top 2 highest paid employees.

query Locations{
  locations
	 (where: { city: { eq: "Seattle" } }) {
    city
    departments_location_id 
		 (sort: [{ department_name: "asc" }]) {
      department_name
      employees_department_id
			 (sort: [{ salary: "desc" }],
			  limit: 2 ) {
        first_name
        last_name
        salary
      }
    }
  }
}

And that looks like this –