Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
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
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.
So I wouldn't be able to test my stuff without updating the existing migration? Mmmh that sucks...
Anyway, thanks for you response ;)
Unfortunately, yes.
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.
@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
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 or to participate in this conversation.