Alembic SQLite ALTER TABLE with self-referencing foreign key

Question

The Alembic migration for a SQLite database:

def upgrade():
    with op.batch_alter_table('my_table') as batch_op:
        batch_op.add_column(sa.Column('parent_id', sa.String(24)))
        batch_op.create_foreign_key('parent_constraint', 'my_table', ['parent_id'], ['id'])

which is supposed to create a foreign key parent_id referencing id of the same table my_table, creates a reference to a table called _alembic_batch_temp:

CREATE TABLE "my_table" (
    id VARCHAR(24) NOT NULL, 
    parent_id VARCHAR(24), 
    PRIMARY KEY (id), 
    CONSTRAINT parent_constraint FOREIGN KEY(parent_id) REFERENCES _alembic_batch_temp (id)
)

How to create self-referencing constraints when altering a table?

Answer 1

After some research I found that the problem here is the way Alembic does the batch migration. In short, at the current version (0.7.6) of Alembic it's not possible to create relation with self by migration.

1. As described in the Alembic documentation, to do the migration, new table is created with a temporary name and changes from the alter table code. In this case:

   CREATE TABLE _alembic_batch_temp (
       id VARCHAR(24) NOT NULL, 
       parent_id VARCHAR(24), 
       PRIMARY KEY (id), 
       CONSTRAINT parent_constraint FOREIGN KEY(parent_id) REFERENCES _alembic_batch_temp (id)
   )
   

2. The table is filled with the data from the old table:

   INSERT INTO _alembic_batch_temp (id) SELECT id FROM my_table;
   

3. Then the old table is removed:

   DROP TABLE my_table;
   

4. Finally the newly created table is renamed to it's proper name:

   ALTER TABLE _alembic_batch_temp RENAME TO my_table;
   

The problem with this way of doing things is already visible in the first code snippet. The newly created foreign key is referencing the temporary table and once it's created it can't be changed due to restrictions in SQLite. So after the renaming of the table you end up with the table you provided:

CREATE TABLE "my_table" (  # new name
    id VARCHAR(24) NOT NULL, 
    parent_id VARCHAR(24), 
    PRIMARY KEY (id), 
    CONSTRAINT parent_constraint FOREIGN KEY(parent_id) REFERENCES _alembic_batch_temp (id)  # old reference
)

To Avoid this situation you can create the batch migration manually:

1. Rename the old table to some temporary name:

   ALTER TABLE my_table RENAME TO migration_temp_table;
   

2. Create new table with proper name and proper reference:

   CREATE TABLE my_table (
       id VARCHAR(24) NOT NULL, 
       parent_id VARCHAR(24), 
       PRIMARY KEY (id), 
       CONSTRAINT parent_constraint FOREIGN KEY(parent_id) REFERENCES my_table (id)
   )
   

3. Copy the data:

   INSERT INTO my_table (id) SELECT id FROM migration_temp_table;
   

4. Remove the old table:

   DROP TABLE migration_temp_table;