Db2 for i, GraphQL & Node.js (Part 2) #55

This is a follow-up to my previous GraphQL (Feb 2020) on IBM i post, where this time we use Join Monster to solve the n+1 problem. The n+1 problem indicates having to run more than one SQL query per GraphQL root query. Using Join Monster (JM), we can turn a deep nested query into a single query - which is much better for database and query performance.

Of course, because this is Node.js, we'll be using ODBC and express to serve up requests and SQL statements.

Setup

The first step is to get all the required Node.js modules installed.

$ mkdir gqldemo
$ cd gqldemo
$ npm i express express-graphql join-monster odbc

We're also going to create a .env file with our database credentials and app port in it. While developing you can use the VS Code debugger to load these environment variables, or perhaps you want to add dotenv as a dev dependency.

PORT=3000
DB_HOST=x
DB_ID=x
DB_PASSWORD=x

Next, you're going to need the Db2 dialect. JM doesn't ship with a Db2 dialect, so I created a very simple one to get it off the ground. Create a file called util/db2dialect.js and give it this content.

Lastly, a basic class in util/db.js which wraps the ODBC pool into a seperate module we can re-use:

const odbc = require("odbc");

module.exports = class {
  pool;
  static async connect(connectionString) {
    this.pool = await odbc.pool(connectionString);
  }

  static async query(sql, params) {
    const result = await this.pool.query(sql, params);

    return result;
  }
}

Sample data

If you need some sample data to play with, Db2 ships with a procedure to create a sample database. I will be using this in tis post.

CALL QSYS.CREATE_SQL_SAMPLE ('SAMPLE')

App startup

We need to create the index for our application. In this we do a few things:

• Bring in the modules we need to use (express, express-graphql, our db class, and the schema which we create later)
• Define the route /graphql which directs to graphqlHTTP - this spins up a webpage we can use to test our GraphQL queries.
• Start the up up (startup function) by connecting to the database and listening to requests on a port.

const { graphqlHTTP } = require('express-graphql');
const express = require('express');

const app = express();
const db2 = require('./util/db');

const schema = require('./schema');

app.use('/graphql', graphqlHTTP({
  schema: schema,
  graphiql: true,
}));

async function startup() {
  const connStr = [
    'DRIVER=IBM i Access ODBC Driver',
    `SYSTEM=${process.env.DB_HOST}`,
    `UID=${process.env.DB_ID}`,
    `Password=${process.env.DB_PASSWORD}`,
    `Naming=1`,
    `DBQ=,*USRLIBL`
  ].join(`;`);

  await db2.connect(connStr);

  app.listen(process.env.PORT);
}


startup();

Defining the schema

This part can get a little bit complex. For GraphQL (and JM) to work correctly, you need to make sure each row in your tables has a unique key (or composite key). If you don't, I suggest you create a view for your data and create a (composite) key. If you struggle with that.. you likely aren't ready for GraphQL (sorry).

• I would recommend you have some great relational structure in your tables. If you don't, I would suggest you use views to create a relationship structure (for example, from SAMPLE, a department has employees and can be connected by a key)
• Luckily, short names aren't a big deal here as GraphQL lets us replace the SQL column names with pretty names

Schema file

I am going to define each GraphQL type in a file named ./schema.js. At the top, you should bring in any potenial modules you will need.

const joinMonster = require('join-monster').default;
const db2 = require('./util/db');

const dialectModule = require(`./util/db2dialect`);

const { GraphQLSchema } = require('graphql');
const { GraphQLObjectType, GraphQLList, GraphQLString, GraphQLInt, GraphQLNonNull } = require('graphql');

// objects and root definition go here

module.exports = new GraphQLSchema({
  description: 'my schema',
  query: QueryRoot,
});

Department object

We'll start with the DEPARTMENT table. It has 5 columns

• DEPTNO - which is also the key to the row
• DEPTNAME - the department name
• MGRNO - the manager number, which can be tied to EMPLOYEE.EMPNO
• ADMRDEPT - basically the parent department and can be tied to DEPARTMENT.DEPTNO
• LOCATION - a lovely, simple, string location

Since each row in this table is a Department, that's what our GraphQL object will be called.

• Each GraphQL object will have ties to JM by the use of the extensions property.
• When defining each object, we have to tell JM which table we are refering to.
• When defining each field in an object, we have to tell JM which column in the table it maps to OR what other object it maps to.
  • You will see below that manager has the Employee type and we also tell JM how to join those two objects/tables together.
  • Same with parentDepartment

// As part of schema.js

const Department = new GraphQLObjectType({
  name: 'Department',
  extensions: {
    joinMonster: {
      sqlTable: 'sample.department',
      uniqueKey: 'DEPTNO' // id is different for every row
    }
  },
  fields: () => ({
    id: {
      type: GraphQLString,
      extensions: {
        joinMonster: {
          sqlColumn: 'DEPTNO'
        }
      }
    },
    name: {
      type: GraphQLString,
      extensions: {
        joinMonster: {
          sqlColumn: 'DEPTNAME'
        }
      }
    },
    manager: {
      type: Employee,
      extensions: {
        joinMonster: {
          sqlJoin: (baseTable, referTable, args) =>
            `${baseTable}.MGRNO = ${referTable}.EMPNO`
        }
      }
    },
    parentDepartment: {
      type: Department,
      extensions: {
        joinMonster: {
          sqlJoin: (baseTable, referTable, args) =>
            `${baseTable}.ADMRDEPT = ${referTable}.DEPTNO`
        }
      }
    },
    location: {
      type: GraphQLString,
      extensions: {
        joinMonster: {
          sqlColumn: 'LOCATION'
        }
      }
    }
  })
});

Employee object

It's very similar to before, except this time it's for the EMPLOYEE table. I left our a bunch of columns to make this rather simple. Of course, EMPLOYEE has lots of reference keys, but I chose not to include them here.

const Employee = new GraphQLObjectType({
  name: 'Employee',
  extensions: {
    joinMonster: {
      sqlTable: 'sample.employee',
      uniqueKey: 'EMPNO' // id is different for every row
    }
  },
  fields: () => ({
    id: {
      type: GraphQLString,
      extensions: {
        joinMonster: {
          sqlColumn: 'EMPNO'
        }
      }
    },
    firstName: {
      type: GraphQLString,
      extensions: {
        joinMonster: {
          sqlColumn: 'FIRSTNME',
        }
      }
    },
    lastName: {
      type: GraphQLString,
      extensions: {
        joinMonster: {
          sqlColumn: 'LASTNAME',
        }
      }
    }
  })
});

The resolver

We need to define our resolver, which will convert the GraphQL query into an SQL statement using our Db2 dialect.

const resolve = (parent, args, context, resolveInfo) => {
  return joinMonster(resolveInfo, {}, sql => {
    // console.log(sql);
    return db2.query(sql);
  }, { dialectModule });
};

Defining the root

The root query lets us define what queries people can actually run against our objects/models. Below, we define 3 possible queries.

1. departments to get all departments
2. department to get a single department by ID
3. employees to get all employees

Notice the type property on departments and department. When we want a list, we use wrap our Department object in GraphQLList, otherwise we just use Department.

const QueryRoot = new GraphQLObjectType({
  name: 'Query',
  fields: () => ({
    departments: {
      type: new GraphQLList(Department),
      resolve
    },
    department: {
      type: Department,
      args: {
        id: { type: new GraphQLNonNull(GraphQLString) },
      },
      extensions: {
        joinMonster: {
          where: (table, args, context) => {
            return `${table}.DEPTNO = '${args.id}'`
          }
        }
      },
      resolve
    },
    employees: {
      type: new GraphQLList(Employee),
      resolve
    },
  })
})

Running a query

The nice part about express-graphql is that it provides a web interface for running queries. But not only does it run queries, it also provides documentation and content assist for your schema - super neat. If you head to localhost:<PORT> (or wherever you're running it), it should launch the GraphiQL interface:

From here, you can run type your query and get content assist too. Here is some examples and the result for them.