You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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:
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');constexpress=require('express');constapp=express();constdb2=require('./util/db');constschema=require('./schema');app.use('/graphql',graphqlHTTP({schema: schema,graphiql: true,}));asyncfunctionstartup(){constconnStr=['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(`;`);awaitdb2.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.
constjoinMonster=require('join-monster').default;constdb2=require('./util/db');constdialectModule=require(`./util/db2dialect`);const{ GraphQLSchema }=require('graphql');const{ GraphQLObjectType, GraphQLList, GraphQLString, GraphQLInt, GraphQLNonNull }=require('graphql');// objects and root definition go heremodule.exports=newGraphQLSchema({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.jsconstDepartment=newGraphQLObjectType({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.
constEmployee=newGraphQLObjectType({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.
The root query lets us define what queries people can actually run against our objects/models. Below, we define 3 possible queries.
departments to get all departments
department to get a single department by ID
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.
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.
The text was updated successfully, but these errors were encountered:
would it be worthwhile to note the changes needed to swap to graphql-http since express-graphql is deprecated? Just something I ran into while following this along.
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.
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 adddotenv
as a dev dependency.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: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.
App startup
We need to create the index for our application. In this we do a few things:
express
,express-graphql
, ourdb
class, and theschema
which we create later)/graphql
which directs tographqlHTTP
- this spins up a webpage we can use to test our GraphQL queries.startup
function) by connecting to the database and listening to requests on a port.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).
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.Department object
We'll start with the
DEPARTMENT
table. It has 5 columnsDEPTNO
- which is also the key to the rowDEPTNAME
- the department nameMGRNO
- the manager number, which can be tied toEMPLOYEE.EMPNO
ADMRDEPT
- basically the parent department and can be tied toDEPARTMENT.DEPTNO
LOCATION
- a lovely, simple, string locationSince each row in this table is a Department, that's what our GraphQL object will be called.
extensions
property.manager
has theEmployee
type and we also tell JM how to join those two objects/tables together.parentDepartment
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.The resolver
We need to define our resolver, which will convert the GraphQL query into an SQL statement using our Db2 dialect.
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.
departments
to get all departmentsdepartment
to get a single department by IDemployees
to get all employeesNotice the
type
property ondepartments
anddepartment
. When we want a list, we use wrap ourDepartment
object inGraphQLList
, otherwise we just useDepartment
.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 tolocalhost:<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.
The text was updated successfully, but these errors were encountered: