Database Migrations - Pull-Request-Club/PRC GitHub Wiki

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