There are better ways than this, especially in Ruby on Rails, but here's a manual idea for managing database changes during development.

  • All database change scripts be put in _db directory
  • Three kinds of scripts:
    • create_db.sql, which drops all existing database objects and creates the original schema.
    • alter_db_yyyymmddThhmmss.sql, which changes the schema.
    • update_db_yyyymmddThhmmss.sql, which changes the data.
  • With this method, the entire schema can be recreated by running the scripts in order. (update_db is obviously a problem.)
  • Scripts should run within transactions.