Add columns with custom types in Laravel Migrations

I was certainly surprised when I found out that there is no built-in method to create table columns with custom types in Laravel migrations. The default list of column types available in Laravel may be more than enough for most applications, but, I think there are some valid use cases for opening the hood and directly working on the engine.

The scenario

Many DBMS offer non-standard column types that you might want to use to unlock some untapped potential. For me this was adding support for PostgreSQL’s built in enumerated types (enums) and arrays. Now, I am not claiming that using these particular types is generally a good idea (especially arrays). As always, you need to evaluate the upsides and downsides for your specific application.

How it’s done

Say, you have an enum type named “foo” and you want to create a new table with a column of this type. You might intuitively reach for the addColumn function as it let’s you specify a type name:

<?php
DB::unprepared("CREATE TYPE foo AS ENUM ('a', 'b', 'c');");

Schema::create('my_table', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->addColumn('foo', 'my_foo_col');
});

If you ran this migration you would be greeted with the following error message:

Method Illuminate\Database\Schema\Grammars\PostgresGrammar::typeFoo does not exist.

You see, when adding a column of any type to a table there must exist a corresponding method typeYourTypeName in the respective Grammar class (in my case PostgresGrammar). This method is expected to return the underlying type name for your specific DBMS, which is necessary to even out the differences between DBMS vendors. For example an Integer in PostgreSQL will get your an int whereas in MySQL it is integer.

So, in order to get rid of the error we need to somehow make a method named typeFoo available on the PostgresGrammar class. Of course, we could subclass the grammar, add our method, and then inject it using Laravel’s service container, but, that is a lot of work. Luckily for us, the grammar class uses the trait Macroable through which we can add our method to the existing class.

<?php
Grammar::macro('typeFoo', function () {
    return 'foo';
});

DB::unprepared('CREATE TYPE foo AS ENUM ('a', 'b', 'c');');

Schema::create('my_table', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->addColumn('foo', 'my_foo_col');
});

With only an extra three lines of code our migration runs fine and does what we want.

Make it generic

The practitioners under you might call it a day, check it in, and deploy, which is perfectly fine. But, what if we wanted to add more enum types in the future? Do we really need to add a macro for each one of them? Fortunately not.

We can make use of the fact that the addColumn function takes an optional third parameter appropriately named $parameters which can be used to specify additional options for the column type. With it we can create a single macro that can handle all enum types.

<?php
Grammar::macro('typeRaw', function (Fluent $column) {
    return $column->get('raw_type');
});

DB::unprepared("CREATE TYPE foo AS ENUM ('a', 'b', 'c');");
DB::unprepared("CREATE TYPE bar AS ENUM ('x', 'y', 'z');");

Schema::create('my_table', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->addColumn('raw', 'my_foo', ['raw_type' => 'foo']);
    $table->addColumn('raw', 'my_bar', ['raw_type' => 'bar']);
    $table->addColumn('raw', 'my_multiple_foos', ['raw_type' => 'foo[]']);
});

To wrap it all up, we can define a macro on the Blueprint class to get rid of the verbose syntax and make it look all nice and clean.

<?php
Blueprint::macro('addColumnRaw', function ($rawType, $name) {
    return $this->addColumn('raw', $name, ['raw_type' => $rawType]);
});

$table->addColumnRaw('foo', 'my_foo');

Check out this github repository for a full working sample and in particular this migration file for the relevant code.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.