I've recently been working on a new project (more details once it's finished!). I wanted to take the opportunity to also learn GraphQL, which I've heard there's a bit of hype around. So for the backend of this new project I decided to use GraphQL, Node/Express and PostgreSQL.

This will be a short article on how I set up my project's backend using these libraries (focusing on GraphQL). It will include how to create utilise GraphQL's queries and mutations.

It's assumed you have a basic understanding of GraphQL, ExpressJS and PostgreSQL already.

I've created a public Github repository for all the code that is written below:

HarveyD/graphql-express-postgres-starter

A template to help you get your back-end running using GraphQL, Node/Express and PostgreSQL - HarveyD/graphql-express-postgres-starter

HarveyDGitHub

PostgreSQL

After installing PostgreSQL, open up PgAdmin (GUI for exploring your Postgres server) and create a new database.

Now we're going to create some two tables (users and projects) and insert some data for our example. Run the following sql scripts within PgAdmin in your database:

CREATE TABLE users(
	ID serial PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    joined TIMESTAMP,
    last_logged_in TIMESTAMP
);

INSERT INTO users (username, email, joined, last_logged_in)
VALUES ('HarveyD', 'harvey@email.com', current_timestamp, current_timestamp);

CREATE TABLE project(
	ID serial PRIMARY KEY,
	creator_id integer NOT NULL,
	created TIMESTAMP NOT NULL,
	title VARCHAR (50),
	description VARCHAR (255),
	CONSTRAINT fk_project_user
		FOREIGN KEY (creator_id)
		REFERENCES users (ID)
);

INSERT INTO project (creator_id, created, title, description)
VALUES (1, current_timestamp, 'A project title', 'A simple description')

Run select * from users and you should see the value that was just inserted.

Node

After running npm init, install the following packages:

npm install --save pg-promise express graphql express-graphql dotenv

Connection to Postgres

The first thing we have to do is create an adaptor that will allow us to query our local PostgreSQL server using Node. Create a file called pgAdaptor.js, inside it put:

require('dotenv').config()
const pgPromise = require('pg-promise');

const pgp = pgPromise({}); // Empty object means no additional config required

const config = {
    host: process.env.POSTGRES_HOST,
    port: process.env.POSTGRES_PORT,
    database: process.env.POSTGRES_DB,
    user: process.env.POSTGRES_USER,
    password: process.env.POSTGRES_PASSWORD
};

const db = pgp(config);

exports.db = db;

You'll notice I'm storing my PostgreSQL secrets in a .env file, it should look like:

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=databaseName
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres

To test our adaptor works as expected, create a simply query in a test file (test.js) like below:

const { db } = require("./pgAdaptor");

db.one('select * from users')
    .then(res => {
        console.log(res);
    });

and run it using node: node test.js. If the adaptor was configured correctly, the single user we added in the initial DB scripts should be output in the console.

GraphQL

First, create a directory schemas and in it, create three files:

• types.js
• query.js
• mutation.js

Types

First, we need to define the shape of our types. Within types.js, add:

const graphql = require("graphql");
const { GraphQLObjectType, GraphQLString } = graphql;

const UserType = new GraphQLObjectType({
  name: "User",
  type: "Query",
  fields: {
    id: { type: GraphQLString },
    username: { type: GraphQLString },
    email: { type: GraphQLString },
    joined: { type: GraphQLString },
    last_logged_in: { type: GraphQLString }
  }
});

const ProjectType = new GraphQLObjectType({
  name: "Project",
  type: "Query",
  fields: {
    id: { type: GraphQLString },
    creator_id: { type: GraphQLString },
    created: { type: GraphQLString },
    title: { type: GraphQLString },
    description: { type: GraphQLString }
  }
});

exports.UserType = UserType;
exports.ProjectType = ProjectType;

You'll notice the fields we have under UserType and ProjectType match the shape of our table in PostgreSQL. This will allow GraphQL to work with the data queried from our tables.

Queries

Now we will define the queries that we want GraphQL to execute. Within queries.js, add:

const { db } = require("../pgAdaptor");
const { GraphQLObjectType, GraphQLID } = require("graphql");
const { UserType, ProjectType } = require("./types");

const RootQuery = new GraphQLObjectType({
  name: "RootQueryType",
  type: "Query",
  fields: {
    project: {
      type: ProjectType,
      args: { id: { type: GraphQLID } },
      resolve(parentValue, args) {
        const query = `SELECT * FROM project WHERE id=$1`;
        const values = [args.id];

        return db
          .one(query, values)
          .then(res => res)
          .catch(err => err);
      }
    },
    user: {
      type: UserType,
      args: { id: { type: GraphQLID } },
      resolve(parentValue, args) {
        const query = `SELECT * FROM users WHERE id=$1`;
        const values = [args.id];

        return db
          .one(query, values)
          .then(res => res)
          .catch(err => err);
      }
    }
  }
});

exports.query = RootQuery;

We've imported the two types we created, and created two objects under fields. Each of the objects (project and user) accept one argument: id. This id is passed into a PostgreSQL query which simply retrieves the matching row. This will allow us to perform a GraphQL query like:

{ 
    users(id: 1) {
        username
        email
    }
}

and

{ 
    projects(id: 1) {
        title
        description
    }
}

These two examples are very basic, you could introduce additional queries such as getting all rows or filtering rows.

Mutations

Now we probably want a way to add/update/delete entries to our database using GraphQL. GraphQL calls these operations mutations. This article will only be introducing an add mutation for Projects.

In mutations.js, add:

const graphql = require("graphql");
const db = require("../pgAdaptor").db;
const { GraphQLObjectType, GraphQLID, GraphQLString, GraphQLBoolean } = graphql;
const { ProjectType } = require("./types");

const RootMutation = new GraphQLObjectType({
  name: "RootMutationType",
  type: "Mutation",
  fields: {
    addProject: {
      type: ProjectType,
      args: {
        creatorId: { type: GraphQLID },
        title: { type: GraphQLString },
        description: { type: GraphQLString }
      },
      resolve(parentValue, args) {
        const query = `INSERT INTO project(creator_id, created, title, description) VALUES ($1, $2, $3, $4) RETURNING title`;
        const values = [
          args.creatorId,
          new Date(),
          args.title,
          args.description
        ];

        return db
          .one(query, values)
          .then(res => res)
          .catch(err => err);
      }
    }
  }
});

exports.mutation = RootMutation;

Mutations look very similar to queries, except we have given RootMutation the type Mutation and implemented a different resolve function.

The above Mutation will allow us to perform the following in GraphQL:

mutation {
  addProject(creatorId: 1, title: "Test", description: "test description", imgUrl: "hello") {
    title
  }
}

Which will insert a new Project into our database with the title: Test, description: test description and imgUrl: hello.

Express

All that's left to do, is hook our mutations and queries into GraphQL and Express.

Create a file called app.js in the root directory. Add the following:

"use strict";
const graphql = require("graphql");
const express = require("express");
const expressGraphQl = require("express-graphql");
const { GraphQLSchema } = graphql;
const { query } = require("./schemas/queries");
const { mutation } = require("./schemas/mutations");

const schema = new GraphQLSchema({
  query,
  mutation
});

var app = express();
app.use(
  '/',
  expressGraphQl({
    schema: schema,
    graphiql: true
  })
);

app.listen(3000, () =>
  console.log('GraphQL server running on localhost:3000')
);

Here, we are just importing the queries and mutations we created and placing them in a GraphQLSchema object, from here GraphQL handles the rest.

Testing

Open up your browser, navigate to http://localhost:3000 and you should see a GraphQL web GUI. Test it out by running the following query:

mutation {
  addProject(creatorId: 1, title: "Test Project", description: "A test project") {
    title
  }
}

then performing a query on that mutation:

{
  project(id: 2) {
    title
    description
  }
}

The query should return the title and description of the project you created using the mutation.

Hope you found this tutorial easy to follow along. All the best with your future GraphQL endeavours!