Detection of field type JSON to Text change fails (for PostgreSQL)

Issue #451 closed
Maciek Olko
created an issue

How to recreate:

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, MetaData, JSON

engine = create_engine('postgresql://user:pass@localhost/jsontotext_test', echo=True)

metadata = MetaData()
Table('users', metadata,
      Column('id', Integer, primary_key=True),
      Column('name', JSON),
      )

metadata.create_all(engine)

Execute that. Then change script to:

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, MetaData, Text

engine = create_engine('postgresql://user:pass@localhost/jsontotext_test', echo=True)

metadata = MetaData()
Table('users', metadata,
      Column('id', Integer, primary_key=True),
      Column('name', Text),
      )

metadata.create_all(engine)

Configure compare_type=True. Then run: alembic revision --autogenerate -m "JSON to Text test" on those MetaData and DB URL.

Effect:

"""json to text

Revision ID: 868a9e249d4d
Revises: 
Create Date: 2017-09-15 12:29:55.222771

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '868a9e249d4d'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

Expected:

"""json to text

Revision ID: 868a9e249d4d
Revises: 
Create Date: 2017-09-15 12:29:55.222771

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '868a9e249d4d'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('users', 'name', type_=sa.Text)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('users', 'name', type_=sa.JSON)
    # ### end Alembic commands ###

Comments (5)

  1. Michael Bayer repo owner

    can't reproduce. Here's rev1:

    def upgrade():
        # ### commands auto generated by Alembic - please adjust! ###
        op.create_table('users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.JSON(), nullable=True),
        sa.PrimaryKeyConstraint('id')
        )
        # ### end Alembic commands ###
    
    
    def downgrade():
        # ### commands auto generated by Alembic - please adjust! ###
        op.drop_table('users')
        # ### end Alembic commands ###
    

    change model to use Text:

    ## env.py
    
    if False:
        Table('users', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', JSON),
              )
    
    if True:
        Table('users', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', Text),
              )
    

    run autogenerate, cmd output shows the compare taking place:

    [classic@photon2 alembic]$ PYTHONPATH=~/dev/sqlalchemy/lib/ python -m alembic.config revision -m "rev2" --autogenerate
    INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
    INFO  [alembic.runtime.migration] Will assume transactional DDL.
    INFO  [alembic.ddl.postgresql] Detected sequence named 'users_id_seq' as owned by integer column 'users(id)', assuming SERIAL and omitting
    INFO  [alembic.autogenerate.compare] Detected type change from JSON(astext_type=Text()) to Text() on 'users.name'
      Generating /home/classic/dev/alembic/foo/versions/3be218947606_rev2.py ... done
    

    rev is generated:

    def upgrade():
        # ### commands auto generated by Alembic - please adjust! ###
        op.alter_column('users', 'name',
                   existing_type=postgresql.JSON(astext_type=sa.Text()),
                   type_=sa.Text(),
                   existing_nullable=True)
        # ### end Alembic commands ###
    
    
    def downgrade():
        # ### commands auto generated by Alembic - please adjust! ###
        op.alter_column('users', 'name',
                   existing_type=sa.Text(),
                   type_=postgresql.JSON(astext_type=sa.Text()),
                   existing_nullable=True)
        # ### end Alembic commands ###
    

    this is using alembic 0.9.5 SQLAlchemy 1.1.14 checked out on their release tags.

  2. Log in to comment