Flask-migrate: Alembic didn't detect column change: Error: no support for ALTER of constraints in SQLite dialect

[keithalpichi]

I have been following the Flask Web Book and have changed the name of a column named "author_id" to "user_id". Come to find out that Alembic does not detect Column name changes and I have been having issues since the update of this column.

When I run a migration I get:

alembic.util.CommandError: Target database is not up to date.

When I run an upgrade I get:

NotImplementedError: No support for ALTER of constraints in SQLite dialect

Here is the Model I have edited:

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.Text)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id')) #<-------- changes were made here

I've checked the sqlite schema and here's what I get for the Posts Table that I have edited, plus the User Table that it should be referencing:

CREATE TABLE users (
    id INTEGER NOT NULL, 
    username VARCHAR(64), 
    role_id INTEGER, confirmed BOOLEAN, email VARCHAR(64), password_hash VARCHAR(128), about_me TEXT, last_seen DATETIME, location VARCHAR(64), member_since DATETIME, name VARCHAR(64), avatar_hash VARCHAR(32), 
    PRIMARY KEY (id), 
    FOREIGN KEY(role_id) REFERENCES roles (id)
);

 CREATE TABLE "posts" (
    id INTEGER NOT NULL, 
    body TEXT, 
    timestamp DATETIME, 
    user_id INTEGER, 
    PRIMARY KEY (id)
);

So it seems the name of the column has been changed (not sure how). However, it seems the migration upgrades are not applying the foreign key changes to sqlite (even the User table has a foreign key to the Role table). So i've checked the latest migration script and the upgrade seems to be correct:

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_foreign_key(None, 'posts', 'users', ['user_id'], ['id']) #<---- here
    ### end Alembic commands ###

I've been doing some research and have come across Alembic batch operations to apply a "move and copy" work-around. I haven't implemented this path yet because I wasn't sure if it was the right thing to do. If it is I'm assuming you put the batch operation directly into an upgrade script?

My hypothesis is that since I have changed the name of the "user_id" column in the Post Model, the database isn't referencing the relationship connection between the two Models. I believe this is so because when I run the server there is an error: UndefinedError: 'None' has no attribute 'gravatar' for the line "post.user.gravatar(size=40)"

[miguelgrinberg]

Come to find out that Alembic does not detect Column name changes

This is incorrect. Alembic detects renamed columns and writes a correct migration script for those. The problem is the sqlite does not support renaming columns. My recommendation is that you switch to another database engine if you plan to do these type of operations often. Both MySQL and Postgres have support for column renames.

The error alembic.util.CommandError: Target database is not up to date. means that your database is out of sync with your models. This is a direct consequence of not being able to run the last migration, the one that renames the author_id column. If you back out of that change in the models and the migration script you will be fine, I think.

[deveshaggrawal19]

I tried to alter the constraints on foreign key from
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, unique=True) to
user_id = db.Column(db.Integer, db.ForeignKey('user.id', use_alter=True, post_update=True), nullable=False, unique=True)
but after migrations it is showing no changes in schema is detected. How can I make it detect the changes. Please be descriptive as I don't know how to write migrations. Thankyou

[miguelgrinberg]

@deveshaggrawal19 Alembic does not detect everything automatically, it has a set of things that it looks for. I understand you don't want to write migrations, but sometimes that's the only way, sorry. In addition to that, you are going to find that if you use SQLite some things that Alembic does detect cannot be changed because of several limitations in that database.

[volokolamskspb]

Hi, @miguelgrinberg
I have similar problem.
I'm doing exercises from "The Flask Mega-Tutorial Part VI: Profile Page and Avatars" from your tutorial:
https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-vi-profile-page-and-avatars

I added those fields to the User table:
about_me = db.Column(db.String(140))
last_seen = db.Column(db.DateTime, default=datetime.utcnow)

And when I run "flask db migrate -m "new fields in user model" I'm getting this Error: "Target database is not up to date"
I tried "flask db upgrade" but it also throws Errors: "Dublicate field email".
What am I doing wrong?

[miguelgrinberg]

@volokolamskspb this happens when your database is out of sync with the migration repository. The duplicate field email error means that your migration repository has a new migration that is not in your database that adds the email field, but when Flask-Migrate tries to apply that migration it finds that an email field is already in the database. A possible cause for this is if you were making changes to the database outside of the migrations, for example by running db.create_all(), which should never be used when you use migrations.

If you don't have any important data in your database, the easiest way to correct the situation is to delete the database file, and then run flask db upgrade again. That should create a brand new database and apply all migrations cleanly.

[miguelgrinberg]

This issue will be automatically closed due to being inactive for more than six months. Please reopen if you need more assistance.