Flask-migrate: change model attributes and rename corresponding database columns

Question

I have a bit of experience with Flask but not very much with databases (Flask-migrate / alembic / SqlAlchemy).

I'm following this tutorial and things are working quite alright.

I have a User model like this:

# user_model.py

from app import DB
... other imports

class User(UserMixin, DB.Model):

    __tablename__ = 'users'
    id = DB.Column(DB.Integer, primary_key=True)
    username = DB.Column(DB.String(64), index=True, unique=True)
    email = DB.Column(DB.String(120), index=True, unique=True)
    password_hash = DB.Column(DB.String(128))

I can then initialize the db, do migrations, upgrades etc.

The problem started when I wanted to change that id attribute, which in Python is not a great variable name choice. Let's say I want to rename that to user_id instead.

Now obviously the db already exists and there is some data inside. I thought maybe by some kind of magic from Flask-Migrate/Alembic just modifying the User class would work. That is just change the id line above to:

user_id = DB.Column(DB.Integer, primary_key=True)

If I do this and run flask db migrate I get:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added column 'users.user_id'
INFO  [alembic.autogenerate.compare] Detected removed column 'users.id'

So actually Alembic detects this as a column being removed and a new one added, which I suppose makes sense.

But this in fact doesn't work if I run flask db upgrade. I get the following error:

ERROR [alembic.env] (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL [SQL: 'ALTER TABLE users ADD COLUMN user_id INTEGER NOT NULL']

The error is quite clear. The point is that I don't want to add a new column, I just want to rename an existing one.

Looking around I also tried to modify the script.py handling the upgrade to use the alter_column method:

def upgrade():
    ${upgrades if upgrades else "pass"}
    # just added this line below
    op.alter_column('users', 'id', nullable=False, new_column_name='user_id')

However this also doesn't seem to work (I get the same error as above).

So the question boils down to a very simple one: how do I rename a database columns in a Flask app using Flask-Migrate? Or in other words, if I wish to modify the attributes of a given model, what do I have to do so that the corresponding column names in the database are correctly renamed?

Answer 1

To simply rename a column in an alembic script (which is the same as flask-migrate), what you do is correct:

op.alter_column('users', 'id', nullable=False, new_column_name='user_id')

The problem comes from, in my opinion, that you need also to change its constraint as primary key:

op.drop_constraint('user_pkey', 'users', type_='primarykey')
op.create_primary_key('user_pkey', 'users', ['user_id'])

You may need to adjust the name of the primary key you re-create depending of your database type (It works like this for me with PostgreSQL)

Autogenerated alembic scripts should always be reviewed, quite often they do not do what we want if it's not for simple changes.

Note: If your column id was used as a foreign key, you may also want to change the foreign key constraints in other tables.

Alter Primary Key in Alembic describes the same kind of problem.