I'm working on a Spring Boot project where we are currently using Liquibase for database migrations to a PostgreSQL database. Our project involves creating tables, persisting data and performing updates. However, we are facing challenges with rollbacks because we are using SQL scripts.

To address this, I am considering replacing Liquibase with a custom solution that uses JSON configuration for database migrations. Here is what I have done so far.

1. Created a JSON file that describes the database schema and data changes.
2. Created a service that reads the JSON configuration and applies the changes to the database.
3. Integrated the Service into our Spring Boot application.

Here is an example of the JSON configuration (db_migrations.json):

{
  "migrations": [
    {
      "id": "001",
      "description": "Create table1",
      "up": [
        "CREATE TABLE table1 (id SERIAL PRIMARY KEY, field1 VARCHAR(255) NOT NULL, field2 VARCHAR(255), field3 VARCHAR(255), field4 VARCHAR(255), field5 VARCHAR(255), field6 VARCHAR(255) NOT NULL, field7 JSONB NOT NULL, field8 TIMESTAMP, field9 TIMESTAMP, field10 VARCHAR(255), field11 UUID NOT NULL)"
      ],
      "down": [
        "DROP TABLE table1"
      ]
    }
  ]
}

And here is the JsonMigrationService:

@Service
public class JsonMigrationService {

    private final JdbcTemplate jdbcTemplate;
    private final ObjectMapper objectMapper;

    @Autowired
    public JsonMigrationService(JdbcTemplate jdbcTemplate, ObjectMapper objectMapper) {
        this.jdbcTemplate = jdbcTemplate;
        this.objectMapper = objectMapper;
    }

    @PostConstruct
    public void applyMigrations() throws IOException {
        File file = new File("src/main/resources/db_migrations.json");
        JsonNode rootNode = objectMapper.readTree(file);
        JsonNode migrations = rootNode.path("migrations");

        for (JsonNode migration : migrations) {
            String id = migration.path("id").asText();
            String description = migration.path("description").asText();
            JsonNode upCommands = migration.path("up");

            for (JsonNode command : upCommands) {
                jdbcTemplate.execute(command.asText());
            }
        }
    }

    public void rollbackMigration(String migrationId) throws IOException {
        File file = new File("src/main/resources/db_migrations.json");
        JsonNode rootNode = objectMapper.readTree(file);
        JsonNode migrations = rootNode.path("migrations");

        for (JsonNode migration : migrations) {
            if (migration.path("id").asText().equals(migrationId)) {
                JsonNode downCommands = migration.path("down");
                for (JsonNode command : downCommands) {
                    jdbcTemplate.execute(command.asText());
                }
                break;
            }
        }
    }
}

How can I improve the rollback mechanism to ensure it is robust and reliable?

Are there any existing libraries or frameworks that support JSON based database migrations that I should consider instead of building a custom solution?

Any advice or suggestions would be greatly appreciated. Thank you!

I'm working on a Spring Boot project where we are currently using Liquibase for database migrations to a PostgreSQL database. Our project involves creating tables, persisting data and performing updates. However, we are facing challenges with rollbacks because we are using SQL scripts.

To address this, I am considering replacing Liquibase with a custom solution that uses JSON configuration for database migrations. Here is what I have done so far.

1. Created a JSON file that describes the database schema and data changes.
2. Created a service that reads the JSON configuration and applies the changes to the database.
3. Integrated the Service into our Spring Boot application.

Here is an example of the JSON configuration (db_migrations.json):

{
  "migrations": [
    {
      "id": "001",
      "description": "Create table1",
      "up": [
        "CREATE TABLE table1 (id SERIAL PRIMARY KEY, field1 VARCHAR(255) NOT NULL, field2 VARCHAR(255), field3 VARCHAR(255), field4 VARCHAR(255), field5 VARCHAR(255), field6 VARCHAR(255) NOT NULL, field7 JSONB NOT NULL, field8 TIMESTAMP, field9 TIMESTAMP, field10 VARCHAR(255), field11 UUID NOT NULL)"
      ],
      "down": [
        "DROP TABLE table1"
      ]
    }
  ]
}

And here is the JsonMigrationService:

@Service
public class JsonMigrationService {

    private final JdbcTemplate jdbcTemplate;
    private final ObjectMapper objectMapper;

    @Autowired
    public JsonMigrationService(JdbcTemplate jdbcTemplate, ObjectMapper objectMapper) {
        this.jdbcTemplate = jdbcTemplate;
        this.objectMapper = objectMapper;
    }

    @PostConstruct
    public void applyMigrations() throws IOException {
        File file = new File("src/main/resources/db_migrations.json");
        JsonNode rootNode = objectMapper.readTree(file);
        JsonNode migrations = rootNode.path("migrations");

        for (JsonNode migration : migrations) {
            String id = migration.path("id").asText();
            String description = migration.path("description").asText();
            JsonNode upCommands = migration.path("up");

            for (JsonNode command : upCommands) {
                jdbcTemplate.execute(command.asText());
            }
        }
    }

    public void rollbackMigration(String migrationId) throws IOException {
        File file = new File("src/main/resources/db_migrations.json");
        JsonNode rootNode = objectMapper.readTree(file);
        JsonNode migrations = rootNode.path("migrations");

        for (JsonNode migration : migrations) {
            if (migration.path("id").asText().equals(migrationId)) {
                JsonNode downCommands = migration.path("down");
                for (JsonNode command : downCommands) {
                    jdbcTemplate.execute(command.asText());
                }
                break;
            }
        }
    }
}

How can I improve the rollback mechanism to ensure it is robust and reliable?

Are there any existing libraries or frameworks that support JSON based database migrations that I should consider instead of building a custom solution?

Any advice or suggestions would be greatly appreciated. Thank you!

• java
• postgresql
• spring-boot
• database-migration
• liquibase

• You are already liquirebase what makes you think a custom solution would support rollbacks? Liquibase supports xml, yaml, json or sql so not sure what made you move away from that. – M. Deinum Commented Jan 22 at 15:27
• @M.Deinum we're using raw sql scripts. Each change set must have a corresponding rollback script, which can be very hard to maintain and prone to errors. I am open to your feedback and suggestions. If there are ways to address these challenges within Liquibase, I would be happy to consider staying with it. – Ali BOUHLEL Commented Jan 22 at 15:35
• I don't see how yours is different, only you now apply it in one set. I would also suggest you explain your use-case a bit more as rollback is something that happens automatically if a tx doesn't commit. But as stated nothing is preventing you from using JSON with liquibase. – M. Deinum Commented Jan 22 at 15:49

1 Answer 1

Liquibase already supports JSON along with SQL, XML, and YAML. Here's a doc to help you get started with JSON: https://docs.liquibase/start/get-started/liquibase-json.html

If you're specifically looking for JSON rollbacks, here's an example using JSON: https://docs.liquibase/commands/rollback/rollback.html#json_example_custom_generic

Liquibase already handles rollbacks for many change types automatically. You can see those along with other info to write custom rollbacks at: https://docs.liquibase/workflows/liquibase-community/automatic-custom-rollbacks.html

For more info on the general rollback workflow there's also this doc: https://docs.liquibase/workflows/liquibase-community/using-rollback.html

You can definitely create your own solution, but I would recommend against creating bespoke tools that already have established open-source alternatives with active communities.