How changing a column type on SQLite silently deleted rows in another table

How changing a column type on SQLite silently deleted rows in another table

While making the content of this site translatable, I wrote a migration that turns several text columns into JSON of the form {"en": "...", "es": "...", "zh": "..."} and wraps the existing values as English. Production runs on MySQL; my tests and local environment run on SQLite with RefreshDatabase. The migration looked harmless. In the test suite, it made rows in a different table disappear without a single error.

The symptom

One of the affected columns lives on users. The projects table has a user_id foreign key to users with ON DELETE CASCADE. The test that checks the data conversion inserts some legacy rows, runs the migration and then looks at the result. After the migration, the user was still there, with the column correctly converted. The user's projects were gone.

No exception, no failed query, no warning in the log. The only signal was an assertion that expected rows and found none.

Why SQLite rebuilds the table

SQLite's ALTER TABLE is deliberately small. It can rename a table, rename a column, add a column and, in recent versions, drop one. It cannot change the type of an existing column. Any tool that offers "change column", Laravel's ->change() included, has to emulate it with the procedure the SQLite documentation itself recommends:

CREATE TABLE __temp__users (...new definition...);
INSERT INTO __temp__users (...) SELECT ... FROM users;
DROP TABLE users;
ALTER TABLE __temp__users RENAME TO users;

From the point of view of the data in users, this is fine: every row is copied and comes back under the original name. The problem is the third line.

Where the cascade comes in

The SQLite documentation is explicit about it: when foreign key enforcement is on, DROP TABLE performs an implicit DELETE FROM on the table before removing it. That implicit delete behaves like any other delete, so every ON DELETE CASCADE pointing at the table fires.

In my case, dropping the old users table deleted every user row first, the cascade deleted every project that pointed at them, and then the renamed copy brought the users back. The parent table survived the rebuild intact. The child table did not, and nothing in the process considered that an error, because from SQLite's point of view nothing went wrong: a row was deleted, and the rule said to delete its children.

Why turning foreign keys off did not help

The textbook advice for this procedure is to run PRAGMA foreign_keys = OFF before the rebuild and turn it back on afterwards, and Laravel tries to do this around its own table rebuilds on SQLite. In my tests that protection simply did not take effect.

The reason is another line in the SQLite documentation: PRAGMA foreign_keys is a no-op inside a transaction. You cannot switch enforcement on or off once a transaction has started. RefreshDatabase wraps every test in a transaction so it can roll everything back afterwards, and my conversion test ran the migration inside that test. The pragma was issued, it returned without complaint, and foreign keys stayed on.

That is what made the bug so quiet. Every individual piece was doing exactly what it is documented to do.

The fix

I did not need to rebuild those tables in the first place. The migration now asks the database for the current column type and only calls ->change() for the columns whose type is actually different:

foreach (self::FIELDS as $table => $columns) {
    $pending = array_filter(
        $columns,
        fn ($column) => Schema::getColumnType($table, $column) !== 'text'
    );

    if ($pending !== []) {
        Schema::table($table, function (Blueprint $t) use ($pending) {
            foreach ($pending as $column) {
                $t->longText($column)->nullable()->change();
            }
        });
    }
}

Where the type already matches, there is no rebuild, no DROP and no cascade. Where it genuinely differs, the change still runs. The data conversion is a separate step that only wraps values that are not already JSON objects, so running the migration twice leaves the same result.

If you really do need to change a column type on a table that other tables reference with a cascade, make sure the rebuild runs outside a transaction (a Laravel migration can opt out with public $withinTransaction = false;), with foreign keys off, and check integrity afterwards with PRAGMA foreign_key_check. And test it against data, not just an empty schema.

Takeaways for Laravel projects on SQLite

• SQLite in tests is not a smaller MySQL. Schema changes in particular follow different rules, and a migration that is a cheap ALTER on MySQL can be a full table rebuild on SQLite.
• Data loss from a cascade does not raise errors. If a migration touches a table that others depend on, have a test that creates child rows first and counts them afterwards.
• Keep migrations minimal and idempotent. Altering only what actually needs to change is not just tidier; here it was the fix.
• Read the SQLite documentation for ALTER TABLE and for foreign keys once. Both of the behaviours that caused this bug are documented in plain language there.

The bug never reached production, because production does not run on SQLite. But the tests are only useful if they behave like the code they are protecting, and for a while mine were quietly testing a database that had just lost half of its data.