Laravel migration to change an unsignedMediumInteger column

Question

I have a need to create a migration that changes an existing 'unsignedMediumInteger' column to nullable().

I have the following in my migration:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use AppNew\Models\SubscriptionType;

return new class extends Migration
{
    public function up()
    {
        Schema::table('subscription_types', function ($table) {
            $table->unsignedMediumInteger('max_number_of_employees')->nullable()->change();
        });
    }
};

This throws an error as below:

  INFO  Running migrations.  

  2023_10_25_103141_make_subscription_types_max_number_of_employees_field_nullable  108ms FAIL

   Doctrine\DBAL\Exception 

  Unknown column type "mediuminteger" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgotten to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information.

...any pointers as to get around this please?

Thanks, K...

Answer 1

Try this approach:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        DB::statement("ALTER TABLE <table_name> MODIFY COLUMN <column_name> MEDIUMINT UNSIGNED NULL");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::statement("ALTER TABLE <table_name> MODIFY COLUMN <column_name> MEDIUMINT UNSIGNED NOT NULL");
    }
};

Answer 2

The best way is to use DB::statement method to execute a raw SQL statement with the alter query

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration {
    public function up()
    {
        DB::statement('ALTER TABLE subscription_types MODIFY max_number_of_employees MEDIUMINT UNSIGNED NULL');
    }
};

Answer 3

This way, there's no need to touch any packages. You can simply declare the MediumInteger type in your application and use it in the Doctrine DBAL package.

---

Requirements

• Laravel 8.x or higher
• Doctrine DBAL 3.x or higher

Solution

Create the class corresponding to our type based on the following template:

App\Database\Types\MediumInteger.php

<?php

namespace App\Database\Types;

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

/**
 * Custom type for medium integer.
 */
class MediumInteger extends IntegerType
{
    // Important: leave it as is, Laravel Schema will look for it by this name
    const NAME = 'mediuminteger';

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        // getMediumIntTypeDeclarationSQL() not defined
        // return $platform->getMediumIntTypeDeclarationSQL($fieldDeclaration);

        // _getCommonIntegerTypeDeclarationSQL() protected, so cant call it
        // return 'MEDIUMINT' . $platform->_getCommonIntegerTypeDeclarationSQL($fieldDeclaration);

        // call the generation of Integer type, prepend the missing word MEDIUM
        return 'MEDIUM' . $platform->getIntegerTypeDeclarationSQL($fieldDeclaration);
    }

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        // This is executed when the value is read from the database. Make your conversions here, optionally using the $platform.
        return $value === null ? null : (int) $value;
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        // This is executed when the value is written to the database. Make your conversions here, optionally using the $platform.
        return $value === null ? null : (int) $value;
    }

    public function getName()
    {
        return self::NAME;
    }
}

(#1 DBAL Native Declaration) Following this, declare it either globally or only in the migration where it's needed.

Important: Preferably use Laravel's built-in configuration file instead of that.

use App\Database\Types\MediumInteger;
use Doctrine\DBAL\Types\Type;

if (! Type::hasType(MediumInteger::NAME)) {
    Type::addType(MediumInteger::NAME, MediumInteger::class);
}

(#2 Laravel Declaration - RECOMMENDED) The Laravel framework provides the ability to declare custom types once we've created the corresponding classes for them. To achieve this, we need to follow the following pattern in the config/database.php file:

use App\Database\Types\MediumInteger;

'dbal' => [
    'types' => [
        MediumInteger::NAME => MediumInteger::class,
    ],
],

---

More information

• Custom Mapping Types - Doctrine DBAL Docs
• Modifying Columns - Laravel Docs