Is there a way to create a citext field using Laravel 5 Migrations?

Question

Specifically, since MySQL doesn't have that field type, raw DB queries seem like a bad idea. Since pgsql lacks a case insensitive text field by default, we had to use this extension (which works perfectly, of course), but now are faced with a dilemma wrt the migration.

Answer 1

Update

I have created a package that implements this functionality. It adds a passthru() method to the migrations, so that you can create any field type you'd like. In this example, after installing the package and adding the service provider, you'd just do $table->passthru('citext', 'name'); in the migration file. The package is called "laravel-nomad", and can be found on github and packagist.

---

Yes, this can be done. It requires extending a few of the core files, but it is doable. Specifically, you will need a new connection, schema grammar, and blueprint.

First, create a new directory under your app directory to hold your custom files. For example, app/Extension. The files placed in this directory will be extending the core Illuminate files, so you will be replicating the Illuminate folder structure under this new directory.

Blueprint

To add a new method available to the migration, you need to update the Blueprint. You will create a custom Blueprint class that extends the core Blueprint class. This custom Blueprint class will have the new method in it (e.g. ciText()).

app/Extension/Database/Schema/PostgresCustomBlueprint.php

<?php namespace App\Extension\Database\Schema;

use Illuminate\Database\Schema\Blueprint;

class PostgresCustomBlueprint extends Blueprint {

    /**
     * Create a new case-insensitive text column on the table.
     *
     * @param  string  $column
     * @return \Illuminate\Support\Fluent
     */
    public function ciText($column)
    {
        return $this->addColumn('ciText', $column);
    }
    
}

Schema Grammar

With the Blueprint updated to allow the ciText() method, you need to update the schema grammar to be able to handle the ciText field. You will create a custom schema grammar class that extends the core PostgresGrammar schema grammar class. This custom schema grammar will have the method that converts the Blueprint ciText column into the citext field.

app/Extension/Database/Schema/Grammars/PostgresCustomGrammar.php

<?php namespace App\Extension\Database\Schema\Grammars;

use Illuminate\Database\Schema\Grammars\PostgresGrammar;
use Illuminate\Support\Fluent;

class PostgresCustomGrammar extends PostgresGrammar {

    /**
     * Create the column definition for a citext type.
     *
     * @param  \Illuminate\Support\Fluent  $column
     * @return string
     */
    protected function typeCiText(Fluent $column)
    {
        return 'citext';
    }

}

Connection

Now that you have a custom Blueprint and a custom schema grammar that will allow use of the citext field, you need to create a custom Connection that will use these new custom classes. This custom Connection class will extend the core PostgresConnection class in order to override the methods needed to use the custom schema grammar and the custom Blueprint.

app/Extension/Database/PostgresCustomConnection.php

<?php namespace App\Extension\Database;

use Illuminate\Database\PostgresConnection;
use App\Extension\Database\Schema\Grammars\PostgresCustomGrammar as SchemaGrammar;
use App\Extension\Database\Schema\PostgresCustomBlueprint;

class PostgresCustomConnection extends PostgresConnection {

    /**
     * Get the default schema grammar instance.
     *
     * @return \App\Extension\Database\Schema\Grammars\PostgresCustomGrammar
     */
    protected function getDefaultSchemaGrammar()
    {
        return $this->withTablePrefix(new SchemaGrammar);
    }

    /**
     * Get a schema builder instance for the connection.
     *
     * @return \Illuminate\Database\Schema\Builder
     */
    public function getSchemaBuilder()
    {
        $parentBuilder = parent::getSchemaBuilder();

        // add a blueprint resolver closure that returns the custom blueprint
        $parentBuilder->blueprintResolver(function($table, $callback) {
            return new PostgresCustomBlueprint($table, $callback);
        });

        return $parentBuilder;
    }

}

Service Provider

Now that the custom Connection is setup, you need to tell Laravel to use it. You can either override the built in pgsql driver to use the PostgresCustomConnection class, or you can create a new driver name (e.g. pgsql-custom) for the new connection.

If you just want to override the built in pgsql driver, you need to add the following line to the register() method in your app/Providers/AppServiceProvider.php file:

$this->app->bind('db.connection.pgsql', 'App\Extension\Database\PostgresCustomConnection');

If you would like to create a new driver name (e.g. pgsql-custom), you need to add the following two lines to the register() method in your app/Providers/AppServiceProvider.php file:

$this->app->bind('db.connector.pgsql-custom', 'Illuminate\Database\Connectors\PostgresConnector');
$this->app->bind('db.connection.pgsql-custom', 'App\Extension\Database\PostgresCustomConnection');

Database Config

If you create a new driver name, make sure to update your config/database.php file to set the value for the driver key on your connection to pgsql-custom (or whatever you named your driver).

Artisan

Finally, run the artisan commands to make sure all your classes can be found and to update any cached (compiled) version of app/Providers/AppServiceProvider:

composer dump-autoload
php artisan clear-compiled
php artisan optimize

Migration

Now you are able to use the ciText() method inside your migrations and it will create a citext field.

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function(Blueprint $table)
        {
            $table->engine = 'InnoDB';

            $table->increments('id');
            $table->timestamps();
            $table->ciText('name');
            $table->ciText('email')->unique();
            $table->string('password', 60);
            $table->rememberToken();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('users');
    }
}