PRAGMA foreign_keys = OFF Ignored During SQLite Migration, Causing Cascade Deletions

[okira-e]

Issue Summary:

When performing a migration in LibSQL using Atlas, certain changes require temporarily deleting a table and recreating it with modifications. This process should disable foreign key constraints with the PRAGMA foreign_keys = OFF command to prevent unintended cascade deletions. However, it appears that the PRAGMA foreign_keys = OFF command is not being applied or is ineffective, leading to the following issue:

• When a table is deleted, related records in other tables with ON DELETE CASCADE foreign key constraints are also deleted, resulting in data loss.

Steps to Reproduce:

1. Start a TursoDB database with the following schema:

schema "main" { }

table "parent_table" {
    schema = schema.main

    column "id" {
        type = text
    }
    
    column "name" {
        type = text
        null = true
    }

    column "description" {
        type = text
        null = false
    }
    
    primary_key {
        columns = [column.id]
    }
}

table "child_table" {
    schema = schema.main

    column "id" {
        type = text
    }

    column "parent_id" {
        type = text
    }
    
    column "name" {
        type = text
        null = true
    }

    column "description" {
        type = text
        null = true
    }
    
    primary_key {
        columns = [column.id]
    }

    foreign_key "fk_parent_id" {
        columns     = [column.parent_id]
        ref_columns = [table.parent_table.column.id]
        on_delete   = CASCADE
        on_update   = CASCADE
    }
}

2. Run the following SQL to add rows to both tables:

INSERT INTO parent_table (id, name, description) VALUES (1, 'Parent 1', 'Parent number 1');
INSERT INTO child_table (id, parent_id, name, description) VALUES (1, 1, 'Child name', 'Child of Parent 1');

3. Change the "description" column type on the "parent_table" to have "null" be true:

    column "description" {
        type = text
        null = true
    }

4. Run the migration and check if the child_table still has the row we inserted earlier.

Expected Behavior:

The migration applies without cascade deletions due to the foreign key constraints being disabled by PRAGMA foreign_keys = OFF. And the child_table still has a row.

Actual Behavior:

The child_table has been emptied because a parent table that it has a foreign key towards was (temporarily) deleted.

Impact:

This issue can lead to significant data loss when modifying tables in a database with foreign key constraints, making it unsafe to perform migrations in SQLite with Atlas in certain scenarios.

Environment:

- Turso database version: 0.24.29
- Atlas version: v0.30.1-c1ad9ab-canary
- OS/Platform: macOS 15.1.1 (arm64)