Index creation with if_not_exists not supported for SQLAlchemy 1.4.49

[inikolaev]

Describe the bug

Index creation with if_not_exists is not supported for SQLAlchemy 1.4.x

Expected behavior

It should be possible to specify if_not_exists when creating an index and using SQLAlchemy version that supports it.

To Reproduce

• Manually create a simple table with a few fields
• Manually create an index on that table
• Create a migration to create an
• Run the migration

Error

Traceback (most recent call last):
  File "/bin/alembic", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/lib/python3.11/site-packages/alembic/config.py", line 630, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/lib/python3.11/site-packages/alembic/config.py", line 624, in main
    self.run_cmd(cfg, options)
  File "/lib/python3.11/site-packages/alembic/config.py", line 601, in run_cmd
    fn(
  File "/lib/python3.11/site-packages/alembic/command.py", line 399, in upgrade
    script.run_env()
  File "/lib/python3.11/site-packages/alembic/script/base.py", line 578, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 109, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/alembic/env.py", line 129, in <module>
    run_migrations_online()
  File "/alembic/env.py", line 122, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/lib/python3.11/site-packages/alembic/runtime/environment.py", line 937, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/lib/python3.11/site-packages/alembic/runtime/migration.py", line 624, in run_migrations
    step.migration_fn(**kw)
  File "/alembic/versions/2023_10_12_1551-a19846cced8b_indices.py", line 22, in upgrade
    op.create_index(
  File "<string>", line 8, in create_index
  File "<string>", line 3, in create_index
  File "/lib/python3.11/site-packages/alembic/operations/ops.py", line 994, in create_index
    return operations.invoke(op)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/lib/python3.11/site-packages/alembic/operations/base.py", line 393, in invoke
    return fn(self, operation)
           ^^^^^^^^^^^^^^^^^^^
  File "/lib/python3.11/site-packages/alembic/operations/toimpl.py", line 102, in create_index
    raise NotImplementedError("SQLAlchemy 2.0+ required")
NotImplementedError: SQLAlchemy 2.0+ required

Versions.

• OS: macOS 14.1
• Python: 3.11
• Alembic: 1.12.0
• SQLAlchemy: 1.4.49
• Database: PostgreSQL
• DBAPI: psycopg3

Additional context

Is it really so that SQLAlchemy 1.4.x does not support this? At least checking the source it looks like it does.

Commenting out this check seems to work just fine and I can see the following part of the statement generated correctly:

CREATE INDEX CONCURRENTLY IF NOT EXISTS

Should the requirements be lowered and allow SQLAlchemy version that does support this feature? I have seen some constants available that check for other SQLAlchemy versions, so should be pretty straightforward to support this, unless there a reason not to.

I don't think we will migrate to 2.0 soon and it would be great to have this feature around.

Have a nice day!

[zzzeek]

the PR for #151 had assumed if_exists support was added for all constructs in SQLAlchemy 2.0. This is true with the exception of CreateIndex, DropIndex, which had this flag for a longer period of time in the 1.x series. We can remove the notimplemented for these two operations specifically provided a PR includes tests that run on 1.4 (there should be existing tests that would just need to be opened up for 1.4). thanks!

[sqla-tester]

l-hedgehog has proposed a fix for this issue in the main branch:

Open up if_(not_)?exists to SQLAlchemy 1.4+ https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/4974

[CaselIT]

missed this

[zzzeek]

oh great we have an issue