ACNH GraphQL API Server

Overview

ACNH GraphQL API Server is designed to handle Animal Crossing - New Horizons data requests relating to all in-built resources in the game. Some examples include the bug/fish, clothing, and tool objects. This project uses data compiled by an open source Animal Crossing data team. (The link to their spreadsheet can be found on the project’s GitHub README page.)

The first time I came across the spreadsheet, I knew that I wanted to make an application which utilizes the different attributes of each object type.

For example, a Villager type can have the following fields:

Every villager can have similar attributes to another villager. For instance, multiple villagers can be sporty. Or, multiple villagers can be sporty and are cats. Given this type of structure, I thought it would be great to create a backend application which utilizes filterable options to retrieve specific sets of data.

GraphQL

First off, what is GraphQL? To put simply, GraphQL is a data query language for APIs that completes queries at runtime. GraphQL has distinctive advantages:

• Retrieve multiple resources via a single request
• Retrieve the exact fields you want
• Strong type system

The culmination of these properties fulfills half of my goal to create a dynamic API system that utilizes the different Animal Crossing data fields. But… can it do better?

For some background, the current GraphQL model looks something like this:

query ItemDemo {
  item(name: [parameter]) {
    items {
      Name
      Buy
      Sell
      HHASet
      HHASeries
    }
  }
}

From the schema, we can see that we’re able to pass in parameters, in this case the name parameter, in order to request a specific item from the database. But, what happens when we want to find an item based on buy or sell? Using the same model, we may be able to add in more optional parameters allowed by the GraphQL specification:

query ItemDemo {
  item(name: [parameter], sell: [sell price], buy: [buy price]) {
    items {
      Name
      Buy
      Sell
      HHASet
      HHASeries
    }
  }
}

We can add as many more optional parameters as we want, but we will soon run into a different problem. How do we know what default values we should use for every parameter? How should we scale this solution for the rest of the data not like item? What if we run into more fields that are added into the database?

I figured that I need an extra tool besides GraphQL to customize database queries.

The Shopify Solution Inspiration

Granted that I am not an experienced solutions architect, I decided to hit Google when I ran into this roadblock. I couldn’t find many open source code or threads on how to solve this kind of problem, so I decided to hit GraphQL’s website to try to see if there are large companies sharing their GraphQL implementation publicly.

When I read Shopify’s documentation, I knew I found my answer. The largest hint that I saw on their website was on their search syntax page. I saw that the data they provide to developers is quite similar to mine in format, albeit the dataset I’m working with is much simpler. It also gave me the ah-ha! moment where I realized that what they’re describing can be solved by a concept I learned at UC Davis!

Abstract Syntax Trees

Reading Shopify’s documentation made me realize that if I wanted to fine-tune search results using dynamic parameters, the best way to do that would be using abstract syntax tree parsing. The data that my API is using is located in a PostgreSQL database. SQL, like many programming languages, has a logical parsing method. If you can break apart an SQL statement, you can also build one!

In my case, there’s always an assumption to what the user is asking for. If a GraphQL request asks for limitations on sell or buy price, SQL should logically assume that it’s dealing with <, >, <=, >=, and = operators. If a request asks for a certain villager species, SQL should assume that it’s dealing with only an = operator. There are also special rules such as if a request wants multiple specifications. What if the server is requested for items that are either 900 or 8000 bells?

Order combination also matters! In order to preserve these rules, the server must be able to parse parentheses. In other words, API users should be able to request logical SQL commands on the Animal Crossing data.

Consider the following example of an inner filter request: query: "buy:\"<= 3000 AND > 2000\" color:\"(gray AND beige) OR black\"". This request can be broken down into two parts - buy and color. Both buy and color can be broken down even more. We want items that are between 3000 but greater than 2000 bells in buy price which are either gray and beige or black. These parameters can be translated into SQL code: SELECT * FROM table WHERE (buy <= 3000 AND buy > 2000) AND ((color = 'gray' AND color = 'beige') OR color = 'black'). In order to get from the GraphQL request to the final SQL code, the lexer parses each inner filter request and orders them via my pre-defined SQL rules.

For example, OR connectives have higher precedence than AND. AND and OR connectives are automatically marked as binary expressions. Each binary expression is further recursively parsed until it reaches a literal. Likewise, numerical operations are marked as unary expressions and are also recursively parsed until it reaches a literal.

Essentially, the server allows customizable options from the user, parses them using AST trees (via custom lexer and parser), and then rebuilds the options into a valid SQL statement. You can check out the specifics of this process in the expr and parse packages in the git repository.

And that’s it! The secret behind this server is that it uses AST trees to build customizable requests on the backend!

Resources

There’s so much more I can say about what I learned on this project, but I don’t want this description to get too long (I might link future posts on this page). I am, however, very happy that I was able to use abstract syntax trees (I thought it was quite theoretical during school) on a personal project!

Valuable resources that I used:

• GORM - ORM library for Golang
• GraphQL - GraphQL implementation from graphql-go
• GraphQL Handler - GraphQL handle
• GraphQL Website & Tutorial
• Shopify
• Lee Byron
• ECS 140A - Professor Cindy Rubio at UC Davis for AST Lectures
• Open Source Animal Crossing New Horizons Data

Valuable resources you should use to learn GraphQL:

• GraphQL Design Tutorial - Shopify