Laravel PHPUnit failing on ALTER TABLE using SQLite

Question

I have a migration which I made at the beginning of my project, basically adding a TEXT column called 'description' which is set to NOT NULL.

Now several months down the track I need to change that to allow null.

I can't use Laravel 5.5 change() function as I have a enum in my column list and it bugs out, so i need to add it as a raw query in a migration like so;

DB::statement('ALTER TABLE `galleries` MODIFY `description` TEXT NULL;');

When i do a php artisan migrate against my local mysql database it all works great, BUT when i try to run my test suite, it all breaks.

Im using SQLite for my test suite, and the error im getting is as follows;

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

If anyone else has come up against this issue and fixed it, i would love to hear how you did it.

Thanks

I was able to get it to work by using DB::raw('ALTER TABLE ....') — Jesus Galvan
– Jesus Galvan, Commented Jun 17, 2020 at 7:35

patricus · Accepted Answer · 2017-11-09 08:39:35Z

8

SQLite only allows you to rename the table or add a column. The ALTER TABLE statement cannot change or remove columns.

In order to change or remove a column in SQLite, you need to create a new table with the desired schema, copy the data from the original table to the new table, delete the original table, and then rename the new table to the original name.

This is all abstracted out for you by Laravel and DBAL, so your best bet may be to get help with figuring out the issue with your enum column (though that would be a separate question).

You can read more about altering tables in the SQLite docs here.

answered Nov 9, 2017 at 8:39

patricus

62.9k15 gold badges157 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BigJobbies Over a year ago

Damn, thats a bit of an issue ... I guess my best bet would be to kill the enum and replace with something like varchar(50) .. Thank you for your answer

patricus Over a year ago

@BigJobbies If you have the option to remove the enum, that would be your best bet. They're generally frowned upon in databases.

Collectives™ on Stack Overflow

Laravel PHPUnit failing on ALTER TABLE using SQLite

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related