Versioning Objects in a DB and Replaying Transactions with SQLAlchemy

Question

Ok, I have the following problem.

I'd like to use SQLAlchemy to version objects in my DB. I understand how to do this by, eg, creating my own mixin. Changes will include inserts and updates, but not schema changes.

I'd also like to promote my DB to a QA/Production server by replaying those changes, a la a git merge.

The easiest way to do this is to add a column to the objects in the DB with a version number/timestamp, and write a SELECT MAX type query. But I was talking to a dev friend, and Ruby seems to have something (ActiveRecord?) that generates update scripts that you can keep in git. Then promoting the DB is just a matter of running those scripts.

I found SQLAlchemy Migrate, which versions the schema (which I don't expect to change). But it's not clear that that versions the data (unless I'm missing something).

At any rate, this seems like a problem people have a good way of solving, any hints?

(I'm not wedded to any particular technology, but we have a good understanding of Python, and it'd have to be pretty compelling to do this using something else. I chose SQLAlchemy in the example above because it's the Python ORM with which I am most familiar.)

Answer 1

In order to avoid large amount of copy/paste, please see the following:

• Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python. (Written by the author of SQLAlchemy)
• and following stackoverflow questions related to it:
  • Creating seed data in a flask-migrate or alembic migration
  • How do I execute inserts and updates in an Alembic upgrade script?
  • where your implementation would most probably use also bulk_insert() method

This might not be as automated as you wish it to be, but it will support migration of static data.