How to use SQLAlchemy @compiles decorator for geoalchemy2 types

Question

I'm trying to create an in-memory SQLite database off of SQLAlchemy ORMs originally designed for a Postgres database. To allow the SQLite engine to convert postgres specific datatypes, I've relied on the SQLAlchemy @compiles decorator, which works as intended for types in the sqlalchemy.dialects.postgresql namespace.

How can I convert the geoalchemy2.Raster data type to a engine-agnostic data type (it could be a sqlalchemy.BLOB type for all I care. The important thing is that I can create tables that are similar, they don't need to be exact)?

This example code converts the DOUBLE_PRECISION type as intended, but I'm at a loss for how to modify the geoalchemy2.Raster data type which is a UserDefinedType. I'd like to be able to write a similar method to compile_DOUBLE_PRECISION() that changes Raster into a BLOB type.

import sqlalchemy
from sqlalchemy import Column, Integer
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.sqlite.base import SQLiteTypeCompiler
from geoalchemy2 import Raster

Base = declarative_base()

class SomeModel(Base):
    __tablename__ = "some_model"
    id = Column(Integer, primary_key=True)
    double_precision = Column(DOUBLE_PRECISION)
    raster = Column(Raster)

# The following @compiles works as intended
@compiles(DOUBLE_PRECISION, 'sqlite')
def compile_DOUBLE_PRECISION(element, compiler: SQLiteTypeCompiler, **kw):
    """ Handles postgres DOUBLE_PRECISION datatype as REAL in sqlite. """
    return compiler.visit_real(element, **kw)

def test_throws_error_due_to_raster_data_type():
    engine = sqlalchemy.create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)

Answer 1

I'm not familiar with postgresql but maybe something like this could work

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2 import Raster

Base = declarative_base()


def raster_if_postgresql_else_blob():
    return sa.BLOB().with_variant(Raster, "postgresql")


class SomeModel(Base):
    __tablename__ = "some_model"
    id = sa.Column(sa.Integer, primary_key=True)
    raster = sa.Column(raster_if_postgresql_else_blob())


lite_engine = sa.create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(lite_engine)