Variables in Sqitch

Sqitch supports variables in the config file like this:

[target "foo"]
    engine = pg
    uri = your-connection-string-here
    registry = sqitch_db_foo

[target.foo.variables]
    database = foo

This makes a variable called database available in the deployment script. I use PostgreSQL, and Sqitch uses psql to deploy. So the variables are available as psql variables in the scripts. What can we do with these variables?

One of my projects uses the same Sqitch configuration for multiple database deployments on the same PostgreSQL instance. Assuming that the database exists, the settings in the config above for registry and database are all the scripts need to function correctly across multiple databases.

Setting registry to a database-specific value prevents Sqitch from mixing up deployments belonging to different databases.

Here’s a deploy file that uses the database variable to create database-specific roles:

BEGIN;

\set ON_ERROR_STOP off

\set _admin_role '_admin_' :database
\set _admin_user 'admin_' :database

\set _app_role '_app_' :database
\set _app_user 'app_' :database


CREATE ROLE :"_admin_role" WITH NOLOGIN;
CREATE ROLE :"_admin_user" WITH LOGIN INHERIT;

CREATE ROLE :"_app_role" WITH NOLOGIN;
CREATE ROLE :"_app_user" WITH LOGIN INHERIT;

GRANT :"_admin_role" TO :"_admin_user";
GRANT :"_app_role" TO :"_app_user";

\unset _admin_role
\unset _admin_user

\unset _app_role
\unset _app_user

\set ON_ERROR_STOP on

COMMIT;

Disabling ON_ERROR_STOP prevents the CREATE ROLE calls from halting the process if the role already exists. Using Sqitch variables makes reusing deploy scripts across databases surprisingly easy!