4

This question is specific to Sqitch, but may have relevance with other database migration management tools.

The first Sqitch change in my application defines its general schema: it creates all my tables tables and defines their columns.

Let's say that first change is named appschema, and that one of the tables it creates is lessons with columns chapter, section, and title.

Now, a couple already-deployed changes later, I want to drop the title column from the lessons table.

I run sqitch add move_lesson_titles_out_of_db and write that change's deploy script as

ALTER TABLE lessons DROP COLUMN title;

I sqitch deploy that to my local dev target and it works as expected.

But now, sqitch verify dev fails, because the appschema change's verify script looks to confirm the title column exists. Up until now I'd been able to run sqitch verify dev and all previous changes would pass. Perhaps it's my misunderstanding, but I'd been under the impression that all changes should continue to verify when run in order on properly-deployed and up-to-date Sqitch target.

I could sqitch rework appschema instead of adding a change, but Sqitch documentation is very clear that both the original and reworked change must be idempotent, and since appschema contains a bunch of CREATE TABLEs, it is already not idempotent. Furthermore, if I understand sqitch correctly, to deploy this reworked appschema would revert all my changes (since it is the first change in my Plan), back to an empty database, then replay everything back.

I would end up with the correct schema, but at the cost of erasing all data. Obviously, not something I plan to do on my production target.

Is there a better method to add a change which drops a column, without dooming sqitch verify to fail on the earlier change which created that column? Or is this verification failure by design?

If by design, is there any way I can still benefit from verifying the many other things the appschema change does that haven't subsequently changed (namely, the defining the entire rest of my app's schema)?

Jacob Ford
  • 4,553
  • 5
  • 27
  • 42
  • One general way to avoid such problems is to split your initial schema migration into separate small migrations per table and per change. This will provide you greater flexibility in doing iterative changes that come with their own small chunks of the verify script. The verify script will be accurate for each of the iterations instead one verify script for the whole schema. – kachar Nov 15 '20 at 21:41
  • @kachar but how would that help? At some point the column has to be added, and a verify will check that it is there, and then later when it is remove that verify will still fail... – singpolyma Mar 01 '21 at 00:43
  • @singpolyma each migration has their own verify sql script, so at every given point of the verify is correct. When we add new column we add new verify script to verify it. When we drop column we add verify script to verify the lack of the column. – kachar Mar 02 '21 at 12:22
  • 1
    @kachar yes, but we also run all the verify scripts against the whole database (when using `sqtich verify`) so if one checks that the column is there and one checks that it is gone that will fail, which is the question being asked here IIUC. – singpolyma Mar 02 '21 at 16:04

0 Answers0