Using autogenerate first time on multi-database env generates invalid revision file.

[sqlalchemy-bot]

Migrated issue, originally created by pipoket (@pipoket)

Description

I was looking into alembic to figure out whether i could use it on my project, but ended up finding out some strange behavior.

As my project is using multiple databases, I followed the documentation to prepare alembic environment and tried to generate 'initial' migration revision file with current database schema.

However, strangely, the revision file generated contained all the tables from every other database on single database upgrade/downgrade function. And as expected, running the migration on clean database using the revision file made all the tables to be created on single database.

How to Reproduce the Problem

Here is the minimized test case for reproducing what I explained above.

Model & Configurations

This is the models.py file for model declaration.

from sqlalchemy import Table, Column, Integer
from sqlalchemy.ext.declarative import declarative_base


BaseA = declarative_base()

class TableA(BaseA):
    __tablename__ = "TableA"

    id = Column(Integer, autoincrement=True, primary_key=True)


BaseB = declarative_base()

class TableB(BaseB):
    __tablename__ = "TableB"

    id = Column(Integer, autoincrement=True, primary_key=True)

Alembic is configured like following:

// alembic.ini
// Only updated 'databases' portion of the file with sections for each database

// ...(snip)...

databases = my_database_a, my_database_b

[my_database_a]
sqlalchemy.url = mysql+mysqldb://user_a:user_a@127.0.0.1:18920/my_database_a?charset=utf8

[my_database_b]
sqlalchemy.url = mysql+mysqldb://user_b:user_b@127.0.0.1:18920/my_database_b?charset=utf8

// ...(snip)...

# alembic/env.py
# Only updated 'target_metadata' portion of the file

# ...(snip)...

import os
import sys
MODEL_PATH = os.path.join(os.path.abspath(os.path.dirname(__file__)), "..")
sys.path.append(MODEL_PATH)

from model import *
target_metadata = {
        'my_database_a': BaseA.metadata,
        'my_database_b': BaseB.metadata,
}

# ...(snip)...

Note that above alembic.ini file has non-standard port for mysql connection which I use on my development environment (and yes, mysqld is properly running and connection can be made).

Procedure

1. Make empty database for both tables

2. Try to generate revision file using autogenerate feature like blow

$ alembic revision --autogenerate -m "initial"
INFO  [alembic.env] Migrating database my_database_b
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'TableB'
INFO  [alembic.env] Migrating database my_database_a
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'TableA'
  Generating /home/pipoket/testcase/alembic/versions/5863df07ca83_init.py ... done

3. Generated revision file contains all the tables within single database, this is the problem.

"""init

Revision ID: 5863df07ca83
Revises:
Create Date: 2015-08-20 14:36:12.803208

"""

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

from alembic import op
import sqlalchemy as sa


def upgrade(engine_name):
    globals()["upgrade_%s" % engine_name]()


def downgrade(engine_name):
    globals()["downgrade_%s" % engine_name]()





def upgrade_my_database_a():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('TableB',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('TableA',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###


def downgrade_my_database_a():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('TableA')
    op.drop_table('TableB')
    ### end Alembic commands ###


def upgrade_my_database_b():
    pass


def downgrade_my_database_b():
    pass

4. Making first migration using file above creates all the tables within single database, as expected from the revision file itself.

 $ alembic upgrade 5863df07ca83
 INFO  [alembic.env] Migrating database my_database_b
 INFO  [alembic.runtime.migration] Context impl MySQLImpl.
 INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
 INFO  [alembic.runtime.migration] Running upgrade  -> 5863df07ca83, init
 INFO  [alembic.env] Migrating database my_database_a
 INFO  [alembic.runtime.migration] Context impl MySQLImpl.
 INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
 INFO  [alembic.runtime.migration] Running upgrade  -> 5863df07ca83, init

 $ mysql -uroot -p
 mysql> use my_database_a;
 Database changed
 mysql> show tables;
 +-------------------------+
 | Tables_in_my_database_a |
 +-------------------------+
 | TableA                  |
 | TableB                  |
 | alembic_version         |
 +-------------------------+
 3 rows in set (0.00 sec)
 mysql> use my_database_b;
 Database changed
 mysql> show tables;
 +-------------------------+
 | Tables_in_my_database_b |
 +-------------------------+
 | alembic_version         |
 +-------------------------+
 1 row in set (0.00 sec)

Expected Behavior

I expected autogenerate feature to generate the revision file like fowllowing:

"""init

Revision ID: 5863df07ca83
Revises:
Create Date: 2015-08-20 14:36:12.803208

"""

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

from alembic import op
import sqlalchemy as sa


def upgrade(engine_name):
    globals()["upgrade_%s" % engine_name]()


def downgrade(engine_name):
    globals()["downgrade_%s" % engine_name]()





def upgrade_my_database_a():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('TableB',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('TableA',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###


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


def upgrade_my_database_b():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('TableB',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###


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

Conclusion

There is a possibility that I might have completely misunderstood the proper usage of alembic. If so, please feel free to let me know. Otherwise, I would really appreciate it if you look into the case above whether it is a bug or expected behavior.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

thanks for reporting. This is regression in 0.8.0. Please use release 0.7.7 for now for this issue until 0.8.1 is released.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: high priority

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: autogenerate - rendering

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "fasttrack"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Fixed a regression 0.8 whereby the "multidb" environment template
  failed to produce independent migration script segments for the
  output template. This was due to the reorganization of the script
  rendering system for 0.8. To accommodate this change, the
  :class:.MigrationScript structure will in the case of multiple
  calls to :meth:.MigrationContext.run_migrations produce lists
  for the :attr:.MigrationScript.upgrade_ops and
  :attr:.MigrationScript.downgrade_ops attributes; each :class:.UpgradeOps
  and :class:.DowngradeOps instance keeps track of its own
  upgrade_token and downgrade_token, and each are rendered
  individually.
  fixes Using autogenerate first time on multi-database env generates invalid revision file. #318

→ e05f0c3

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

thanks again for reporting, the multidb template is now run directly in the test suite for the autogenerate use case to ensure it doesn't break again. this will be in release 0.8.1.

[sqlalchemy-bot]

pipoket (@pipoket) wrote:

Wow, I didn't expect such a prompt response with decent patch including the test case :D
Glad that I could help and many thanks!