ksql-migrations add ability to apply rollback to migrations

[noygafni]

Is your feature request related to a problem? Please describe. I'm am frustrated that when i need to to rollback a change in ksqldb, when working with ksql-migrations I have no way to achieve this. When all migrations tools for other databases all have this options.

Describe the solution you'd like It is possible to create an up.sql and down.sql files for each migration. the up.sql will be the actual migration - same as the migration sql file avialible today. and down.sql will be the rollback sql statements.

I would like to have a way to run a the command like: ksql-migrations -c ksql-migrations.properries apply -v <version> --down this will apply the specified version migration rollback statement.

or: ksql-migrations -c ksql-migrations.properries apply --goto <version> this will apply rollback for all migrations until the specified version

complete example for version V000001__Create_customers: up.sql:

CREATE STREAM customers (
    id VARCHAR key,
    name VARCHAR,
    age INTEGER
) WITH (
    kafka_topic = 'postgres.public.customers',
    value_format = 'JSON',
    PARTITIONS=1
);

down.sql:

DROP STREAM customers;

this will apply the migration: ksql-migrations -c ksql-migrations.properries apply -v V000001__Create_customers

this will apply the rollback: ksql-migrations -c ksql-migrations.properries apply -v V000001__Create_customers --down

Describe alternatives you've considered The only possible alternative right now is to change manually the sql on the db itself and then change the migration file, because the metadata already considering the migration as applied and there is no way to change it

Additional context The solution I suggested is very much inspired by the way hasura cli tool handles migrations, which is a very convenient to use. here is a link to hasura migration docs: https://hasura.io/docs/latest/migrations-metadata-seeds/manage-migrations/

[suhas-satish]

@noygafni , thanks for your feature request. We can put it on our backlog but unfortunately, dont have a short term timeline on which we can prioritize it.

[noygafni]

@suhas-satish Thank you, are you aware if there is any workaround currently to achieve the "rollback" situation?