CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemyalembicflask-migrate
simpleCoder
simpleCoder

Reputation: 202

sqlalchemy 'relation does not exist' after flask migrate

I have a sqlalchemy flask app that I am migrating from sqlalchemy 1.3 to 2.0.

I have added Mapped typing to all of the tables and am now running into a problem with the alembic/flask-migrate migrations.

I have a test case that walks through the migrations (generated with alembic types) using flask_migrate.upgrade

The upgrade looks something like this:

import sqlalchemy as sa

from alembic import op

# revision identifiers, used by Alembic.
revision = '291b82f31d83'
down_revision = 'ec473daa084f'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # start by cleaning out the old tables
    op.drop_table('test_table')

    # recreate them with constraint names
    op.create_table(
        'test_table',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('some_other_id', sa.String(), nullable=True),
        sa.PrimaryKeyConstraint('id', name=op.f('pk_test_table')),
        sa.UniqueConstraint(
            'some_other_id', name=op.f('uq_test_table_some_other_id')
        ),
    )

The test case runs:

db.session.execute(text("INSERT INTO test_table VALUES (1, 'empty_other_id');"))

I can see things like the following in the server output:

-- Running upgrade ec473daa084f -> 291b82f31d83

DROP TABLE test_table;

CREATE TABLE test_table (
    id SERIAL NOT NULL, 
    some_other_id VARCHAR, 
    CONSTRAINT pk_test_table PRIMARY KEY (id), 
    CONSTRAINT uq_test_table_some_other_id UNIQUE (some_other_id)
);

UPDATE alembic_version SET version_num='291b82f31d83' WHERE alembic_version.version_num = 'ec473daa084f';

COMMIT;

2024-05-14 18:19:04,799 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2024-05-14 18:19:04,799 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-05-14 18:19:04,800 INFO sqlalchemy.engine.Engine select current_schema()
2024-05-14 18:19:04,800 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-05-14 18:19:04,800 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2024-05-14 18:19:04,800 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-05-14 18:19:04,801 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-14 18:19:04.802 PDT [1632900] ERROR:  relation "test_table" does not exist at character 13
2024-05-14 18:19:04.802 PDT [1632900] STATEMENT:  INSERT INTO test_table VALUES (1, 'empty_other_id');
2024-05-14 18:19:04,802 INFO sqlalchemy.engine.Engine ROLLBACK

Here are my current dependencies:


alembic = "^1.13.1"
Flask = "2.3.3"
Flask-Cors = "4.0.1"
Flask-Login = "0.6.2"
flask-migrate = "^4.0.7"
types-flask-migrate = "^4.0.0.20240311"
flask-sqlalchemy = "^3.1.1"
flask-talisman = "1.1.0"
opencensus-ext-flask = "0.8.1"
psycopg2 = "2.9.3"
sqlalchemy = "^2.0.30"
sqlalchemy-migrate = "^0.13.0"
types-Flask = "1.1.6"

What I don't get is why this is happening, I've tried to change to the newer flask-migrate methodology but there's no change in behavior.

any thoughts?

Upvotes: 1

Views: 164

Answers (1)

simpleCoder
simpleCoder

Reputation: 202

After asking in the Github discussion for the project I got the answer:

  1. I needed to update my env.py file to match the current one
  2. I needed to make sure I calling flask_migrate.upgrade without the sql=True flag as that makes the upgrade run in offline (e.g., not affecting a DB) mode. Because we are running against a "live" PostgreSQL DB that's on an in-memory FS we need to make sure it's running in online mode for the migrations to be applied.

See the rest of the discussion here: https://github.com/miguelgrinberg/Flask-Migrate/discussions/544#discussioncomment-9457800

Upvotes: 0

Related Questions