sqlalchemy uuid for sqlite

uuid_user.py

########################
# UUID for SQLite hack #
########################

from sqlalchemy.types import TypeDecorator, CHAR
from sqlalchemy.dialects.postgresql import UUID
import uuid


class GUID(TypeDecorator):
    """Platform-independent GUID type.

    Uses PostgreSQL's UUID type, otherwise uses
    CHAR(32), storing as stringified hex values.

    """
    impl = CHAR

    def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(UUID())
        else:
            return dialect.type_descriptor(CHAR(32))

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        elif dialect.name == 'postgresql':
            return str(value)
        else:
            if not isinstance(value, uuid.UUID):
                return "%.32x" % uuid.UUID(value).int
            else:
                # hexstring
                return "%.32x" % value.int

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            if not isinstance(value, uuid.UUID):
                value = uuid.UUID(value)
            return value
          
class Base(db.Model):

    __abstract__ = True

    id = db.Column(GUID(), primary_key=True, default=lambda: str(uuid.uuid4()))
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())
    updated_at = db.Column(db.DateTime,
                    default=db.func.current_timestamp(),
                    onupdate=db.func.current_timestamp())

    
class User(Base):

    __tablename__ = 'users'

    username = db.Column(db.String, nullable=False, unique=True)
    email = db.Column(db.String, nullable=False, unique=True)
    encrypted_password = db.Column(db.String, nullable=False)
    
    def set_password(self, password):
        self.encrypted_password = bc.generate_password_hash(password)

    def verify_password(self, password):
        return bc.check_password_hash(self.encrypted_password, password)

I may be misunderstanding but it seems wrong to have the default values for the columns, in the case where they are GUIDs, be set to a fixed value at class definition time. Rather, those default values should be set to lambda functions or functions that generate new values at insert/update time:

    id = db.Column(GUID(), primary_key=True, default=lambda: str(uuid.uuid4()))

For the created_at and updated_at columns, the sqlalchemy doc (https://docs.sqlalchemy.org/en/14/core/defaults.html#client-invoked-sql-expressions) states in a note :

When using SQL functions with the func construct, we “call” the named function, e.g. with parenthesis as in func.now(). This differs from when we specify a Python callable as a default such as datetime.datetime, where we pass the function itself, but we don’t invoke it ourselves. In the case of a SQL function, invoking func.now() returns the SQL expression object that will render the “NOW” function into the SQL being emitted.

So the correct way to set those fields seems to be db.func.METHOD().

For the ID, Openstack seems to also be using a lambda (https://github.com/openstack/heat/blob/master/heat/db/sqlalchemy/models.py#L113) + the previous note states that we should pass the function itself so I will edit the gist to reflect that, thank you.

Thanks! This solved a unit testing problem for me! PyCharm says that the TypeDecorator class should implement these:

def process_literal_param(self, value, dialect)

@property
def python_type(self)

Are they needed or will it work without them?

could you please add a license to it?

I don't think I need to put a license but here we go : https://gist.github.com/gmolveau/ea2068a821fbac4da08b7ed85b8328cb

Thank you!

Thanks!!

Great gist!

Is server_default=DefaultClause(xxx)) a better choice when handling id, created_at and updated_at?

And there's a warning:

SAWarning: TypeDecorator GUID() will not produce a cache key because the ``cache_ok`` attribute is not set to True.  
This can have significant performance implications including some performance degradations in comparison to prior 
SQLAlchemy versions.  
Set this attribute to True if this type object's state is safe to use in a cache key, or False to disable this warning.

This almost works.
Missing sort_key_function

This is my version:

class GUID(TypeDecorator):
    """Platform-independent GUID type.
    Uses PostgreSQL's UUID type, otherwise uses
    CHAR(32), storing as stringified hex values.
    """

    impl = CHAR
    cache_ok = True

    def load_dialect_impl(self, dialect):
        if dialect.name == "postgresql":
            return dialect.type_descriptor(UUID())
        else:
            return dialect.type_descriptor(CHAR(32))

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        elif dialect.name == "postgresql":
            return str(value)
        else:
            if not isinstance(value, uuid.UUID):
                return "%.32x" % uuid.UUID(value).int
            else:
                # hexstring
                return "%.32x" % value.int
    
    def _uuid_value(self, value):
        if value is None:
            return value
        else:
            if not isinstance(value, uuid.UUID):
                value = uuid.UUID(value)
            return value         

    def process_result_value(self, value, dialect):
        return self._uuid_value(value)

    def sort_key_function(self, value):
        return self._uuid_value(value)

Awesome! Also you might consider changing the name of process_result_value to something else. :)

Thank you, this was useful!

@splint3rsec: why so? This is needed by SQLAlchemy.

crazy creative workaround. thx for your work!

Still so useful it's a second link in a google for "fastapi UUID sqlite" query for me. Thanks!

I've found sqlalchemy.types.Uuid to be working.

It does the clever thing, use native UUID support when available, use string when it's not.