SQLAlchemy-Migrate or Alembic deletes data when renaming model

Question

I am using SQLAlchemy-Migrate to manage migrations for my PostgreSQL database. I changed the __tablename__ for a model, and running the migration changed the name in the database, but all the rows in the table were deleted. How can I rename a model without deleting data?

class Contract(db.Model):
    __tablename__ = 'contract'
    id = db.Column(db.Integer, primary_key=True)
    is_valid = db.Column(db.Boolean, default=IS_VALID)

I rename it from contract to contracts and get this migration:

def upgrade(migrate_engine):
    pre_meta.bind = migrate_engine
    post_meta.bind = migrate_engine
    pre_meta.tables['contract'].drop()
    post_meta.tables['contracts'].create()

It drops the old table and creates a new one. I never examined other migration scripts because they always ran without dropping the data.

Please include your (relevant) definitions, both before and after your changes, and the migrations you've used. In other words produce a [mcve]. It sounds like you've tried assigning a new value to a model class' `__tablename__` attribute in a live system after the class has been constructed. As you've figured out, this will not work because the corresponding `Table` object is constructed by Declarative during class construction. Renaming a table in Postgresql is simple, and does not require dropping anything: `ALTER TABLE the_table RENAME TO another_table;`. — Ilja Everilä, Jul 27 '17 at 08:51

davidism · Accepted Answer · 2021-06-23T13:44:35.650

SQLAlchemy-Migrate does not know that the table named "contract" in the database is the same as the model named "contracts" in the code. They're different names, and it only does a simple comparison. This is why you always review the generated migration scripts to make sure they do the right thing.

From the SQLAlchemy-Migrate docs, rename a table with the rename method.

pre_meta.tables['contract'].rename('contracts')

If you're using Alembic (or Flask-Alembic, or Flask-Migrate) instead of SQLAlchemy-Migrate, the same thing happens. Use the rename_table method.

op.rename_table('contract', 'contracts')

score 3 · Answer 2 · answered Sep 10 '22 at 19:13

I had a similar problem, for some readon, when using

op.rename_table("old_name", "new_name")

I kept getting

psycopg2.errors.DuplicateTable: relation "new_name" already exists

In the end, I used

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # op.drop_table('uploaded_resource')
    op.drop_table('sessions')
    
    op.execute('DROP TABLE IF EXISTS new_name')
    op.execute('ALTER TABLE old_name RENAME TO new_name')
    op.execute('ALTER SEQUENCE old_name_id_seq RENAME TO new_name_id_seq')
    op.execute('ALTER INDEX old_name_pkey RENAME TO new_name_pkey')
    ....

and

def downgrade():
    ...
    op.execute('ALTER TABLE new_name RENAME TO old_name')
    op.execute('ALTER SEQUENCE new_name_id_seq RENAME TO old_name_id_seq')
    op.execute('ALTER INDEX new_name_pkey RENAME TO old_name_pkey')

Which is less then ideal.

This also helped a fair bit https://petegraham.co.uk/rename-postgres-table-with-alembic/

KyleDev · Answer 3 · 2023-04-05T18:01:16.643

Just to add to @davidism's answer, when using the op.rename_table() function, you should make sure to add the relevant downgrade code in as well.

For example: If you have op.rename_table('table_name', 'new_table_name') inside of your upgrade() function, then you need to include op.rename_table('new_table_name', 'table_name') inside of the downgrade() function just in case anything goes wrong.

Hope this helps anyone that sees this, seeing that this is an old question already.

SQLAlchemy-Migrate or Alembic deletes data when renaming model

3 Answers3