Cómo cambiar el tipo de una columna en SQLite borró en silencio filas de otra tabla

Cómo cambiar el tipo de una columna en SQLite borró en silencio filas de otra tabla

Mientras hacía traducible el contenido de esta web, escribí una migración que convierte varias columnas de texto en JSON con la forma {"en": "...", "es": "...", "zh": "..."} y conserva los valores existentes como versión en inglés. Producción corre sobre MySQL; mis tests y el entorno local, sobre SQLite con RefreshDatabase. La migración parecía inofensiva. En la batería de tests, hacía desaparecer filas de otra tabla sin un solo error.

El síntoma

Una de las columnas afectadas está en users. La tabla projects tiene una clave foránea user_id hacia users con ON DELETE CASCADE. El test que comprueba la conversión de datos inserta algunas filas antiguas, ejecuta la migración y revisa el resultado. Después de la migración, el usuario seguía ahí, con la columna bien convertida. Sus proyectos habían desaparecido.

Ninguna excepción, ninguna consulta fallida, ningún aviso en el log. La única señal fue una aserción que esperaba filas y no encontró ninguna.

Por qué SQLite reconstruye la tabla

El ALTER TABLE de SQLite es pequeño a propósito. Puede renombrar una tabla, renombrar una columna, añadir una columna y, en versiones recientes, eliminarla. No puede cambiar el tipo de una columna existente. Cualquier herramienta que ofrezca "cambiar columna", incluido el ->change() de Laravel, tiene que emularlo con el procedimiento que recomienda la propia documentación de SQLite:

CREATE TABLE __temp__users (...nueva definición...);
INSERT INTO __temp__users (...) SELECT ... FROM users;
DROP TABLE users;
ALTER TABLE __temp__users RENAME TO users;

Para los datos de users, esto no tiene ningún problema: todas las filas se copian y vuelven con el nombre original. El problema está en la tercera línea.

Dónde entra el cascade

La documentación de SQLite lo dice claramente: cuando las claves foráneas están activas, DROP TABLE hace un DELETE FROM implícito sobre la tabla antes de eliminarla. Ese borrado implícito se comporta como cualquier otro, así que se dispara cada ON DELETE CASCADE que apunte a la tabla.

En mi caso, eliminar la tabla users antigua borró primero todas sus filas, el cascade borró todos los proyectos que apuntaban a ellas y después la copia renombrada devolvió los usuarios. La tabla padre sobrevivió intacta a la reconstrucción. La tabla hija no, y nada en el proceso lo consideró un error, porque para SQLite no había pasado nada raro: se borró una fila y la regla decía que había que borrar sus hijas.

Por qué desactivar las claves foráneas no sirvió

El consejo de manual para este procedimiento es ejecutar PRAGMA foreign_keys = OFF antes de la reconstrucción y volver a activarlo después, y Laravel intenta hacerlo alrededor de sus propias reconstrucciones de tablas en SQLite. En mis tests, esa protección sencillamente no surtió efecto.

La razón es otra línea de la documentación de SQLite: PRAGMA foreign_keys no hace nada dentro de una transacción. No se puede activar ni desactivar la comprobación una vez empezada una transacción. RefreshDatabase envuelve cada test en una transacción para poder deshacerlo todo al final, y mi test de conversión ejecutaba la migración dentro de ese test. El pragma se lanzaba, terminaba sin quejarse y las claves foráneas seguían activas.

Eso es lo que hizo el bug tan silencioso. Cada pieza hacía exactamente lo que dice su documentación.

La solución

Para empezar, no necesitaba reconstruir esas tablas. La migración pregunta ahora a la base de datos el tipo actual de cada columna y solo llama a ->change() en las columnas cuyo tipo es realmente distinto:

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();
            }
        });
    }
}

Donde el tipo ya coincide, no hay reconstrucción, ni DROP, ni cascade. Donde de verdad es distinto, el cambio se sigue aplicando. La conversión de datos es un paso aparte que solo envuelve los valores que no son ya objetos JSON, así que ejecutar la migración dos veces deja el mismo resultado.

Si de verdad necesitas cambiar el tipo de una columna en una tabla a la que otras apuntan con cascade, asegúrate de que la reconstrucción se ejecuta fuera de una transacción (una migración de Laravel puede desactivarla con public $withinTransaction = false;), con las claves foráneas desactivadas, y comprueba la integridad después con PRAGMA foreign_key_check. Y pruébalo con datos, no solo con un esquema vacío.

Conclusiones para proyectos Laravel con SQLite

• SQLite en los tests no es un MySQL pequeño. Los cambios de esquema, en concreto, siguen reglas distintas, y una migración que en MySQL es un ALTER barato puede ser en SQLite una reconstrucción completa de la tabla.
• La pérdida de datos por un cascade no lanza errores. Si una migración toca una tabla de la que dependen otras, ten un test que cree antes filas hijas y las cuente después.
• Mantén las migraciones mínimas e idempotentes. Alterar solo lo que de verdad tiene que cambiar no es solo más limpio: aquí fue la solución.
• Lee una vez la documentación de SQLite sobre ALTER TABLE y sobre claves foráneas. Los dos comportamientos que causaron este bug están explicados ahí con todas las letras.

El bug nunca llegó a producción, porque producción no usa SQLite. Pero los tests solo sirven si se comportan como el código que protegen, y durante un tiempo los míos estaban comprobando, sin decir nada, una base de datos que acababa de perder la mitad de sus datos.