[Bug]: Reflecting database object results in json column being represented as string

[CaderIdris]

What happened?

When reflecting a table with a JSON column, the type is incorrectly returned as a String. SQLite and PostgreSQL both correctly return the expected JSON type.

I've had to downgrade to SQLAlchemy 2.0.44 due to issue #1379, all other relevant packages are on the latest version.

Not too familiar with the project but happy to lend a hand if needed.

MVWE:

from typing import Any, ClassVar

from sqlalchemy import (
    MetaData
)
from sqlalchemy.engine import create_engine
from sqlalchemy.types import JSON
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class _Base(DeclarativeBase):
    type_annotation_map: ClassVar[dict[type, Any]] = {
        dict[str, Any]: JSON
    }
    metadata = MetaData(schema="main")


class Example(_Base):
    __tablename__ = "example"
    id: Mapped[str] = mapped_column(primary_key=True)
    json: Mapped[dict[str, Any]] = mapped_column(nullable=False)


if __name__ == "__main__":
    conn_engine = create_engine(
        f"duckdb:///test.db"
    )
    _Base.metadata.create_all(conn_engine)
    metadata = MetaData(schema="main")
    metadata.reflect(bind=conn_engine)
    print("Table repr:")
    print(repr(metadata.tables["main.example"]))
    print("JSON column type:")
    print(metadata.tables["main.example"].c.json.type)

DuckDB Engine Version

0.17.0

DuckDB Version

1.4.3

SQLAlchemy Version

2.0.44

Relevant log output

DuckDBEngineWarning: duckdb-engine doesn't yet support reflection on indices
  warnings.warn(
Table repr:
Table('example', MetaData(), Column('id', String(), table=<example>, nullable=False), Column('json', String(), table=<example>, nullable=False), schema='main')
JSON column type:
VARCHAR

Code of Conduct

• I agree to follow this project's Code of Conduct

[CaderIdris]

I thought this might be due to json being an extension in duckdb, but adding json to the preload_extensions argument and/or running the load command on connection results in the same issue.

def duckdb_load_extensions(e: Connection, _) -> None:  # noqa: ANN001
    e.execute("LOAD json;")  # type: ignore[call-overload]


if __name__ == "__main__":
    conn_engine = create_engine(
        f"duckdb:///test.db",
        connect_args={
            'preload_extensions': ['json']
        }
    )
    event.listen(
        conn_engine,
        "connect",
        duckdb_load_extensions
    )

[CaderIdris]

Having looked into it a bit more, the CLI shows the JSON type when using both the DESCRIBE and SHOW functions.

DESCRIBE example;

{"column_name":"id","column_type":"VARCHAR","null":"NO","key":"PRI","default":null,"extra":null}
{"column_name":"json","column_type":"JSON","null":"NO","key":null,"default":null,"extra":null}

When loading the json extension and selecting all duckdb types, the logical type of JSON is VARCHAR and the type category is STRING. Are the types inferred from the logical type or type category when reflecting, potentially causing the issue?

LOAD json;
SELECT * FROM duckdb_types() WHERE type_name = 'JSON';

{
	"database_name":"system",
	"database_oid":0,
	"schema_name":"main",
	"schema_oid":0,
	"type_oid":null,
	"type_name":"JSON",
	"type_size":16,
	"logical_type":"VARCHAR",
	"type_category":"STRING",
	"comment":null,
	"tags":"{}",
	"internal":true,
	"labels":null
}