SQLite migration ALTER TABLE not working

Published 2 years ago by bobbybouwmann

Hi guys,

So I have a table with clients. Each client has a name and an email and so on. We first decided that the email was required. Now we don't want that anymore and the column needs to be nullable. To do this we use a migration of course .

create_clients_table migration

class CreateClientsTable extends Migration {

    public function up()
    {
        Schema::create('clients', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('sc_number')->unique();
            $table->string('name');
            $table->string('email');
            $table->string('status');
            $table->timestamps();
            $table->softDeletes();
        });
    }

}

update_clients_email_to_nullable migration

class UpdateClientEmailToNullable extends Migration {

    public function up()
    {
        DB::statement('ALTER TABLE clients MODIFY email VARCHAR(255) DEFAULT NULL;');
    }

}

This is all working fine with mysql. However I use the same migrations for testing. I use an sqlite database for this. When I run the migration I get this error:

 [Illuminate\Database\QueryException]
 SQLSTATE[HY000]: General error: 1 near "MODIFY": syntax error (SQL: ALTER TABLE clients MODIFY email VARCHAR(255) DEFAULT NULL;)

 [PDOException]
SQLSTATE[HY000]: General error: 1 near "MODIFY": syntax error

One solution would be to clear my database and change the first migration and add the nullable attribute. Since this project is running on a production server I don't want to do this. This will delete all data! The best way would be a migration!

Any thought would be welcome :D

Best Answer (As Selected By bobbybouwmann)
toniperic

Yeah, there have been threads like this one before. It is known that you cannot alter column in sqlite once it has been created.

It's on the list of the SQL Features That SQLite Does Not Implement

Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

toniperic

Yeah, there have been threads like this one before. It is known that you cannot alter column in sqlite once it has been created.

It's on the list of the SQL Features That SQLite Does Not Implement

Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

bobbybouwmann

So I wouldn't be able to test my stuff without updating the existing migration? Mmmh that sucks...

Anyway, thanks for you response ;)

toniperic

Unfortunately, yes.

thepsion5

You can get around the issue by using a fully migrated and seeded SQLite database file (where you could make the change manually) and then having your test setup method replace the current test db with the fresh file. I do something similar with a package that does some integration testing with a DB:

abstract class TestCase extends \PHPUnit_Framework_TestCase
{
    protected function setupDatabase()
    {
        $dbFile = __DIR__ . '/setup/db';
        //if there's an existing test datbase file, remove it
        if( is_file($dbFile) ) {
            exec("rm $dbFile.sqlite"); 
        }
        //copy the freshly migrated and seeded database file to the correct location for use in tests
        exec("cp {$dbFile}_initial.sqlite $dbFile.sqlite");
    }

    protected function setUp()
    {
        $this->setupDatabase();
        parent::setUp();
    }
}

You might also see some significant speed gains with this approach too.

bobbybouwmann

@thepsion5 Thank you for your suggestion! I will try it out and see if it fits my needs ;)

For now I updated the old migration and my tests run fine now :P

edmills

Why don't you point your testing config at a local rather than production db ?

Open config/testing/database.php file and copy details from config/local/database.php.

Easy...

Please sign in or create an account to participate in this conversation.