[Django] #36355: sqlmigrate gives unexpected output for an AlterField on a PK when another app has a model with a FK

[Django]

#36355: sqlmigrate gives unexpected output for an AlterField on a PK when another
app has a model with a FK
--------------------------+--------------------------------------
Reporter: Tim Bell | Type: Bug
Status: new | Component: Migrations
Version: 5.2 | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------+--------------------------------------
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
[https://docs.djangoproject.com/en/5.2/ref/django-admin/#django-admin-
sqlmigrate 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
[https://github.com/timb07/sqlmigrate_bug here]. The
[https://github.com/timb07/sqlmigrate_bug/commit/e3002b11d02c7526408d2cceb174f60d66a54eb3
initial commit] represents this state.)

Then change the `id` field of `MyModel` to `BigAutoField`, and run
`makemigrations` again.
([https://github.com/timb07/sqlmigrate_bug/commit/d9106ad1b691392fc6f12aa2d19c5bce8f059910
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36355>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

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

----------------------------+------------------------------------
Reporter: Tim Bell | Owner: (none)

Type: Bug | Status: new
Component: Migrations | Version: 5.2

Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

----------------------------+------------------------------------
Changes (by Lily Foote):

* cc: Lily Foote (added)
* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/36355#comment:1>

[Django]

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

----------------------------+----------------------------------------
Reporter: Tim Bell | Owner: Ahmed Nassar
Type: Bug | Status: assigned

Component: Migrations | Version: 5.2
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

----------------------------+----------------------------------------
Changes (by Ahmed Nassar):

* owner: (none) => Ahmed Nassar
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36355#comment:2>