sqlalchemy / sqlalchemy/alembic

autogenerate postgresql DOMAIN type column

Open
#1,357 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

autogenerate - detection bug open for pull requests postgresql
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,

  • CITEXT is not imported from the postgresql dialect and there a syntax error in the generated migration.
  • The check constraint is dropped in the generated DOMAIN definition

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_type is inferred as the underlying datatype rather than the domain
  • the generated DOMAIN type suffers from the same issues listed above

Expected behavior

  • The type is templated correctly, including the check constraint (see #1360)
  • No alter_column command 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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.