I'm building my first bigger database-backend with sqlalchemy and need a table to store configurations (key-value pairs). Keys are strings, values can be quite different, and I'd like to keep the variable-type. This is what I came up with:

class Configuration(Base):
    __tablename__ = "configuration"

    def __init__(self, *args, **kw):
        if len(args) > 0:
            kw['name'] = args[0]
        if len(args) > 1:
            kw['value'] = args[1] 
        try:
            json.loads(kw["value"])
        except Exception:
            try:
                kw["_value"] = json.dumps(kw["value"])
            except KeyError:
                pass
        super().__init__(*args, **kw)

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, CheckConstraint("name <> ''", name="check_name_not_empty"), unique=True)
    _value: Mapped[str] = mapped_column(String, CheckConstraint("_value <> ''", name="check_value_not_empty"))

    @property
    def value(self):
        return json.loads(self._value)

    @value.setter
    def value(self, new_value):
        self._value = json.dumps(new_value)

The basic idea is to have a column '_value' that stores the value as a json-string but the user only iteracts with the value-propery so the conversion into the json-format is hidden (as long as the objects can be converted).

The init is not so nice but I wanted to support args and keyword-args.