GraphQL

How to connect GraphQL and PostgreSQL

Here Ill explain how to connect GraphQL to PostgreSQL.

Published · Jun 24 2019

  

GraphQL is just a query language for your API. I find GraphQL a really good alternative to REST. GraphQL makes front-end programming very easy because it allows you to use a single endpoint which reduces response time and avoids under and over fetching.

However, those benefits come at a cost that involves setting a server, schema query and a resolver.
 
Here I’ll explain how to connect GraphQL to PostgreSQL.

To interact with your database you can use the same data connector libraries you usually use. The only thing you need to do is to properly implement the query and mutation resolve methods.

https://github.com/chris001177/pg-graphql

PostgreSQL Database
CREATE TABLE people (
    id              serial PRIMARY KEY,
    firstname       varchar(40) NOT NULL,
    lastname        varchar(40) NOT NULL
);

CREATE TABLE emails (
    id          serial PRIMARY KEY,
    email       varchar(256) NOT NULL,
    person      integer REFERENCES people (id),
    “primary”   boolean DEFAULT false
);

The first table represents a people and the second one represents peoples’ emails.

GraphQL Schema
Query {
  person(id: ID): Person
}

type Person {
  id: ID
  firstname: String
  lastname: String
  emails: [Email!]!
}

type Email {
  id: ID
  email: String
  primary: Boolean
}

Query
SELECT * FROM "emails" WHERE person=person.id;

I want to grab all emails which specific person has.
First step is for the GraphQL resolver to fetch the person from database, second step will take the person’s ID and fetch the emails for him.

1. Setup Express Server
const express = require('express');
const expressGraphQL = require('express-graphql');
const schema = require('./schema');
const app = express();


app.use('/graphql', expressGraphQL({
   schema,
   graphiql: true
}))
app.listen(4000, () => {
   console.log('Listening...')
})

First, you have to install express and then express-graphql. 
Later, you’ll see how to setup schema which I’m requiring at the top. 
Also, I have Graphiql set to true. Graphiql is a GraphQL IDE in which I can easily check my queries. 

When GraphiQL makes a request, GraphQL is going to reach out to my database and say: “I’m looking for that specific person.” If you’ve wondered, our request’s going to be an async one.

2. Setup GraphQL Schema
const graphql = require('graphql');
const connectionString = 'myURI';
const pgp = require('pg-promise')();
const db = {}
db.conn = pgp(connectionString);
const {
   GraphQLObjectType,
   GraphQLID,
   GraphQLString,
   GraphQLBoolean,
   GraphQLList,
   GraphQLSchema
} = graphql;
const PersonType = new GraphQLObjectType({
   name: 'Person',
   fields: () => ({
      id: { type: GraphQLID },
      firstname: { type: GraphQLString },
      lastname: { type: GraphQLString },
      emails: {
         type: new GraphQLList(EmailType),
         resolve(parentValue, args) {
            const query = `SELECT * FROM "emails" WHERE
            person=${parentValue.id}`;
            return db.conn.many(query)
               .then(data => {
                  return data;
               })
               .catch(err => {
                  return 'The error is', err;
               });
         }
      }
   })
})
const EmailType = new GraphQLObjectType({
   name: 'Email',
   fields: {
      id: { type: GraphQLID },
      email: { type: GraphQLString },
      primary: { type: GraphQLBoolean }
   }
})
const RootQuery = new GraphQLObjectType({
   name: 'RootQueryType',
   fields: {
      person: {
      type: PersonType,
      args: { id: { type: GraphQLID } },
      resolve(parentValue, args) {
         const query = `SELECT * FROM "people" WHERE id=${args.id}`;
         return db.conn.one(query)
            .then(data => {
               return data;
            })
            .catch(err => {
                return 'The error is', err;
            });
      }
   },
   emails: {
      type: EmailType,
      args: { id: { type: GraphQLID } },
      resolve(parentValue, args) {
         const query = `SELECT * FROM "emails" WHERE id=${args.id}`;
         return db.conn.one(query)
            .then(data => {
               return data;
            })
            .catch(err => {
               return 'The error is', err;
            });
        }
      }
   }
})
module.exports = new GraphQLSchema({
   query: RootQuery
})

I’m using pg-promise to connect to my database. Then, I’m defining all the types which I’m going to use.

You might see a difference between fields in the EmailType and the PersonType. Email fields are a plain object but PersonType is wrapped with an arrow function. 
Why is that?

If we just leave a plain object and we want to execute that code we will an error: “EmailType is not defined.” That happens because Javascript compiler doesn’t know about the EmailType when defining PersonType because EmailType is defined after PersonType.

We can move EmailType before PersonType and it will work but what if inside the EmailType we have another property which is connected to PersonType. With the way closures inside javascript work — function gets defined but does not get executed until after this entire file has been executed. That’s not GraphQL’s issue, that’s Javascript issue when dealing with closures and closure scopes.

Start your code with:
node server.js

Visit:
http://localhost:4000/graphql

Add client query:
query {
  person(id: "1") {
    id
    firstname
    lastname
    emails {
      id
      email
      verified
    }
  }
}

Run the query and see the results. :)

I hope that this basic example will help you understand GraphQL better.