IF EXISTS/IF NOT EXISTS directives

[sqlalchemy-bot]

Migrated issue, originally created by Rémy Roy (@remyroy)

IF EXISTS and IF NOT EXISTS are already part of many DBMSs DDL vocabulary. They have many use cases which are highly useful in some situations.

For instance, creating a table if it does not already exists or dropping a column if it exists.

It would be nice to have those directives with op in alembic. I guess they could be implemented using the native support for DBMSs that support them or with some introspection for those that do not.

Thanks

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

this is great but mostly needs to be implemented within SQLAlchemy, alembic here would only need to refer to new API. sqlalchemy.schema.CreateTable, CreateSequence and I suppose CreateIndex (also DropTable, DropSequence, DropIndex) would need a new flag "exists=True" which instructs the dialect to render the appropriate IF [NOT] EXISTS directives on a per-dialect basis.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: feature
• added labels: feature

[sqlalchemy-bot]

Rémy Roy (@remyroy) wrote:

I've created an enhancement for the SQLAlchemy project: http://www.sqlalchemy.org/trac/ticket/2843#ticket

[sqlalchemy-bot]

Patrick Lee (@pllee) wrote:

It looks like this feature is supported by SQLAlchemy now with checkfirst=True. Is there a way to use this when generating upgrade scripts in offline mode?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

@pllee - no, offline mode has no database connection, therefore the query against the database's schema catalogs is not possible.

[sqlalchemy-bot]

Patrick Lee (@pllee) wrote:

@zzzeek thanks for the quick response. Is there a way to force it to do it? If not I would gladly submit a pull request if I can figure it out.

I have an application where the tables could be at any state in the migration and it wouldn't matter if all of the statements had if not exists appended to all the create statements. Instead of keeping track for the particular instance what was the last upgrade run it would just run all of the upgrades since the beginning. I can go into more details if need be on why this functionality makes sense for us.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

a PR against SQLAlchemy would be part of it but none of that is getting released soon enough for you to solve your immediate issues.

here's a recipe that will work fine for now.

from sqlalchemy.schema import CreateTable, DropTable
from sqlalchemy.ext.compiler import compiles
import re


@compiles(CreateTable)
def _add_if_not_exists(element, compiler, **kw):
    output = compiler.visit_create_table(element, **kw)
    if element.element.info.get("ifexists"):
        output = re.sub(
            "^\s*CREATE TABLE", "CREATE TABLE IF NOT EXISTS", output, re.S)
    return output


@compiles(DropTable)
def _add_if_exists(element, compiler, **kw):
    output = compiler.visit_drop_table(element, **kw)
    if element.element.info.get("ifexists"):
        output = re.sub(
            "^\s*DROP TABLE", "DROP TABLE IF EXISTS", output, re.S)
    return output

if __name__ == '__main__':
    from alembic.migration import MigrationContext
    from alembic.operations import Operations

    from sqlalchemy import create_engine, Column, Integer, String

    eng = create_engine("sqlite://", echo=True)

    conn = eng.connect()
    ctx = MigrationContext.configure(conn)
    op = Operations(ctx)

    table1 = op.create_table(
        "some_table",
        Column('id', Integer, primary_key=True),
        Column('bar', String(50)),
        info={"ifexists": True}
    )

    op.drop_table("some_table", info={"ifexists": True})

[sqlalchemy-bot]

Patrick Lee (@pllee) wrote:

@zzzeek Thanks for this, much appreciated.

[sqlalchemy-bot]

EirN (@eirnym) wrote:

Created GIST for this solution for different types:

https://gist.github.com/eirnym/afe8afb772a79407300a

[sqlalchemy-bot]

Zac Witte (@zacwitte) wrote:

Has there been any update on this? If I have the op op.drop_index('display_name_2', table_name='categories') how do I add the IF EXISTS condition?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Has there been any update on this?

an update by definition would be listed here....

If I have the op op.drop_index('display_name_2', table_name='categories') how do I add the IF EXISTS condition?

for now:

1. just execute it directly, op.execute("DROP INDEX IF EXISTS display_name_2")

2. check for it using "name in [i['name'] for i in inspect(engine).get_indexes()]"

3. since this seems to be Alembic, there's paths to just extend the directive yourself but that requires some more effort up front

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

the comment right above has another way to extend the directive: https://gist.github.com/eirnym/afe8afb772a79407300a

[sqlalchemy-bot]

dx wrote:

So, is this a thing that is supposed to be implemented on the sqlalchemy side, or on this side?

A comment dated 2013-10-19 (a few days after the creation of this ticket) in the referenced sqlalchemy ticket says:

yup so adding this to CreateTable/DropTable etc. and then into the appropriate alembic.op as discussed would be all you need; create_all()/drop_all() etc. and the ddl.py sequence isn't used by alembic

Most of the comments in this ticket seem to say the opposite - that this needs to be implemented on the sqlalchemy side. But I don't see how it would make sense to implement it there if its ddl.py isn't used by alembic.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

alembic uses SQLAlchemy's ddl.py for all the CREATE / DROP directives, everything that is not ALTER.

[sqlalchemy-bot]

Boaz Ackerman (@boazin) wrote:

@zzzeek - anything new about this regarding create_index?
When adding an index in production the same index added to the next release will fail.

Doesn't it make sense to always creating an index with IF EXISTS? (postgres)

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

anything new about this regarding create_index?

I don't see anything in our lists of PRs so...no ? :)

When adding an index in production the same index added to the next release will fail.

I'm not following what you mean.

Doesn't it make sense to always creating an index with IF EXISTS? (postgres)

"IF EXISTS" is not standard SQL so it won't work in all cases, and it doesn't make sense to have some DBs arbitrarily behave differently. "IF EXISTS" is not needed unless your target database is not correctly in sync with your migrations which would mean someone is manually altering a database. I would think one would want migrations to fail as soon as possible if that's the case since it means the target database is in an undefined state.

[CaselIT]

since sqlalchemy implemented sqlalchemy/sqlalchemy#7354 this should now be pretty straight forward

[sqla-tester]

Адриан Максим Александрович has proposed a fix for this issue in the main branch:

Added parameters if_exists and if_not_exists for index operations. https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/4782