Replace Liquibase with JSON Based Configuration for Database Migrations in Spring Boot

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!

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

If you're specifically looking for JSON rollbacks, here's an example using JSON: https://docs.liquibase.com/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.com/workflows/liquibase-community/automatic-custom-rollbacks.html

For more info on the general rollback workflow there's also this doc: https://docs.liquibase.com/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.