Repository: yukinarit/pyserde (python)

Issue:
Support for SQLAlchemy dataclass
SQLAlchemy 2.0 introduced PEP 681 support to its integration with dataclasses, but unfortunately this is implemented through a generic type `Mapped[T]` that at the moment is not correctly understood.

```python
from typing import Optional

from serde import serde, from_dict
from sqlalchemy import String, JSON
from sqlalchemy.orm import DeclarativeBase, MappedAsDataclass
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


class Base(MappedAsDataclass, DeclarativeBase):
    pass


@serde
class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    fullname: Mapped[str] = mapped_column(String(30))
    nickname: Mapped[Optional[str]]
    meta: Mapped[Optional[JSON]] = mapped_column(type_=JSON)


user = from_dict(User, {"id": 1, "name": "john", "fullname": "John Doe"})

```
Leads to an error:
```
Traceback (most recent call last):
  File "[...]/example.py", line 25, in <module>
    user = from_dict(User, {"id": 1, "name": "john", "fullname": "John Doe"})
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "[...]/lib/python3.12/site-packages/serde/de.py", line 529, in from_dict
    return from_obj(cls, o, named=True, reuse_instances=reuse_instances)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "[...]/lib/python3.12/site-packages/serde/de.py", line 497, in from_obj
    raise SerdeError(e) from None
serde.compat.SerdeError: Unsupported type: int
```

Pull request description:
feat: add support for SQLAlchemy dataclass-mapped tables
Resolves #517

This hopefully add support for SQLAlchemy dataclass-mapped tables without breaking anything else

The following code should run without errors:
```python
from typing import Optional

import sqlalchemy as sa
from sqlalchemy import String, create_engine, JSON
from sqlalchemy.orm import DeclarativeBase, MappedAsDataclass, Session
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column

from serde import serde, from_dict
from serde.json import to_json


class Base(MappedAsDataclass, DeclarativeBase):
    pass


@serde
class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    fullname: Mapped[str] = mapped_column(String(30))
    nickname: Mapped[Optional[str]]
    meta: Mapped[Optional[JSON]] = mapped_column(type_=JSON)


engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

user = from_dict(User, {"id": 1, "name": "john", "fullname": "John Doe"})
with Session(engine) as session:
    session.add(user)
    session.commit()

    user = session.scalar(sa.select(User))

to_json(user)
```

Patch:
diff --git a/README.md b/README.md
index eae5c3d..f4ac99b 100644
--- a/README.md
+++ b/README.md
@@ -77,6 +77,7 @@ Foo(i=10, s='foo', f=100.0, b=True)
         - [`ipaddress`](https://docs.python.org/3/library/ipaddress.html)
     - PyPI library
         - [`numpy`](https://github.com/numpy/numpy) types
+        - [`SQLAlchemy`](https://github.com/sqlalchemy/sqlalchemy) Declarative Dataclass Mapping (experimental)
 - [Class Attributes](https://github.com/yukinarit/pyserde/blob/main/docs/en/class-attributes.md)
 - [Field Attributes](https://github.com/yukinarit/pyserde/blob/main/docs/en/field-attributes.md)
 - [Decorators](https://github.com/yukinarit/pyserde/blob/main/docs/en/decorators.md)
diff --git a/docs/en/types.md b/docs/en/types.md
index dba6054..1caf70b 100644
--- a/docs/en/types.md
+++ b/docs/en/types.md
@@ -24,6 +24,7 @@ Here is the list of the supported types. See the simple example for each type in
     * [`ipaddress`](https://docs.python.org/3/library/ipaddress.html) [^22]
 * PyPI library
     * [`numpy`](https://github.com/numpy/numpy) types [^23]
+    * [`SQLAlchemy`](https://github.com/sqlalchemy/sqlalchemy) Declarative Dataclass Mapping (experimental) [^24]
 
 You can write pretty complex class like this:
 ```python
@@ -88,6 +89,11 @@ npfoo = NPFoo(
 NPFoo(i=1, j=2, f=3.0, g=4.0, h=False, u=array([1, 2]), v=array([3, 4]), w=array([1, 2], dtype=int32), x=array([3, 4]), y=array([5., 6.], dtype=float32), z=array([7., 8.]))
 ```
 
+## SQLAlchemy Declarative Dataclass Mapping (experimental)
+While experimental support for SQLAlchemy Declarative Dataclass Mapping integration has been added, certain features such as `@serde(type_check=strict)` and `serde.field()` are not currently supported.
+
+It's recommended to exercise caution when relying on experimental features in production environments. It's also advisable to thoroughly test your code and be aware of potential limitations or unexpected behavior.
+
 ## Needs a new type support?
 
 If you need to use a type which is currently not supported in the standard library, please creat a Github issue to request. For types in third party python packages, unless it's polular like numpy, we don't plan to support it to keep pyserde as simple as possible. We recommend to use custom class or field serializer.
@@ -135,3 +141,5 @@ If you need to use a type which is currently not supported in the standard libra
 [^22]: See [examples/type_ipaddress.py](https://github.com/yukinarit/pyserde/blob/main/examples/type_ipaddress.py)
 
 [^23]: See [examples/type_numpy.py](https://github.com/yukinarit/pyserde/blob/main/examples/type_numpy.py)
+
+[^24]: See [examples/type_sqlalchemy.py](https://github.com/yukinarit/pyserde/blob/main/examples/type_sqlalchemy.py)
diff --git a/examples/runner.py b/examples/runner.py
index b111033..26d0168 100644
--- a/examples/runner.py
+++ b/examples/runner.py
@@ -115,6 +115,13 @@ def run_all() -> None:
     run(type_uuid)
     run(type_numpy)
 
+    try:
+        import type_sqlalchemy
+
+        run(type_sqlalchemy)
+    except ImportError:
+        pass
+
 
 def run(module: typing.Any) -> None:
     print("-----------------")
diff --git a/examples/type_sqlalchemy.py b/examples/type_sqlalchemy.py
new file mode 100644
index 0000000..00954bf
--- /dev/null
+++ b/examples/type_sqlalchemy.py
@@ -0,0 +1,65 @@
+from typing import Optional
+
+from sqlalchemy import Text, JSON, ForeignKey
+from sqlalchemy.orm import DeclarativeBase, MappedAsDataclass, Mapped, mapped_column, relationship
+
+from serde import serde
+from serde.json import from_json, to_json
+
+
+class Base(MappedAsDataclass, DeclarativeBase):  # type: ignore[misc]
+    pass
+
+
+@serde
+class Project(Base):
+    __tablename__ = "projects"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    name: Mapped[str] = mapped_column(Text, nullable=False)
+    owner_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
+
+
+@serde
+class User(Base):
+    __tablename__ = "users"
+
+    id: Mapped[int] = mapped_column(primary_key=True)
+    name: Mapped[str] = mapped_column(Text, nullable=False)
+    fullname: Mapped[str] = mapped_column(Text, nullable=False)
+    nickname: Mapped[Optional[str]] = mapped_column(Text)
+    attributes: Mapped[Optional[dict[str, str]]] = mapped_column(JSON)
+    projects: Mapped[list[Project]] = relationship(backref="owner")
+
+
+def main() -> None:
+    user = User(
+        id=1,
+        name="john",
+        fullname="John Doe",
+        nickname=None,
+        attributes={"color": "green"},
+        projects=[Project(id=1, name="Dummy", owner_id=1)],
+    )
+    print(f"Into Json: {to_json(user)}")
+
+    s = """{
+        "id": 1,
+        "name": "john",
+        "fullname": "John Doe",
+        "nickname": null,
+        "attributes": {
+            "color": "green"
+        },
+        "projects": [
+            {
+                "id": 1,
+                "name": "Dummy",
+                "owner_id": 1
+            }
+        ]
+    }"""
+    print(f"From Json: {from_json(User, s)}")
+
+
+if __name__ == "__main__":
+    main()
diff --git a/pyproject.toml b/pyproject.toml
index 972e713..08bfde1 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -41,6 +41,7 @@ numpy = [
 orjson = { version = "*", markers = "extra == 'orjson' or extra == 'all'", optional = true }
 plum-dispatch = ">=2,<2.3"
 beartype = ">=0.18.4"
+sqlalchemy = { version = ">2", markers = "extra == 'sqlalchemy' or extra == 'all'", optional = true }
 
 [tool.poetry.dev-dependencies]
 pyyaml = "*"
@@ -73,7 +74,8 @@ numpy = ["numpy"]
 toml = ["tomli", "tomli-w"]
 yaml = ["pyyaml"]
 orjson = ["orjson"]
-all = ["msgpack", "tomli", "tomli-w", "pyyaml", "numpy", "orjson"]
+sqlalchemy = ["sqlalchemy"]
+all = ["msgpack", "tomli", "tomli-w", "pyyaml", "numpy", "orjson", "sqlalchemy"]
 
 [build-system]
 requires = ["poetry-core>=1.0.0", "poetry-dynamic-versioning"]
@@ -118,6 +120,7 @@ exclude = [
   "tests/test_literal.py",
   "tests/test_numpy.py",
   "tests/test_union.py",
+  "tests/test_sqlalchemy.py",
   "serde/__init__.py"
 ]
 pythonVersion = "3.12"
@@ -138,6 +141,7 @@ exclude = [
   "tests/test_literal.py",
   "tests/test_numpy.py",
   "tests/test_union.py",
+  "tests/test_sqlalchemy.py",
 ]
 
 [tool.ruff]
diff --git a/serde/compat.py b/serde/compat.py
index 75e46a0..0248327 100644
--- a/serde/compat.py
+++ b/serde/compat.py
@@ -22,6 +22,8 @@ from typing import TypeVar, Generic, Any, ClassVar, Optional, NewType, Union
 import typing_inspect
 from typing_extensions import TypeGuard
 
+from .sqlalchemy import is_sqlalchemy_inspectable
+
 
 def get_np_origin(tp: type[Any]) -> Optional[Any]:
     return None
@@ -257,6 +259,8 @@ def dataclass_fields(cls: type[Any]) -> Iterator[dataclasses.Field]:  # type: ig
         real_type = resolved_hints.get(f.name)
         if real_type is not None:
             f.type = real_type
+            if is_generic(real_type) and is_sqlalchemy_inspectable(cls):
+                f.type = get_args(real_type)[0]
 
     return iter(raw_fields)
 
@@ -698,6 +702,10 @@ def is_new_type_primitive(typ: Union[type[Any], NewType]) -> bool:
         return any(isinstance(typ, ty) for ty in PRIMITIVES)
 
 
+def has_generic_base(typ: Any) -> bool:
+    return Generic in getattr(typ, "__mro__", ()) or Generic in getattr(typ, "__bases__", ())
+
+
 def is_generic(typ: Any) -> bool:
     """
     Test if the type is derived from `typing.Generic`.
@@ -711,7 +719,7 @@ def is_generic(typ: Any) -> bool:
     False
     """
     origin = get_origin(typ)
-    return origin is not None and Generic in getattr(origin, "__bases__", ())
+    return origin is not None and has_generic_base(origin)
 
 
 def is_class_var(typ: type[Any]) -> bool:
@@ -796,7 +804,7 @@ def get_type_var_names(cls: type[Any]) -> Optional[list[str]]:
 
     type_arg_names: list[str] = []
     for base in bases:
-        type_arg_names.extend(arg.__name__ for arg in get_args(base))
+        type_arg_names.extend(arg.__name__ for arg in get_args(base) if hasattr(arg, "__name__"))
 
     return type_arg_names
 
diff --git a/serde/sqlalchemy.py b/serde/sqlalchemy.py
new file mode 100644
index 0000000..89b09b6
--- /dev/null
+++ b/serde/sqlalchemy.py
@@ -0,0 +1,17 @@
+from typing import Any
+
+try:
+    from sqlalchemy.inspection import inspect
+    from sqlalchemy.exc import NoInspectionAvailable
+
+    def is_sqlalchemy_inspectable(subject: Any) -> bool:
+        try:
+            inspect(subject)
+            return True
+        except NoInspectionAvailable:
+            return False
+
+except ImportError:
+
+    def is_sqlalchemy_inspectable(subject: Any) -> bool:
+        return False


Test patch:
diff --git a/.github/workflows/test.yml b/.github/workflows/test.yml
index 762ab17..486aed0 100644
--- a/.github/workflows/test.yml
+++ b/.github/workflows/test.yml
@@ -37,6 +37,14 @@ jobs:
         run: |
           make examples
         if: matrix.python-version == '3.12'
+      - name: Run tests (sqlalchemy)
+        run: |
+          poetry install --extras sqlalchemy
+          make test
+      - name: Run examples (sqlalchemy)
+        run: |
+          make examples
+        if: matrix.python-version == '3.12'
 
   check_formatting:
     name: Check formatting
@@ -74,7 +82,7 @@ jobs:
           cache: poetry
           cache-dependency-path: pyproject.toml
       - name: Install dependencies
-        run: poetry install
+        run: poetry install --extras sqlalchemy
       - name: Check coverage
         run: make coverage
       - name: Upload coverage report to codecov.io
diff --git a/tests/test_sqlalchemy.py b/tests/test_sqlalchemy.py
new file mode 100644
index 0000000..e860554
--- /dev/null
+++ b/tests/test_sqlalchemy.py
@@ -0,0 +1,99 @@
+import logging
+from typing import Optional
+
+import pytest
+
+import serde
+from .common import (
+    opt_case,
+    opt_case_ids,
+    FormatFuncs,
+    format_dict,
+    format_tuple,
+    format_json,
+    format_msgpack,
+    format_yaml,
+    format_pickle,
+    format_toml,
+    all_formats,
+)
+
+sa = pytest.importorskip("sqlalchemy", "2.0.0")
+sa_orm = pytest.importorskip("sqlalchemy.orm")
+
+log = logging.getLogger("test")
+
+serde.init(True)
+
+all_except_toml: FormatFuncs = (
+    format_dict + format_tuple + format_json + format_msgpack + format_yaml + format_pickle
+)
+
+
+@pytest.mark.parametrize("opt", opt_case, ids=opt_case_ids())
+@pytest.mark.parametrize("se,de", all_except_toml)
+def test_sqlalchemy_simple(se, de, opt):
+    log.info(f"Running test with se={se.__name__} de={de.__name__} opts={opt}")
+
+    class Base(sa_orm.MappedAsDataclass, sa_orm.DeclarativeBase):
+        pass
+
+    @serde.serde(**opt)
+    class User(Base):
+        __tablename__ = "user"
+
+        id: sa_orm.Mapped[int] = sa_orm.mapped_column(primary_key=True)
+        name: sa_orm.Mapped[str]
+        fullname: sa_orm.Mapped[str] = sa_orm.mapped_column(sa.String(30))
+        nickname: sa_orm.Mapped[Optional[str]] = sa_orm.mapped_column(init=False)
+
+    user = User(1, "john", "John Doe")
+    assert user == de(User, se(user))
+
+
+@pytest.mark.parametrize("opt", opt_case, ids=opt_case_ids())
+@pytest.mark.parametrize("se,de", format_toml)
+def test_sqlalchemy_simple_toml(se, de, opt):
+    log.info(f"Running test with se={se.__name__} de={de.__name__} opts={opt}")
+
+    class Base(sa_orm.MappedAsDataclass, sa_orm.DeclarativeBase):
+        pass
+
+    @serde.serde(**opt)
+    class User(Base):
+        __tablename__ = "user"
+
+        id: sa_orm.Mapped[int] = sa_orm.mapped_column(primary_key=True)
+        name: sa_orm.Mapped[str]
+        fullname: sa_orm.Mapped[str] = sa_orm.mapped_column(sa.String(30))
+
+    user = User(1, "john", "John Doe")
+    assert user == de(User, se(user))
+
+
+@pytest.mark.parametrize("opt", opt_case, ids=opt_case_ids())
+@pytest.mark.parametrize("se,de", all_formats)
+def test_sqlalchemy_nested(se, de, opt):
+    log.info(f"Running test with se={se.__name__} de={de.__name__} opts={opt}")
+
+    class Base(sa_orm.MappedAsDataclass, sa_orm.DeclarativeBase):
+        pass
+
+    @serde.serde(**opt)
+    class Project(Base):
+        __tablename__ = "projects"
+        id: sa_orm.Mapped[int] = sa_orm.mapped_column(primary_key=True)
+        name: sa_orm.Mapped[str] = sa_orm.mapped_column(sa.Text, nullable=False)
+        owner_id: sa_orm.Mapped[int] = sa_orm.mapped_column(sa.ForeignKey("users.id"))
+
+    @serde.serde(**opt)
+    class User(Base):
+        __tablename__ = "users"
+
+        id: sa_orm.Mapped[int] = sa_orm.mapped_column(primary_key=True)
+        name: sa_orm.Mapped[str] = sa_orm.mapped_column(sa.Text, nullable=False)
+        fullname: sa_orm.Mapped[str] = sa_orm.mapped_column(sa.Text, nullable=False)
+        projects: sa_orm.Mapped[list[Project]] = sa_orm.relationship(backref="owner")
+
+    user = User(1, "john", "John Doe", [Project(1, "Dummy", 1)])
+    assert user == de(User, se(user))