Is it possible to create custom “Columns” in SQLAlchemy? is this breaking OOP?

I’m new in SQLAlchemy and I’m trying to understand some concepts.

Here is a code example without SQLAlchemy:

class Token:
    def __init__(self, key):
        # generate token based on a string

class User:
    def __init__(self, name):
        self.name = name
        self.token = Token(name)

    def check_token(token_to_check)
        return self.token.is_valid(token_to_check)

How can I move this to SQLAlchemy?. I think I have to do something like:

class UserDatabase(Base):
    __tablename__ = 'testing_sql_alchemy_v2_users'
    name = Column(String(256))
    token = Column(String(256))

But, when I get the User, token will be a String instead of a Token object. Can I create a Column with my objects?. Example:

token = Column(Token)

If I can’t do this, all my objects that use a database must have only “simple” variables (string, int, etc). I think this breaks OOP, right?.

Answer

When defining columns in a model (class UserDatabase), you are limited to types that exist in the database engine being used by you.

However, some database engines allow you to overcome this difficulty.

In PostgreSQL it is possible to define your own custom types, either by pure SQL or with the usage of ORM such as SQLAlchemy.

import sqlalchemy.types as types

class MyType(types.TypeDecorator):
    '''Prefixes Unicode values with "PREFIX:" on the way in and
    strips it off on the way out.
    '''

    impl = types.Unicode

    def process_bind_param(self, value, dialect):
        return "PREFIX:" + value

    def process_result_value(self, value, dialect):
        return value[7:]

    def copy(self, **kw):
        return MyType(self.impl.length)

source: SQLAlchemy Docs

As you can see it is implementing additional logic on top of already existing types, therefore it is limited.

But for your use case it should be sufficient – you can make your own type on top of varchar type and perform some logic to token-ify that string.