Using Sqitch for automated database operations (not schema migrations) via GitHub Actions

[overdrive3000]

Hi everyone!

I’m exploring how to use Sqitch to manage database operations in an automated fashion, specifically by integrating it with GitHub Actions to handle the execution of Sqitch plans.

From what I understand in the docs and tutorials, the main purpose of Sqitch is to track all database changes in an Infrastructure-as-Code style, focusing primarily on schema migrations.

However, for my use case, I’m not looking to manage schema migrations, but rather to handle database operations (DBOps) such as:

• Installing PostgreSQL extensions
• Managing database users and roles
• Managing schema-level grants (e.g., creating read-only or admin users)

We’re currently using Terraform to provision our DB instances (managed by AWS Aurora), and we use Ansible for these operational tasks. But as our system grows, the Ansible approach is becoming more convoluted and harder to maintain. We came across Sqitch and it looks like a promising tool, but I’m not sure if it’s intended for this kind of use case.

Additionally, we have some databases with multiple schemas assigned to different applications. For example, an application that owns schemaA might need read-only access to schemaB. Ideally, we’d like to manage these permission scenarios using something like Sqitch as well.

A specific challenge I’m thinking about:
I’d like to define a single deploy step (e.g., create_readonly_user) that I can parameterize to create users dynamically. I was considering looping in GitHub Actions and calling this step multiple times—once per user, with parameters pulled from AWS Parameter Store.

But I’m not sure if this approach is compatible with how Sqitch tracks database changes. I worry that running the same deploy script multiple times with different parameters might break its change tracking or history.

👉 Has anyone here used Sqitch for database operations like these (beyond schema migrations)?
👉 Do you think Sqitch is a good fit for this kind of workflow?

I’d really appreciate hearing from anyone who has tackled similar problems—or if you think Sqitch isn’t the right tool for the job.

Thanks in advance!

[jc-cf]

No reply here from Sqitch users/experts? I'm also interested in this - in our case management of DB artifacts (SPs, functions, views) rather than/as well as migrations.

[rotten]

We manage the underlying (Aurora) database configuration with Terraform. When terraform runs, it triggers a lambda that does the DBA level work on the databases - managing users & extensions and other similar tasks that can only be done by the DBA. We've designed all of our lambdas so they can be run multiple times without issue, using clauses like "IF NOT EXISTS".

We use sqitch to manage the DBO - application owner - work, such as creating tables, functions, schemas, etc. We trigger sqitch much more often, every time there is an application deployment.

To facilitate the verify feature of sqitch, we have a suite of functions that sqitch uses to check permissions, columns, types, etc. I actually have the unit tests for the functions crafted in the sqitch verify for the verify functions.

Note that the application user does not typically run as the DBO, since it doesn't change schema, just data, or just read-only on the data. If we need the app user to do something like refresh a materialize view we usually use the SECURITY DEFINER option on a function. Sometimes we'll trigger those jobs with celery, and sometimes with pg_cron.

Sqitch isn't great at managing fixtures - reference data, but you can get it to work ok and even closely emulate something like Django's loaddata function.

It is also a little hard to evolve an object in the database and revert to a specific point in time. Sqitch has the mechanisms to do that, but it isn't super friendly for junior engineers to work though that process when they first encounter this system. So documentation and process need to be in place to make the best use of it within your organization.

I can see the temptation to do the DBA work with sqitch instead of a lambda. I'd probably have a separate sqitch implementation for that from the DBO one, and only trigger it when needed. Actually you can do docker images with lambdas now, so you could possibly trigger sqitch in a lambda. It isn't a bad idea, and then you can have a "verify" lambda and a "migrate" lambda and a "revert" lambda.