Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Jawsh's avatar

SQLite -- Issue dropping columns, SQLite/Laravel providing incorrect errors

In my structure, I have several enum columns. I was unaware that SQLite cannot use enum, so when someone dropped a complaint that migration was failing I found out the hard way.

To remedy the issue, I'm creating a migration file that drops and replaces the existing enum columns with new string columns that are given integrity through mutators in the model.

The problem is, SQLite is complaining about non-existent columns when dropping the old ones, even though I know that they exist and that the drop works.

        // SQLite cannot use the Enum data type, so we're going to change these to strings.
        Schema::table('options', function(BLueprint $table)
        {
            if (Schema::hasColumn('options', 'format'))
            {
                $table->dropColumn('format');
            }
            
            if (Schema::hasColumn('options', 'data_type'))
            {
                $table->dropColumn('data_type');
            }
            
            if (Schema::hasColumn('options', 'option_type'))
            {
                $table->dropColumn('option_type');
            }
        });

If I run this multiple times, I get a different error, and then it works.

# php artisan migrate

  [Illuminate\Database\QueryException]                                                                
  SQLSTATE[HY000]: General error: 1 no such column: format (SQL: CREATE TEMPORARY TABLE __temp__opti  
  ons AS SELECT option_name, default_value, option_value, format_parameters, validation_parameters,   
  validation_class, option_type, format FROM options)                                                 

 [PDOException]                                            
  SQLSTATE[HY000]: General error: 1 no such column: format  


# php artisan migrate
              
  [Illuminate\Database\QueryException]                                                                
  SQLSTATE[HY000]: General error: 1 no such column: data_type (SQL: CREATE TEMPORARY TABLE __temp__o  
  ptions AS SELECT option_name, default_value, option_value, format_parameters, validation_parameter  
  s, validation_class, data_type FROM options)                                                        

  [PDOException]                                               
  SQLSTATE[HY000]: General error: 1 no such column: data_type  

                                                               
# php artisan migrate
Migrated: 2015_07_25_031202_remove_enum_columns

So somewhere along the way, either SQLite or Eloquent is bullshitting me. Either those columns don't exist when I ask if they exist, and then it doesn't lie the 2nd time I ask, or Eloquent is successfully dropping the columns and reporting an error for no reason.

Any input at all is welcomed.

0 likes
4 replies
Jawsh's avatar
Jawsh
OP
Best Answer
Level 2

After desperately fiddling with it and vendor files I found the cure.

Put each drop in its own transaction.

        // SQLite cannot use the Enum data type, so we're going to change these to strings.
        if (Schema::hasColumn('options', 'format'))
        {
            Schema::table('options', function(BLueprint $table)
            {
                $table->dropColumn('format');
            });
        }
        
        if (Schema::hasColumn('options', 'data_type'))
        {
            Schema::table('options', function(BLueprint $table)
            {
                $table->dropColumn('data_type');
            });
        }
        
        if (Schema::hasColumn('options', 'option_type'))
        {
            Schema::table('options', function(BLueprint $table)
            {
                $table->dropColumn('option_type');
            });
        }
14 likes
leehblue's avatar

You can pass an array of column names to the dropColumn function like this:

$table->dropColumn(['col1', 'col2', 'col3']);
7 likes

Please or to participate in this conversation.