Doctrine - Unknown database type citext

[bradroberts]

I'm getting an error:

[Doctrine\DBAL\DBALException] Unknown database type citext requested, Doctrine\DBAL\Platforms\PostgreSqlPlatform may not support it.

Not sure how to troubleshoot this. Can you point me in the right direction? Here's my migration file:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\Schema;
use ShiftOneLabs\LaravelNomad\Extension\Database\Schema\Blueprint;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name')->nullable();
            $table->passthru('citext', 'email')->unique();
            $table->string('password', 60);
            $table->rememberToken();
            $table->timestamps();
        });
    }

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

[patrickcarlohickman]

Is there an issue with the migration, or is this error occurring somewhere else? I did not have any issues running that migration.

Can you show the code that is causing the error? Thanks.

[mhennequet]

Default Doctrine platForm definition for PostgreSql doesn't describe CITEXT field type. You need to map it manually in your config

# app/config/config.yml
doctrine:
    dbal:
        driver:   pdo_pgsql
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8
        mapping_types:
             citext: string

[maximmandrik]

Don't forget to install citext in postgresql.

# app/config/doctrine.yaml
doctrine:
    dbal:
        # Data operations (does not affect SQL query)
        types:
            citext: App\DoctrineExtensions\DBAL\Types\Citext
        # Assigns type to SQL query
        mapping_types:
            citext: citext

# src/DoctrineExtensions/DBAL/Types/Citext.php
<?php

declare(strict_types=1);

namespace App\DoctrineExtensions\DBAL\Types;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\TextType;

final class Citext extends TextType
{
    const CITEXT = 'citext';

    /**
     * {@inheritdoc}
     */
    public function getName()
    {
        return self::CITEXT;
    }

    /**
     * {@inheritdoc}
     */
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return $platform->getDoctrineTypeMapping(self::CITEXT);
    }
}

[evy0311]

Not sure if your issue was solved, but for others having this issue, check out this fix for the latest version of Doctrine: https://github.com/evy0311/dbal

[SeinopSys]

@evy0311 Using a fork just for this is overkill in my opinion, Doctrine allows extending its functionality well enough that the custom code necessary could be implemented with just a composer package.

The code provided in https://github.com/shiftonelabs/laravel-nomad/issues/2#issuecomment-463388050 combined with these lines should be sufficient to add citext support to Doctrine DBAL (I have tested this just now.)

use App\DoctrineExtensions\DBAL\Types\Citext;
use Doctrine\DBAL\Types\Type;

Type::addType(Citext::CITEXT, Citext::class);
$doctrine_dbal_connection->getDatabasePlatform()->registerDoctrineTypeMapping('citext', Citext::CITEXT);

Making a static class method or a function with the above code that can be ran by the user before any DBAL code is ran (which would otherwise complain about the citext type missing) should be all that's needed.

[tpetry]

I encountered the same problem, the problem is that Laravel and also Doctrine Dbal just don't have support for some PostgreSQL specific data types, the Citext extension was just the tip of the iceberg of problems for me.

So if someone has the problem again and ends up here via Google, I have introduced Laravel to the Citext extension and many more and will now gradually implement even more PostgreSQL specific functionalities:

https://github.com/tpetry/laravel-postgresql-enhanced