We will definitely also need a migration tool. We've only briefly looked into the sqlalchemy-migrate tool, but were immediately disappointed in its apparent requirement to keep versions of the schema.
In our book, we see the ideal tool as one that doesn't care about versions: it just looks at the current database schema versus the schema loaded into sqlalchemy and decides what DDL statements are needed to bring the current schema inline with what has been loaded into python. We understand there are dangers/limitations to this approach, but in 95% of the cases, this is all you need or want. There is an 'upgrade_db_from_model' which maybe does exactly this, but our understanding is lacking. The whole point is to avoid needing to define a single change in two places. For example, if I want to add a column to a table and I require the programmer to remember to place this change in both the 'master' tables.py file *as well as* an incremental schema version file, I am only asking for trouble that sooner or later someone (or I) will miss one of the two and end in problems. Are there any tools that do this (reflect and introspect to create dynamic DDL upgrade statements)? On May 17, 1:50 pm, Michael Bayer <[email protected]> wrote: > On May 17, 2010, at 1:13 PM, Chris Withers wrote: > > > Hi All, > > > I want our production systems to start caring about the versions of their > > schemas. sqlalchemy-migrate was the first project I came across which > > addresses this. What other projects should I look at in this area, or is > > sqlalchemy-migrate the obvious choice? > > > If it is, how can I, in my application, check what version of the schema > > the current database is? (the idea being that the app will refuse to start > > unless the schema version is that expected by the app) > > > Related: how can I short circuit this when the database is empty and bump > > the schema version up to the latest for the app? (ie: dev instances, where > > the process is to blow the db away often so no need for migration) > > sqlalchemy-migrate is the obvious choice. I am also developing a > micro-migrations system called Alembic (http://bitbucket.org/zzzeek/alembic) > in conjunction with my current work project but not all features have been > implemented yet - its expected that I'll be getting them in a more polished > state in the coming months. The Migrate project can of course steal any and > all desireable features and code from Alembic freely as I would like > sqlalchemy-migrate to remain the default choice. > > as for the related issue, I think its best that your setup provide a > "setup-app" type of command which generates an initial schema, and embeds the > current migrate version number. Here's a snip of a related Pylons > websetup.py: > > from migrate.versioning.api import version_control, version, upgrade > from migrate.versioning.exceptions import DatabaseAlreadyControlledError > > # Create the tables if they aren't there already > meta.Base.metadata.create_all(bind=meta.engine, checkfirst=True) > > # setup migrate versioning table if not present > try: > latest_version = version("migrate") > version_control(pylons.config['sqlalchemy.url'], "migrate", > version=latest_version, echo=True) > except DatabaseAlreadyControlledError: > log.info("migrate table already present") > > # do any migrate upgrades pending... > upgrade(pylons.config['sqlalchemy.url'], "migrate", > version=latest_version, echo=True) > > The migrations system should only be used for changes to an existing schema. > Other "check this before running" types of functionality can be accomplished > similarly. > > As a side note, I also have an elaborate monkeypatch to migrate to get it to > work with transactional DDL. Simple support for transactional DDL is one > of the goals of the new tool I am writing. If you're not on Postgresql or > MS-SQL, then you can't use transactional DDL anyway. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
