sqlalchemy / sqlalchemy/alembic
autogenerate postgresql DOMAIN type column
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 4.4k
- Forks
- 375
- PR merge metrics
- No merged PRs in 30d
Description
Describe the bug
I have the following postgres domain in my model
# adapted from html5 standard
# https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/email#basic_validation
_POSTGRES_EMAIL_RE = (
r"^"
r"[a-z0-9.!#$%&''*+/=?^_`{|}~-]+"
r"@[a-z0-9](?:[a-z0-9-]{0,61}[a-z0-9])?"
r"(?:\.[a-z0-9](?:[a-z0-9-]{0,61}[a-z0-9])?)*"
r"$"
)
EMAIL_DOMAIN = pg_dialect.DOMAIN(
'email',
pg_dialect.CITEXT(),
check=rf"value ~ '{_POSTGRES_EMAIL_RE}'"
)
class Base(DeclarativeBase):
pass
class Contact(Base):
__tablename__ = 'contacts'
id: Mapped[int] = mapped_column(Integer, primary_key=True)
email: Mapped[str] = mapped_column(EMAIL_DOMAIN)
When autogenerating the field, alembic outputs a create_table command like the following
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('contacts',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('email', postgresql.DOMAIN('email', CITEXT()), nullable=False),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
Notably,
CITEXTis not imported from thepostgresqldialect and there a syntax error in the generated migration.- The
checkconstraint is dropped in the generatedDOMAINdefinition
Fixing these and applying the upgrade creates the correct schema, however every subsequent attempt to autogenerate for the model results in spurious alter_column commands like the following:
op.alter_column('contacts', 'email',
existing_type=postgresql.CITEXT(),
type_=postgresql.DOMAIN('email', CITEXT()),
existing_nullable=False)
In particular,
- the
existing_typeis inferred as the underlying datatype rather than the domain - the generated
DOMAINtype suffers from the same issues listed above
Expected behavior
The type is templated correctly, including the check constraint(see #1360)- No
alter_columncommand is issued if the underlying model has not changed.
To Reproduce
Please try to provide a Minimal, Complete, and Verifiable example, with the migration script and/or the SQLAlchemy tables or models involved.
See also Reporting Bugs on the website.
# Insert code here
Error
# Copy error here. Please include the full stack trace.
Versions.
- OS: Ubuntu 22.04.3 LTS
- Python: 3.11.2
- Alembic: 1.12.0
- SQLAlchemy: 2.0.21
- Database: 15.4
- DBAPI: psycopg[c]==3.1.12
Additional context
Have a nice day!
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Reproduce the issue with the supplied SQLAlchemy model and PostgreSQL DOMAIN, then trace Alembic's PostgreSQL autogeneration and type-rendering entry points. Done means the generated DOMAIN preserves its underlying type and check constraint, and a second autogeneration produces no spurious alter_column operation.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python, sqlalchemy
- Domain
- databases, tooling
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100