Using enums with schema_translate_map

106 views
Skip to first unread message

Brian Hill

unread,
Feb 17, 2020, 12:47:29 PM2/17/20
to sqlalchemy-alembic
I'm having trouble using enums in conjunction with schema_translate_map for postgres migrations.

My model, single table, single enum.

import enum
from sqlalchemy import MetaData, Enum, Column, Integer
from sqlalchemy.ext.declarative import declarative_base


metadata = MetaData()
Base = declarative_base(metadata=metadata)




class Enum1(enum.Enum):
    One = 1
    Two = 2




class Table1(Base):
    __tablename__ = 'table1'
    id = Column(Integer, primary_key=True)
    type1 = Column(Enum(Enum1))



Version file.


"""initial revision


Revision ID: 844dd0269c1b
Revises:
Create Date: 2020-02-17 12:23:31.125308


"""
from alembic import op
import sqlalchemy as sa




# revision identifiers, used by Alembic.
revision = '844dd0269c1b'
down_revision = None
branch_labels = None
depends_on = None




def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('table1',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('type1', sa.Enum('One', 'Two', name='enum1'), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###




def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('table1')
    # ### end Alembic commands ###




Run migration funtion from env.py (creating foo schema):



def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )


    with connectable.connect() as connection:


        # use different schema
        connection.execute(f'create schema if not exists foo')
        connection = connection.execution_options(
            schema_translate_map={None: 'foo'}
        )


        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            include_schema=True,
        )


        with context.begin_transaction():
            context.run_migrations()



I get the following error when i run alembic upgrade head:


sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type "enum1" does not exist
LINE 4:  type1 enum1,
               ^


[SQL:
CREATE TABLE foo.table1 (
        id SERIAL NOT NULL,
        type1 enum1,
        PRIMARY KEY (id)
)


]
(Background on this error at: http://sqlalche.me/e/f405)



It works when I comment out schema_translate_map.

I feel like I'm missing something fundamental about using schema_translate_map for multi-tenant/schema.

Mike Bayer

unread,
Feb 17, 2020, 2:45:35 PM2/17/20
to sqlalchem...@googlegroups.com
schema_translate_map is not yet supported with all Alembic ops:


you will need to fill in the "schema" parameter explicitly when you call upon op.create_table()
--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

Mike Bayer

unread,
Feb 17, 2020, 2:50:09 PM2/17/20
to sqlalchem...@googlegroups.com
it's possible also that PG Enum CREATE TYPE doesn't work with schema_translate_map, would need to evaluate that on the SQLAlchemy side.

Mike Bayer

unread,
Feb 17, 2020, 2:52:16 PM2/17/20
to sqlalchem...@googlegroups.com

Mike Bayer

unread,
Feb 17, 2020, 3:05:30 PM2/17/20
to sqlalchem...@googlegroups.com
Here is your SQL output, enum is created correctly:

CREATE TYPE test_schema.enum1 AS ENUM ('One', 'Two')


however table does not refer to the correct enum:

CREATE TABLE test_schema.table1 (
id SERIAL NOT NULL,
type1 enum1,
PRIMARY KEY (id)
)


this is SQLAlchemy bug https://github.com/sqlalchemy/sqlalchemy/issues/5158 will be fixed in 1.3.14

Brian Hill

unread,
Feb 17, 2020, 3:19:05 PM2/17/20
to sqlalchemy-alembic
Thanks for figuring this out.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsub...@googlegroups.com.


--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsub...@googlegroups.com.


--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsub...@googlegroups.com.


--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsub...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages