—August 3rd, 2015

Building a GraphQL Server with Node.js and SQL

GraphQL is a new technology from Facebook. It aims to replace REST as a go-to API standard for developing rich applications. In this article we’ll build a simple GraphQL server using Node.js and an SQL database.

In this blog post we are going to build a GraphQL backend server for a news feed like the one described in the blog post about building a news feed with Relay. Our server will be very simple – we will only have one endpoint that runs GraphQL queries. Data models available through it will be User and Story, where each story will have an author. (In Facebook’s blog post “stories” are from user’s news feed, not his own stories. This article will use a simpler model for clarity.)

We will use:

Node.js
GraphQL.js library
SQLite3 database for storage – it’s simple and doesn’t require separate server
Hapi as an HTTP server – there is only going to be one endpoint, so in this
example there would be little difference between server APIs. We use Hapi as
HTTP server ourselves, so we’ll use that.
Babel – we’ll write in ES6/7, because it’s awesome 😉

This article assumes some understanding of GraphQL (GraphQL Introduction should be enough), so it won’t cover the basics for brevity.

Full code is available on GitHub.

Anatomy of GraphQL

GraphQL consists of two parts: a type system that allows one to describe the schema of data that is available on the server and a query language that allows the client to describe the data it needs and its shape. An important thing about the schema is that it makes no assumptions about how the data is stored, it only describes what the consumer of the API can use.

A one-to-many relationship is a relationship where a parent record can be linked to many children. For example, a user can be an author of many stories. In our SQL database it means the Story table will have an author column, storing an id of a User as a foreign key. The User table will not need to store any references to stories in the SQL schema.

However in the GraphQL schema, Story will have an author field of type User and User will have stories. This allows queries to follow relationships (connections) from both sides, regardless of the underlying SQL schema. The way the data is stored and represented in the database is an implementation detail.

Basic building block of the GraphQL schema is Object. Object has a name and a map of fields. In their simplest form, fields will only have a type. Here is a simple example User object type, with fields id and name.

const User = new GraphQLObjectType({
  name: 'User',
  fields: () => ({
    id: {
      type: GraphQLID
    },
    name: {
      type: GraphQLString
    }
  })
});

All fields can accept arguments and have a resolve method. resolve is a key part of hooking the schema and types up with an actual data storage and defines which data the field will return. The GraphQL executor calls resolve method for each field it traverses. By default, resolve just returns the matching property of the parent (so for the field id it returns parent['id']).

To use that type we’ve just defined, GraphQL schema requires a special object type, query, to be defined. query fields will serve as an entry point for the GraphQL queries.

const Query = new GraphQLObjectType({
  name: 'Query',
  fields: () => ({
    viewer: {
      type: User,
      resolve() {
        return {
          id: '123',
          name: 'freiksenet'
        }
      }
    }
  })
});

const Schema = new GraphQLSchema({
  query: Query
});

We can test it by calling the graphql function with a query.

graphql(Schema, `{
  viewer {
    id,
    text
  }
}`).then((result) => console.log(result));
// ==>
{
  data: {
    viewer: {
      id: '123',
      name: 'freiksenet'
    }
  }
}

resolve is passed some additional arguments. The most important ones are the first three:

a result of the parent resolve (if it was a promise, then it is fulfilled
first)
arguments passed to the field, like format passed to text in this example query

{
  viewer {
    id,
    text(format: "markdown")
  }
}

global context passed to graphql function call as the third argument. This can be used to pass the data that resolve on any level might need. It can be, for instance, a database connection, a user session or a reference to a cache.

As there is a default resolve implementation, we don’t need to provide an implementation for all fields.

Connecting to the database

Let’s create a small SQLite3 database and fill it in with data. We’ll start with one table: Story (you’ll find the script in ./scripts/createdb.sql).

CREATE TABLE User
(
  id INTEGER PRIMARY KEY,
  name VARCHAR(255)
);

INSERT INTO User('name')
VALUES
('freiksenet'),
('fson'),
('Hallie'),
('Sophia'),
('Riya'),
('Kari'),
('Estrid'),
('Burwenna'),
('Emma'),
('Kaia'),
('Halldora'),
('Dorte');

Then we’ll add an open database connection to our GraphQL context. Unfortunately, sqlite3 node library doesn’t return promises, so we have to use Bluebird’s promisify to monkey patch it.

import sqlite3 from 'sqlite3';
import {promisify} from 'bluebird';

const db = new sqlite3.Database('db.sqlite3');

db.get = promisify(db.get);
db.all = promisify(db.all);

graphql(Schema, `{
  viewer {
    id,
    name
  }
}`, {db}).then((result) => console.log(result));

The result won’t change yet, but now our resolve will have an open database connection available to it. Time to use it.

const Query = new GraphQLObjectType({
  name: 'Query',
  fields: () => ({
    viewer: {
      type: User,
      resolve(parent, args, {db}) {
        return db.get(`
          SELECT * FROM User WHERE name = 'freiksenet'
        `);
      }
    }
  })
});

If we call our graphql example again we get a new result.

{
  data: {
    viewer: {
      id: "1",
      name: "freiksenet"
    }
  }
}

viewer root field is used at Facebook to retrieve currently logged-in user. One can replicate such functionality by passing, e.g., user id from session to GraphQL context and using it in the viewer query. We can modify our Query to use the context, but in this example we will pass the same logged-in user all the time.

const Query = new GraphQLObjectType({
  name: 'Query',
  fields: () => ({
    viewer: {
      type: User,
      resolve(parent, args, {db, userId}) {
        return db.get(`
          SELECT * FROM User WHERE id = $id
        `, {id: userId});
      }
    }
  })
});

graphql(Schema, `{
  viewer {
    id,
    name
  }
}`, {db, userId: 1}).then((result) => console.log(result));

Using arguments

Let’s add a root field that allows us to retrieve any User by id. Note how we will use GraphQLNonNull modifier to make the argument required.

const Query = new GraphQLObjectType({
  name: 'Query',
  fields: () => ({
    viewer: {
      type: User,
      resolve(parent, args, {db, userId}) {
        return db.get(`
          SELECT * FROM User WHERE id = $id
        `, {id: userId});
      }
    },
    user: {
      type: User,
      args: {
        id: {
          type: new GraphQLNonNull(GraphQLID)
        }
      },
      resolve(parent, {id}, {db}) {
        return db.get(`
          SELECT * FROM User WHERE id = $id
          `, {$id: id});
      }
    },
  })
});

We’ll use the following query to test this:

{
  user(id: "2") {
    id,
    text
  }
}
// ==>
{
  data: {
    user: {
      id: "2",
      name: "fson"
    }
  }
}

Relationships

To add some stories for the users, we’ll first create a database table.

CREATE TABLE Story
(
  id INTEGER PRIMARY KEY,
  text TEXT,
  author INTEGER,
  FOREIGN KEY(author) REFERENCES User(id)
);

INSERT INTO Story('text', 'author')
VALUES ... # see scripts/createdb.sql

Now we can define Story in our schema.

const Story = new GraphQLObjectType({
  name: 'Story',
  fields: () => ({
    id: {
      type: GraphQLID
    },
    text: {
      type: GraphQLString
    },
    author: {
      type: User,
    }
  })
});

Unlike SQL, we define author to be of actual User type, instead of a foreign key. We also want to be able to get stories from the User side, so we modify the type accordingly.

const User = new GraphQLObjectType({
  name: 'User',
  fields: () => ({
    id: {
      type: GraphQLID
    },
    name: {
      type: GraphQLString
    },
    stories: {
      type: new GraphQLList(Story),
    }
  })
});

We’ll also add a story root field for convenience.

story: {
  type: Story,
  args: {
    id: {
      type: new GraphQLNonNull(GraphQLID)
    }
  },
  resolve(parent, {id}, {db}) {
    return db.get(`
      SELECT * FROM Story WHERE id = $id
      `, {$id: id});
  }
}

So far so good. However, GraphQL doesn’t know anything about our storage system, so if we try to retrieve stories from User or author from Story, we won’t be able to get what we want – after all, author is a foreign key id in our database and stories are not in our database at all. Luckily, we can define a custom resolve for those fields to fix that.

const Story = new GraphQLObjectType({
  name: 'Story',
  fields: () => ({
    id: {
      type: GraphQLID
    },
    text: {
      type: GraphQLString
    },
    author: {
      type: User,
      resolve(parent, args, {db}) {
        return db.get(`
          SELECT * FROM User WHERE id = $id
        `, {$id: parent.author});
      }
    }
  })
});

const User = new GraphQLObjectType({
  name: 'User',
  fields: () => ({
    id: {
      type: GraphQLID
    },
    name: {
      type: GraphQLString
    },
    stories: {
      type: new GraphQLList(Story),
      resolve(parent, args, {db}) {
        return db.all(`
          SELECT * FROM Story WHERE author = $user
        `, {$user: parent.id});
      }
    }
  })
});

Now everything should work as expected.

{
  user(id: "4") {
    id,
    name,
    stories {
      id,
      text
    }
  }
}
// ==>
{
data: {
  user: {
    id: "4"
    name: "Sophia"
    stories: [
      {
        id: "8"
        text: "\"How about if I sleep a little bit longer and forget all this nonsense\", he thought, but that was something he was unable to do because he was used to sleeping on his right, and in his present state couldn't get into that position."
      }
    ]
  }
}

{
  story(id: "1") {
    id,
    text,
    author {
      id,
      name
    }
  }
}
// ==>
{
  data: {
    story: {
      id: "1"
      text: "One morning, when Gregor Samsa woke from troubled dreams, he found himself transformed in his bed into a horrible vermin."
      author: {
        id: "1"
        name: "freiksenet"
      }
    }
  }
}

Hooking up with the server

GraphQL needs only one endpoint, so it’s extremely easy to add it to the server. It’s usually a POST endpoint that accept a JSON payload with two keys – query and variables. If we parse JSON, we can pass it to GraphQL function like we did in our tests. You can see whole server code at ./server.js, The handler is shown here.

async function graphQLHandler(request, reply) {
  const {query, variables = {}} = request.payload;
  const result = await graphql(
    Schema,
    query,
    {db: request.db},
    variables
  );
  return reply(result);
}

server.route({
  method: 'POST',
  path: '/',
  handler: graphQLHandler
});

Going forward

There аrе so many ways we can expand our server and it’s obviously out of scope of a single blog post. Topics we are likely to cover in follow-up posts include:

Connections – our relationships are quite primitive; one can’t paginate or filter
them, and there is no way to find the number of related items. We’ll cover
Connection pattern, that is used to solve this at Facebook in more depth. This
pattern is used by Relay to implement pagination on the client.
Mutations – operations with side effects are called mutations in GraphQL. They
can be used to modify the data stored in the database. For those operations,
one would often want to do data validation, preferably without much extra
work. We already have Object definitions, which include information about their
fields and thus can be used for validation. Unfortunately, it’s not possible
to pass Objects as arguments in GraphQL, because they can have circular
dependencies and so are unrepresentable as JSON. However, there is an
alternative called InputObject that can be used in arguments. We’ll cover how to use them in
mutations and their automatic generation from Object definitions.
We will also cover some of InputObject use cases outside of the mutations.
Users and permissions

Building a GraphQL Server with Node.js and SQL

Anatomy of GraphQL

Connecting to the database

Using arguments

Relationships

Hooking up with the server

Going forward

Further reading

Reindex Blog

Anatomy of GraphQL #

Connecting to the database #

Using arguments #

Relationships #

Hooking up with the server #

Going forward #

Further reading #

Reindex Blog

Anatomy of GraphQL

Connecting to the database

Using arguments

Relationships

Hooking up with the server

Going forward

Further reading