SQLAlchemy Utils, PasswordType field and Alembic migration

Recently, I wanted to use PasswordType field from SQLAlchemy Utils.

The previous field was some custom implementation which was working in a similar way as PasswordType from SQLAlchemy, with sha256.

I wanted to change it because I found a bug in our custom implementation and I realized that we can use ready implementation from SQLAlchemy Utils.

I’ve changed

password = Column(Password(256), nullable=False)

password = Column(PasswordType(
    schemes=['pbkdf2_sha512']
))

where Password was based on sqlalchemy.types.String.

I wanted to create a migration.

alembic revision --autogenerate -m 'Use PasswordType from SQLAlchemy utils'

In my case it printed something like this:

INFO  [alembic.autogenerate.compare] Detected NULL on column 'mymodel.password'
INFO  [alembic.autogenerate.compare] Detected type change from VARCHAR(length=256) to PasswordType(length=1137) on 'mymodel.password'

Autogenerated migration:

"""Use PasswordType from SQLAlchemy utils

Revision ID: a79871436520
Revises: 828798e9fb9f
Create Date: 2017-05-27 21:31:04.497795

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'a79871436520'
down_revision = '828798e9fb9f'
branch_labels = None
depends_on = None

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('mymodel', 'password',
               existing_type=sa.VARCHAR(length=256),
               type_=sqlalchemy_utils.types.password.PasswordType(length=1137),
               nullable=True)
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('mymodel', 'password',
               existing_type=sqlalchemy_utils.types.password.PasswordType(length=1137),
               type_=sa.VARCHAR(length=256),
               nullable=False)
    ### end Alembic commands ###

When I wanted to use it, I got an exception about missing import.

alembic upgrade head

NameError: name 'sqlalchemy_utils' is not defined

It was simple. I just imported sqlalchemy_utils.

I run alembic upgrade head once again.

KeyError: "unknown CryptContext keyword: 'length'"

That was also very simple. I’ve just changed length to max_length.

Next issue.

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "password" cannot be cast automatically to type bytea
HINT:  You might need to specify "USING password::bytea".
 [SQL: 'ALTER TABLE mymodel ALTER COLUMN password TYPE BYTEA ']

This error required additional parameter in upgrade method.

postgresql_using='password::bytea'

You can read about it in documentation.

postgresql_using – String argument which will indicate a SQL expression to render within the Postgresql-specific USING clause within ALTER COLUMN. This string is taken directly as raw SQL which must explicitly include any necessary quoting or escaping of tokens within the expression.

Final migrations look like this:

"""Use PasswordType from SQLAlchemy utils

Revision ID: a79871436520
Revises: 828798e9fb9f
Create Date: 2017-05-27 21:31:04.497795

"""
from alembic import op
import sqlalchemy as sa
import sqlalchemy_utils


# revision identifiers, used by Alembic.
revision = 'a79871436520'
down_revision = '828798e9fb9f'
branch_labels = None
depends_on = None

def upgrade():
    op.alter_column(
        'mymodel', 'password',
        existing_type=sa.VARCHAR(length=256),
        type_=sqlalchemy_utils.types.password.PasswordType(
            max_length=1137
        ),
        nullable=True,
        postgresql_using='password::bytea'
    )


def downgrade():
    op.alter_column(
        'mymodel', 'password',
        existing_type=sqlalchemy_utils.types.password.PasswordType(
            max_length=1137
        ),
        type_=sa.VARCHAR(length=256),
        nullable=False
    )

Works as expected!

root@51805978fa17:/project# alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 828798e9fb9f -> a79871436520, Use PasswordType from SQLAlchemy utils
root@51805978fa17:/project# alembic downgrade -1
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade a79871436520 -> 828798e9fb9f, Use PasswordType from SQLAlchemy utils