Alembic doesn't cleaup up after itself

Issue #25 resolved
Daniel Miller
created an issue

Some alembic commands (for example, upgrade and downgrade) create an engine and a connection, but these resources are not cleaned up on exit. I have some tests that create a new database, run alembic commands, and finally drop the database. However, the database cannot be dropped until the alembic resources are cleaned up. These resources are created in env.py. Should the env.py templates include some resource cleanup?

{{{

!python

engine = engine_from_config( config.get_section(config.config_ini_section), prefix='sqlalchemy.')

connection = engine.connect() try: context.configure( connection=connection, target_metadata=target_metadata )

with context.begin_transaction():
    context.run_migrations()

finally: connection.close() engine.dispose() }}}

Comments (4)

  1. Michael Bayer repo owner
    • changed milestone to 0.2.0

    I really would prefer if a "with:" could be used here, but as Connection doesn't seem to have that interface right now, there can be a close() at the end. The dispose() I'm much less excited about. People really get the wrong idea about dispose() just by seeing that it exists. It isn't needed in any way for your tables to be dropped, just the close() so that transactional locks are released.

    If you're embedding into a test here, something I do on this end as well, your env.py would best be modified to get at the engine that's being run for the tests.

  2. Daniel Miller reporter

    with connection: would be great.

    With regard to engine.dispose(), you seem to be suggesting that its not necessary. However, when I omit it my tests fail because there are still active connections in the pool and I can't drop the database. What I'd really like is engine.close(), which would close all active (and not detached) connections and invalidate the engine so no new connections can be made, like how the DBAPI's connection.close() invalidates all cursors that are using the connection.

    It's not the test's job to dig into the things that are being tested to free up resources.

  3. Michael Bayer repo owner

    My thinking at the moment on this is that it's not really like connection.close() closing out cursors as much as a function like DBAPI->close_all_connections(), which they of course don't have because it's redundant. They'd tell you to call close() on each connection.

    If you want to track all connections, you can do a "connect" event and put all the connections into a WeakKeyDictionary as they are intercepted by the event. The close() then closes out whatever is in that dictionary. The SQLA test suite does it, and it was extremely difficult to make it work in all cases; I'm not sure if it still doesn't miss some edge cases.

    So my reservations building this in are a. it dilutes the simple model of "close your connections", b. it's very hard to make it work 100% and c. it promotes even more confusion about the role of the engine.

    I'm shocked how often I see tutorials, mailing list emails, doing this:

    def query_the_database():
        """Query with SQLAlchemy.  SQLAlchemy requires that we create an 
        engine, *and* connect in order to get to the database"
        engine = create_engine(...)
        connection = engine.connect()
        results = connection.execute("...")
        connection.close()
        engine.dispose()
        return results
    

    It's news to me that PG doesn't let you DROP DATABASE if someone is just connected to it, but this seems to be the case.

    Anyway this is more a sqlalchemy feature request, for the task at hand i added the close() as well as just used NullPool which I think is more appropriate here, curious users will learn more that way than if they saw a dispose(), that's in 279f8359031a363e0c43a49dd87ac133eba4f6e9. reopen if that doesn't work for you, thanks !

  4. Log in to comment