How to generate alembic_version table with current head version

[Pan Luo]

If someone new come to the project and they use  db.create_all() or some script to create a fresh new database with declared models. There is no alembic_version table and he/she will run into problems when doing the upgrade. Is there something in alembic to support it? I have been poking around and came up with the following:

 alembic_cfg = Config("alembic.ini")
 context = MigrationContext.configure(db.engine.connect())
 current_rev = context.get_current_revision()
 context._ensure_version_table()

 script = ScriptDirectory.from_config(alembic_cfg)
 head_revision = script.get_current_head()
 context.stamp(script, head_revision)

 command.downgrade(alembic_cfg, "base")

However, it works with file based sqlite db but no in memory one: Error: Destination base is not a valid downgrade target from current head(s)

It seems the migrationcontext use different from the one I generated and the one used in the alembic, so the current revision was empty when I do the downgrade (just for. Not sure why file based sqlite works.

Thanks.

[Michael Bayer]

A SQLite :memory: database only exists with the span of a single SQLite database connection.   You get that above when you call db.engine.connect().   But then when you call command.downgrade(), you are not passing this connection to that command in any way; the connection is stuck inside your MigrationContext which isn’t present in command.downgrade(alembic_cfg).   command.downgrade() then calls upon your env.py in order to get the database connection, and you probably have another engine.connect() in there using a different engine (and therefore connection pool).  This makes a brand new :memory: connection that has no state on it.

So you’d want to send an argument into the alembic_cfg in this case which refers to that connection from db.engine.connect(), which your env.py can pull out and make use of instead of connecting in some other way.  This requires modifications to your env.py to look for this connection and use it, instead of connecting again. Or you can set up this connection as a global variable somewhere.

This is a common issue so at some point I will add recipes to the documentation that show how to transfer a connection from the outside of command.xyz(), through the env.py and into the migration context.

[Pan Luo]

Looks like alembic_cfg only accepts string options. (set_main_option()). Is there a way to pass connect object? Thanks.

Cheers,
Pan

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy-alembic/1EMiDnOlQjI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy-alem...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Michael Bayer]

just tack it on the Config object for now, I’ll have to add more support for this kind of feature.

Pan Luo <luop...@gmail.com> wrote:

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.

[Pan Luo]

Thanks Michael for the tip. Here is the code I came up. Please feel free to make changes and use in your cookbook:

Use Case 1: If someone new come to the project and they use  db.create_all() or some script to create a fresh new database. There is no alembic_version table and the person will run into problems when doing the upgrade. The following code snippet can be added to database (re)create script to make sure the new database has most up-to-date version.

        # create db tables

        db.create_all()

# add database version table and add current head version

alembic_cfg = Config("alembic.ini")

connection = db.engine.connect()

context = MigrationContext.configure(connection)

context._ensure_version_table()

script = ScriptDirectory.from_config(alembic_cfg)

head_revision = script.get_current_head()

context.stamp(script, head_revision)

Use Case 2: When doing tests, you may want to test your database migration script as well. Here is the code snippet:

        # in your tests

        def test_migration(self):

                # create database

                db.create_all()

# create config object

alembic_cfg = Config("alembic.ini")

# get connection from db object

connection = db.engine.connect()

alembic_cfg.connection = connection

# make sure version table exists or create one

context = MigrationContext.configure(connection)

context._ensure_version_table()

# insert/update the head version

script = ScriptDirectory.from_config(alembic_cfg)

head_revision = script.get_current_head()

context.stamp(script, head_revision)

command.downgrade(alembic_cfg, "base")

command.upgrade(alembic_cfg, "head")

        # in your alembic/env.py 

    def run_migrations_online():

"""Run migrations in 'online' mode.

In this scenario we need to create an Engine

and associate a connection with the context.

"""

# the connection may come from the config object

if hasattr(config, 'connection'):

# load connection from config object

connection = config.connection

else:

# load connection from config file

engine = engine_from_config(

config.get_section(config.config_ini_section),

prefix='sqlalchemy.',

poolclass=pool.NullPool

)

connection = engine.connect()

context.configure(

connection=connection,

target_metadata=target_metadata

)

try:

with context.begin_transaction():

context.run_migrations()

finally:

# don't close connection if it is from outside

if not hasattr(config, 'connection'):

connection.close()

       

Hope it helps.

Cheers,
Pan

[Michael Bayer]

Pan Luo <luop...@gmail.com> wrote:

Thanks Michael for the tip. Here is the code I came up. Please feel free to make changes and use in your cookbook:

Use Case 1: If someone new come to the project and they use  db.create_all() or some script to create a fresh new database. There is no alembic_version table and the person will run into problems when doing the upgrade. The following code snippet can be added to database (re)create script to make sure the new database has most up-to-date version.

        # create db tables

        db.create_all()

# add database version table and add current head version

alembic_cfg = Config("alembic.ini")

connection = db.engine.connect()

context = MigrationContext.configure(connection)

context._ensure_version_table()

script = ScriptDirectory.from_config(alembic_cfg)

head_revision = script.get_current_head()

context.stamp(script, head_revision)

you shouldn’t be calling _ensure_version_table().  You should be calling command.stamp() which will run the full migration environment and ensure the version table is present.    The recipe at http://alembic.readthedocs.org/en/latest/cookbook.html#building-an-up-to-date-database-from-scratch should be doing what this is trying to do, and if it doesn’t work right now, that’s a bug.   Additionally, there is no need for “get_current_head()”, command.stamp() can be called with the string “head” directly.

Use Case 2: When doing tests, you may want to test your database migration script as well. Here is the code snippet:

        # in your tests

        def test_migration(self):

                # create database

                db.create_all()

# create config object

alembic_cfg = Config("alembic.ini")

# get connection from db object

connection = db.engine.connect()

alembic_cfg.connection = connection

# make sure version table exists or create one

context = MigrationContext.configure(connection)

context._ensure_version_table()

# insert/update the head version

script = ScriptDirectory.from_config(alembic_cfg)

head_revision = script.get_current_head()

context.stamp(script, head_revision)

same here.  the only difference here is that you want to send through a SQLite :memory: database, so you can attach that to alembic_cfg and have it invoked inside of your env.py as the source of connectivity.

[Pan Luo]

Thanks Michael. It works and simplify my code a lot!

Cheers,
Pan