Data Hopping: From SQL to GraphQL to JSON

DATA HOPPING

CERN, the European Organization for Nuclear Research, claimed their Large Hadron Collider collects 10 of petabytes of data per year. The Vatican Apostolic Database is around 2.9 petabytes. If there is one thing that can be said about databases: they scale. They scale really well. Although NoSQL, Hadoop, and their brethren are fantastic choices for storing data relevant to your apps there is still, undoubtedly, validity behind traditional relational databases. Rather than get into the details about what the advantages and disadvantages are around NoSQL vs SQL (a simple Google search will surely yield a plethora of documents covering each side of that debate) we are going to assume that you are already on the SQL wagon.

The intention of this article is, by no means, to teach you how to query petabytes of data on nuclear research. We are going to assume you already know everything there is to know about that and focus on something truly important: Board Games. More specifically the entire database of board games as provided by the website Board Game Geek.

The Data

This particular dataset that we are going to be working with is a single table. Traditionally this is not how you structure a database. We call this a denormalized dataset. In order to normalize a dataset you would break the dataset in several smaller tables in order to reduce redundancy, reduce storage, and improve data quality (it is much easier to make sure that elements are named correctly when they are on a single row in a table). With this, though, comes complexity of understanding complex joins in your database and the need for creating views to reduce complexity to non-data savvy users (not to mention the ETL headaches).

In an effort to keep the complexity to a minimum we will be using the denormalized dataset via SQLite. SQLite is a good choice as it is cross-platform and it is file based so we don’t need to worry about the complexities of creating a database. Just don’t expect this to be a fully scalable solution. Lets look at what a query would look like in our database:

$ sqlite3 database.sqlite
sqlite> select [attributes.boardgamedesigner]  
from BoardGames 
where [details.name] = "Power Grid";
Friedemann Friese

The SQL Language is out of scope for this particular blog so, for the purposes of this discussion, know that this query retrieves the column named boardgamedesigner from the table BoardGames where the name of the game is “Power Grid”.

The API

With the database firmly in place and a solid understanding of the structure of our table we are poised to start implementing our API so that users can actually query their data. We are, again, at a cross-roads of how to build our API: RESTful or GraphQL. As I stated with the SQL vs NoSQL debate, again, I have no interest in starting a flame war about the one true solution. They both have their own distinct advantages and that should be researched prior to delivering a project as to which meets your specific needs. For the purposes here, however, we want something that is fast and will only return targeted data. This is a prime use case for GraphQL and thus our tool of choice.

We’ll be using the following node modules to build out server.js:

• sqlite: A wrapper library that adds ES6 promises and SQL-based migrations API to sqlite3 (docs).
• express: Web framework for Node
• express-graphql: GraphQL HTTP Server Middleware

These three modules will allow us to connect to and query our SQLite database and map the data to our GraphQL schema. Speaking of schemas lets take a look at our GraphQL schema:

var { buildSchema } = require('graphql');
var schema = buildSchema(`
  type BoardGame {
    getDesc: String!
    getImage: String!
  }
  type Query {
    getGame(gameName: String): BoardGame
  }
`);

The schema uses the getGame function of Query to retrieve the name of the game and the BoardGame object contains the methods to return the description and image related to that board game based on the results returned from the query.

Next we define our BoardGame object. Note that we are using ES6 classes.

class BoardGame {
  constructor(gameName) {
    this.name = gameName;
  }
  getDetails() {
    return db.get(‘
     SELECT * 
     FROM Boardgames 
     WHERE [details.name] =?’, this.name)
  }
  getDesc() {
    return this.data["details.description"];
  }
  getImage() {
    return this.data["details.image"];
  }
}

This is going to use a constructor to set the name of the game. This will be passed to the instance when a new BoardGame object is created using the getGame function. It also defines three functions: getDetails() which gets the data from the database for the specific game, getDesc() which returns the description of the game as defined by the query, and getImage() which gets the URL for the image of the board game as defined by the query.

Finally we create our root which is going to get passed to our Express app along with the schema for our data

var root = {
  getGame: function({gameName}) {
    var b = new BoardGame(gameName);
    return b.getDetails().then((d) => {
      b.data = d;
      return b;
    })
  }
};

This will instantiate our BoardGame object and execute the query to get the details from the database. Finally we create our GraphQL HTTP server with the schema and rootValue we defined above.

app.use('/graphql', graphqlHTTP({
  schema: schema,
  rootValue: root,
  graphiql: true,
}));

Note that we set graphiql to true. This ensures that we can access the GraphiQL UI in the browser:

http://localhost:4000/graphql

The GraphiQL UI

The full code for the API can be found on the GitHub repo.

The Client

The client is our bread and butter. We want to expose our API to users in a way that is fast, friendly, and highly customizable. By creating an interface as we did above we enable our users to write a query against our GraphQL API as such

{
  getGame(gameName: "Agricola") {
    getDesc
    getImage
  }
}

The root is a function that passes the name-value pair for gameName which is what will be used when we query our SQLite database. The getDesc and getImage are the two public methods that we set up which enable to user to obtain specific details about the object they are querying. By adding in more public methods we can easily expose more of the elements returned by the query. Ultimately this gives the user a concise way of structuring a query to pull the specific data they need subsetted to the exact context they are looking to retrieve.

Conculsion

Remember that, at the end of the day, these are just tools. There are no right or wrong tools to use for building applications on the web. I could of easily written this using MongoDB and REST and achieved the same, exact results. Granted the API may not of been as nice and the maintenance of the MongoDB might of proved challenging which is why every tool needs to be carefully planned out and decided upon based on the needs of the individual, the team, and the customer.

• BG

Github Repo: https://github.com/ignoreintuition/graphql_bgg