CODEWITHSUNDEEP
gogoose
Malaka Gunawardhana
Malaka Gunawardhana

Reputation: 283

Rollback when doing a database migration with goose

I am using this goose flavor. I want my migration script to roll back when there is an error. Wrapping my statements within -- +goose StatementStart and -- +goose StatementEnd did not work for me.

-- +goose Up
-- SQL in section 'Up' is executed when this migration is applied
-- +goose StatementBegin
ALTER TABLE books
    ADD COLUMN author VARCHAR(10) NOT NULL AFTER name;

UPDATE books
SET author = created_by
WHERE created > '2021-01-05';
-- +goose StatementEnd

-- +goose Down
-- SQL section 'Down' is executed when this migration is rolled back
-- +goose StatementBegin
ALTER TABLE books
    DROP COLUMN author;
-- +goose StatementEnd

This results in Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE....

Is this possible with goose? Or do I need to write only a single query in one migration file?

Upvotes: 0

Views: 3153

Answers (1)

Malaka Gunawardhana
Malaka Gunawardhana

Reputation: 283

As per the MySQL 5.7 documentation, most DDL queries cannot be executed as transactions.

So this should be not possible to done with any workaround since it is tied to underlying database type and version.

@mh-cbon thank you you for pointing out that.

Upvotes: 0

Related Questions