sqlalchemy / sqlalchemy/alembic

evaulate support of schema_translate_map feature of SQLAlchemy with alembic migrations

Open
#555 15 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

missing behavior multitenancy op directives
Dominant language
Python
Stars
4.4k
Forks
375
PR merge metrics
No merged PRs in 30d

Description

Hello

I'm using the following version script for upgrade:

    op.create_table('feature_class',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=1024), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('name')
    )
    op.add_column(
        'features',
        sa.Column('feature_class_id', sa.Integer(), nullable=True),
    )

The table features still exists from a version before.
When running the upgrade it fails with the error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "features" does not exist
 [SQL: 'ALTER TABLE features ADD COLUMN feature_class_id INTEGER'] (Background on this error at: http://sqlalche.me/e/f405)

I enabled SQLAlchemy SQL statement output and was able to see the following SQL statements for the operation:

CREATE TABLE develop_test.feature_class (
        id SERIAL NOT NULL,
        name VARCHAR(1024) NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (name)
)

ALTER TABLE features ADD COLUMN feature_class_id INTEGER

As you can see the CREATE TABLE statement is using the schema name develop_test, but not the ALTER TABLE statement.

I apply the schema on this database by using the following SQLAlchemy command:

engine.update_execution_options(schema_translate_map={None: 'develop_test'})

Where engine is the engine object, which have been bound on the declarative class which I use for all table classes.

In my env.py script I use exactly this engine object to create the connection:

    # database is an "container"-like object, which collects several information about 
    # my database, like the engine, metadata and schema objects
    connectable = database.engine 

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            process_revision_directives=process_revision_directives,
            version_table_schema=database.schema 
        )

        with context.begin_transaction():
            context.run_migrations()

Maybe I'm using the schema in a wrong way?

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

Start in env.py at context.configure and the migration operations shown in the issue. Trace how the connection's schema_translate_map reaches run_migrations, comparing CREATE TABLE with op.add_column's generated ALTER TABLE. Reproduce the behavior against PostgreSQL and document or test the expected schema-qualified behavior.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python, sqlalchemy
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.