[aerich migrate] PostgreSQL: Cannot drop constraint after deleting FK on a model

Abstract

I use docker-compose for running my code.

I have a model with a useless FK (since another model have this FK by extension). I've already done a migration via aerich before, so all my tables are generated. I've deleted the FK on the model, then I've ran aerich migrate --name remove_committee_fk_field, which generate the migration file. I've ran aerich upgrade.

I got a traceback with the error tortoise.exceptions.OperationalError: constraint "fk_recordse_committe_b8357190" of relation "recordselectionmodel" does not exist.

After further investigation, the problem came from the SQL code generated by the migrate command. The upgrade code try to drop a constraint that doesn't exist, without checking if it exist in the first place.

Code

Before modifiation

from tortoise.models import Model
from tortoise import fields

class RecordSelectionModel(Model):
    class Meta:
        tablename = "record_selections"
        unique_together = ("record", "committee_archive")

    id = fields.IntField(pk=True)
    committee = fields.ForeignKeyField("models.CommitteeModel", related_name="record_selections")
    record = fields.ForeignKeyField("models.RecordModel", related_name="record_selections")
    committee_archive = fields.ForeignKeyField("models.CommitteeArchiveModel", related_name="record_selections")

After modification

from tortoise.models import Model
from tortoise import fields

class RecordSelectionModel(Model):
    class Meta:
        tablename = "record_selections"
        unique_together = ("record", "committee_archive")

    id = fields.IntField(pk=True)
    record = fields.ForeignKeyField("models.RecordModel", related_name="record_selections")
    committee_archive = fields.ForeignKeyField("models.CommitteeArchiveModel", related_name="record_selections")

Command executed

aerich migrate --name remove_committee_fk_field

aerich upgrade

Expected behavior

Deleting the constraint in the table.

Result

Traceback (most recent call last):
  File "/usr/local/bin/aerich", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.9/site-packages/aerich/cli.py", line 258, in main
    cli()
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1128, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/aerich/cli.py", line 33, in wrapper
    loop.run_until_complete(f(*args, **kwargs))
  File "/usr/local/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "/usr/local/lib/python3.9/site-packages/aerich/cli.py", line 102, in upgrade
    migrated = await command.upgrade()
  File "/usr/local/lib/python3.9/site-packages/aerich/__init__.py", line 53, in upgrade
    await conn.execute_script(upgrade_query)
  File "/usr/local/lib/python3.9/site-packages/tortoise/backends/asyncpg/client.py", line 38, in translate_exceptions_
    raise OperationalError(exc)
tortoise.exceptions.OperationalError: constraint "fk_recordse_committe_b8357190" of relation "recordselectionmodel" does not exist

SQL file generated by migrate command

-- upgrade --
ALTER TABLE "recordselectionmodel" DROP CONSTRAINT "fk_recordse_committe_b8357190";
ALTER TABLE "recordselectionmodel" DROP COLUMN "committee_id";
CREATE UNIQUE INDEX "uid_recordselec_record__699c3f" ON "recordselectionmodel" ("record_id", "committee_archive_id");
-- downgrade --
DROP INDEX "uid_recordselec_record__699c3f";
ALTER TABLE "recordselectionmodel" ADD "committee_id" INT NOT NULL;
ALTER TABLE "recordselectionmodel" ADD CONSTRAINT "fk_recordse_committe_b8357190" FOREIGN KEY ("committee_id") REFERENCES "committees" ("id") ON DELETE CASCADE;

Solution (what the SQL generated should have been)

-- upgrade --
ALTER TABLE "recordselectionmodel" DROP CONSTRAINT IF EXISTS "fk_recordse_committe_b8357190";
ALTER TABLE "recordselectionmodel" DROP COLUMN "committee_id";
CREATE UNIQUE INDEX "uid_recordselec_record__699c3f" ON "recordselectionmodel" ("record_id", "committee_archive_id");
-- downgrade --
DROP INDEX "uid_recordselec_record__699c3f";
ALTER TABLE "recordselectionmodel" ADD "committee_id" INT NOT NULL;
ALTER TABLE "recordselectionmodel" ADD CONSTRAINT "fk_recordse_committe_b8357190" FOREIGN KEY ("committee_id") REFERENCES "committees" ("id") ON DELETE CASCADE;

So I think you should add an if exist condition on the drop constraint.

Really nice job otherwise.

tortoise / aerich