Flask db upgrade error

If you've done any work with SQLite databases you surely know that this database is very limited in terms of making changes to the database schema. When working with a migration framework such as…

If you’ve done any work with SQLite databases you surely know that this database is very limited in terms of making changes to the database schema. When working with a migration framework such as Flask-Migrate, it is common to end up with migration scripts that fail to upgrade or downgrade just because they need to remove or modify a column in a table, something that SQLite does not support.

In this article I’m going to discuss this limitation of the SQLite database, and show you a workaround that is specific to Flask-Migrate and Alembic.

SQLite’s ALTER TABLE Implementation

Changes that you make to the fields in your model or to the constraints associated with them will end up as an ALTER TABLE statement sent to the database. If you are using MySQL, Postgres or most other database servers besides SQLite, this isn’t a problem. With SQLite, however, the ALTER TABLE command only supports adding or renaming columns. Any other change to columns or constraints is going to be rejected with an error.

You can test this yourself very easily. Take any Flask-SQLAlchemy application (you can use one of mine) and after making sure your database is up to date, remove or comment out a column in one of the models. Then generate a migration:

(venv) $ flask db migrate -m "remove a column"

If you open the generated migration script everything will look correct. Below you can see the migration that was generated after I removed a column named about_me from the User model:

"""remove a column

Revision ID: ec813e760b53
Revises: 834b1a697901
Create Date: 2020-07-19 18:18:44.066766

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'ec813e760b53'
down_revision = '834b1a697901'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('user', 'about_me')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('about_me', sa.VARCHAR(length=140), nullable=True))
    # ### end Alembic commands ###

The problem occurs when you try to upgrade the database with this migration:

(venv) $ flask db upgrade
[2020-07-19 18:21:14,268] INFO in __init__: Microblog startup
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 834b1a697901 -> ec813e760b53, remove a column
Traceback (most recent call last):
  File "/Users/mgrinberg/Documents/dev/python/microblog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1283, in _execute_context
    self.dialect.do_execute(
  File "/Users/mgrinberg/Documents/dev/python/microblog/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near "DROP": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
[ ... removed a long list of uninteresting stack frames ... ]
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "DROP": syntax error
[SQL: ALTER TABLE user DROP COLUMN about_me]
(Background on this error at: http://sqlalche.me/e/e3q8)

If you tried this on your database, delete the broken migration script before continuing. You will learn how to generate migrations that work better in the next section.

Using Batch Mode

Unfortunately there is no simple way to perform these operations that SQLite has not implemented. The only way to do this is to generate a brand new table with the new schema and copy all the data. Alembic includes support for migrating a table in this way with a feature called «batch mode».

You can enable batch mode in Flask-Migrate right when you initialize the extension. If you use the direct method of initialization:

migrate = Migrate(app, db, render_as_batch=True)

If you use the two-step initialization:

migrate = Migrate()

def create_app():
    # ...
    migrate.init_app(app, db, render_as_batch=True)
    # ...

Now that you have batch mode enabled, try to generate the migration again:

(venv) $ flask db migrate -m "remove a column"

Nothing appears to be different, but if you look at the new migration script, you will see differences:

"""remove a column

Revision ID: adfac8a1b2ee
Revises: 834b1a697901
Create Date: 2020-07-19 18:32:27.782197

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'adfac8a1b2ee'
down_revision = '834b1a697901'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.drop_column('about_me')

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.add_column(sa.Column('about_me', sa.VARCHAR(length=140), nullable=True))

    # ### end Alembic commands ###

Complications

There are situations in which batch mode alone does not solve upgrade errors.

A nasty type of issue occurs when the ALTER TABLE error occurs in the middle of a migration, after some operations were already applied. This could leave your database in an inconsistent state, where some changes from the migration script have been applied, but because of the error the version information is still pointing to the previous migration.

To unblock a database after a partial migration was applied I follow these steps:

  • Determine which of the operations were applied.
  • Delete everything from the upgrade() function.
  • Edit the downgrade() function so that it only contains the reverse of the operations that were applied to your database.
  • Run flask db upgrade. This is going to succeed because now you are running an empty upgrade. The database version will be upgraded.
  • Run flask db downgrade. This will undo those partial changes that were applied earlier, and reset the database version back to the last good state.
  • Delete the migration script and try again with batch mode enabled.

Another common issue occurs when your table has unnamed constraints, which the batch mode process can’t delete or modify because there is no way to refer to them by name. The Alembic documentation has some information on how to deal with unnamed constraints when using batch mode.

Conclusion

I hope this was a useful tip that can help you improve your migration workflow. Have you had issues with SQLite migrations that are not covered in this article? Let me know below in the comments.

Источник

Содержание

  1. Error on flask db upgrade #341
  2. Comments
  3. Fixing ALTER TABLE errors with Flask-Migrate and SQLite
  4. SQLite’s ALTER TABLE Implementation
  5. Using Batch Mode
  6. Complications
  7. Conclusion

Error on flask db upgrade #341

Hi! I’m trying to implement flask_migrate on my project. I use app factory pattern and followed the instructions from the blog. After setting «FLASK_APP=run.py», flask db init works fine and creates every file as expected. I create a test model, run flask db migrate , and the script is generated just fine. But when I run flask db upgrade, I get an error:

(venv) [guillermo@arch softalq]$ flask db upgrade
/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/flask_sqlalchemy/init.py:835: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future. Set it to True or False to suppress this warning.
‘SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and ‘
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> cc85dbd830d7, empty message
Traceback (most recent call last):
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py», line 1284, in _execute_context
cursor, statement, parameters, context
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py», line 590, in do_execute
cursor.execute(statement, parameters)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/cursors.py», line 170, in execute
result = self._query(query)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/cursors.py», line 328, in _query
conn.query(q)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/connections.py», line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/connections.py», line 732, in _read_query_result
result.read()
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/connections.py», line 1075, in read
first_packet = self.connection._read_packet()
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/connections.py», line 657, in _read_packet
packet_header = self._read_bytes(4)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/connections.py», line 707, in _read_bytes
CR.CR_SERVER_LOST, «Lost connection to MySQL server during query»)
pymysql.err.OperationalError: (2013, ‘Lost connection to MySQL server during query’)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File «/home/guillermo/PycharmProjects/softalq/venv/bin/flask», line 10, in
sys.exit(main())
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/flask/cli.py», line 967, in main
cli.main(args=sys.argv[1:], prog_name=»python -m flask» if as_module else None)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/flask/cli.py», line 586, in main
return super(FlaskGroup, self).main(*args, **kwargs)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/click/core.py», line 782, in main
rv = self.invoke(ctx)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/click/core.py», line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/click/core.py», line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/click/core.py», line 1066, in invoke
return ctx.invoke(self.callback, **ctx.params)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/click/core.py», line 610, in invoke
return callback(*args, **kwargs)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/click/decorators.py», line 21, in new_func
return f(get_current_context(), *args, **kwargs)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/flask/cli.py», line 426, in decorator
return __ctx.invoke(f, *args, **kwargs)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/click/core.py», line 610, in invoke
return callback(*args, **kwargs)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/flask_migrate/cli.py», line 134, in upgrade
_upgrade(directory, revision, sql, tag, x_arg)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/flask_migrate/init.py», line 96, in wrapped
f(*args, **kwargs)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/flask_migrate/init.py», line 271, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/command.py», line 298, in upgrade
script.run_env()
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/script/base.py», line 489, in run_env
util.load_python_file(self.dir, «env.py»)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/util/pyfiles.py», line 98, in load_python_file
module = load_module_py(module_id, path)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/util/compat.py», line 184, in load_module_py
spec.loader.exec_module(module)
File «», line 728, in exec_module
File «», line 219, in call_with_frames_removed
File «migrations/env.py», line 96, in
run_migrations_online()
File «migrations/env.py», line 90, in run_migrations_online
context.run_migrations()
File «», line 8, in run_migrations
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/runtime/environment.py», line 846, in run_migrations
self.get_context().run_migrations(**kw)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/runtime/migration.py», line 520, in run_migrations
step.migration_fn(**kw)
File «/home/guillermo/PycharmProjects/softalq/migrations/versions/cc85dbd830d7.py», line 21, in upgrade
op.drop_column(‘datos’, ‘test’)
File «», line 8, in drop_column
File «», line 3, in drop_column
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/operations/ops.py», line 2049, in drop_column
return operations.invoke(op)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/operations/base.py», line 374, in invoke
return fn(self, operation)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/operations/toimpl.py», line 81, in drop_column
operation.table_name, column, schema=operation.schema, **operation.kw
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/ddl/impl.py», line 240, in drop_column
self._exec(base.DropColumn(table_name, column, schema=schema))
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/alembic/ddl/impl.py», line 140, in _exec
return conn.execute(construct, *multiparams, **params)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py», line 1020, in execute
return meth(self, multiparams, params)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py», line 72, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py», line 1082, in _execute_ddl
compiled,
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py», line 1324, in execute_context
e, statement, parameters, cursor, context
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py», line 1518, in handle_dbapi_exception
sqlalchemy_exception, with_traceback=exc_info[2], from=e
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py», line 178, in raise
raise exception
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py», line 1284, in _execute_context
cursor, statement, parameters, context
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py», line 590, in do_execute
cursor.execute(statement, parameters)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/cursors.py», line 170, in execute
result = self._query(query)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/cursors.py», line 328, in _query
conn.query(q)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/connections.py», line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/connections.py», line 732, in _read_query_result
result.read()
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/connections.py», line 1075, in read
first_packet = self.connection._read_packet()
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/connections.py», line 657, in _read_packet
packet_header = self._read_bytes(4)
File «/home/guillermo/PycharmProjects/softalq/venv/lib/python3.7/site-packages/pymysql/connections.py», line 707, in _read_bytes
CR.CR_SERVER_LOST, «Lost connection to MySQL server during query»)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, ‘Lost connection to MySQL server during query’)
[SQL: ALTER TABLE datos DROP COLUMN test]
(Background on this error at: http://sqlalche.me/e/e3q8)

The changes are reflected to the db, but if I make another change in models.py and try to do another flask db migrate , it reports the db is not up to date. I’m using Mariadb on Arch Linux, latest SQLAlchemy and Flask_migrate with Python 3.7. I hope I’ve given enough info.
Cheers

The text was updated successfully, but these errors were encountered:

Источник

Fixing ALTER TABLE errors with Flask-Migrate and SQLite

If you’ve done any work with SQLite databases you surely know that this database is very limited in terms of making changes to the database schema. When working with a migration framework such as Flask-Migrate, it is common to end up with migration scripts that fail to upgrade or downgrade just because they need to remove or modify a column in a table, something that SQLite does not support.

In this article I’m going to discuss this limitation of the SQLite database, and show you a workaround that is specific to Flask-Migrate and Alembic.

SQLite’s ALTER TABLE Implementation

Changes that you make to the fields in your model or to the constraints associated with them will end up as an ALTER TABLE statement sent to the database. If you are using MySQL, Postgres or most other database servers besides SQLite, this isn’t a problem. With SQLite, however, the ALTER TABLE command only supports adding or renaming columns. Any other change to columns or constraints is going to be rejected with an error.

You can test this yourself very easily. Take any Flask-SQLAlchemy application (you can use one of mine) and after making sure your database is up to date, remove or comment out a column in one of the models. Then generate a migration:

If you open the generated migration script everything will look correct. Below you can see the migration that was generated after I removed a column named about_me from the User model:

The problem occurs when you try to upgrade the database with this migration:

If you tried this on your database, delete the broken migration script before continuing. You will learn how to generate migrations that work better in the next section.

Using Batch Mode

Unfortunately there is no simple way to perform these operations that SQLite has not implemented. The only way to do this is to generate a brand new table with the new schema and copy all the data. Alembic includes support for migrating a table in this way with a feature called «batch mode».

You can enable batch mode in Flask-Migrate right when you initialize the extension. If you use the direct method of initialization:

If you use the two-step initialization:

Now that you have batch mode enabled, try to generate the migration again:

Nothing appears to be different, but if you look at the new migration script, you will see differences:

Complications

There are situations in which batch mode alone does not solve upgrade errors.

A nasty type of issue occurs when the ALTER TABLE error occurs in the middle of a migration, after some operations were already applied. This could leave your database in an inconsistent state, where some changes from the migration script have been applied, but because of the error the version information is still pointing to the previous migration.

To unblock a database after a partial migration was applied I follow these steps:

  • Determine which of the operations were applied.
  • Delete everything from the upgrade() function.
  • Edit the downgrade() function so that it only contains the reverse of the operations that were applied to your database.
  • Run flask db upgrade . This is going to succeed because now you are running an empty upgrade. The database version will be upgraded.
  • Run flask db downgrade . This will undo those partial changes that were applied earlier, and reset the database version back to the last good state.
  • Delete the migration script and try again with batch mode enabled.

Another common issue occurs when your table has unnamed constraints, which the batch mode process can’t delete or modify because there is no way to refer to them by name. The Alembic documentation has some information on how to deal with unnamed constraints when using batch mode.

Conclusion

I hope this was a useful tip that can help you improve your migration workflow. Have you had issues with SQLite migrations that are not covered in this article? Let me know below in the comments.

Hello, and thank you for visiting my blog! If you enjoyed this article, please consider supporting my work on this blog on Patreon!

Источник

Источник

On a client project recently we wanted to understand what happens when a flask db upgrade operation against a Postgres database fails.

More specifically we wanted to find out what happens when a batch of scripts is being applied, and 1 script in the middle of the batch fails, e.g: in this scenario:

Given a migration set consisting of [X, Y, Z]
When the migration X succeeds
And the migration Y fails
Then ?

Flask could do one of two things:

  1. Leave script X applied to the database, roll back script Y
  2. Roll back both scripts X and Y

However in the documentation it wasn’t wholly clear what it would do.

We theorised the following would happen:

Scenario: Single migration in set succeeds
    Given a migration set consisting of [X]
    When the migration X succeeds
    Then the migration X is applied to the database
    
Scenario: Single migration in set fails
    Given a migration set consisting of [X]
    When the migration X fails
    Then the migration X is rolled back
    
Scenario: Multiple migrations in set, 1 fails in the set
    Given a migration set consisting of [X, Y, Z]
    When the migration X succeeds
    And the migration Y fails
    Then the migration Y is rolled back
    And the migration X is rolled back
    And the migration Z is never applied

How would we test this though?

Validating the Theory

All the below code can be found in this repository along with instructions on how to set this up locally: https://github.com/lukemerrett/flask-migration-rollback-research

Environment

Firstly we’ll provision an environment with a Postgres database running using Docker compose, hosted on port 6456.

# docker-compose.yml

version: "3.9"

services:
  db:
    image: postgres
    restart: always
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - "6456:5432"

Then spin it up using docker compose up -d

Application

Next we have an application that is used to generate 4 dummy database migration scripts that looks like this:

# app.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://postgres:postgres@localhost:6456/postgres"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)
migrate = Migrate(app, db)


class User(db.Model):
    name = db.Column(db.String(128), primary_key=True)
    date = db.Column(db.DateTime())
    department = db.Column(db.String(128))

Modifying the User model and running flask db migrate will generate a new script applying those changes.  The above code is the «final state» of the migration.

Migration Scripts

Through modifying the User and running flask db migrate a few times we end up with the below 4 migration scripts:

Script 1: dd7c69423ad9_.py

Creates the user table with an id integer primary key and a name string field.

"""empty message
Revision ID: dd7c69423ad9
Revises: 
Create Date: 2021-08-20 09:41:57.938972
"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('user',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=128), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('user')
    # ### end Alembic commands ###

Script 2: 5a77262c502b_.py

Adds the date column of type date time to the user table:

"""empty message
Revision ID: 5a77262c502b
Revises: dd7c69423ad9
Create Date: 2021-08-20 11:57:30.389344
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '5a77262c502b'
down_revision = 'dd7c69423ad9'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('date', sa.DateTime(), nullable=True))
    # ### end Alembic commands ###


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

Script 3: 653d68312991_.py — Broken Script

This is a modified script that will fail, it tries to drop a column on a table that doesn’t exist.  We’ve had to manually add that line in to break it.

When flask db upgrade reaches this script, it will error.

"""empty message
Revision ID: 653d68312991
Revises: 5a77262c502b
Create Date: 2021-08-20 12:04:14.107041
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '653d68312991'
down_revision = '5a77262c502b'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('user', 'name',
               existing_type=sa.VARCHAR(length=128),
               nullable=False)
    op.drop_column('jim', 'id')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False))
    op.alter_column('user', 'name',
               existing_type=sa.VARCHAR(length=128),
               nullable=True)
    # ### end Alembic commands ###

Script 4: 5929dc9c624a_.py

The final script, one we expect to never run due to the broken script before it, adds a department field to the user table:

"""empty message
Revision ID: 5929dc9c624a
Revises: 653d68312991
Create Date: 2021-08-20 12:26:57.567531
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '5929dc9c624a'
down_revision = '653d68312991'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('department', sa.String(length=128), nullable=True))
    # ### end Alembic commands ###


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

Running the Upgrade Tests

Now we need to test our 3 scenarios with the 4 scripts created above.

Scenario: Single migration in set succeeds
    Given a migration set consisting of [X]
    When the migration X succeeds
    Then the migration X is applied to the database

To test this we run flask db upgrade dd7c69423ad9 (the first script in our batch) which applies a valid script to the database.  This succeeds as expected and the database is brought up to date with the new user table.

Scenario: Single migration in set fails
    Given a migration set consisting of [X]
    When the migration X fails
    Then the migration X is rolled back

To test this we first run flask db upgrade 5a77262c502b, which is the second script that we also know works correctly.  The database now has the first 2 scripts applied and is ready for the broken script to be run,

Running flask db upgrade 653d68312991 (the broken script) correctly fails and any changes are rolled back.  Because we’ve run scripts dd7c69423ad9 and 5a77262c502b in separate upgrade operations they are still applied to the database, in this scenario only script 653d68312991 is rolled back.

Scenario: Multiple migrations in set, 1 fails in the set
    Given a migration set consisting of [X, Y, Z]
    When the migration X succeeds
    And the migration Y fails
    Then the migration Y is rolled back
    And the migration X is rolled back
    And the migration Z is never applied

Firstly we need to get our database into the correct state so there is a batch of upgrades that starts with 1 valid script, then a broken one, then a valid one again.

To do this we run:

# Ensure we've at least applied the stable first revision
flask db upgrade dd7c69423ad9
# Ensure if other revisions have been applied since, we return to the stable revision
flask db downgrade dd7c69423ad9

Leaving us with scripts 5a77262c502b (working), 653d68312991 (broken) and 5929dc9c624a (working) in a batch to be applied on the next upgrade.

Now running flask db upgrade will do the following:

  1. Apply 5a77262c502b, succeeded
  2. Apply 653d68312991, failed
  3. Roll back 653d68312991
  4. Roll back 5a77262c502b
  5. Never attempt to run 5929dc9c624a

Findings

If any of the scripts in a batch fail during a flask db upgrade, then none of the scripts in that batch are applied.

This means the database isn’t left in a state where only partial migrations have been applied.

This proves all our theorised scenarios are correct.

This works exactly the same in multidb mode; if 1 script across any of the databases fails on upgrade, every script in the batch across all databases is rolled back (see the behaviour-on-multi-db branch in the aforementioned repo).

Источник

Понравилась статья? Поделить с друзьями:
  • Firefox cors error
  • Flask 404 ошибка
  • Flashtool ошибка brom error 0x1
  • Flashtool ошибка 5007
  • Flashtool ошибка 3152