First off. Apologies if this has been answered but I can not find the answer any where.
I need to define a lowercase index on a Flask-SQLAlchemy object.
The problem I have is I need a models username and email fields to be stored lowercase so that I can check them with User.query.filter(func.lower(username) == func.lower(username)).first()
Up to now I have always dealt with this by just inserting these fields as lowercase but in this particular instance I need the username to preserve the case that it was defined with.
I think I am on the right tracks but am running in to an issue I have not seen before.
class User(UserMixin, db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
# Indexes for username and email are defined below User
# They are functional lowercase indexes
username = db.Column(db.String(32))
email = db.Column(db.String(255))
password_hash = db.Column(db.String(255))
...
db.Index('ix_users_username', func.lower(User.username), unique=True)
db.Index('ix_users_email', func.lower(User.email), unique=True)
I can't see a problem with this. I am guessing that the func.lower()
requires the Index to be defined separate from what I can tell with my other searches.
Now the problem comes when I run the migration I get the following errors:
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'users'
.../lib/python3.4/site-packages/alembic/util/messaging.py:69: UserWarning: autogenerate skipping functional index ix_users_email; not supported by SQLAlchemy reflection
warnings.warn(msg)
.../lib/python3.4/site-packages/alembic/util/messaging.py:69: UserWarning: autogenerate skipping functional index ix_users_username; not supported by SQLAlchemy reflection
I'm not 100% sure why this isn't supported by reflection. I was expecting in my migration that it would add them as it did before but with it wrapped in lower.
I am not adverse to writing the migration manually (as I'm presuming its possible, not 100% sure how) but can anyone point out why this doesn't work out of the box?
Thanks in advance
Joe
Update
I solved this by adding the following line in to the migration.
op.create_index('ix_users_username', 'users', [sa.text('lower(username)')])
op.create_index('ix_users_email', 'users', [sa.text('lower(email)')])
See Question&Answers more detail:
os