What is the correct way to make SQLalchemy store strings as lowercase?

Question

I'm using SQLAlchemy to talk to my database. Because not many people will be using my application (at least initially), I figure SQLite is the quickest/easiest back end.

I've got a User, and it has a unique ID that's string based, e.g. [email protected], or Mr. Fnord. I don't care what format the id is in - just that it's unique. However, I want to this to be a case-insensitive uniqueness. So Mr. Fnord and mr. fNoRd would be equivalent.

Apparently there's a COLLATE setting on the schema you can use, but (at least with sqlite) it doesn't seem to be straignt forward. My solution was to use properties on the class to lowercase everything before it went to the table, but that seemed brittle/hackish.

Are properties the best way to handle lowercasing everything, or is there a better way to make things case insensitive via SQLAlchemy/SQLite?

Answer 1

I haven't tried this personally, but perhaps you could use a custom comparator operator? I.e.

class CaseInsensitiveColumnComparator(ColumnProperty.Comparator):
    def __eq__(self, other):
        return func.lower(self.__clause_element__()) == func.lower(other)

This way, your ID can be stored in any case, but will compare as lowercase.

---

Another idea - augment sqlalchemy.types.Text, and use that for your ID.

import sqlalchemy.types as types

class LowerCaseText(types.TypeDecorator):
    '''Converts strings to lower case on the way in.'''

    impl = types.Text

    def process_bind_param(self, value, dialect):
        return value.lower()

class User(Base):
    __tablename__ = 'user'
    id = Column(LowerCaseText, primary_key=True)
    ...