Creating a 2D React match replay tool for PUBG - Part 2

This is the second post in the pubg.sh series.

The full API source code can be found at pubg.sh/api on GitHub

GraphQL backend

Weighing in at less than 1K lines of code, the backend portion of the application is rather small. It adds a caching layer on top of PUBG’s API and queues users for data retrieval when the rate limit is reached. Once data is fetched for a user, their page is updated via websockets automatically.

An underlying motiviation for building pubg.sh was to experiment with and learn GraphQL. Main takeaway: it’s great. Although the inversion of control in the resolver chain can be cumbersome when wanting to fetch nested data with joins to save on database round trips, that’s often an unnecessary optimization. The gains in state management on the front-end with the Apollo client (covered in part 3) far outweigh any pains on the backend.

Embrace SQL

I’m not a big fan of ORMs - I’ve used Hibernate extensively in my past Java days, and don’t care to repeat that experience. Instead, I choose to embrace SQL and craft my own queries. Using ES6’s tagged template literals, we can achieve nearly native-feeling queries while guarding against injection attacks. I wrote a library, pgr to help with this. The pgr README shows some example usage, but here’s a method lifted from pubg.sh-api:

Sidenote: I didn’t come up with the tagged template literal pattern. Other libraries, like sql-template-strings, have been doing it for years. I just tailored the ergonomics to suit my programming style and requirements.

async find(shardId, { id, name }) {
    return query.one(sql`
        SELECT p.id, p.name, p.last_fetched_at AS "lastFetchedAt",
            pfi.fetch_interval_ms AS "fetchIntervalMs"
        FROM players p
            LEFT JOIN player_fetch_intervals pfi ON p.name = pfi.name
        WHERE shard_id = ${shardId}
            ${sql.if('AND p.id = ?', id)}
            ${sql.if('AND p.name = ?', name)}
    `, {
        rowMapper: row => ({ ...row, shardId }),
    })
}

There’s some neat stuff going on here. Let’s unpack it.

  1. The WHERE shard_id = ${shardId} line might look vulnerable to SQL injection at first glance (I certainly thought so). However, since the query is wrapped in the sql tagged template, pgr will sanitize the incoming input with the battle-hardened pg-format library.

  2. The find method is capable of finding a player by either name or id. We handle this in the sql.if() statements. These will append the appropriate query, replacing the ? for the argument, if and only if the argument is truthy - otherwise, the line is omitted altogether.

  3. rowMapper is a function you can provide to run each row in the result set through. In this case, at most one row will be returned since we’re using query.one, but it will still go through the mapper. We’re simply appending the shardId to the result.

I’ll take SQL all day.

Up next

The core of the application lies on the front-end. Let’s explore that in Part 3: React + Canvas (with Konva.js) frontend.