Cascade Delete Issue

[jbertman]

Cascade Delete Issue

[collerek]

Fixed in 0.9.0 by #92

[collerek]

That's not the case as ormar uses only core part of the sqlalchemy, so the one to create tables and build selects/inserts etc. So most of the issues for sa won't help you with ormar as they usually refer to orm part (the one that you inherit from base etc.). You need to specifically search for core issues but even this might not help you as ormar is built on top of this.

When you call get or all in ormar you actually get the results back and later you delete this particular instance. If you want to delete without loading there is delete also in QuerySet, i recently updated the docs in this regard.

Will check what's going on with the cascades.

Btw. Did you create the tables with ormar or connect to existing ones? Did you re-create/alter the tables after adding ondelete?

[jbertman]

@collerek I tried to generate a new revision after adding ondelete, but no changes were detected by alembic. I can try to do it from scratch and report back.

EDIT: seems to have resulted in the same thing - let me know if I can provide anything of use.

[collerek]

There is something wrong i investigate what exactly. In sqlite it sometimes does not create the flat. Can you check if the database structure in your case includes foreign keys definition? Or just the columns and no relations whatsoever?

I think it's something with the scope/locals/globals during resolution of foreign keys.

[jbertman]

AFAICT, it does not appear to create any FK relations or constraints on the DB (or in the generated Alembic revisions) for Postgres

…

On Mon, Feb 1, 2021, 11:08 AM collerek ***@***.***> wrote: There is something wrong i investigate what exactly. In sqlite it sometimes does not create the flat. Can you check if the database structure in your case includes foreign keys definition? Or just the columns and no relations whatsoever? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#90 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABDS46XDPIO3VY3X72HOHELS43GYZANCNFSM4WZRDL3Q> .

[jbertman]

While not ideal, I solved this manually by adding the constraints to my Alembic script as such (still using the above example):

    op.create_table(
        "projects",
        sa.Column("id", sa.CHAR(36), nullable=False),
        sa.Column("created_date", sa.DateTime(), nullable=True),
        sa.Column("updated_date", sa.DateTime(), nullable=True),
        sa.Column("name", sa.String(length=256), nullable=False),
        # <SNIP>
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_table(
        "quotes",
        sa.Column("id", sa.CHAR(36), nullable=False),
        sa.Column("created_date", sa.DateTime(), nullable=True),
        sa.Column("updated_date", sa.DateTime(), nullable=True),
        sa.Column("project", sa.CHAR(36), nullable=True),
        # <SNIP>
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(
            ["project"], ["projects.id"], ondelete="CASCADE", onupdate="CASCADE"
        ),
    )

That allows everything else to function as normal, and lets the DB take care of cascading the operation (can't speak to support for other backends, but this works for postgres).

[collerek]

Yeah, but that how it's supposed to look like out of the box :)

I found the bug it's related to deep-cloning sqlalchemy.Columns, will fix this and release new version soon.

Also will add names to foreign_keys so alembic can clearly identify constraints and not recreate them during consecutive runs.