Trouble when using alembic with sqlalchemy_utils

Question

In my sqlalchemy model i use sqlalchemy_utils' choicetype:

id = db.Column(db.Integer, primary_key=True)
code = db.Column(db.Integer, nullable=True)
level = db.Column(mytypes.types.ChoiceType(LEVEL))

I did everything as described here http://alembic.readthedocs.org/en/latest/autogenerate.html#autogen-module-prefix. In my model i imported choicetype from my module mytypes.types:

from sqlalchemy_utils.types.choice import ChoiceType

, in alembic/env.py i added context

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    user_module_prefix="mytypes.types."
    # ...
)

, and in the script.py.mako

import mytypes.types

.The problem is when i am making revision of my model, i getting something
like this

from alembic import op
import sqlalchemy as sa
import mytypes.types

def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column('logging', sa.Column('level', mytypes.types.ChoiceType(length=255), nullable=True))
### end Alembic commands ###

Why alembic didn't pass "LEVEL" argument to choicetype and why it passed length=255 instead?

score 13 · Answer 1 · answered Apr 20 '20 at 10:44

I've come up with a more automated solution for this problem. In my env.py, in the function run_migrations_online(), I've added a custom render that handles the sqlalchemy_utils types. I've only tested with ChoiceType and UUIDType, but they work for me.

So in env.py and the custom render function:

  def render_item(type_, obj, autogen_context):
        """Apply custom rendering for selected items."""

        if type_ == "type" and obj.__class__.__module__.startswith("sqlalchemy_utils."):
            autogen_context.imports.add(f"import {obj.__class__.__module__}")
            if hasattr(obj, "choices"):
                return f"{obj.__class__.__module__}.{obj.__class__.__name__}(choices={obj.choices})"
            else:
                return f"{obj.__class__.__module__}.{obj.__class__.__name__}()"

        # default rendering for other objects
        return False

Then I modified the existing context configure to use it:

   with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            render_item=render_item,
            process_revision_directives=process_revision_directives,
            **current_app.extensions["migrate"].configure_args,
        )

This means when I run manage.py db migrate with a new sqlalchemy_utils it correctly imports the module and adds the type to the migrarions. I've add special handling for the choices attributes of the ChoiceType. It's possible this will need to be extended for more types/attributes.

Works perfectly, also in combination with `user_module_prefix`. Just for completeness, here's the link in the documentation regarding a custom rendering of types: [https://alembic.sqlalchemy.org/en/latest/autogenerate.html#affecting-the-rendering-of-types-themselves](https://alembic.sqlalchemy.org/en/latest/autogenerate.html#affecting-the-rendering-of-types-themselves). — taffit, Jul 29 '20 at 12:38

score 4 · Answer 2 · answered Aug 23 '15 at 18:46

4

I fixed it by manually change this mytypes.types.ChoiceType(length=255) to mytypes.types.ChoiceType(MyEnum) and import it.

answered Aug 23 '15 at 18:46

Ryang MinHo

107
3
8

1

I was thinking on this, but if you change the `MyEnum` definition, that migration will not be correct because is mutable. Migrations must be constant over time (because you add new migrations to update the database schema), so i think you have to hardcode the values on that version of the migration. – UselesssCat Jan 20 '21 at 17:13

score 0 · Answer 3 · answered Aug 23 '19 at 13:25

Alembic and SqlAlchemy-utils are not friends like we expect them to be. So Do import your models file in into your alembic migration version file and edit the upgrade function accordingly.

Like it's done here https://sqlalchemy-utils.readthedocs.io/en/latest/data_types.html#module-sqlalchemy_utils.types.choice

score 0 · Answer 4 · answered Jan 21 '20 at 07:46

length is not a valid parameter to ChoiceType, that alembic passes it as a parameter is erroneous. so you have to either import an Enum class that defines your options, this may typically be defined as so:

import Enum

class Role(Enum):
    admin = 1
    regular = 2

or a list with tuples:

    STATUS = [
        ('0', 'open'),
        ('1', 'closed')
    ]

or even define them directly in the migration file. Then re-run the migration with the new changes.

Ensure to also import the sqlalchemy_utils library into the migration file.

Alemic does not integrate well with sqlalchemy_utils and generates erroneous scripts with regard to how the custom sqlalchemy_utils custom fields should be defined. This doesn't happen only for ChoiceType but for other datatypes such as PasswordType.

For all these cases, you have to go into the migration script and manually fix the offending lines.

Trouble when using alembic with sqlalchemy_utils

4 Answers4

Linked