Alembic: Creating Composite Types in Alembic Migrations

[cozos]

Is there any way to create Composite Types in Alembic?

I want to do something like:

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

Thanks!

[cozos]

I see there is CompositeType in sqlalchemy_utils's.

It has SchemaType's create method is defined:
https://github.com/kvesteri/sqlalchemy-utils/blob/master/sqlalchemy_utils/types/pg_composite.py#L239-L244

And there is the CREATE TYPE ... expression here:
https://github.com/kvesteri/sqlalchemy-utils/blob/master/sqlalchemy_utils/types/pg_composite.py#L348-L351

But when I have something like this in my Alembic migration:

from sqlalchemy_utils import CompositeType

def upgrade():
    op.create_table(
        'my_table',
        sa.Column("id", sa.BIGINT(), primary_key=True)
        sa.Column(
            'my_composite_column',
            CompositeType(
                'my_type',
                [
                    sa.Column('foo', sa.VARCHAR()),
                    sa.Column('bar', sa.Integer()),
                ],
            ),
        ),    
    )

I get this:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type "my_type" does not exist
LINE 3:  my_composite_column my_type, 
                             ^

[SQL: 
CREATE TABLE my_table (
    id BIGINT, 
    my_composite_column my_type, 
)

How do I get Alembic to create the type?

[zzzeek]

This is likely some issue in SQLAlchemy utils. If I create a native ENUM in Postgresql:

def upgrade():
    op.create_table(
        "mytable", sa.Column("some_data", sa.Enum("one", "two", "three", name='myenum', native_enum=True))
    )

I get the CREATE TYPE:

INFO  [alembic.runtime.migration] Running upgrade  -> 7fc871f59aa1, rev1
INFO  [sqlalchemy.engine.Engine] CREATE TYPE myenum AS ENUM ('one', 'two', 'three')
INFO  [sqlalchemy.engine.Engine] {}
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE mytable (
    some_data myenum
)

this is because the enum is set up using events which the CompositeType should be using as well and I don't see any immediate issue in its source code that would suggest these things wouldn't happen. I would make sure you're on the latest alembic.

[zzzeek]

OK the CompositeType is not implementing the hooks that SQLAlchemy uses for these CREATE TYPE commands to be emitted. It is only associating itself with a MetaData object here: https://github.com/kvesteri/sqlalchemy-utils/blob/master/sqlalchemy_utils/types/pg_composite.py#L311

however, it also is being associated with the Table and Metadata and events are invoked on it which it can use in order to run its CREATE TYPE / DROP TYPE routines. it would need to implement these methods as we see on Postgresql ENUM: https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/base.py#L1483 that is _on_table_create / _on_table_drop / _on_metadata_create / _on_metadata_drop. the one awkward thing is that these are currently underscored methods which technically means I have the option to change them, although there are no plans for this at the moment.

There are other ways that the CompositeType could make this happen on its own, which would be to set up events the way SchemaType does so that when it gets associated with a column and then a table and then does the events, but then again, this would require using the underscored _set_parent() method.

So if you can report this over to sqlalchemy_utils, for now they need to implement _on_[metadata|table]_[create|drop], and I would love to work with them to provide a long term public hook for this since nobody has ever come to me telling me they want to use SchemaType.

for your immediate issue, you can create the type directly right now, create the CompositeType outside of the op.create_table(), and do:

    my_type = CompositeType(...)
    my_type.create(op.get_bind(), checkfirst=False)
    op.create_table(..., Column('my_composite', my_type), ...)

so, downstream issue + workaround, have a nice day!

[cozos]

Thank you very much for the in-depth investigation + answer! I will follow up with sqlalchemy_utils.

[cozos]

I created this issue: https://github.com/kvesteri/sqlalchemy-utils/issues/434