Database Migrations

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

Initial setup

• Run one line at a time

  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`"
  sqitch config --bool deploy.verify true
  

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 running

  sqitch deploy db:sqlite:prc.db --verify
  

  Deploy will automatically verify, so no need to run verify manually.

• cd back .. out of sqitch/ folder.

• Create new/updated result classes as:

  carton run script/prc_create.pl \
  model PRCDB DBIC::Schema PRC::Schema \
  create=static overwrite_modifications=1 \
  components=TimeStamp \
  rel_name_map="{repoes => \"repos\", \
  user_emails_opt_in => \"user_email_opt_ins\"}" \
  dbi:SQLite:sqitch/prc.db on_connect_do="PRAGMA foreign_keys = ON"
  

• Add changes selectively. Do not commit removal of set_on_update or set_on_create.

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 main (or wherever this new migration is).

• cd into sqitch/ folder (where prc.db is)

• Run

  sqitch deploy db:sqlite:prc.db --verify
  

Reverting last migration

This can be useful in dev environment. And can be used to verify your "revert" script works fine.

• cd into sqitch/ folder (where prc.db is)

• Run

  sqitch revert db:sqlite:prc.db --to=HEAD^1