Re: Updates/Changes to a database

On Jul 12, 2007, at 10:07 AM, imageguy wrote:

> I am building an application with Postrges as the backend foundation.
> This is my first application and it has struck me that as we add
> features/functionality to the application and database with each new
> version, we will need some method of obtaining the current structure
> of the customers database and then modifying/updating the structure so
> that it matches the application revision standard.
>
> Are there pre-existing tools out there that does this sort of thing ??
>
> My present direction is to create a small SQLite db that has there
> expected structure, compare each table against the SQL
> "information_Schema.columns" and the create a series of SQL commands
> to be executed that would add columns and/or table as needed.
>
> -- any thoughts or comments ?

I tend to keep a schema version field (typically in a one-row, one-
column
table) in the database.

Then I have a set of SQL scripts that'll upgrade from version n to
version n+1, and they can be applied manually or automatically
in sequence to bring the schema version up to the version
required by the application.

That's pretty common amongst big database backed apps, and
if you're nice you also provide downgrade scripts to back out
revisions.

Maintaining the SQL patch scripts by hand isn't too hard to do,
but I've found these tools useful too:
http://dbmstools.sourceforge.net/

Cheers,
Steve