How to connect GraphQL and PostgreSQL

Here Ill explain how to connect GraphQL to PostgreSQL.



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.


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:

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.

Never miss a post from Snehal Kumar, when you sign up for Ednsquare.