51

I have a web application using sqlalchemy (within Pylons). I need to effiently change the schema to be able to change the production version at least on a daily basis, maybe more, without losing the data.

I have played a little bit with sqlalchemy-migrate over the week-end and I would say that it gave me a bad impression. First I think it cannot help with migration between two databases engines; that's something that could probably be done with sqlalchemy alone. Second the docs do not seem up to date. I had to change some command-line options, like giving the repository path at each command, this could be a bug of migrate.

But the worst thing it the "manage.py test" command. Not only it actually modifies the database (this point is clearly indicated in the documentation so I can't blame migrate) but my first migration script just made plain stupid schema migration, leaving the upgraded-downgraded db with a different schema than the original. But the "manage.py test" just answered something like

 success !

That is, it did not even check if the schema was left in a coherent state. So is it worth using migrate? Is there any advantage compared to the Do It Yourself method associated with good practices as proposed by S.Lott ? Are there alternatives to sqlalchemy-migrate actually simplifying the migration process or am I just trying to use migrate with a bad a priori (then please show me why is't clearly superior to creating CSV columns as proposed in the link above)?

Many Thanks!

Community
  • 1
  • 1
ascobol
  • 7,554
  • 7
  • 49
  • 70

3 Answers3

80

Use Alembic instead:

http://pypi.python.org/pypi/alembic

Thanks for comments, edited to add some reasoning --

It's developed by the author of SQLAlchemy, and it's brand new and well supported. I don't know enough about sqlalchemy-migrate to give a good comparison. But I took a quick read through the clear and concise Alembic docs, then got my own autogenerated migration working in a very short time.

Autogeneration: Not its only mode of operation, but if you choose, Alembic will read your application's sqlalchemy configuration (for instance, your declarative model classes that set up all your tables, constraints, and mappings) and compare to the actual current state of your database, and output a Python script that represents the delta between the two. You then pass that script to Alembic's upgrade command and there you go, the differences are resolved. A small amount of editing the migration script by hand is usually needed, and that's (a) just the nature of migrations, and (b) something you want to do anyway to make sure you were fully aware of the exact steps that the migration is going to perform before you run it.

Alembic brings a DVCS-like ability to the way your migrations are tracked, too. It makes it really easy to return to any past state of your db schema.

cdaddr
  • 1,330
  • 10
  • 9
  • 2
    I heard Alembic first time, so checked out the link. Wow it's written by Mike Bayer -- the author of SQLAlchemy! I will definitely try it out. – Kenji Noguchi Jun 07 '12 at 02:01
  • If you use Flask I recommend to use Alembic together with Flask-Migrate which makes everything a lot easier. http://flask-migrate.readthedocs.org – fnkr Oct 07 '14 at 11:02
  • Followed your advice and switched, and now [I have a question](http://stackoverflow.com/q/27911685/656912). – orome Jan 12 '15 at 22:21
  • The only way alembic will be perfect is when they enforce specific best practices to follow when creating your db, choosing an engine etc. Other than that just go for no -sql databases – Patrick Mutuku Aug 07 '18 at 21:13
8

Alembic being out ( http://pypi.python.org/pypi/alembic ) and maintained by SQLAlchemy author and given the fact that sqlalchemy-migrate development looks stalled, with practically no commits this year ( http://code.google.com/p/sqlalchemy-migrate/source/list ), I think it's not worth using it anymore, I'll switch my current project to Alembic.

If it was still heavily maintained, I would be confident on the ability of the project to keep synchronised with SQLAlchemy ( Which was the case before ).

Kel Solaar
  • 3,660
  • 1
  • 23
  • 29
3

I personally love using it. It's awesome because new installs (dev, test, prod) can be bootstrapped very easily. Not only that, but it provides a home for the app as it grows and provides good entry points for those migrations that need to take place as you move from version to version of your application. Something needs to perform the alter/etc on dev, testing, and production servers.

Is it perfect? Nope. You can leave your db in a bad state, but that's why you have dev/testing/production versions of things.

Personally I use it to bootstrap my unit tests in pylons using an sqlite db for running unit tests against, but we use mysql in production. So there are some cross db platform advatages of using it.

Rick
  • 15,484
  • 5
  • 25
  • 29