August 6, 2017

November 25, 2019

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)

to

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.

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
© 2020 Przemysław Kołodziejczyk