Postgres arrays returned as string by sqlalchemy

[gnuletik]

Context

Given the following SQL schema

CREATE TYPE public.state AS ENUM (
    'CREATED',
    'RUNNING'
);

CREATE TABLE public.member (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    states public.state[] DEFAULT ARRAY['CREATED'::public.state] NOT NULL
);

Queries:

-- name: GetMember :one
SELECT * FROM member WHERE id = $1;

sqlc-gen-python generates the following model:

class State(str, enum.Enum):
    CREATED = "CREATED"
    RUNNING = "RUNNING"


@dataclasses.dataclass()
class Member:
    id: uuid.UUID
    states: List[State]

And the following query:

GET_MEMBER = """-- name: get_member \\:one
SELECT id, states FROM member WHERE id = :p1
"""

class Querier:
    def __init__(self, conn: sqlalchemy.engine.Connection):
        self._conn = conn

    def get_member(self, *, member_id: uuid.UUID) -> Optional[models.Member]:
        row = self._conn.execute(sqlalchemy.text(GET_MEMBER), {"p1": member_id}).first()
        if row is None:
            return None
        return models.Member(
            id=row[0],
            states=row[1],
        )

Issue

When using the get_member() method, the states property doesn't have the expected type.
It should be a List[State] but the type is a string of the following format: {CREATED,RUNNING}.

member = queries.get_member(some_uuid)

# the following line is not expected.
type(member.states) # <class 'str'>

Workaround

Our current workaround is to manually parse the string but this would be better handled by SQLAlchemy / SQLC.

It seems that SQLAlchemy supports array types when using the ORM interface:
https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ARRAY
But I'm not sure how to use this with the execute method.

For reference, the List type is generated by sqlc-gen-python:

sqlc-gen-python/internal/gen.go

Lines 35 to 50 in 8b3fe83

	type pyType struct {
	InnerType string
	IsArray bool
	IsNull bool
	}
	func (t pyType) Annotation() *pyast.Node {
	ann := poet.Name(t.InnerType)
	if t.IsArray {
	ann = subscriptNode("List", ann)
	}
	if t.IsNull {
	ann = subscriptNode("Optional", ann)
	}
	return ann
	}