Ideally, I agree. Practically speaking, though, we came from a company where dozens and dozens of developers worked on the system and it was structured exactly this way (a master file and a series of incremental upgrade scripts). It was always getting messed up between the two sets of schema definitions until eventually we developed a schema comparison tool and all those problems seemed to vanish.
I'm obviously not saying SQLAlchemy needs to provide this, but just trying to make a case for its usefulness. Thanks for your input. On May 17, 3:34 pm, Michael Bayer <[email protected]> wrote: > there's a tool for Django called South that does this. But personally I > think writing a few lines of "alter_column()" is preferable to a heaping dose > of schema-guessing magic. > > On May 17, 2010, at 3:28 PM, Kent wrote: > > > > > 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 > > 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 > 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.
