[yangyang]

This looks interesting.

If you're using SQLAlchemy, Alembic is a good migration management / generation / application tool: http://alembic.zzzcomputing.com/en/latest/, and works with most (all?) the databases supported by SQLAlchemy. It doesn't handle every kind of database object / attribute thereof, but it's reasonably easily extensible.

[djrobstep]

Hi, migra author here. I wrote migra partly out of frustration with Alembic and similar migration tools.

Some of the problems:

- Annoying version number management

- Management of long "chain" of migration files

- Unnecessary coupling between ORM and migration tool

- Heavyweight process to generate and run each migration, which slows down local development

- Bad testability

I talked in more depth about this topic at last year's PostgresOpen, if you're interested: https://www.youtube.com/watch?v=xr498W8oMRo

[zzzeek]

Alembic / SQLAlchemy author here.

> Annoying version number management

the GUIDs? OK.

> Management of long "chain" of migration files

Alembic does not require migration files to be linked together in any particular way, except that individual migrations have dependencies on others. Each file represents an individual series of changes to a database, which is a reasonable system for developers where a particular new feature comes with a particular new set of schema artifacts. It integrates very well with version control and accommodates for workflows like branching and merging of source trees.

That said, I would assume the Migra way is that you have the entire database schema in a versioned file so you can diff directly, and the "generate the changes" aspect is on the fly (otherwise, if you were persisting the DDL for changes, I don't see how you'd avoid having a "chain" of migration files dependent on each other). That is more lightweight if you can get by with it. When you have your tables written out in terms of SQLAlchemy metadata, that's your "schema". So it's there but just in terms of SQLAlchemy structures. Alembic could generate the migrations and just run them without you ever having to see them or commit, however I can't provide a diff tool that I can guarantee is 100% accurate and would not need its results to be checked before running. By having the individual changeset go into source control, that allows the developer who creates that migration to be the one to make sure it generates correctly, as well as being able to add other elements of the change that aren't necessarily part of a plain schema diff, where it then can be committed permanently without the chance of it mis-generating at a later time.

> Unnecessary coupling between ORM and migration tool

100% false. There is no usage of SQLAlchemy ORM within Alembic anywhere. The "sqlalchemy.orm" library is not imported. There's a single unused artifact in the test suite that was copied over from SQLAlchemy:

    $ grep -e "sqlalchemy.orm" `find alembic -name "*.py"`
    alembic/testing/plugin/plugin_base.py:  from sqlalchemy.orm.util import randomize_unitofwork

You're likely referring to the fact that Alembic builds on schema structures defined in terms of SQLAlchemy Core. It's hard to say this is "necessary" or "unnecessary", the schema has to be defined somewhere, with Migra it seems like it's typically hand-written DDL or some other generation tool, with Alembic it's SQLAlchemy Core, since it's for people using SQLAlchemy already.

> Heavyweight process to generate and run each migration, which slows down local development

Alembic has an autogenerate feature that is in some ways similar to migra's schema diff but it does have areas where it does not accommodate for certain structures, the most prominent one is regarding Postgresql enumerated types. But this is not a design flaw, it's a missing feature I'd love someone to work on.

There is also of course the need to express your schema in terms of Python structures, which is probably what you're referring towards when you say "coupled with the ORM" as well as "heavyweight", but this assumes an application is already based on SQLAlchemy and already has this information.

If not, then Migra is a good choice, assuming database agnosticism is also not required, and if you have some totally other means of generating your schema, which I'd imagine is hand-written DDL, if I'm understanding the documentation correctly.

> Bad testability

This is an area where Alembic is lacking in guidance and pre-built features, but not in general capability. We have Alembic migration test fixtures in Openstack that might serve as a basis for something more generically part of Alembic but I don't have the resources to work on this solo. But Alembic does include schema comparison tools and the examples for testing in Migra don't look much different from what one would do with Alembic (indeed this is what we do in openstack, compare schemas using Alembic).

[nathancahill]

Just wanted to say thanks for the awesome work you've done with those projects. I've been using the Flask/SQLAlchemy/Alembic stack to run many, many projects over the last 4 or 5 years.

[djrobstep]

Hey, thanks for the response!

I've written a lot of code on top of SQLAlchemy over the years, big fan of your work.

These points are mostly philosophical differences, I appreciate that not everybody has the same perspective I do.

But to me, thinking of database changes in terms of current state/goal state and the differences between them, is more intuitive than as a sequence of versions.

By heavyweight, I mean the need to generate or modify a migration file and version each time a change is required. When I'm working on an app and playing around with table structures I find it much easier to be able to just type "sync" and have everything automatically match what's defined in my models or setup scripts or whatever, without generating a new numbered version each time I make a change.

You don't have to build your schemas through hand-written DDL. You can still build your schemas through Python structures or however you want, as long as you can generate a target with it to compare against.

[zzzeek]

yes I can definitely see Migra is more productive when switching around between schemas in development. But I'm not sure how I'd go about using this approach if i need to deploy a fixed series of schema migrations to a production server farm.

[njharman]

> Alembic is a good migration ... tool

Having used it for 2 years that is not the adjective I would apply. I infrequently go looking for a better solution. But, it's a hard problem to solve, so...

[yangyang]

I've used it for about that long, with no real problems at all. We have probably a hundred or so migrations, and have added support for various things not supported out of the box (triggers, functions, exclusion constraints and a few more), and it's been very solid.

What kind of situation are you using it in where it falls short?