4

Is it possible to revert a specific change if there are no dependencies in sqitch? For example, I set up my project like the code below and deploy it and load some data. A day or so later (or perhaps the same day), a stakeholder decides that I need to add some more columns to fct_tickets or make another change to that table.

If I try to revert fct_tickets, it will revert all subsequent tables which is a shame because I have loaded data to them already.

I have tried certain flags (--upon, --unto, etc.), but it still wants to revert everything after fct_tickets in my sqitch.plan file.

sqitch add scm_example --template pg_create_schema -s schema=example -n 'Create schema for Example data.'
sqitch add fct_tickets --requires scm_example -n 'Create table for ticket data.'
sqitch add fct_chats --requires scm_example -n 'Create table for chat data.'
sqitch add fct_calls --requires scm_example -n 'Create table for call data.'
sqitch add dim_users --requires scm_example -n 'Create table for user mapping data.'
sqitch add dim_source_files --requires scm_example -n 'Create table to track all files downloaded from the SFTP.'

I could alter the table and add columns, but when it is a fresh day 1 project, it is nice to have a clean slate.

It is no big deal - I am just wondering if I am missing something simple since fct_tickets has no dependencies.

ldacey
  • 518
  • 8
  • 16

1 Answers1

7

No, you cannot revert a single change other than the most recently-deployed change. This is by design. Sqitch uses a Merkle tree pattern similar to Git and Blockchain to ensure deployment integrity. This means that deployment is a linked chain in the order specified in your plan file. If you have deployed your Sqitch project to an environment in which data has been loaded, you're better off adding a new change to add the new column.

A pattern I often follow when doing database development prior to a production release is to rebase changes often. That means changing the fct_tickets deploy script to add the new column, then rebating on fct_tickets^, which will revert all changes after the change just before fct_tickets, and then redeploy them all. I avoid loading data in such systems as part of the development process, instead of either keeping data that are essential to the data model in a separate change, or else in a separate file that gets loaded independently, say unit test fixtures.

If you have a test system or something that other folks have added data to, and it's not a final tagged release, then your best option is probably to dump the fct_tickets table to a file, rebase with the change, then reload the data from that file. Be sure to set a default on the column, or else modify the dump file to add the data in each row before loading it into the revamped table.

Arsen Khachaturyan
  • 7,904
  • 4
  • 42
  • 42
theory
  • 9,178
  • 10
  • 59
  • 129
  • 1
    Thanks for the details. I made some separate 'sqitch add' entries to add the extra columns but I will keep the rebase method in mind. Thanks for the response and for the tool - it is great. I had just started to use Alembic before finding sqitch but I prefer just using pure SQL for migrations. – ldacey May 08 '19 at 11:20