Database Migrations

We are going to use Sqitch for database migrations. See this link for a quick tutorial.

Initial setup

• Run

  sqitch config --user engine.sqlite.client which sqlite3 sqitch config --user user.name "git config user.name" sqitch config --user user.email "git config user.email"

Note that these will be stored in your ~/.sqitch/sqitch.conf. Your name and email will be publicly visible in sqitch.plan of our repository when you submit a migration.

Adding a new migration

• cd into sqitch/ folder

• Run following

  sqitch add explanatory-name -n "A longer explanation"

Replace explanatory-name with something short that explains what this migration is about, and A longer explanation with whatever comment you want to add.

• Put your deploy query to deploy file between BEGIN and COMMIT.
• Put your revert query to revert file between BEGIN and COMMIT.
• Put your verify query to verify file between BEGIN and ROLLBACK.
• Deploy sqitch on your prc.db with sqitch deploy db:sqlite:prc.db.
• Verify sqitch on your prc.db with sqitch verify db:sqlite:new.db.
• Test reverting all sqitch on a copy of your prc.db with sqitch revert db:sqlite:prc_copy.db.
• If all went well, get rid of prc_copy.db and keep going with prc.db with latest migration deployed.

Deploying a migration

This will be needed when someone else submit a database migration, and you need to update your existing database.

• Go to latest master (or wherever this new migration is).
• Run sqitch deploy db:sqlite:prc.db.