Leveraging DTOs for JSON database columns as an alternative to additional tables

Today I get to ramble a bit about a relatively new solution I've come up with for a problem that I'm sure a lot of you game developers have had before.

Here's the gist.

In Trounced (a simplistic kingdom builder game) players can have multiple kingdoms. Each kingdom then has its own land, armies, hero, alliances, and more. We're going to take a closer look specifically at land and armies.

In the original database design, land and armies were each their own tables with a foreign key to attach them to the kingdom.

Schema::create('lands', function (Blueprint $table) {
    $table->id();
    $table->unsignedInteger('kingdom_id');
    $table->foreign('kingdom_id')->references('id')->on('kingdoms');
    // Other stuff here
});

Then on the kingdom model, I could easily access them via a relationship.

// Models\Kingdom.php
public function land(): HasOne
{
    return $this->hasOne(Land::class);
}

public function army(): HasOne
{
    return $this->hasOne(Army::class);
}

However, there weren't any situations where I only fetched the individual army or land of a kingdom without also needing (or already having) the kingdom.

So I always ended up eager loading that data with the kingdom when I needed it.

// Always this
$kingdom = Kingdom::where('id', $kingdom_id)
    ->with('lands', 'armies')
    ->first();

// Never this
$land = Land::firstWhere('kingdom_id', $kingdom_id);

Now I'm pretty against premature optimization. But when it comes to game development I've learned (through painful experience) to be more prudent when it comes to infrastructure and database design. Some things are easier to change down the line than others.

So late one night as I couldn't sleep, I thought to myself "is it really necessary to query 3 tables to get this data, or could I just query 1?".

There has to be a better way.

I've been looking for an excuse to dabble in NoSQL for a bit now. While Trounced isn't the place for it, I figured this could be a chance to dip my toe in the schemaless pool.

So I decided to use JSONB columns (Postgres) on the kingdoms table with the lands and armies data. The result being now there's only one table to fetch and update all that data, which will result in improved performance as the game scales.

Consistent Structure

The biggest issue I've faced when it comes to using JSON columns is maintaining an expected structure.

While it's true that any arbitrary data can go in the column, you typically have certain parameters and validation to enforce. No one benefits when you throw data around willy-nilly.

I looked at a few options for putting data into JSON and while it's easy enough to validate on the way in, I also wanted to pull a predictable and usable structure (read: a class) out of the database.

Laravel already has the ability to cast specific fields on a model into an array or even a collection.

// Models\Kingdom.php
protected $casts = [
    'land' => 'array',
    'army' => 'array',
];

Which makes it easy to do things like:

// Adjusting the free land in a kingdom
$kingdom->land['free'] += 100;

// Fetching the free land in a kingdom
$freeLand = $kingdom->land['free'];

But this syntax is still a bit cumbersome.

There's no IDE autocompletion.

There's no validation.

And I'd prefer being able to access my properties with arrows all the way down rather than having to know where the array structure begins.

// Is it this...?
$freeLand = $kingdom->land->free;

// ...or this?
$freeLand = $kingdom->land['free'];

Not to mention validating deeply nested structures can easily becomes a tangle of conditional logic.

Data Transfer Objects...to the rescue!

Enter Data Transfer Objects, or DTOs.

If you aren't familiar, check out Martin Fowler's brief summary on them (and read a few of his other articles while you're there. Seriously, the man is a genius.)

I opted to use Spatie's DTO package and a bit of additional custom code for easy casting. I decided to map each JSON column to its own DTO, so I created two classes for land and for army.

On their own, DTOs aren't terribly useful. It's a lot of work to map your data and keep it up to date as your application changes. There's always a temptation to cheat the system for edge cases. Then all of a sudden your clean solution has turned against you and you wish you had just stuck to associative arrays.

So I decided to leverage Laravel's custom casts on my kingdom model to make it painful to take any shortcuts down the road.

// Models\Kingdom.php
protected $casts = [
    'land' => KingdomLand::class,
    'army' => KingdomArmy::class,
];

There's more behind the scenes (which we'll get into), but whenever I fetch a kingdom the JSON column of land is cast into the KingdomLand DTO. When I save the kingdom it translates that DTO into JSON before sticking it in the database.

Let's take a closer look, starting at the back and working our way forward.

1. Castable DTO

DTOs by default aren't castable the way Laravel expects. However, because Laravel supports custom casts, we can easily create a generic wrapper cast to use for all our DTOs.

This is the DTO Cast that implements Laravel's rules for creating a custom cast.

Loads of code ahead. Summary below.

namespace App\Infrastructure\Casts;

use Illuminate\Contracts\Database\Eloquent\CastsAttributes;

class DTOCast implements CastsAttributes
{
    protected string $class;

    /**
     * @param string $class The DataTransferObject class to cast to
     */
    public function __construct(string $class)
    {
        $this->class = $class;
    }

    /**
     * Cast the stored value to the configured DataTransferObject.
     */
    public function get($model, $key, $value, $attributes)
    {
        if (is_null($value)) {
            return;
        }

        if (is_array($value)) {
            return new $this->class($value);
        }

        return new $this->class(json_decode($value, true));
    }

    /**
     * Prepare the given value for storage.
     */
    public function set($model, $key, $value, $attributes)
    {
        if (is_null($value)) {
            return;
        }

        if (is_array($value)) {
            $value = new $this->class($value);
        }

        if (!$value instanceof $this->class) {
            throw new InvalidArgumentException("Value must be of type [$this->class], array, or null");
        }

        return json_encode($value->toArray());
    }
}

I want to pass any of my DTOs into this class and appropriately get the DTO or set JSON.

From there, I wanted to add a few more useful methods that'll get used in all the DTOs. This is the class instance that my DTOs will extend from, so I also make sure to extend from Spatie's DataTransferObject to get access to all those goodies.

namespace App\Infrastructure\Values;

use App\Infrastructure\Casts\DTOCast;
use Illuminate\Contracts\Database\Eloquent\Castable;
use Illuminate\Contracts\Support\Arrayable;
use Spatie\DataTransferObject\DataTransferObject;
use JsonSerializable;

abstract class CastableDTO extends DataTransferObject implements Castable, JsonSerializable, Arrayable
{
    public static function castUsing(array $arguments)
    {
        return new DTOCast(static::class);
    }

    public function toJson()
    {
        return json_encode($this->toArray());
    }

    public static function fromJson($json)
    {
        return new static(json_decode($json, true));
    }

    public function jsonSerialize()
    {
        return $this->toJson();
    }
}

This abstracts the generics so I can cleanly create my own castable DTOs with very little boilerplate.

The End Result

Here's what the KingdomLand DTO ends up looking like. Notice I'm extending from my CastableDTO I just created. This is what gets type-hinted in the $casts array on the kingdom model.

namespace App\Domain\Kingdom\Values;

use App\Infrastructure\Values\CastableDTO;

class KingdomLand extends CastableDTO
{
    public int $free = 0;
    public int $mines = 0;
    public int $forts = 0;
    public int $barracks = 0;
    public int $training_grounds = 0;
    public int $total = 0;
}

Clean.

Now, whenever I fetch a kingdom from the database, the land column automatically gets cast into this class. I can confidently interact with it before saving back to the database.

This makes it easy to validate each individual property. The Spatie package allows for custom validations as well, so the sky's the limit. Plus I can easily set default values for each property.

What if I wanted to add a new type of land structure later on? Easy, just add the property to the KingdomLand DTO with a good default and it's off to the races.

What are the bonuses of doing it this way?

• IDE typehinting and autocompletion.
• Validation.
• Easily nested DTOs for more complex data structures.
• Store all the data in a single JSON column, meaning fewer queries.

Plus you can also add your own custom methods to the class (making it a Value Object) to make your syntax more expressive.

class KingdomLand extends CastableDTO
{
    // public int $blabla...

    public function getMilitaryCount(): int
    {
        return $this->forts + $this->barracks + $this->training_grounds;
    }
}

// Simple and expressive
$kingdom->land->getMilitaryCount();

Is this the best place for those types of methods? I could probably be persuaded otherwise, but it is certainly an easy place to store logic and it's easy to work with.

The Downsides

Querying limited data from these JSON columns is a challenge I have yet to find a clean solution for.

For example, when searching for kingdoms to attack I only want to show the total acreage of a given kingdom. The attacker shouldn't know how much of the kingdom's land is designated to offense vs defense.

So to get this limited info, I have to query it like so.

$kingdom = Kingdom::select('name', 'land->>total as land_total', 'alert_level')
    // ->where...
    ->get();

Not very clean.

And now I also have to remember to access that data in a specific way.

// Works
$kingdom->land_total;

// Doesn't work
$kingdom->land->total;

Ick.

I could probably opt to grab the entire land column, cast it to my DTO, and then filter out the data I need, but that feels wasteful to ship data over the wire that is immediately thrown out.

Is there a better solution? Probably, and I've love to hear it if someone has it. But at least for now, the current DTO solution is miles ahead of the previous one.