Matthew Daly's Blog

I'm a web developer in Norfolk. This is my blog...

13th October 2019 11:10 pm

Flexible Data Types With the JSON Field

Relational databases have many advantages over other data stores. They’re (mostly) solid, mature products, they have the means to prevent data duplication while still allowing related data to be accessed, and they allow for easy enforcement of data types. However, the latter point has also historically made them less flexible compared to document databases such as MongoDB, which allow for fields to be set dynamically, making it much easier to set content arbitrarily.

One area in which this has caused particular problems is with content management systems, where you might want to be able to set custom content types that need to be treated the same in some circumstances, and have some fields in common, but store different data. If you want to be able to store arbitrary data against a particular entity, historically the main way to do that is to create a meta table to contain keys and values, and set the entity ID as a foreign key in the new table.

Wordpress is a common example of this. Any piece of content is stored in the wp_posts table, which in addition to the generic structure of a post, also includes the post_type field. It’s possible to create and register your own post types, but it’s not possible to store additional custom data in that table. Instead, it’s stored as keys and values in the wp_postmeta table, meaning you need to do a join to retrieve that content at the same time, making for more complex queries.

Another approach is to have a generic entity table that contains the common fields, and separate tables for the rest, and then set up a one-to-one relationship between them. However, that can be fiddly too because it doesn’t allow for custom types in the same way, so it may not fit with your use case if you need to be able to create arbitrary content types, such as for a CMS that allowed for custom content types.

Introducing JSON fields

JSON fields allow you to bring some of the flexibility of document databases to the relational world. They allow you to store whatever arbitrary text data you wish as JSON, and retrieve it as usual. More importantly, they also allow you to query by that data, so you can easily filter by fields that need not be set in stone with a database schema.

This means that in the above example of a CMS, instead of having a separate meta table, you can instead store the meta values in a JSON field, thus removing the need for a join and simplifying querying by those values.

PostgreSQL has had this capability for a long time, but it’s only comparatively recently that MySQL and MariaDB versions that support it have become widespread. Here I’ll show you how you might use it in a Laravel application.

The example will be a content management system with flexible content types. The first step is to create the migration to add the new content table:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateContent extends Migration
* Run the migrations.
* @return void
public function up()
Schema::create('content', function (Blueprint $table) {
$table->string('type', 20);
* Reverse the migrations.
* @return void
public function down()

Here we’re specifying the following fields:

  • An auto-incrementing ID (feel free to swap this out for a UUID if it makes sense for your application)
  • A string denoting the content type. If you want to limit the values these can accept, you can replace it with an ENUM field
  • The JSON field, named attributes
  • The standard Laravel timestamp fields, created_at and updated_at

If there are certain fields that are common to all of your content types, it would also make sense to define them as fields in the usual way, rather than use the JSON field, since compulsory fields should be enforced by the database.

Next, we’ll create the model:

namespace App;
use Illuminate\Database\Eloquent\Model;
class Content extends Model
protected $table = 'content';
protected $casts = [
'attributes' => 'array'

Note that we cast the attributes field to an array. If we didn’t do this, we’d need to manually run json_encode() and json_decode() on the field to get it back in a usable form. As it is, we can now easily retrieve fields using array access.

With that done, we can now set up some data:

$c = new App\Content;
$c->type = 'page';
$c->attributes = [
"type" => "info",
"title" => "Terms",
"content" => "Our Terms",
"layout" => "info",
$c = new App\Content;
$c->type = 'link';
$c->attributes = [
"type" => "external",
"link" => "",
$c = new App\Content;
$c->type = 'page';
$c->attributes = [
"type" => "promotional",
"title" => "My page",
"content" => "This is my page",
"layout" => "default",

As you can see, we’ve been able to set out whatever arbitrary fields we wish on these items. We can then call toArray() on a model to get all the fields, including the attributes, or we can call $c->attributes to get all those attributes together. We can also get a field via array access, eg $c->attributes['type'].

Querying the data

The syntax for querying JSON fields is a little bit fiddly:

SELECT * FROM `content` WHERE attributes -> '$.type' = 'promotional';

Fortunately, Eloquent makes it much simpler:

Content::where('attributes->type', 'promotional')->get();

It’s also possible to order by a JSON field value, but at the time of writing there’s no neat syntax for it, so you have to drop down to writing it using orderByRaw in Eloquent:

Content::orderByRaw("attributes-> '$.type'")->get();

Eloquent also supports a few other JSON query types, such as querying if an array contains a value, and I suggest referring to the documentation if you want to know more.

Other applications

There are many other scenarios where this approach can be useful. For instance, e-commerce sites often sell many different products that may have arbitrary properties, and it may be necessary to sort and filter by different properties for different types of products. A store that sells, among other things, shoes and storage containers, might need a colour and capacity field for storage containers, and a colour and size field for shoes. Using this approach, you can set up your database in such a way that those arbitrary fields can be set up when needed, and used for filtering.

This approach is not without its downsides. Any data that’s stored in a JSON field can’t be validated by the database in the same way, so the burden of ensuring that it remains in a consistent state is moved to your application code. However, it’s no worse than it would be if you used a document database, and unlike with a document database you can combine JSON and more traditional fields as you see fit.

22nd September 2019 7:00 pm

Storing Wordpress Configuration in Environment Variables

Wordpress configuration can be a serious pain in the proverbial. Hard-coding configuration details in a PHP file is not a terribly safe way of storing the details for your database, as if the server is misconfigured they can be exposed. In addition, it can be a chore to copy and populate the wp-config.php file to a new deploy.

A fundamental principle of The Twelve-Factor App is that config should be stored in the environment. While Wordpress does predate this, there’s no reason why we can’t abide by this. Storing Wordpress configuration in environment variables rather than the wp-config.php file has the following advantages:

  • It’s more secure since the config is not stored in a file in the web root, but in the web server config
  • It makes managing the wp-config.php file less of a chore - it can be safely committed to version control, and you won’t need to change it to match your local configuration, running the risk of accidentally committing and pushing to production with broken config
  • Deployment to new servers is simpler because there’s no need to update the wp-config.php
  • The risk of neglecting to change the database details and accidentally messing up the production database when working locally is virtually eliminated

I’ve seen solutions for this that use DotEnv, but you don’t actually need to install that to be able to use environment variables with Wordpress. In fact, in some way it’s better if you don’t as too many developers use .env files in production. PHP natively has the ability to get data from environment variables using the getenv() function, so it’s easier to use that than to pull in a third-party library.

Here’s an abbreviated example of a wp-config.php file that’s been updated to pull the settings from environment variables:

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define( 'DB_NAME', getenv('DB_NAME') );
/** MySQL database username */
define( 'DB_USER', getenv('DB_USER') );
/** MySQL database password */
define( 'DB_PASSWORD', getenv('DB_PASSWORD') );
/** MySQL hostname */
define( 'DB_HOST', getenv('DB_HOST') );
/** Database Charset to use in creating database tables. */
define( 'DB_CHARSET', 'utf8' );
/** The Database Collate type. Don't change this if in doubt. */
define( 'DB_COLLATE', '' );
define( 'AUTH_KEY', getenv('AUTH_KEY') );
define( 'SECURE_AUTH_KEY', getenv('SECURE_AUTH_KEY') );
define( 'LOGGED_IN_KEY', getenv('LOGGED_IN_KEY') );
define( 'NONCE_KEY', getenv('NONCE_KEY') );
define( 'AUTH_SALT', getenv('AUTH_SALT') );
define( 'SECURE_AUTH_SALT', getenv('SECURE_AUTH_SALT') );
define( 'LOGGED_IN_SALT', getenv('LOGGED_IN_SALT') );
define( 'NONCE_SALT', getenv('NONCE_SALT') );
$table_prefix = 'wp_';
define( 'WP_DEBUG', getenv('WP_DEBUG') );
/* That's all, stop editing! Happy publishing. */
/** Absolute path to the WordPress directory. */
if ( ! defined( 'ABSPATH' ) ) {
define( 'ABSPATH', dirname( __FILE__ ) . '/' );
/** Sets up WordPress vars and included files. */
require_once( ABSPATH . 'wp-settings.php' );

If you’re using Lando for local development, you will need to specify a file to include that contains the environment variables you wish to set, as in this example:

name: wordpress
recipe: wordpress
webroot: .
- .env

This filename can be any arbitrarily chosen name. Then, you define the values for those variables in the same way you normally would in a .env file. Here’s an abbreviated example that excludes the crypto settings (though those should be placed here too):


This will work fine during local development, but in production, or if you’re using something like Vagrant for local development, you’ll want to set the environment variables in the server configuration. For Apache, this is best set in the Virtualhost configuration, although you should be able to set it in an .htaccess file if all else fails. You need to use the SetEnv directive, as in this example:

SetEnv DB_NAME wordpress
SetEnv DB_USER wordpress
SetEnv DB_PASSWORD wordpress
SetEnv DB_HOST database
SetEnv WP_DEBUG true

For Nginx, assuming you’re using FastCGI, you need to set it in the server configuration for that site using the fastcgi_param directive, as shown below:

fastcgi_param DB_NAME wordpress;
fastcgi_param DB_USER wordpress;
fastcgi_param DB_PASSWORD wordpress;
fastcgi_param DB_HOST database;
fastcgi_param WP_DEBUG true;

Since Wordpress doesn’t ship with any kind of command-line task runner, this should be sufficient for most installs. However, if you’re using WP CLI, that will break it as it won’t have access to environment variables set by Apache or Nginx, so you’ll also need to set them for the user that runs WP CLI by adding them to their Bash config in the usual way.

21st September 2019 11:30 am

Using Mix Versioning Outside Laravel

Laravel Mix is a really convenient front end scaffold, and not just in the context of a Laravel application. Last year, I added it to a legacy application I maintain, with positive results, and I’m including it in a CMS I’m working on.

However, I’ve always had issues trying to implement versioning outside a Laravel application. I’ve used the timestamp technique described here a lot in the past, but nowadays I do most of my work in a Lando container, and I’ve had a lot of issues with timestamp changes not being picked up, forcing me to restart my container regularly when working on front-end assets. Switching to using Mix versioning seemed like a good way to resolve that issue, but of course the mix() helper isn’t available elsewhere.

Fortunately, its not all that hard to implement your own solution. Under the bonnet, Mix versioning works as follows:

  • The build generates an array of compiled static assets, with the key being the path to the asset, and the value being the path with a query string appended, and then saves it as mix-manifest.json
  • The mix() helper loads the mix-manifest.json file, converts it to JSON, fetches the array entry by path, and then returns the appropriate value for passing back from the helper

With that in mind, I wrote the following Twig filter to handle assets versioned with Mix:

<?php declare(strict_types=1);
namespace Project\Core\Views\Filters;
use Exception;
final class Mix
public function __invoke(string $path): string
$manifest = json_decode(file_get_contents('mix-manifest.json'), true);
if (! array_key_exists("/" . $path, $manifest)) {
throw new Exception(
"Unable to locate Mix file: {$path}"
if (!file_exists($path)) {
throw new Exception('Included file does not exist');
return $manifest["/" . $path];

This works on the basis that the web root is set in the public/ folder, and that the compiled CSS and Javascript files are placed there - if that’s not the case you may need to adapt this accordingly.

You also need to add the version() call to your webpack.mix.js:

const mix = require('laravel-mix');
| Mix Asset Management
| Mix provides a clean, fluent API for defining some Webpack build steps
| for your Laravel application. By default, we are compiling the Sass
| file for the application as well as bundling up all the JS files.
.js('resources/js/app.js', 'public/js')
.sass('resources/sass/app.scss', 'public/css')

Then, when you instantiate Twig, you can add the new filter using something like this:

$twig = new Environment($container->get('Twig\Loader\FilesystemLoader'), $config);
$mix = $container->get('Project\Core\Views\Filters\Mix');
$twig->addFilter(new TwigFilter('mix', $mix));

Now, the filter should be usable in your Twig views as shown:

<!doctype html>
<html lang="en">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="{{ 'css/app.css'| mix }}" />
<title>{{ title }}</title>
{% include 'header.html' %}
{% block body %}
{% endblock %}
<script src="{{ 'js/app.js'| mix }}"></script>

If you’re using a different framework or templating system, there should be a way to create helpers, and it should be possible to implement this technique fairly easily. I was able to do so in the context of a legacy Zend application, so it should be possible with other legacy frameworks like CodeIgniter.

7th September 2019 8:16 pm

Setting Private Properties in Tests

Sometimes when writing a test, you come across a situation where you need to set a private field that’s not accessible through any existing route. For instance, I’ve been working with Doctrine a bit lately, and since the ID on an entity is generated automatically, it should not be possible to change it via a setter, but at the same time, we sometimes have the need to set it in a test.

Fortunately, there is a way to do that. Using PHP’s reflection API, you can temporarily mark a property on an object as accessible, so as to be able to set it without either passing it to the constructor or creating a setter method that will only ever be used by the test. We first create a ReflectionClass instance from the object, then get the property. We mark it as accessible, and then set the value on the instance, as shown below:

<?php declare(strict_types = 1);
namespace Tests\Unit;
use Tests\TestCase;
use Project\Document;
use ReflectionClass;
final class DocumentTest extends TestCase
public function testGetId()
$doc = new Document();
$reflect = new ReflectionClass($doc);
$id = $reflect->getProperty('id');
$id->setValue($doc, 1);
$this->assertEquals(1, $doc->getId());

If you’re likely to need this in more than one place, you may want to pull this functionality out into a trait for reuse:

<?php declare(strict_types = 1);
namespace Tests\Traits;
use ReflectionClass;
trait SetsPrivateProperties
* Sets a private property
* @param mixed $object
* @param string $property
* @param mixed $value
* @return void
public function setPrivateProperty($object, string $property, $value)
$reflect = new ReflectionClass($object);
$prop = $reflect->getProperty($property);
$prop->setValue($object, $value);

Then your test can be simplified as follows:

<?php declare(strict_types = 1);
namespace Tests\Unit;
use Tests\TestCase;
use Project\Document;
use Tests\Traits\SetsPrivateProperties;
final class DocumentTest extends TestCase
use SetsPrivateProperties;
public function testGetId()
$doc = new Document();
$this->setPrivateProperty($doc, 'id', 1);
$this->assertEquals(1, $doc->getId());

While this is a slightly contrived and limited example, and this situation is quite rare, I’ve found it to be a useful technique under certain circumstances.

28th July 2019 8:55 pm

Skipping Environment Specific Phpunit Tests

If you’re doing client work, you don’t generally have to worry too much about working with any services other than those that will be installed in your production environment. For instance, if you’re using Memcached as your cache backend, you needn’t go to the trouble of checking that it works with Redis too unless the project actively switches. However, for more general purpose software that may be deployed to a variety of different environments, you may have to test it in all of those environments, which can be a chore.

Lately I’ve been working on a micro CMS for a personal project, and ran into a bit of an issue. This CMS uses the Stash caching library, and I wanted it to actively support all of the cache backends Stash provides. The CMS is configured using YAML, and I’d written a factory class that takes in the cache configuration and returns an adapter. The problem was that there are three adapters that require additional software to be installed, namely the APC, Redis and Memcached adapters. Installing all the packages to use all three of the adapters is onerous, and while it’s a good idea to test them all, it’s generally not worth the bother of adding all of them to your local development environment where you need your tests to run as fast as possible. Instead you’re better off deferring those tests that require additional dependencies to your continuous integration server, which can afford to be a lot slower.

Fortunately, PHPUnit allows you to mark a test as skipped by calling markTestSkipped(). In the past I’ve used this or the similar markTestIncomplete() method when a test wasn’t finished, but it’s also useful for skipping tests based on the environment. We can either test for the presence of the dependency and mark the test as skipped if it’s not present, or set the test up inside a try…catch block and call markTestSkipped() if the test throws an exception due to a missing dependency, as in this example:

<?php declare(strict_types = 1);
namespace Tests\Unit\Factories;
use Tests\TestCase;
use App\Factories\CacheFactory;
use Stash\Exception\RuntimeException;
use Mockery as m;
final class CacheFactoryTest extends TestCase
public function testRedis()
$factory = new CacheFactory;
try {
$pool = $factory->make([
'driver' => 'redis',
'servers' => [[
} catch (RuntimeException $e) {
$this->markTestSkipped('Dependency not installed');
$this->assertInstanceOf('Stash\Pool', $pool);
$this->assertInstanceOf('Stash\Driver\Redis', $pool->getDriver());

As a general rule of thumb, when running your tests locally, it’s more important that your test suite run quickly than provide 100% coverage. Tests that are slower or require multiple services to be installed can still be run by your continuous integration server, which can afford to be slower since it’s not a blocker in the same way. In addition, I’m only ever really interested in coverage stats on the CI server, since enabling that slows PHPUnit down a lot, so since coverage is a non-issue locally we can happily leave covering that dependency to our CI server. In this case, the project is hosted on Github and uses Travis CI for running the tests and Coveralls for recording coverage, so we can leave the full test suite to be run on Travis CI, ensuring full coverage, while skipping those tests that require Redis, Memcached or APC locally.

Having a comprehensive test suite, and running it regularly during development, is important, but that doesn’t mean it’s compulsory you run every test regularly. In a case like this, where there are multiple adapters for the same basic functionality, you can often afford to avoid running those that test adapters with more exacting requirements.

Recent Posts

Flexible Data Types With the JSON Field

Storing Wordpress Configuration in Environment Variables

Using Mix Versioning Outside Laravel

Setting Private Properties in Tests

Skipping Environment Specific Phpunit Tests

About me

I'm a web and mobile app developer based in Norfolk. My skillset includes Python, PHP and Javascript, and I have extensive experience working with CodeIgniter, Laravel, Zend Framework, Django, Phonegap and React.js.