SQLAlchemy JSON column not supported

[bystepii]

Environment

• Python version: 3.11.14
• SQLAlchemy version: 2.0.44
• pydynamodb version: 0.7.4

Description

Using a Column(JSON) in a SQLModel or SQLAlchemy table raises an exception because the DynamoDBDialect does not implement a _json_deserializer attribute.
This prevents reading or writing models that include JSON fields.

Minimal Reproducible Example

import os
from pydynamodb import sqlalchemy_dynamodb
from sqlalchemy.engine import create_engine
from sqlalchemy.sql.schema import Column
from sqlalchemy.types import JSON
from sqlmodel import Session, Field, SQLModel, select
import boto3

class LLMTable(SQLModel, table=True):
    __tablename__ = "llm_table"
    name: str = Field(primary_key=True, unique=True)
    spec: dict = Field(default={}, sa_column=Column(JSON))
    is_default: bool = Field(default=False)


session = boto3.Session()
client = session.client("dynamodb")
credentials = session.get_credentials()
aws_access_key_id = credentials.access_key
aws_secret_access_key = credentials.secret_key
region_name = session.region_name

conn_str = (
    "dynamodb://{aws_access_key_id}:{aws_secret_access_key}@dynamodb.{region_name}.amazonaws.com:443"
).format(
    aws_access_key_id=aws_access_key_id,
    aws_secret_access_key=aws_secret_access_key,
    region_name=region_name,
)
engine = create_engine(conn_str, echo=True)

with Session(engine) as session:
    statement = select(LLMTable)
    results = session.exec(statement)
    llm = results.first()
    print(llm)

Observed Behavior

DynamoDB returns an error:

2025-11-09 14:29:19,663 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-11-09 14:29:19,664 INFO sqlalchemy.engine.Engine SELECT name, spec, is_default 
FROM llm_table
2025-11-09 14:29:19,664 INFO sqlalchemy.engine.Engine [generated in 0.00010s] ()
2025-11-09 14:29:20,060 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
  File "/tmp/test/main.py", line 39, in <module>
    results = session.exec(statement)
              ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlmodel/orm/session.py", line 83, in exec
    results = super().execute(
              ^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2249, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 306, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1419, in execute
    return meth(
           ^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 526, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1641, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2358, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    result = context._setup_result_proxy()
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 1870, in _setup_result_proxy
    result = _cursor.CursorResult(self, strategy, cursor_description)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py", line 1447, in __init__
    metadata = self._init_metadata(context, cursor_description)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py", line 1497, in _init_metadata
    metadata = CursorResultMetaData(self, cursor_description)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py", line 376, in __init__
    raw = self._merge_cursor_description(
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py", line 626, in _merge_cursor_description
    return [
           ^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py", line 633, in <listcomp>
    context.get_result_processor(
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 1804, in get_result_processor
    return type_._cached_result_processor(self.dialect, coltype)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/sql/type_api.py", line 950, in _cached_result_processor
    rp = d["impl"].result_processor(dialect, coltype)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/miniconda3/envs/test/lib/python3.11/site-packages/sqlalchemy/sql/sqltypes.py", line 2813, in result_processor
    json_deserializer = dialect._json_deserializer or json.loads
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'DynamoDBDialect' object has no attribute '_json_deserializer'

Expected Behavior

The dialect should support mapping JSON columns to DynamoDB Map types.

[passren]

@bystepii Could you add unit test code for this update? Thank you

[passren]

@bystepii I merged your PR into 0.7.6 branch but test cases got failed. please check and raise PR to 0.7.6 after fix. Thank you.

https://github.com/passren/PyDynamoDB/actions/runs/19414073657/job/55539644599

You are able to refer to this page to run a local test before raising PR:
https://github.com/passren/PyDynamoDB/wiki/6.-Contribute-Your-Code

[bystepii]

@passren Thank you for the reference.

I am able to reproduce the failing test case, sorry for that.

I am primarily working with SQLModel (which just stores JSON in a string using json.dumps() and json.loads()), and after adding the json_serializer I can successfully use a JSON column with SQLModel.

However, I am not sure how sqlalchemy (and pydynamodb) internally handles JSON type (seems it stores JSON as Map type in DynamoDB).

Feel free to revert the failing test case, or to modify it so it passes for sqlalchemy.

[passren]

After tested with more test cases, I found declarative table is able to serialize/deserializer json correctly but it got failed on reflect table if use JSON type on the column.. looking into it.

[passren]

@bystepii Json type issue get addressed in 0.7.6.