Feature Request: Optional native SQLAlchemy Enum for dy.Enum in to_sqlalchemy_columns (Postgres)

[jackoberman]

TL;DR: Opt-in dy.Enum → sa.Enum on Postgres for Alembic drift detection; keep CHAR/String as the default.

Summary

I would like to use dy.Enum with a Python enum.Enum / StrEnum for Polars validation and Schema.to_sqlalchemy_columns() for SQLAlchemy table definitions paired with Alembic. Today dy.Enum always maps to CHAR(n) or String(n), not sa.Enum. That keeps Polars and SQL storage aligned as strings, but it breaks the common pattern of one Python enum as the source of truth when Postgres uses a native ENUM type and Alembic should detect new members.
It would be helpful to have an opt-in way for dy.Enum to emit sqlalchemy.Enum (and on Postgres, a named PG enum type), similar to how dy.Struct already maps to dialect-specific types (e.g. JSONB on Postgres).

Current behavior

For a schema like this:

from enum import StrEnum, auto
import dataframely as dy

class Status(StrEnum):
    PENDING = auto()
    APPROVED = auto()
    EXPIRED = auto()

class Staged(dy.Schema):
    status = dy.Enum(Status)

Staged.to_sqlalchemy_columns(postgresql.dialect()) produces a string column (e.g. VARCHAR(8) when category lengths differ), because Enum.sqlalchemy_dtype uses sa.CHAR / sa.String based on max category length:

def sqlalchemy_dtype(self, dialect: sa.Dialect) -> sa_TypeEngine:
    category_lengths = [len(c) for c in self.categories]
    if all(length == category_lengths[0] for length in category_lengths):
        return sa.CHAR(category_lengths[0])
    return sa.String(max(category_lengths))

This matches the docstring (“enum (string) values”) and existing tests, and is fine when the database also stores plain strings.

Issue

Many SQLAlchemy + Postgres setups use native enum types for constrained columns:

sa.Column("status", sa.Enum(MyStatus, name="my_status"), nullable=False)

Benefits:

• DB-level constraint on allowed values
• Alembic autogenerate / alembic check can surface new enum members as schema drift
• Consistent with hand-written sa.Table definitions that already use sa.Enum(...)

When the table is built only from to_sqlalchemy_columns(), dy.Enum and sa.Enum diverge: adding a member to the Python enum updates Polars validation but not the reflected SQL type Alembic compares against, unless every enum column is manually overridden after to_sqlalchemy_columns().

That undermines the goal of defining tables once from the dy.Schema and keeping migrations in sync.

Idea

Add an opt-in flag on dy.Enum (names are suggestions):

• sqlalchemy_native=True, or
• sqlalchemy_enum=True plus optional sqlalchemy_enum_name: str | None for the Postgres type name

For example:

class Staged(dy.Schema):
    status = dy.Enum(Status, sqlalchemy_native=True)  # name TBD

cols = Staged.to_sqlalchemy_columns(postgresql.dialect())
# status column type should be sa.Enum(Status, name="...", ...)
# not sa.String(8)

Default behavior should stay CHAR / String so existing tests and deployments are unchanged.

Version

• dataframely: 2.10.1
• sqlalchemy: 2.1.0b2, PostgreSql, Alembic

Happy to open a PR!

Side note

This is my first time opening an issue on an open source project! Let me know if this is poorly written!

[AndreasAlbertQC]

Thanks for reaching out Jack Oberman (@jackoberman). Very clear description and reasonable problem to want to solve :)

I was hoping to come up with a suggestion that would not expose the sqlalchemy-specific stuff in the type constructor because that feels like a detail is bubbling up that should have remained below the surface since it's only relevant for one specific backend functionality. The only way I can think of accomplishing that would be to not make these properties of the dy.Enum class, but instead pass them as kwargs to dy.Enum.to_sqlalchemy_columns. However, if you want to control enum behavior on a column level, that will get annoying quickly because you'd have to pass the args through dy.Schema.to_sqlalchemy_columns, which is not column-specific.

Long story short: After thinking about it, I feel like your suggestion is good :) Maybe we can establish the convention that all sqlalchemy-related arguments are just prefixed with sqlalchemy_ as you already show, so we can at least organize them neatly. I would prefer very clear namings here, so maybe sqlalchemy_use_enum and sqlalchemy_enum_name ?

In any case: PR is welcome!

[jackoberman]

Makes sense... perhaps adding param use_sqlalchemy_enum=False to to_sqlalchemy_columns() is simpler. It would limit the ability to control behavior at a column level, but it might be a simpler workaround until long-term convention is settled.

I'll go ahead and drop a PR that adds sqlalchemy_use_enum=False param to dy.Enum, but if you change your mind and would prefer the param on to_sqlalchemy_columns() instead, I can revise.

And thanks for the feedback Andreas Albert (@AndreasAlbertQC) !