using alembic with already existing databases

[André Dieb]

Hello,

I'm trying to start using alembic for future migrations, replacing
sqlalchemy-migrate.

My database is already very populated and I tried to follow the
Tutorial steps, starting by initing it, then configuring env.py to
autogenerate revisions.

My problem is: when I run revision --autogenerate, it detects all my
tables as deleted and in the end detects some changes correctly. How
do I tell alembic my tables already exist?

Thanks in advance

[Michael Bayer]

Hi André -

Key to the "autogenerate" approach is that the MetaData object you pass to Alembic contains in it all the information about the database being reflected. So if the database has tables A, B, and C, the MetaData would need to also have tables named A, B and C, otherwise Alembic autogen would consider those tables to have been removed.

A straightforward way to do this would be to take the MetaData object from your application, after all the Table metadata is present in it for your app. Then add to it the information about existing tables using reflect():

# Load the existing schema
meta.reflect(engine)

What that will do is for every table in your database that isn't already present in the MetaData, a representation will be loaded.

A more specific approach would be to reflect only those tables that are in the existing DB that aren't part of your newer model:

meta.reflect(engine, only=["A", "B", C"])

the above process will effectively load the state of tables A, B, and C as it is, which will prevent them from being considered for change.

Caveats here include that meta.reflect() can be time consuming if the database has many tables. Alembic may at some point include an option such as one to disable "drops" from being invoked with autogenerate, or provide a way to specify which collections of tablenames are up for autogenerate detection and which are not, probably a callable. I've added issue #27 for this which is a very simple feature add. https://bitbucket.org/zzzeek/alembic/issue/27/provide-a-callable-to-autogenerate-that

[André Dieb]

Hey Michael,

This is awesome. Thank you for taking the time to explain it! I love when project's creator are passionate about their projects.

I just used the reflect() and it worked just nicely. The tables are not detected as deleted anymore and the migrations seem okay.

Only one thing I noticed was that alembic was detecting alembic table itself

INFO  [alembic.autogenerate] Detected added table u'alembic_version'

I thought about adding the class to my meta, but it sounded so hacky I'd rather ask. Is there any other better way to ignore the alembic_version table?

It seems the table was automatically created sometime between when I init'd the project and created my first migration.

Thank you.

--
@andredieb

[Michael Bayer]

On Feb 3, 2012, at 11:41 PM, André Dieb wrote:

Hey Michael,

This is awesome. Thank you for taking the time to explain it! I love when project's creator are passionate about their projects.

I just used the reflect() and it worked just nicely. The tables are not detected as deleted anymore and the migrations seem okay.

Only one thing I noticed was that alembic was detecting alembic table itself

INFO  [alembic.autogenerate] Detected added table u'alembic_version'

I thought about adding the class to my meta, but it sounded so hacky I'd rather ask. Is there any other better way to ignore the alembic_version table?

It seems the table was automatically created sometime between when I init'd the project and created my first migration.

yeah that would be a bug in that it's an unexpected side effect of the approach I gave you, added #28 for this.

[nicola loddo]

Hy. I don't now if this is the correct post for do this question, but I have a problem like this. 

Until today I used alembic in a project and had worked perfectly, the db contain tables created previously than aren't in the models object of my project, but whenever I do a migration with autogenerate, alembic donìt detect them.

But today I do a migration and in the upgrade Alembic try to dropped every table not reflected in my models. I don't understand why  

[Mike Bayer]

well if you want alembic to ignore certain tables that are otherwise
not mentioned anywhere, you set up an include_object function that
filters out what it should look at and what it doesn't:

http://alembic.readthedocs.org/en/latest/api.html#alembic.environment.EnvironmentContext.configure.params.include_object

that's if these tables aren't represented in the MetaData / declarative
Base that you are setting up for "target_metadata". If we're
talking here about tables/classes that are part of your model, then
you'd need to make sure they're part of that target_metadata.

The fact that these tables are suddenly showing up though indicates
that something changed, maybe you have a bunch of model classes that
are suddenly not being imported or something.



>
> Thanks in advance
>
> --
> 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
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.