how to handle exceptions in alembic migrations?

[Ofir Herzas]

I have an alembic migration script and I want to add some exception handling but not sure what is the best practice.

Basically, I have several issues to handle:

1. A change was already made and not needed (e.g. if I try to add_column, and this column already exists, I want it to continue)
2. A table is locked (if I try to perform some operation on a table and it is locked, I want to raise an exception)

3. other exceptions?

   def upgrade():
       engine = op.get_bind().engine
       op.add_column('t_break_employee', sa.Column('auto', sa.Boolean()))
       op.add_column('t_employee', sa.Column('settings', sa.Text()))

I thought about adding a class to be used with the 'with' statement on every change. Does it sound reasonable?

for example:

    def upgrade():
        engine = op.get_bind().engine
        with my_test():
            op.add_column('t_break_employee', sa.Column('auto', sa.Boolean()))
        with my_test():
            op.add_column('t_employee', sa.Column('settings', sa.Text()))

In that case, what are the exceptions I need to handle and how do I know if a table is locked?

[Michael Bayer]

seems a little risky, if your application wants to add a column, but someone else added it in some other way, how can you be sure what else has been done to that DB?    Skipping individual ops might address small issues but not big ones.   If you really have a workflow where the target DB might have manual changes applied, maybe instead you want to check for the existence of the column?

def upgrade():

    inspector = inspect(engine)

    cols = inspector.get_columns(“t_break_employee”)

    if “auto” not in [c[‘name’] for c in cols]:

        op.add_column(…)

just a thought.

as far as table is locked, if it’s persistently locked then your script would just hang, just like every other application trying to get to that table, until the deadlock elsewhere is resolved.

[Ofir Herzas]

Hi Michael,

Thanks for your prompt response and for the great tools you develop.

I have 2 reasons for doing that:

1. When I install my application on a new environment (blank db - no alembic_version), Alembic tries to run all migrations but since I run Base.metadata.create_all before Alembic upgrade, the schema is actually up to date and an exception is raised.

2. Sometimes in the development phase, I run the migration but then add some other changes and wish to apply them also

I agree that there might be better ways to accomplish what I want (insert the proper alembic_version on bootstrap and use downgrade), but I do like the notion of a migration that handles some known situations.

Regarding your suggestion, I guess it'll work but what about begging for forgiveness instead of asking for permission? Wouldn't it be better to catch the exception raised and handle it?

Regarding the table lock, this is exactly what I'm trying to prevent (script hang). Isn't there any test I could make before my script hangs?

Thanks,

Ofir

[Michael Bayer]

On Jun 8, 2014, at 11:08 AM, Ofir Herzas <her...@gmail.com> wrote:

> Hi Michael,
> Thanks for your prompt response and for the great tools you develop.
>
> I have 2 reasons for doing that:
> 1. When I install my application on a new environment (blank db - no alembic_version), Alembic tries to run all migrations but since I run Base.metadata.create_all before Alembic upgrade, the schema is actually up to date and an exception is raised.

when you run create_all(), you should also stamp the database with the latest migration. See the example at http://alembic.readthedocs.org/en/latest/tutorial.html#building-an-up-to-date-database-from-scratch for the way to do this. Otherwise you’d be running through a huge number of exceptions, but some of those statements might actually pass, e.g. if a migration adds a column that was later removed, it’s a recipe for things to get out of whack.

> 2. Sometimes in the development phase, I run the migration but then add some other changes and wish to apply them also
>
> I agree that there might be better ways to accomplish what I want (insert the proper alembic_version on bootstrap and use downgrade), but I do like the notion of a migration that handles some known situations.
>
> Regarding your suggestion, I guess it'll work but what about begging for forgiveness instead of asking for permission? Wouldn't it be better to catch the exception raised and handle it?

The issue is that the exceptions here aren’t something that can be handled in an automated way, other than just skipping them all, but that really doesn’t guarantee that the whole migration stream is going to run and leave the database in exactly the state that was started.

>
> Regarding the table lock, this is exactly what I'm trying to prevent (script hang). Isn't there any test I could make before my script hangs?

if tables in your DB is deadlocked, nothing is going to run against those tables. Wouldn’t a test that is checking for an already locked database be something external to your migration scripts? To test for this typically means querying the database’s system views for the presence of open locks. Depends on the platform.

[Ofir Herzas]

On Sunday, June 8, 2014 10:09:12 PM UTC+3, Michael Bayer wrote:

On Jun 8, 2014, at 11:08 AM, Ofir Herzas <her...@gmail.com> wrote:

> Hi Michael,
> Thanks for your prompt response and for the great tools you develop.
>
> I have 2 reasons for doing that:
> 1. When I install my application on a new environment (blank db - no alembic_version), Alembic tries to run all migrations but since I run Base.metadata.create_all before Alembic upgrade, the schema is actually up to date and an exception is raised.

when you run create_all(), you should also stamp the database with the latest migration.  See the example at http://alembic.readthedocs.org/en/latest/tutorial.html#building-an-up-to-date-database-from-scratch for the way to do this.  Otherwise you’d be running through a huge number of exceptions, but some of those statements might actually pass, e.g. if a migration adds a column that was later removed, it’s a recipe for things to get out of whack.

[Ofir] Seems like I missed that part. Thanks!

> 2. Sometimes in the development phase, I run the migration but then add some other changes and wish to apply them also
>
> I agree that there might be better ways to accomplish what I want (insert the proper alembic_version on bootstrap and use downgrade), but I do like the notion of a migration that handles some known situations.
>
> Regarding your suggestion, I guess it'll work but what about begging for forgiveness instead of asking for permission? Wouldn't it be better to catch the exception raised and handle it?

The issue is that the exceptions here aren’t something that can be handled in an automated way, other than just skipping them all, but that really doesn’t guarantee that the whole migration stream is going to run and leave the database in exactly the state that was started. 

[Ofir] In that case, if an exception is raised in the migration process, how can I fix it? (assuming that some migrations already passed, my schema wouldn' t be in stable state)

>
> Regarding the table lock, this is exactly what I'm trying to prevent (script hang). Isn't there any test I could make before my script hangs?

if tables in your DB is deadlocked, nothing is going to run against those tables.   Wouldn’t a test that is checking for an already locked database be something external to your migration scripts?      To test for this typically means querying the database’s system views for the presence of open locks.  Depends on the platform.

[Ofir] I suppose it could be external to my migration script. Even so, is it possible to test it through sqlalchemy without using specific dialects? 

[Michael Bayer]

On Jun 9, 2014, at 3:10 AM, Ofir Herzas <her...@gmail.com> wrote:

On Sunday, June 8, 2014 10:09:12 PM UTC+3, Michael Bayer wrote:

> 2. Sometimes in the development phase, I run the migration but then add some other changes and wish to apply them also
>
> I agree that there might be better ways to accomplish what I want (insert the proper alembic_version on bootstrap and use downgrade), but I do like the notion of a migration that handles some known situations.
>
> Regarding your suggestion, I guess it'll work but what about begging for forgiveness instead of asking for permission? Wouldn't it be better to catch the exception raised and handle it?

The issue is that the exceptions here aren’t something that can be handled in an automated way, other than just skipping them all, but that really doesn’t guarantee that the whole migration stream is going to run and leave the database in exactly the state that was started. 

[Ofir] In that case, if an exception is raised in the migration process, how can I fix it? (assuming that some migrations already passed, my schema wouldn' t be in stable state)

typically you test your migrations on a development database so that when they run on a real DB they don’t fail.     Then, if the backend supports transactional DDL, a failed migration will roll back the whole operation.  So it’s only if you’re on a non-transactional DDL backend (Oracle, MySQL, SQLite due to an issue in pysqlite) that you’d need to manually repair the failed migration.  But generally you want to be getting the scripts to be perfect in a test environment first.

>
> Regarding the table lock, this is exactly what I'm trying to prevent (script hang). Isn't there any test I could make before my script hangs?

if tables in your DB is deadlocked, nothing is going to run against those tables.   Wouldn’t a test that is checking for an already locked database be something external to your migration scripts?      To test for this typically means querying the database’s system views for the presence of open locks.  Depends on the platform.

[Ofir] I suppose it could be external to my migration script. Even so, is it possible to test it through sqlalchemy without using specific dialects? 

im not familiar with such a method, sorry.

[Ofir Herzas]

My resources are limited so I can't test all possibilities (sqlalchemy version, alembic version, db type and version, etc.) before releasing my migrations so every once in a while I get to a customer and have to do some manual cleaning.

Much to my dismay, I'm mostly using MySQL and Oracle so I guess I'll just have to live with it ...

Thanks again,

Ofir

[Michael Bayer]

typically if you’re releasing software it’s a good idea to pin it to specific versions of libraries.   that’s why virtualenvs are so popular, so an app can have exact library versions present.  just a thought….

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.