sqitch: deploying changes across multiple environments

Question

In looking at the sqitch docs, there’s a situation I don’t immediately understand how to deal with.

Like probably many organizations, we progress changes through several environments before they reach production. In our situation, we have a different DBA user on a different Oracle server for each environment, each with its own credentials.

As I understand it, sqitch uses database tables to track what changes have been applied to a server. Maybe I’m dumb, but it just doesn’t jump out at me how sqitch can tell me if a change has been applied to a UAT server, but not yet to a production server.

So basically, I’d like to organize a repository to move changes from one DB environment to the next. Might this be what “sqitch target” and plan files are for? Are there examples I can look at?

Answer 1

If I were you, I would create a centralized DB with DB links to points to each database. After that, I would create an Union of all Repositories and a View (with PIVOT function) to see the deployement path of each patch.

Answer 2

To deploy to multiple environments it would depend on how you're running your sqitch deploy command.

You use the sqitch.conf to declare various targets.

eg.

[core]
    engine = oracle
    top_dir = SQL

[engine "snowflake"]
    reworked_dir = SQL/rework

[target "DEV"]
    uri = "db:oracle:DEV_DB"

[target "QA"]
    uri = "db:oracle:QA_DB"

[target "PROD"]
    uri = "db:oracle:PROD_DB"

With that sqitch.conf setup you can now run deploy to target the required environments.

eg.

sqitch deploy --target DEV
sqitch deploy --target QA
sqitch deploy --target PROD

You won't be able to compare deployments from one environment to another unfortunately.

You can use the sqitch check --target <xxx> command to check for divergences between the planned and deployed changes as stated here: https://sqitch.org/docs/manual/sqitch-check/.

However I've found this to not work properly at times. I haven't been able to determine the exact cause as yet but you're welcome to run the command to check.