GraphQL offers a popular way to query, manipulate, and subscribe to data. Interestingly GraphQL is transport protocol-agnostic, although in reality most requests are served over HTTP. But what’s stopping you from creating GraphQL endpoints in the database? This post hopefully shows that nothing does.
The article assumes you are reasonably familiar with GraphQL, if not, may I suggest you head over to graphql.org for an introduction and some really good demos.
By the way, if you are looking for an alternative way to enable GraphQL for your schema, I suggest you have a look at Oracle REST Data Services (ORDS): ORDS offers GraphQL-support as a feature, documented in Oracle® REST Data Services Developer’s Guide chapter 10 .
The Environment
An Oracle Database 23ai Free container image from Oracle’s container registry was used to write this post. The container instance ran in Podman on Oracle Linux 8 x86-64.
The GraphQL endpoint is implemented using GraphQL’ JavaScript, GraphQL’s reference implementation in JavaScript. You may have guessed it, this is possible thanks to Multilingul Engine (MLE) allowing me to run server-side JavaScript within the database.
As always, before using third-party modules in your project, make sure that your security and compliance department give you the go-ahead before starting your project!
Here is the list of the components used in the project for reference:
{
"devDependencies": {
"@rollup/plugin-node-resolve": "^15.2.3",
"mle-js-oracledb": "^23.3.0",
"prettier": "^3.2.5",
"rollup": "^4.14.3"
},
"type": "module",
"dependencies": {
"graphql": "^16.8.1"
}
}
Node v20.11.1 is installed and maintained via nvm. Linting doesn’t feature in this little post to keep it manageable.
Getting the necessary JavaScript modules
The code is based on GraphQL 16.8.1, the most current version at the time of writing. For some unknown reason several content delivery networks failed to provide an ECMA Script Version of the graphql module, meaning it is necessary to roll your own.
Challenge accepted.
Use your favourite IDE and install the above modules in a new directory, based on the package.json from earlier in this post. Note that versions might be out of date by the time you read this, always make sure you use the latest version of a given package to avoid potential security vulnerabilities. You are going to develop the MLE modules in your favourite IDE (on a file system) first, before they can be loaded into the database.
Creating the GraphQL schema
In the next step you are going to create the GraphQL schema, based on the HR.LOCATIONS table. It should be possible to perform the following 3 queries:
- get location by ID (a primary key lookup)
- get a location based on various criteria
- get all locations
The GraphQL schema defines a location type, representing all the columns in the table. These are:
SQL> desc hr.locations
Name Null? Type
----------------------------------------- -------- ----------------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
There’s also a list type for returning more than 1 location. Finally, the Query Type defines all 3 aforementioned queries. The resulting JavaScript code is shown in the appendix.
The following function assembles the schema and returns it. And yes, that’s a lot of code. A future article shows how to use SDL (Schema Definition Language) instead of JavaScript to create the schema.
/**
* A function to generate a GraphQL Schema based on the Locations
* table stored in the HR schema.
*
* @returns {GraphQLSchema} The generated GraphQLSchema
*/
export async function setupSchema() {
// a lot of detail skipped
const QueryType = new GraphQLObjectType({
name: 'Query',
fields: () => ({
getLocationByID: {
type: LocationType,
args: {
id: { type: GraphQLInt },
},
resolve: async (root, args, context, info) => {
const { id } = args;
return await getLocationByID(id);
},
},
// more detail skipped
}),
});
// this is where everything is put together.
const schema = new GraphQLSchema({
query: QueryType,
});
return schema;
}
The resolver functions in the schema use the MLE JavaScript SQL Driver to fetch information from the database and return it. This is particularly convenient: the IExecuteReturn object provides the result set as an array of objects by default. These can simply be returned – no extra work needed.
The next step is to define the database API (you can find it in the appendix). It’s just 3 functions selecting from the database, each returns a row from the table or an array of rows:
getLocationByID()performs a lookup based on a primary keygetLocation(): lookups based on multiple critieriagetAllLocations(): no surprises there 🙂
Defining the entrypoint
Now that the schema is complete an entry point is needed. A 3rd file, grapQLQuery.js allows the execution of arbitrary GraphQL queries against the schema.
import { setupSchema } from './graphQLSchema.js';
import { graphql } from 'graphql';
/**
* Perform a generic GraphQL query
* @param {String} queryText - the graphQL query
* @param {object} args - arguments to the query, empty array if none
* @returns {object} the JSON representation of the query result. Might contain errors, too
*/
export async function graphQLQuery(queryText, args) {
const schema = await setupSchema();
const results = await graphql({
schema,
source: queryText,
variableValues: args,
});
return results;
}
This function can either be used directly in JavaScript, or later on wrapped into a call specification, made available to SQL and PL/SQL.
Loading the code into the database
Let’s take a step back and recap what has happened so far
- The required 3rd party modules have been downloaded from
npm - 3 files have been created by you:
- the graphQL schema
- the database API
- the entrypoint into GraphQL
What’s next, or rather, how do you get these into the database? Remember from earlier it wasn’t possible to get an ECMAScript version of the GraphQL module from the content delivery network. Using the latest version of a module is strongly recommended however, which means you have to bundle your own code with the third party code. In the JavaScript world this is done using a bundler.
This post features rollup for no particular reason. Based on the following configuration rollup bundles everything (except mle-js-oracledb, which already exists in the database) into a single file, which you use as the module’s source. The file is named rollup.config.mjs and it defines what rollup should do:
import { nodeResolve } from '@rollup/plugin-node-resolve';
export default {
input: 'src/graphQLQuery.js',
output: {
file: 'build/bundle.js',
format: 'esm',
},
plugins: [
nodeResolve({
jsnext: true,
exclude: ['mle-js-oracledb']
}),
],
external: [ 'mle-js-oracledb' ],
};
Note that the rollup configuration file is well-documented. A short call to npx rollup -c creates build/bundle.js which I loaded as GRAPHQL_ENDPOINT_MODULE.
Testing
Now that the module is present you can finally test it all. The first step is to create the call specification for the MLE module. Once the call specification has been created, you can call it from anywhere you call SQL and PL/SQL.
create or replace function graphql_query(
p_query varchar2,
p_args json
) return json
as mle module GRAPHQL_ENDPOINT_MODULE
signature 'graphQLQuery';
/
select
json_serialize(
graphql_query(
'query locByID($id: Int) { getLocationByID(id: $id) { city country_id } }',
JSON('{id: 1000}')
)
pretty
) graphql_result
/
GRAPHQL_RESULT
--------------------------------------------------------------------------------
{
"data" :
{
"getLocationByID" :
{
"city" : "Roma",
"country_id" : "IT"
}
}
}
That’s it! The function accepts a GraphQL query and returns a JSON document containing the requested information.
Summary
This was fun! GraphQL is a popular way to interact with APIs, not quite as popular as REST, but it’s on the rise. You had to write a fair bit of code to create the GraphQL schema. A follow-up post demonstrates how to define the GraphQL schema using SDL rather than code.
Appendix
File graphQLSchema.js
import {
GraphQLSchema,
GraphQLObjectType,
GraphQLString,
GraphQLInt,
GraphQLList,
GraphQLError,
} from 'graphql';
import {
getLocationByID,
getLocation,
getAllLocations,
} from './databaseAPI.js';
/**
* A function to generate a GraphQL Schema based on the Locations
* table stored in the HR schema.
*
* @returns {GraphQLSchema} The generated GraphQLSchema
*/
export async function setupSchema() {
// maps hr.locations (a relational table) to a GraphQL type
const LocationType = new GraphQLObjectType({
name: 'Location',
fields: {
location_id: {
type: GraphQLInt,
resolve: (root, args, context, info) => {
return root.LOCATION_ID;
},
},
street_address: {
type: GraphQLString,
resolve: (root, args, context, info) => {
return root.STREET_ADDRESS;
},
},
postal_code: {
type: GraphQLString,
resolve: (root, args, context, info) => {
return root.POSTAL_CODE;
},
},
city: {
type: GraphQLString,
resolve: (root, args, context, info) => {
return root.CITY;
},
},
state_province: {
type: GraphQLString,
resolve: (root, args, context, info) => {
return root.STATE_PROVINCE;
},
},
country_id: {
type: GraphQLString,
resolve: (root, args, context, info) => {
return root.COUNTRY_ID;
},
},
},
});
// if you want to get all locations back, you have to provide
// a new type that's essentially a list of location types
// defined previously
const LocationsType = new GraphQLList(LocationType);
// Entry point: you must provide at least a Query type. Mutations
// and Subscriptions are optional. The Query type defines which
// queries are accepted, and which parameters (if any) these
// can take. Except for the most basic examples the Query type
// uses custom object types.
const QueryType = new GraphQLObjectType({
name: 'Query',
fields: () => ({
getLocationByID: {
type: LocationType,
args: {
id: { type: GraphQLInt },
city: { type: GraphQLString },
},
resolve: async (root, args, context, info) => {
const { id } = args;
return await getLocationByID(id);
},
},
getLocation: {
type: LocationType,
args: {
city: { type: GraphQLString },
},
resolve: async (root, args, context, info) => {
const { street, postalCode, city, stateProvince, country } =
args;
return await getLocation(
street,
postalCode,
city,
stateProvince,
country,
);
},
},
getAllLocations: {
type: LocationsType,
resolve: async (root, args, context, info) => {
return await getAllLocations();
},
},
}),
});
// this is where everything is put together.
const schema = new GraphQLSchema({
query: QueryType,
});
return schema;
}
File: databaseAPI.js
import oracledb from 'mle-js-oracledb';
/**
* Helper function to connect to the database
* @returns [Connection] a connection to the database
*/
async function getConn() {
return oracledb.defaultConnection();
}
/**
* Fetch a location from the database by ID (= primary key)
* This function is required by the resolver in the GraphQL Query type
* @returns {Location} the location
*/
export async function getLocationByID(id) {
const connection = await getConn();
const result = await connection.execute(
`select
location_id,
street_address,
postal_code,
city,
state_province,
country_id
from
locations
where
location_id = :id`,
[id],
{
outFormat: oracledb.OUT_FORMAT_OBJECT,
},
);
const data = result.rows[0];
return data;
}
/**
* Fetch all locations by parameter from the database
* This function is required by the resolver in the GraphQL Query type
* @returns {[Location]} an array of location types
*/
export async function getLocation(
street,
postalCode,
city,
stateProvince,
country,
) {
const connection = await getConn();
const result = await connection.execute(
`select
location_id,
street_address,
postal_code,
city,
state_province,
country_id
from
locations
where
street = :street`,
[street],
{
outFormat: oracledb.OUT_FORMAT_OBJECT,
},
);
const data = result.rows;
return data;
}
/**
* Fetch all locations from the database
* This function is required by the resolver in the GraphQL Query type
* @returns {[Location]} an array of location types
*/
export async function getAllLocations() {
const connection = await getConn();
const result = await connection.execute(
`select
location_id,
street_address,
postal_code,
city,
state_province,
country_id
from
locations`,
[],
{
outFormat: oracledb.OUT_FORMAT_OBJECT,
},
);
const data = result.rows;
return data;
}