SQLLite modifying a column width

I'm running tests using an in-memory SQLite DB because, as I understand it, MySQL in-memory testing does not support foreign keys.

I created a new migration to widen the user's phone number column but when I run my in-memory SQL tests, they fail when attempting to seed. If I remove the migration that changed the phone number width, everything's fine.

class IncreasePhoneColumnSizeInUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function($table)
        {
            $table->string('phone', 30)->change();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function($table)
        {
            $table->string('phone', 20)->change();
        });
    }
}

In the docs it is implied that changing a column with SQLite is supported as long as you are not changing more than one column per migration. See the notes section below Modifying Columns and Dropping columns: https://laravel.com/docs/5.1/migrations#modifying-columns

I hate to have to go back and edit the original migrations that created the column but I'm not really seeing any other choice here. I'd prefer not to dramatically slow down my tests and use a regular MySQL database. I'd prefer not to stop using foreign keys in order to use MySQL in-memory database.

I've tried this with 5.1.28 and 5.1.35 - same result.

Is there something I'm missing?

ChristopherSFSD

10 years ago

Level 4

I see now that SQLite ignores the string-length values. So perhaps I could wrap the calls to Schema::table in IF statements. Before every in-memory SQLite test I do the following:

Artisan::call('migrate:refresh', ['--seeder' => 'TestDatabaseSeeder', '--database' => 'testing']);

Is there a way I could check against that --database flag's value during the execution of the migration and simply have it ignore this migration when the --database flag is set to 'testing'? If so, how would I grab that?

ChristopherSFSD

10 years ago

Best Answer

Level 4

Figured it out ...

class IncreasePhoneColumnSizeInUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        if ('testing' !== config('database.default'))
        {
            Schema::table('users', function($table)
            {
                $table->string('phone', 30)->change();
            });
        }
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        if ('testing' !== config('database.default'))
        {
            Schema::table('users', function($table)
            {
                $table->string('phone', 20)->change();
            });
        }
    }
}

Just to be clear 'testing' is my SQLite in-memory connection. I should probably rename it but yeah ...

        'testing' => [
            'driver'   => 'sqlite',
            'database' => ':memory:',
            'prefix'   => '',
        ],

Please or to participate in this conversation.