Flyway h2 postgressql mode migration not working

Question

I have to following migration working in postgres:

ALTER TABLE task_def
  DROP COLUMN retry_count,
  DROP COLUMN timeout_seconds;

(and running in prod) but now i want to switch to h2 for my unit test but h2 doesnt seem to accept it My database config in spring boot:

spring.datasource.url=jdbc:h2:./target/testdb;MODE=PostgreSQL
spring.datasource.username="sa"
spring.datasource.password=""
spring.jpa.hibernate.ddl-auto=none
spring.datasource.driver-class-name=org.postgresql.Driver

spring.flyway.url=jdbc:h2:./target/testdb;MODE=PostgreSQL
spring.flyway.user="sa"
spring.flyway.password=""
spring.flyway.schemas=

The error:

 Migration V3__.....sql failed
---------------------------------------
SQL State  : 42S22
Error Code : 42122
Message    : Column "DROP" not found; SQL statement:
ALTER TABLE task_def
  DROP COLUMN retry_count,
  DROP COLUMN timeout_seconds [42122-200]
Location   : db/migration/V3__.....sql
Line       : 1
Statement  : ALTER TABLE task_def
  DROP COLUMN retry_count,
  DROP COLUMN timeout_seconds

Answer 1

I haven't worked with H2 but it looks like it supports the following:

1. 2 Statements instead of one in the same migration (flyway should run them in the same transaction anyway):

ALTER TABLE task_def DROP COLUMN retry_count;
ALTER TABLE task_def DROP COLUMN timeout_seconds;

2. Use different syntax:

ALTER TABLE task_def DROP COLUMN retry_count, timeout_seconds;

Of course if postgresql allows to do so as well.

All in all, I don't think that H2 will be able to cover all the features offered by postgres with its dialect, so failures like this can't be avoided.

So in my experience the following approach works much better:

Create a "test container" of postgres (see testcontainers project) and configure flyway / data source to run against it in tests. Depending on your tests infrastructure you can even not stop the container, but drop the database and run flyway before each test case. Or alternatively you can do like in spring tests - create an artificial transaction before running the test and "fail" it when the test finishes (even if it finishes successfully) so that the db won't be dirty for the next test.