best practices re: keeping schema declarations up-to-date

[Hans Lellelid]

Hi -

I'm working to incorporate sqlalchemy-migrate into an existing
sqlalchemy-based project. I've read the online usage guide, and I
think I understand the basics; however, I would like a little
clarification on the recommended practice for storing table metadata.

For this current project, we use SA to create the db objects and, as
such, have a model/__init__.py which contains both the object
declarations, the schema statements, and the binding between the two.
I rely on the schema stuff being up-to-date, since we have other code
that will want to look at a table definition at runtime to dynamically
build other data structures.

This seems to conflict with the recommended system for using SA-
migrate where my base schema is really only version 0 of the schema,
and then I have to apply my upgrade scripts to that. That works fine
for the DDL, but does not work for keeping my schema classes up-to-
date. What is the recommended approach to deal with this? I'm sure
I'm not the only person out there that needs an up-to-date
programmatic model representation, but wants to also use SA-migrate.

(Currently my approach is to always update the schema declarations in
model/__init__.py and set the initial db version to the current
migrate version when the database is created. This seems to work so
far; however, running the changeset test fails.)

Thanks,
Hans

[Domen Kožar]

Hello,

the preffered way is to separate application from migrations as much
as possible.

This is really a topic that is troublesome for new users since it
makes you duplicate code. You don't have to store your whole schema to
version 0. When you need to change DB schema, copy all the tables that
will be changed to upgrade script, modify them in upgrade/downgrade
functions and update models in application to latest state.

In most cases, all you need to do is:

# all imports

users = Table('users', metadata, autoload=True)
new_col = Column('id', integer)

upgrade(migrate_engine):
users.create_column(new_col)

downgrade(migrate_engine):
users.drop_column(new_col)

# end

Of course, you can find your solution that fits your needs and share
it with migrate users:)

d.

[Hans Lellelid]

Hi Domen,

Ok, great. I think that's what I'm doing now. I was confused because when
I tried to run:

python path/to/my/migrate.py test

it attempted to apply my column change, even though the DB was already at
the right version. I thought, therefore, that maybe I was doing something
wrong.

I don't mind the duplicate code; that makes sense to me.

Thanks for the help. I'm sure I'll have other questions -- and hopefully
some tips for other new users eventually too. :)

Hans

On Mon, 6 Jul 2009 09:33:02 -0700 (PDT), Domen Kožar <iEle...@gmail.com>
wrote: