IF (NOT) EXISTS for ALTER TABLE sub-commands like constraints etc

[EMCain]

Hello, forgive me if an issue already exists for this, I have seen a related one here but that is several years old and status is unclear.

There doesn't seem to be an option to add IF (NOT) EXISTS or CASCADE flags to queries generated by e.g. op.create_unique_constraint or op.drop_index. Here are some raw queries I've added and directly executed lately:

DROP INDEX ix_host_ip 
CASCADE;

or

ALTER TABLE host 
DROP CONSTRAINT IF EXISTS vuln_occurrence_unique_constraint
CASCADE;

I would like to be able to create these with alembic, for example:

op.drop_index('ix_host_ip', table_name='host', cascade=True)
op.drop_constraint_if_exists('vuln_occurrence_host_ip_fkey', 'vuln_occurrence', type_='foreignkey', cascade=True)

(The syntax could be different to better fit the style of existing commands, if needed.)

I haven't contributed yet but I might be able to work on this issue if it is deemed a worthwhile change to the project.

[zzzeek]

I think these would be good contributions, although I would still consider this to be #151, and additionally because the DDL for these constructs comes from SQLAlchemy it would be cleaner to be on the SQLAlhcemy side added to the CreateIndex, DropIndex etc constructs.

The good news is that CASCADE, while not really supported by any major DB except Postgresql, is in the SQL standard, so it can go right in as is. "IF (NOT) EXISTS" seems like it's not part of the standard but it seems to be in every database these days.

anyway the issue you want to be mainly working on is in SQLAlchemy over at: sqlalchemy/sqlalchemy#2843 . CASCADE can be part of it too for those constructs where it applies. you will note how often in that bug, people offer to work on the issue, then disappear, or I ask people to provide a PR, then they disappear, etc. I've gotten PRs for some very complex features in this area like support for schema comments and such so I know it's doable.

[zzzeek]

to clarify, the change has to be both in SQLAlchemy and Alembic - SQLAlchemy's CreateIndex/DropIndex/CreateTable/DropTable/AddConstraint/DropConstraint all get CASCADE and IF (NOT) EXISTS as is available on databases, then Alembic's op directives gain those flags and pass them through. I dont think Alembic's autogenerate feature needs anything at all here as these directives don't apply to a automatically managed schema.

[zzzeek]

also let's have only one issue open, if you want it to be #524 then lets close #151, or the other way around, unless you can point out some difference in the requests I'm not seeing (other than support CASCADE as well),

[lachaib]

@zzzeek just reviving this issue, I've come to get the need for adding IF NOT EXISTS on add_column (and of course the reverse), which seems to be specific to postgresql (did a research on oracle, mssql, sqlite and mysql, none , and I have a patch ready on my side with just that.
Given the dialect specificity and the fact that it seems not standard SQL, I'd go for not adding support in SQLAlchemy and directly add it here.
Would it be ok if I use this issue as reference even if it's not complete for all kind of table alterations or do you prefer me to open a separate issue?

[CaselIT]

it's likely fine to add a pg specific thing to add_column similar to this alembic.sqlalchemy.org/en/latest/ops.html#alembic.operations.Operations.alter_column.params.postgresql_using in alter column.

maybe we could add it also to drop column?

It's likely better to just open a new issue to better track it though

[lachaib]

it's likely fine to add a pg specific thing to add_column similar to this alembic.sqlalchemy.org/en/latest/ops.html#alembic.operations.Operations.alter_column.params.postgresql_using in alter column.

I'll need a bit of rework in that case, I started off thinking it could be a generic if_not_exists attribute/argument

maybe we could add it also to drop column?

Yep, I mentioned I support it in reverse

It's likely better to just open a new issue to better track it though

Sure, will do the spin off

[CaselIT]

I'll need a bit of rework in that case, I started off thinking it could be a generic if_not_exists attribute/argument

if it's only a postgresql option, it may make sense to scope it for postgresql.
like you said it seem supported only by pg, so it's probably better to start by scoping it.