Can I create Postgres `GENERATED ALWAYS AS` columns with SQLAchemy MappedAsDataclass?

Question

In my project (a Postgres database created and managed by SQLAlchemy), I am using MappedAsDataclass and DeclarativeBase to create tables like this:

class Base(MappedAsDataclass, DeclarativeBase):
    """subclasses will be converted to dataclasses"""

class Collection(Base):
    __tablename__ = 'collections'
    collection_id: Mapped[int] = mapped_column(init=False, primary_key=True)
    collection_name: Mapped[str] = mapped_column(default=None, unique = True)

User.__table__

Collection.metadata.create_all(engine)

I want to create this table with a computed column, 'ezname' that removes spaces from entries in 'collection_name' and replaces them with '_' underscore.

I can get what I want by creating the above table and then running the raw Postgres command:

ALTER TABLE collections ADD column ezname varchar GENERATED ALWAYS AS (REGEXP_REPLACE(collection_name, ' ', '_')) stored;

Is there a way to create this column in the ORM model, specifically when declaring tables using MappedAsDataclass and DeclarativeBase?

In my reading of the documentation, I have come across @hybrid_property and column_property, however my attempts to get them to work with my mapped dataclass schema have failed so far.

I have been trying stuff like this (which obviously didn't work)

class Collection(Base):
    __tablename__ = 'collections'
    collection_id: Mapped[int] = mapped_column(init=False, primary_key=True)
    collection_name: Mapped[str] = mapped_column(default=None, unique = True)
    collection_ezname: Mapped[str] = mapped_column(default=None, unique = True, Computed(REGEXP_REPLACE(collection_name, ' ', '_')))

So first I want to know if SQLA can do this, and if so how?

Please note that it is important to me that the logic be part of the table itself once created, and thus be portable outside of the ORM context.

Answer 1

You almost had it, you can use this.

from sqlalchemy import Computed, create_engine, func, select
from sqlalchemy.orm import Mapped, MappedAsDataclass, DeclarativeBase, Session, mapped_column

class Base(MappedAsDataclass, DeclarativeBase):
    pass

class Collection(Base):
    __tablename__ = 'collections'
    collection_id: Mapped[int] = mapped_column(init=False, primary_key=True)
    collection_name: Mapped[str] = mapped_column(default=None, unique=True)
    collection_ezname: Mapped[str] = mapped_column(Computed(func.REGEXP_REPLACE(collection_name, ' ', '_')), unique=True, init=False)

engine = create_engine("")

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Collection('something with space'))
    session.commit()

with Session(engine) as session:
    for i in session.scalars(select(Collection)):
        print(i)

This generates the following

CREATE TABLE collections (
        collection_id SERIAL NOT NULL, 
        collection_name VARCHAR NOT NULL, 
        collection_ezname VARCHAR GENERATED ALWAYS AS (REGEXP_REPLACE(collection_name, ' ', '_')) STORED NOT NULL, 
        PRIMARY KEY (collection_id), 
        UNIQUE (collection_name), 
        UNIQUE (collection_ezname)
)

Output

Collection(collection_id=1, collection_name='something with space', collection_ezname='something_with space')

You may have to change your regex as it currently does not replace multiple spaces and other scenarios I can't think of. Probably REGEXP_REPLACE(collection_name, ' ', '_', 'g')?