Db2 for i and GraphQL - Githubissues

Go check out the updated version of this post.

My friend Connor has been using GraphQL for some time and always tells me how good it is. GraphQL seems very popular over Mongo and Postgres. I thought it was about time I finally write an example that uses Db2 for i to get data. I am happy to say GraphQL makes serving data just that little bit easier. If you want to learn more about GraphQL before reading on, here are some links:

Database

For this post, I will be using the Db2 for i sample tables, which you can generate on your own systems. Read more about that here.

Pieces to GraphQL

There are two pieces that matter in my example:

Type definitions - this tells GraphQL what data it is going to serve.
Resolvers - this tells GraphQL how to actually get the data. Each resolver would probably call a function to get data from the source (TypeORM, Sequelize, db2Model)

Of course, there also needs to be a part that connects to the database - but that is separate from GraphQL. The point of resolvers is that data can be gathered from anywhere. Note: This post does not solve the n+1 problem. There will be another post for that.

Type definitions

For this example, I will be using two tables from the sample schema: DEPARTMENT and EMPLOYEE. This means I will create two models for GraphQL:

type Department {
    id: ID!
    name: String
    manager: String
    managerObject: Employee
    parentDept: String
    location: String
    employees: [Employee]
}

type Employee {
    id: ID!
    first_name: String
    middle_initial: String
    last_name: String
    phone: String
    job: String,
    salary: Float
}

Notes from this:

GraphQL has types.
Where we reference a type, we can reference other GraphQL models
When a type or model is wrapped with square brackets, that indicates an array will be returned
A property with an exclamation mark means it cannot be null; properties are nullable by default

In a GraphQL schema, the root basically calls your JavaScript resolvers and we also have to define those in the type definition:

type RootQuery {
    # Fetches a single department
    Department(id: ID!): Department
    # Fetches all departments
    Departments: [Department]
    # Fetches a single employee
    Employee(id: ID!): Employee

    # Fetches all employees
    Employees: [Employee]
}

schema {
    query: RootQuery
}

Resolvers

For each of the definitions in the RootQuery, we need to create resolvers for. This is the part that would connect to the data source to fetch data and in this case, I am using Db2 for i.

RootQuery: {
    Department (root, { id }, context) {
        return Department.Get(id);
    },
    Departments (root, args, context) {
        return Department.Find();
    },
    Employee (root, { id }, context) {
        return Employee.Get(id);
    },
    Employees (root, args, context) {
        return Employee.Find();
    }
}

As you can see, I am using methods like Get and Find. I didn't write these manually and they were generated using db2Model. db2Model is a tool to generate classes based on Db2 for i tables, creating all the methods for CRUD actions. This is usually where you would use something like TypeORM or Sequelize too. What's important to note in this example is that the properties returning from your sources must match the properties you defined in your GQL type defintions. Now, to make a web API out of this, we just need to setup our launch script with the relavent info:

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

const { graphqlExpress, graphiqlExpress } =  require('graphql-server-express');
const { makeExecutableSchema } =  require('graphql-tools');

const typeDefs =  require('./graphql/typedef');
const resolvers =  require('./graphql/resolvers');

const express =  require('express');
const app =  express();
const bodyParser =  require('body-parser');

// Middlewares
app.use(bodyParser.json());

// Mount GraphQL on /graphql
const schema =  makeExecutableSchema({
    typeDefs,
    resolvers:  resolvers()
});

app.use('/graphql', graphqlExpress({ schema }));
app.use('/graphiql', graphiqlExpress({ endpointURL:  '/graphql' }));

db2.connect(process.env.CONNECTION_STR);

app.listen(3000, () =>  console.log('Express app listening on localhost:3000'));

Not only can we send queries to /graphql now, but running on /graphiql is a useful UI tool to help you write queries.

Now that we have outlined all our models and connected them up to data sources, we can send queries to our endpoint. To get a list of Departments, we would just send the following - but don't forget, because it's GraphQL, we also have to specify the properties we want!

query {
  Departments {
    id
    name
  }
}

{
  "data": {
    "Departments": [
      {
        "id": "A00",
        "name": "SPIFFY COMPUTER SERVICE DIV."
      },
      {
        "id": "B01",
        "name": "PLANNING"
      }
    ]
  }
}

some items omitted

If we wanted to get an Employee by their ID:

query {
  Employee(id: "000020") {
    id
    first_name
  }
}

{
  "data": {
    "Employee": {
      "id": "000020",
      "first_name": "MICHAEL"
    }
  }
}

We can also call multiple resolvers in one query:

query {
  Department(id:"B01") {
    id
    name
  }
  Employee(id: "000020") {
    id
    first_name
  }
}

{
  "data": {
    "Department": {
      "id": "B01",
      "name": "PLANNING"
    },
    "Employee": {
      "id": "000020",
      "first_name": "MICHAEL"
    }
  }
}

Model relationships

There may be instances where you want to get a list of items based on another piece of data. In my definiton for Department, I had these two properties:

    managerObject: Employee
    employees: [Employee]

managerObject will return an Employee model based on the manager column and Employee will return a list of Employees' in that Department. In our resolvers, we can specify what these property need to do to get this information inside of the Employee model:

Department: {
    employees (department) {
        return Employee.Find({workdept: department.id});
    },
    managerObject(department) {
        return Employee.Get(department.manager);
    }
}

Which means in our queries we can now get this information in one request:

query {
  Department(id:"A00") {
    id
    name
    managerObject {first_name}
    employees {id first_name}
  }
}

{
  "data": {
    "Department": {
      "id": "A00",
      "name": "SPIFFY COMPUTER SERVICE DIV.",
      "managerObject": {
        "first_name": "CHRISTINE"
      },
      "employees": [
        {
          "id": "000010",
          "first_name": "CHRISTINE"
        },
        {
          "id": "000110",
          "first_name": "VINCENZO"
        },
        {
          "id": "000120",
          "first_name": "SEAN"
        },
        {
          "id": "200010",
          "first_name": "DIAN"
        },
        {
          "id": "200120",
          "first_name": "GREG"
        }
      ]
    }
  }
}

worksofliam / blog

Db2 for i and GraphQL #44

Database

Pieces to GraphQL

Type definitions

Resolvers

Model relationships