Rollback-sql not working?

General Discussion
1

Hi, I have created a sql changeset file and added --rollback statements. I have deployed the changeset, but the rollback fails with :

Liquibase does not support automatic rollback generation for raw sql changes

My changeset sql file looks like this :

ALTER TABLE public.payments ADD CONSTRAINT payments_account_year_month_unique UNIQUE (account_id, calendar_year, calendar_month);
--rollback ALTER TABLE public.payments DROP CONSTRAINT payments_account_year_month_unique;

ALTER TABLE public.sales ADD COLUMN inserted_at timestamptz(0) not null DEFAULT now();
--rollback ALTER TABLE public.sales DROP COLUMN IF EXISTS inserted_at;

ALTER TABLE public.payments ADD COLUMN inserted_at timestamptz(0) not null DEFAULT now();
--rollback ALTER TABLE public.payments DROP COLUMN IF EXISTS inserted_at;

The command I am using is this :

liquibase --changelog-file db-migration/project/root.changelog.yaml --username "my-user" --password "my-pass" --url jdbc:postgresql://mypostgresdb.cloudsql.venatusmedia.com:5432/prosper rollback-sql --tag f23bb195 --log-level info

It looks like it is simply not picking up the --rolback statements. What am I doing wrong?

2

Assuming that is the entirety of your .sql, it needs to be “liquibase formatted sql” , otherwise the --rollback lines are just regular sql comment lines.

--liquibase formatted sql

--changeset my-user:alter-tables
ALTER TABLE public.payments ADD CONSTRAINT payments_account_year_month_unique UNIQUE (account_id, calendar_year, calendar_month);
--rollback ALTER TABLE public.payments DROP CONSTRAINT payments_account_year_month_unique;

ALTER TABLE public.sales ADD COLUMN inserted_at timestamptz(0) not null DEFAULT now();
--rollback ALTER TABLE public.sales DROP COLUMN IF EXISTS inserted_at;

ALTER TABLE public.payments ADD COLUMN inserted_at timestamptz(0) not null DEFAULT now();
--rollback ALTER TABLE public.payments DROP COLUMN IF EXISTS inserted_at;

An you also need to include this file in your root.changelog.yaml using the “include” tag, not sqlFile:

databaseChangeLog:  
  - include:
      file: db-changelog1.sql
      relativeToChangelogFile: true