CODEWITHSUNDEEP
python-3.xpostgresqlsqliteflaskpeewee
Josip Kolarić
Josip Kolarić

Reputation: 476

Peewee migrations changing foreign key constraint

I have a situation where I need to change the foreign key constraint to implement cascade delete, and write the migration steps for the change. I'm using Flask with peewee as an ORM tool and peewee_migrate as a tool for migrations.

When creating a migration from a migrate template provided in the peewee_migrate router, a set of available methods is added to the migration.

"""Peewee migrations -- 007_Added_cascade_delete.py.

Some examples (model - class or model name)::

    > Model = migrator.orm['model_name']            # Return model in current state by name

    > migrator.sql(sql)                             # Run custom SQL
    > migrator.python(func, *args, **kwargs)        # Run python code
    > migrator.create_model(Model)                  # Create a model (could be used as decorator)
    > migrator.remove_model(model, cascade=True)    # Remove a model
    > migrator.add_fields(model, **fields)          # Add fields to a model
    > migrator.change_fields(model, **fields)       # Change fields
    > migrator.remove_fields(model, *field_names, cascade=True)
    > migrator.rename_field(model, old_field_name, new_field_name)
    > migrator.rename_table(model, new_table_name)
    > migrator.add_index(model, *col_names, unique=False)
    > migrator.drop_index(model, *col_names)
    > migrator.add_not_null(model, *field_names)
    > migrator.drop_not_null(model, *field_names)
    > migrator.add_default(model, field_name, default)

"""

I tried dropping the column with remove_fields method, and then adding a new foreign key column with on cascade delete with add_fields method, but unsuccessful because somehow the column isn't removed but no errors are shown, and that causes an error that the foreign key with that name already exists.

When using change_fields method, I get an error that the drop_foreign_key_constraint is not implemented in the peewee's playhouse migrate because change_fields method tries to drop the foreign key before changing the field, and if the changed field is also a foreign key, tries to add a foreign key constraint with add_foreign_key_constraint method (also not implemented)

@operation
def drop_foreign_key_constraint(self, table, column_name):
    raise NotImplementedError

@operation
def add_foreign_key_constraint(self, table, column_name, field):
    raise NotImplementedError

The only solution I came up with is by using the sql method which allows custom sql to execute, but the issue is that this could only work on a specific database because of the different syntax between postgresql, mysql and sqlite. Database used for development/production is postgres, and sqlite in-memory database is used for testing, which causes all tests to fail, but migrations succeed on development/production, so this is a "workaround".

if current_app.config["TESTING"] != True:
    def migrate(migrator, database, fake=False, **kwargs):
        """Write your migrations here."""

        migrator.sql('alter table {} drop constraint {};'.format(TABLE_NAME, CONSTRAINT_NAME))

        migrator.sql('alter table {} add constraint {} foreign key (device_id) references device(device_id) on delete cascade;'.format(TABLE_NAME, CONSTRAINT_NAME))


    def rollback(migrator, database, fake=False, **kwargs):
        """Write your rollback migrations here."""

        migrator.sql('alter table {} drop constraint {};'.format(TABLE_NAME, CONSTRAINT_NAME))

        migrator.sql('alter table {} add constraint {} foreign key (device_id) references device(device_id);'.format(TABLE_NAME, CONSTRAINT_NAME))
else:
    def migrate(migrator, database, fake=False, **kwargs):
        """Write your migrations here."""

    def rollback(migrator, database, fake=False, **kwargs):
        """Write your rollback migrations here."""

As sqlite doesn't support alter table drop constraint, I understand that you need to create a copy of a table, insert all the data from the original table into a copy, and then drop the original table and rename the copy table, but I think that's an overkill.

Can you suggest me a better way to get this done? Can I do it with peewee_migrate somehow without using custom sql, so that peewee can be database-agnostic?

Upvotes: 1

Views: 2236

Answers (1)

coleifer
coleifer

Reputation: 26235

As sqlite doesn't support alter table drop constraint, I understand that you need to create a copy of a table, insert all the data from the original table into a copy, and then drop the original table and rename the copy table, but I think that's an overkill.

I don't know anything about "peewee_migrate", but peewee itself comes with a migration extension. Peewee exposes a database-agnostic API for doing the usual operations (add/drop/rename columns, constraints, etc), but due to limitations in the types of operations sqlite supports, the sqlite migration implementation has to do some workarounds.

For example sqlite can add columns but cannot drop them. So what peewee does when you remove a column is rename the table, recreates the table without the column, copies data, and drops the old copy.

Unfortunately, sqlite does not support adding constraints after a table is created, so for your case your best option is to probably drop the column and then re-add it.

I've written a test-case showing how this might work and verified that it works correctly:

https://github.com/coleifer/peewee/blob/7e61d86bf6c3f256d09b2a3e1897693dfd68b48d/tests/migrations.py#L665-L715

Upvotes: 1

Related Questions