How to get alembic to generate migrations in the correct order?

[John Anderson]

I have an existing app that I want to add some features to, so I created the new models and then ran --autogenerate and it created me a revision file where in downgrade it was creating all my files and in upgrade it was dropping the tables.   That was backwards from what I expected, since my database didn't have the tables but the model did.

Am I thinking about this in the wrong way?

Here is the generated file:

"""added presentation table

Revision ID: 29b24a536dc5

Revises: None

Create Date: 2012-03-24 15:03:18.268581

"""

# revision identifiers, used by Alembic.

revision = '29b24a536dc5'

down_revision = None

from alembic import op

import sqlalchemy as sa

from sqlalchemy.dialects import postgresql

def upgrade():

    ### commands auto generated by Alembic - please adjust! ###

    op.drop_table(u'presentation')

    op.drop_table(u'file')

    ### end Alembic commands ###

def downgrade():

    ### commands auto generated by Alembic - please adjust! ###

    op.create_table(u'presentation',

    sa.Column(u'pk', sa.INTEGER(), server_default='nextval('presentation_pk_seq'::regclass)', nullable=False),

    sa.Column(u'name', sa.TEXT(), nullable=True),

    sa.Column(u'description', sa.TEXT(), nullable=True),

    sa.Column(u'presenter', sa.TEXT(), nullable=True),

    sa.Column(u'date', postgresql.TIMESTAMP(), nullable=True),

    sa.PrimaryKeyConstraint(u'pk', name=u'presentation_pkey')

    )

    op.create_table(u'file',

    sa.Column(u'pk', sa.INTEGER(), server_default='nextval('file_pk_seq'::regclass)', nullable=False),

    sa.Column(u'user_pk', sa.INTEGER(), nullable=False),

    sa.Column(u'mimetype', sa.TEXT(), nullable=False),

    sa.Column(u'uid', sa.TEXT(), nullable=False),

    sa.Column(u'filename', sa.TEXT(), nullable=False),

    sa.Column(u'size', sa.INTEGER(), nullable=False),

    sa.ForeignKeyConstraint(['user_pk'], [u'user.pk'], name=u'file_user_pk_fkey'),

    sa.PrimaryKeyConstraint(u'pk', name=u'file_pkey')

    )

    ### end Alembic commands ###

[Michael Bayer]

On Mar 25, 2012, at 7:21 PM, John Anderson wrote:

> I have an existing app that I want to add some features to, so I created the new models and then ran --autogenerate and it created me a revision file where in downgrade it was creating all my files and in upgrade it was dropping the tables. That was backwards from what I expected, since my database didn't have the tables but the model did.
>
> Am I thinking about this in the wrong way?
>

the autogen you're illustrating here indicates that when you ran --autogenerate, the database had two tables already present - "presentation" and "file", and the MetaData object you passed to EnvironmentContext.configure contained no tables. Autogen then tries to generate the changes that would relate to making the database look just like the model you gave it - in this case removing those tables in the database which aren't in the model.

Not sure how you're describing the opposite setup producing this, that's not how autogenerate works.

[Michael Bayer]

so continuing, Alembic logs everything it does when it does autogenerate so inspecting that would give you the answer to what decisions it's making.

[John Anderson]

Ok, so here is my database:

 sontek@tacora$ (git::master~3) psql pcolalug

psql (9.1.3)

Digite «help» para obtener ayuda.

pcolalug=# \dt

             Listado de relaciones

 Esquema |       Nombre        | Tipo  | Dueño  

---------+---------------------+-------+--------

 public  | activation          | tabla | sontek

 public  | organization        | tabla | sontek

 public  | organization_member | tabla | sontek

 public  | presentation        | tabla | sontek

 public  | user                | tabla | sontek

 public  | user_group          | tabla | sontek

 public  | usergroupmember     | tabla | sontek

(7 filas)

You see there is no 'file' or 'presentations' table, but I have added those in my models:

class File(SUEntity):

    user_pk = Column(Integer, ForeignKey(User.pk), nullable=False)

    mimetype = Column(UnicodeText, nullable=False)

    uid = Column(UnicodeText, nullable=False)

    filename = Column(UnicodeText, nullable=False)

    size = Column(Integer, nullable=False)

    def public_url(self, request):

        if self.uid:

            return request.static_url(os.path.join(get_data_dir(), 'uploads/%s' % self.uid))

class Presentation(SUEntity):

    """

    A meeting we are having

    """

    name = Column(UnicodeText)

    description = Column(UnicodeText)

    date = Column(Date, nullable = False)

    presenter_pk = Column(Integer, ForeignKey(User.pk), nullable=False)

    presenter = relationship(User)

    file_pk = Column(Integer, ForeignKey(File.pk))

    file = relationship(File)

So now when alembic migration:

sontek@tacora$ (git::master) alembic revision --autogenerate -m "added presentation table"

INFO  [alembic.migration] Context impl PostgresqlImpl.

INFO  [alembic.migration] Will assume transactional DDL.

INFO  [alembic.autogenerate] Detected removed table u'presentation'

  Generating /home/sontek/.virtualenvs/pcolalug/src/pcolalug.com/alembic/versions/

  c390c2f027_added_presentation_t.py...done

(pcolalug)~/.virtualenvs/pcolalug/src/pcolalug.com

sontek@tacora$ (git::master) cat alembic/versions/c390c2f027_added_presentation_t.py 

"""added presentation table

Revision ID: c390c2f027

Revises: None

Create Date: 2012-03-26 11:22:24.972917

"""

# revision identifiers, used by Alembic.

revision = 'c390c2f027'

down_revision = None

from alembic import op

import sqlalchemy as sa

from sqlalchemy.dialects import postgresql

def upgrade():

    ### commands auto generated by Alembic - please adjust! ###

    op.drop_table(u'presentation')

    ### end Alembic commands ###

def downgrade():

    ### commands auto generated by Alembic - please adjust! ###

    op.create_table(u'presentation',

    sa.Column(u'pk', sa.INTEGER(), server_default='nextval('presentation_pk_seq'::regclass)', nullable=False),

    sa.Column(u'name', sa.TEXT(), nullable=True),

    sa.Column(u'description', sa.TEXT(), nullable=True),

    sa.Column(u'presenter', sa.TEXT(), nullable=True),

    sa.Column(u'date', postgresql.TIMESTAMP(), nullable=True),

    sa.PrimaryKeyConstraint(u'pk', name=u'presentation_pkey')

    )

    ### end Alembic commands ###

So it must be detecting that my base class doesn't have any models defined yet? So I just have to figure out why the metadata isn't setup just yet?

[John Anderson]

So it must be detecting that my base class doesn't have any models defined yet? So I just have to figure out why the metadata isn't setup just yet?

Inside env.py I did an import of my models and now everything works perfectly.