Sqitch - single plan row, multiple sql files

Question

I'm looking to move to sqitch, but my team likes their migrations as multiple files. That is, for example, if we create a foreign key and create an index in the same jira ticket, we like PROJ-123-create-fk.sql and PROJ-123-create-index.sql.

I'd like to keep a single row in the sqitch.plan file, so that each jira ticket corresponds to a single row.

Basically, short of adding lines to sqitch.plan, can I do this? Is there a way to "include" other sql files from a master one? Something like

PROJ-123-main.sql

\include PROJ-123-create-fk.sql
\include PROJ-123-create-index.sql

Thanks so much!

Answer 1

The \ir psql directive solved this for me.

in PROJ-123-deploy.sql

\ir PROJ-123-create-fk.sql
\ir PROJ-123-create-index.sql

If the fk and index sql files are in the same directory, they will be executed.

Answer 2

Since you want to store it as a single line in the plan file, it doesn't quite match, but I will explain the method we use below. Maybe there are parts that you want to use in it.

There is a similar situation in my team, but we use sqitch tags. Each version of our application corresponds to a subtask. And each task corresponds to a tag. We create sql files as many as the number of subtasks. Then we combine them in a tag that we created with the name of the main task. In our CI/CD pipeline that we use for the database, we also provide the transition between versions with tags. I wanted to add this method here in case anyone prefers to use a similar structure.

---

Simple example;

Let's have v2.0 of our application installed and a new table and an index are required for v2.1

We create two subtasks named create table and create index under the main task named v2.1

We create two sql files; app_v2.1_table_create.sql to create a table, app_v2.1_index_create.sql to create an index.

After that, we create a sqitch tag called v2.1. Notice it has the same name as the main task.