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

Question

Migrated issue, originally created by Maciek Olko ()

How to recreate:

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

engine = create_engine('postgresql://user:pass/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/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 ###

该提问来源于开源项目：sqlalchemy/alembic

Answer 1

Michael Bayer () wrote:

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 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.