I have created a TypeDecorator based off of the SQLAlchemy example that converts between a 32-character hex string and the BINARY database column in which the UUID is stored:
from __future__ import absolute_import
import uuid
from sqlalchemy import types, func
#https://docs.sqlalchemy.org/en/13/core/custom_types.html#backend-agnostic-guid-type
class HashColumn(types.TypeDecorator):
impl=types.BINARY
def process_bind_param(self, value, dialect):
if value is not None:
return uuid.UUID(hex=value).bytes
def process_result_value(self, value, dialect):
return uuid.UUID(bytes=value).hex
def copy(self, **kw):
return HashColumn(self.impl.length)
Model:
def get_uuid():
return uuid.uuid4().hex
class School(db.Model):
"""
description: A School
"""
__tablename__ = "schools"
id = db.Column('school_id', HashColumn(length=16), primary_key=True, default=get_uuid)
...
However, the problem I am having is that my marshmallow schemas, which are being generated from the SQLAlchemy Models, aren't seeing this column as a 32-character string:
Schema:
from marshmallow_sqlalchemy import SQLAlchemyAutoSchema
...
class SchoolSchema(SQLAlchemyAutoSchema):
class Meta:
model = School
include_relationships = True
load_instance = True
include_fk = True
...
in my code:
try:
new_object = SchoolSchema().load(data, session=db.session)
except ValidationError as err:
print(err.messages)
print(err.valid_data)
when running this code on with a perfectly-valid UUID of a5fad20c691546ae8871390d980aae6d, marshmallow throws a validation error and gives the following output:
{"id": ["Longer than maximum length 16."]}
Since I would like to have the UUID's formated as 32-character hex strings (or python UUID's where applicable) during use, and converted to BINARY(16) right before storing in the database, I need to get rid of this validation error, but I am unsure of how to do that because changing the length parameter on the SQLAlchemy model will mean that the database tables will be created as BINARY(32) instead of BINARY(16), doubling the length.
Is it possible to set up a SQLAlchemy TypeDecorator so that it stores a type of one length (BINARY(16)) in the Database, but presents a different length (CHAR(32)) to Python and/orSQLAlchemy so that marshmallow can correctly validate the length as a 32-character string?
I have already looked on StackOverflow for other questions like this one:
but this seems to be about converting the type itself, which I have already done in my sample code. I cannot seem to find any that mention how to convert the length of a type.
So far I have found two ways to solve this issue:
adjust the types of the TypeDecorator so that it acts like a CHAR column instead of a binary column and use load_dialect_impl to change the type as presented to the database, specifying the different length as a parameter
class HashColumn(types.TypeDecorator):
impl=types.CHAR
def load_dialect_impl(self, dialect):
return dialect.type_descriptor(types.BINARY(16))
...
(the rest of the class is basically the same as in the question)
This change allowed me to change the HashColumn(length=16) definition from my DB models to HashColumn(length=32), allowing marshmallow to correctly interpret the length.
Alternatively, I could change my implementation of my API PATCH/update endpoints to fetch and update the existing object from the database, rather than creating a completely new object and trying to merge their values. This completely removes the Marshmallow validation since the ID is no longer being used to create a new object, however, to me this felt like too much of a workaround and meant that, because marshmallow validation was not being used, it would also not validate any of the other data fields.