1

I have a flask app that is backed by a postgres database using flask-sqlalechmy. I've been using miguel grinberg's flask migrate to handle migrations, although I've come to realize that since it is a wrapper on top of alembic, I'm best served by asking questions framed in alembic.

The problem is that I have an association table that I forgot to add a unique id to.

Here is my class for the table with the new column. But I have some records in my database, so trying to run the default migration script of course gives me the "column cannot contain nullable values" error.

class HouseTurns(db.Model):
    __tablename__ = 'house_turns'
    __table_args__ = {'extend_existing': True}

    id = db.Column(db.Integer, primary_key=True) // the new column I want to add

    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), primary_key=True)
    house_id = db.Column(db.Integer, db.ForeignKey("houses.id"), primary_key=True)
    movie_id = db.Column(db.Integer, db.ForeignKey("movies.id"), primary_key=True)
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())

    user = db.relationship(User, lazy="joined")
    house = db.relationship(House, lazy="joined")
    movie = db.relationship(Movie, lazy="joined")

And here's the migration script generated by alembic

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('house_turns', sa.Column('id', sa.Integer(), nullable=False))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('house_turns', 'id')
    # ### end Alembic commands ###

I am really at a loss for how to write a migration that backfills the ids for the existing records with unique values. They don't necessarily need to be unique ids, just incrementing integers.

Seanyboy Lee
  • 165
  • 1
  • 12

1 Answers1

1

In the upgrade method you need to alter the add_column statement so that the column is initially created with nullable=True, then you can backfill the column, and then alter the column back to nullable=False.

That is, this line:

op.add_column('house_turns', sa.Column('id', sa.Integer(), nullable=False))

becomes:

op.add_column('house_turns', sa.Column('id', sa.Integer(), nullable=True))

You can then add statements to backfill your column. For example, looking at this SO question 'How to update with incrementing value', would give you something along the following lines (untested):

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # Make the id initially nullable
    op.add_column('house_turns', sa.Column('id', sa.Integer(), nullable=True))
    # create the temporary sequence - will be dropped at the end of this session
    op.execute('create temporary sequence sequence_house_turns')
    # set the start value of the sequence to 1
    op.execute("select setval('sequence_house_turns', 1")
    # update the existing rows
    op.execute('update house_turns set id=nextval('sequence_house_turns') where id is null')
    # Make the id required
    op.alter_column('house_turns', 'id', nullable=False)
    # ### end Alembic commands ###
pjcunningham
  • 7,676
  • 1
  • 36
  • 49