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 me 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 .
My Environment
I used an Oracle Database Free container image from Oracle’s container registry to write this post. The container runs in Podman on Oracle Linux 8 on Linux 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 out!
Here’s the version list of the components in my project in case you are interested:
{
"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
I’m using GraphQL 16.8.1, the most current version at the time of writing. For some unknown reason my favourite content delivery network failed to provide an ECMA Script Version of the graphql module, meaning I have to roll my own.
Challenge accepted.
Using my favourite IDE I installed 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. Development of the MLE module takes place on the file system first, before it can be loaded into the database.
Creating the GraphQL schema
In the next step I created the GraphQL schema, based on the HR.LOCATIONS table. I wanted to be able to perform 3 queries:
- get location by ID (a primary key lookup)
- get a location based on various criteria
- get all locations
The 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. I’ll show you in a future article how to use SDL instead of JavaScript to create the schema ;) For now I’ll stick with the GraphQL reference implementation.
/**
* 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, again, it’s listed in the appendix. It’s just 3 functions selecting from the database:
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. And even ORDS :)
Loading the code into the database
What has happened so far?
- The necessary modules have been downloaded from
npm - 3 files have been created
- the graphQL schema
- the database API
- the entrypoint into GraphQL
What’s next, or rather, how do I get these into the database? Remember from earlier I haven’t been able to get an ECMAScript version of the GraphQL module. I want to use it however, which means I have to bundle my code with third party code. In the JavaScript world this is done using a bundler.
I used 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 I’ll then 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' ],
};
It took me a minute to realise that without the plugin-node-resolve module the graphql module wouldn’t be bundled into the resulting file. Oh, by the way, 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 I can finally test it all
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"
}
}
}
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 as far as I can tell. This seems like a long post, but in reality it’s not too hard working with GraphQL in MLE. I am planning another post to demonstrate how to simplify the schema generation using SDL (Schema Definition Language).
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;
}
Martins Blog 