How to properly implement versioned migration in microservices?

Question

PostgreSQL

There is a following task.
There are services that use a set of common custom starter modules .
This starter is a set of libraries, one of which is Camunda BPMN . And it's important to understand that the engine of this Camunda has its own base, that is, each service that uses Camunda's capabilities within itself must pull up the dependency. And each such service has its own base, on which the engine rolls service plates and other things.
The following thing happens: the engine in this common starter is updated, which means that each service is implicitly subject to changes, since the update entails some DDL / DML SQL changes, they are even published. So, you cannot allow data loss when updating the engine. By default, each such service has a versioned migration through liquibase .
My idea is to write general functionality inside the starter that will be available out of the box. And in this case, you need to migrate by writing a script. Each service, pulling up this starter, will also execute this script. And here it is important to say that you need to prevent errors from creeping out, you can’t migrate the Nth number of times, so I decided to use preconditions and got something like this:

--preconditions onFail:CONTINUE onError:CONTINUE
ALTER TABLE ACT_HI_OP_LOG
    ADD CATEGORY_ VARCHAR(64);

--preconditions onFail:CONTINUE onError:CONTINUE
ALTER TABLE ACT_HI_OP_LOG
    ADD EXTERNAL_TASK_ID_ VARCHAR(64);

--preconditions onFail:CONTINUE onError:CONTINUE
CREATE TABLE ACT_GE_SCHEMA_LOG
(
    ID_        VARCHAR(64),
    TIMESTAMP_ timestamp,
    VERSION_   VARCHAR(255),
    PRIMARY KEY (ID_)
);

--preconditions onFail:CONTINUE onError:CONTINUE
INSERT INTO ACT_GE_SCHEMA_LOG
VALUES ('0', CURRENT_TIMESTAMP, '7.11.0');

--preconditions onFail:CONTINUE onError:CONTINUE
CREATE INDEX ACT_IDX_HI_OP_LOG_USER_ID ON ACT_HI_OP_LOG (USER_ID_);

--preconditions onFail:CONTINUE onError:CONTINUE
CREATE INDEX ACT_IDX_HI_OP_LOG_OP_TYPE ON ACT_HI_OP_LOG (OPERATION_TYPE_);

--preconditions onFail:CONTINUE onError:CONTINUE
CREATE INDEX ACT_IDX_HI_OP_LOG_ENTITY_TYPE ON ACT_HI_OP_LOG (ENTITY_TYPE_);

Is this idea correct?

Answer 1

The idea looks very risky.

Answer 2

try sqitch and pgtap