Fixing ALTER TABLE errors with Flask-Migrate and SQLite

Thanks Miguel! And that Flask-Migrate batch configuration was the option all the time? Hours of rewriting migrate scripts with SQLite db...

Thank you for this excellent piece of information! I had been struggling with this issue in the back of my mind off and on for awhile in a "for fun" project I've been working on while learning Flask. I was glad to find your post because it directly addressed my issue and provided a solution!

Thank you so much for this! Such a simple fix :)

This worked really well. Is there a reason to revert back to non-batch mode? Or is this the ideal default for any migration?

Fantastic! Thank you!

@Robert: I really haven't found any issues with batch mode being enabled.

Miguel, thanks for this - and for the whole microblog tutorial. I'm now working on my own project - and running into trouble when adding a new Foreign Key to an existing table. I switched to using batch mode as described here, but then get an error of "Constraint must have a name" when it tries to execute the command: batch_op.create_foreign_key(None, 'tourny', ['tourny_id'], ['id']). This foreign key I am adding will be the second FK in the table... that is not a problem is it?
I tried reading the Alembic documentation link you included at the end for unnamed constraints... but admittedly didn't understand how to apply t. Do you have any add'l advice for what to read to solve this? Many thanks.

@Sara: That None first argument is the foreign key name. Sometimes Alembic does not assign a name there. You should edit that and give the key a unique name. Also put the name in the corresponding delete call in the downgrade method.

Thank you.

I notice that any further attempts to upgrade the database whenever I add a new field and create a relationship between two existing tables throws the error ValueError: Constraint must have a name

For example, upgrading these two tables after creating a relationship between them causes the said error:

class Admin(db.Model):
    # ...
    comments = db.relationship('Comment', backref='admin', lazy='dynamic')

class Comment(db.Model):
    # ...
    admin_id = db.Column(db.Integer, db.ForeingKey='admin.id')

migrate instance has render_as_batch=True

When I check the generate migration script, I notice that there is depends_on = None. The upgrade() function, the create_foreign_key() call uses a constraint name which, I think, at the moment is set to None. Hence, the error.

"""comment table admin_id relationship

Revision ID: b2a878e994e9
Revises: d27105d2cdc3
Create Date: 2021-04-21 04:14:14.607153

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'b2a878e994e9'
down_revision = 'd27105d2cdc3'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('comment', schema=None) as batch_op:
        batch_op.add_column(sa.Column('admin_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key(None, 'admin', ['admin_id'], ['id'])

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('comment', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_column('admin_id')

    # ### end Alembic commands ###

I know you have offered a possible solution to this issue before as seen in this SO question.

Question #1:
Does it mean that in the meantime, since Alembic does not offer automatic naming for this, I have to manually pass a name to create_foreign_key()?

Question #2:
If I pass the name create_foreign_key('admin') to replace create_foreign_key(None), would it work just fine or it has to be something like create_foreign_key('fk_admin') as seen here in the alembic doc?

Question #3:
Replacing the None constraint name: is it looked at from the Admin side or the Comment side? My example above adds "admin" without much thought.

Also, could you please explain what naming_convention is in this method. It offers it as a solution, but I do not understand what is going on there.

@Gitau: I think this can be handled by defining naming conventions for your SQLAlchemy database. See this SO question for some details on how to do this. Once this is done your constraints are going to be given a name instead of None.

Fantastic, thank you! Given how influential the Flask Mega Tutorial continues to be, you may consider adding render_as_batch=True to the tutorial. A couple sentences about SQLite's limitations and the batch mode's workaround would suffice. Thank you for all that you share!

Hello Miguel.

This tutorial helped me a lot.

Thank you.

Hey Miguel thanks for your impact but I am currently having an error raised on my microblog app. It says "sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: user
". Please how do I solve it?

@Alex: you haven't created your database tables. Try running flask db upgrade to get the tables created (assuming you already have your database migrations created).

Hi Miguel, thanks for this excellent tutorial! It helped me a lot!

Thank you so much! This helped me a lot.

Hello Miguel, Thank you very much. But I have a problem. My production database is using PostgreSQL and I had to make some changes with ALTER TABLE. This is what the problematic migration file looks like

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('trade', 'trading_volume',
existing_type=sa.INTEGER(),
type_=sa.Float(),
existing_nullable=True)
op.alter_column('trade', 'fee',
existing_type=sa.INTEGER(),
type_=sa.Float(),
existing_nullable=True)
# ### end Alembic commands ###

def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('trade', 'fee',
existing_type=sa.Float(),
type_=sa.INTEGER(),
existing_nullable=True)
op.alter_column('trade', 'trading_volume',
existing_type=sa.Float(),
type_=sa.INTEGER(),
existing_nullable=True)
# ### end Alembic commands ###

Because of this file, I cannot run 'flask db upgrade' locally with sqlite... Could you provide some suggestions so that my migration files work for both Postgres in prod and sqlite locally?

I can remove this file, enable batch-mode and run 'flask db migrate' again which generates a migration file like below, which also works locally. But I am not sure if I can push this migration file for prod

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
with op.batch_alter_table('trade', schema=None) as batch_op:
batch_op.alter_column('trading_volume',
existing_type=sa.INTEGER(),
type_=sa.Float(),
existing_nullable=True)
batch_op.alter_column('fee',
existing_type=sa.INTEGER(),
type_=sa.Float(),
existing_nullable=True)

# ### end Alembic commands ###

def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
with op.batch_alter_table('trade', schema=None) as batch_op:
batch_op.alter_column('fee',
existing_type=sa.Float(),
type_=sa.INTEGER(),
existing_nullable=True)
batch_op.alter_column('trading_volume',
existing_type=sa.Float(),
type_=sa.INTEGER(),
existing_nullable=True)

# ### end Alembic commands ###

@Bogus: if you can make your migration work in batch mode, then that is the best solution. Batch mode has no effect on Postgres.

But if I make it work in batch mode by removing the migration file that has op.alter_column, it may work locally with sqlite but won't it be a problem when I want to run migration against Postgres since it won't know that there has been some altering table?(because the migration file has been removed)

I am a little worried because, as you mentioned, if batch mode has no effect on Postgres, I may be able to make it work locally with SQlite by removing the migration file that has 'Alter table' but this new series of migration files may not work on Postgres in Prod.. because it does not know, since the file is gone, it needs to alter table.

@Bogus: I didn't mean to say that batch mode migrations do nothing, just that the batch mode is ignored for databases that do not need it. The batch mode migration will also have an "alter column" instruction. For SQLite it will be executed as a copy to a new table. For Postgres it will still be done with an ALTER TABLE SQL command.

I'm having troubles with the batch mode. I set it to True but when i use the "flask -m migrate" it generates new script but it still doesn't do anything. You have any idea why it still looks disabled?

Manually editing the upgrade() with batch_alter_table() works, but I thought it should do it automatically.

@kris: It's impossible for me to diagnose the problem without getting details. My guess is that you are not enabling batch mode correctly, you should not need to edit the batch mode changes by hand.

Thanx Miguel.
I am learning Flask Web Dev from your book. In the process, I am working on different computers via version control and constantly get errors when migrating the database. Finally, I deleted the whole folder 'migrations' and initialized it again. Works for the test database for now.

Best,
Manuel