sqlmigrate gives unexpected output for an AlterField on a PK when another app has a model with a FK

[Tim Bell]

I've recently discovered a scenario where the output of sqlmigrate for a particular migration is significantly different from the actual SQL run when the migration is applied. (I am following the advice ​here to run sqlmigrate "against a copy of the database you wish to later apply it on".) This occurs when a primary key field is being altered when there is a foreign key field on a model in another app that refers to that primary key field.

Consider an app "myapp" with this model:

class MyModel(models.Model):
    id = models.AutoField(primary_key=True)

And another app "otherapp" with this model:

from myapp.models import MyModel

class OtherModelInOtherApp(models.Model):
    my_model = models.ForeignKey(MyModel, on_delete=models.CASCADE)

Run makemigrations and migrate to apply those initial migrations. (A sample repo containing this scenario is ​here. The ​initial commit represents this state.)

Then change the id field of MyModel to BigAutoField, and run makemigrations again. (​This commit represents this state.)

Now run sqlmigrate myapp 0002, giving this output (using Postgresql):

BEGIN;
--
-- Alter field id on mymodel
--
ALTER TABLE "myapp_mymodel" ALTER COLUMN "id" TYPE bigint USING "id"::bigint;
ALTER SEQUENCE IF EXISTS "myapp_mymodel_id_seq" AS bigint;
COMMIT;

There is no mention of the otherapp_othermodelinotherapp, its column my_model_id, or the foreign key constraint referencing myapp_mymodel.id. In contrast, when the migration is applied, the existing FK constraint is dropped, the my_model_id is altered to bigint, and a new FK constraint is created.

My understanding is that the because there is no dependency from the myapp 0002_alter_mymodel_id migration on the otherapp 0001_initial migration, sqlmigrate does not consider the latter migration when determining the state of the models. That means that it doesn't know that there is a foreign key relationship that will be affected by the AlterField migration operation.

Indeed, adding a dependency in otherapp/migrations/0001_initial.py like this:

    run_before = [
        ('myapp', '0002_alter_mymodel_id'),
    ]

results in the output of sqlmigrate being as expected now:

BEGIN;
--
-- Alter field id on mymodel
--
SET CONSTRAINTS "otherapp_othermodeli_my_model_id_cc392f6b_fk_myapp_mym" IMMEDIATE; ALTER TABLE "otherapp_othermodelinotherapp" DROP CONSTRAINT "otherapp_othermodeli_my_model_id_cc392f6b_fk_myapp_mym";
ALTER TABLE "myapp_mymodel" ALTER COLUMN "id" TYPE bigint USING "id"::bigint;
ALTER SEQUENCE IF EXISTS "myapp_mymodel_id_seq" AS bigint;
ALTER TABLE "otherapp_othermodelinotherapp" ALTER COLUMN "my_model_id" TYPE bigint USING "my_model_id"::bigint;
ALTER TABLE "otherapp_othermodelinotherapp" ADD CONSTRAINT "otherapp_othermodelinotherapp_my_model_id_cc392f6b_fk" FOREIGN KEY ("my_model_id") REFERENCES "myapp_mymodel" ("id") DEFERRABLE INITIALLY DEFERRED;
COMMIT;

This behaviour has been observed in various Django 5.x and 4.x versions; I suspect it has always been present since migrations were added to Django.

It would be good if this behaviour was noted in the documentation for sqlmigrate. Ideally though, sqlmigrate should reflect the current state of applied migrations in the database, as it already does to resolve constraint names.