How to manage sqlalchemy model migration in fastapi

user_6891461 0
Viewed 1329

I have used fastapi for project facing issue with sqlachemy model

sqlachemy create model first time but when we change in model not affect on postgres database. Overcome this problem.

  • I have used alembic package for handle model migration it's work in adding new column and removing column but does not work in altering column like rename columns name, change datatype etc.
  • When I rename columns name it's create upgrade opration is droping an existing column and add new one with new name (loss of data)

like: rename is_superuser to is_admin

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column("user", sa.Column("is_admin", sa.Boolean(), nullable=True))
    op.drop_column("user", "is_superuser")
    # ### end Alembic commands ###

see what's going on droping is_superuser column inted of rename

I know the tricky solution is custom adding action in migration op.alter_column('table_name','column_name',new_column_name='new column name')

How to handle model migration with sqlalchemy in fastapi because if i will make mistake in alembic migration it would be denger for me.

Is there any other package or way to handle migration with sqlalchemy

fastapi flask sqlalchemy migration python
1 Answers

use case: rename column from is_superuser to is_admin in the table user

1. using alembic operations

For the above case, we can write the migration like below.

def upgrade():
    op.alter_column('user', 'is_superuser', nullable=True, new_column_name='is_admin')

Now, run the command alembic upgrade head --sql to see the actual SQL that will be applied on the databse.

ALTER TABLE user RENAME is_superuser TO is_admin;

2. using custom SQL

def upgrade():
    op.execute('ALTER TABLE user RENAME is_superuser TO is_admin;')

run command alembic upgrade head to apply the migration on the database.

Reference:

user_48599100
Related