How can I get a Redshift dialect working in Python with SQLAlchemy and Alembic for migrations?

Question

I get the following error when I try to "alembic upgrade +1", which is meant for making database migrations.

File "/home/jason/redshift/env/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
psycopg2.NotSupportedError: Column "analyses.id" has unsupported type "serial".

my SQLAlchemy models.py contains:

from alembic.ddl.postgresql import PostgresqlImpl
class RedshiftImpl(PostgresqlImpl):
    __dialect__ = 'redshift'

and my url/host is:

sqlalchemy.url = redshift://USER:[email protected]:5439/

but for some reason it doesn't seem to be using this dialect. Do I need to modify the env.py? Or the models.py?

Answer 1

I had the same problem; this is a bug in 0.4.1, the latest version available on PyPI at the time of this post. I installed it directly from Github and it worked (master is on 0.5.1a at the time of this writing). https://github.com/binarydud/redshift_sqlalchemy

The problem is in the handling of primary keys. If you install over pip and generate a migration, the problem is in the PrimarykeyConstraint() method which will want to create a serial column, which only exists in postgres and not redshift.

from alembic import op
import sqlalchemy as sa
from redshift_sqlalchemy import dialect as postgresql

def upgrade():
    op.create_table('mytable',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint('id') # problem is here: wants to create a serial type
    )

Also consider changing your migrations to import the dialect from the redshift_sqlalchemy module instead of the sqlalchemy core. Although it doesn't matter in this example, there are edge cases where redshift and postgres dialects aren't 100% aligned.