I'm working on an application, which when deployed is installed locally on client machines, and uses a local database server. Stupidly, I didn't plan for schema changes -- and now it needs to change.
Perusing lots of SO and Programmers questions, and googling, I've found a few strategies:
- many SQL files. One for each version. When upgrading, use version number to decide which SQL file(s) to execute
- one SQL file. No
drop-statements, tables created with `create table if not exists -- so when upgrading or installing the first time, simply run this file. Everything already in the db will be fine, all new tables etc. will be created
- blow away, then rebuild db.
- make a copy of all the data
- drop the entire schema
- load in the new schema
- reload the data
- auto-generated diff file. Seems to be mistrusted by some; tool for generating such a file costs money (??)
I'm looking for advice to help decide between these strategies.
An additional note: the data is upwards-compatible (if that's the right term): none of the schema changes involve dropping tables or columns, or changing FK/PK relationships.