Backwards compatible migration for Spring Batch 5 params table

With the Spring Boot upgrade from version 2.7 to 3.3 we had also to upgrade our Spring Batch as well from Spring Batch 4 to 5 and while there are not many changes, the big one for systems that are in production running with the older version is the change in table structure of BATCH_JOB_EXECUTION_PARAMS. In this post, I will go over a migration strategy we follow to be able to seamlessly migrate this table.

There are not many code changes required and they are well described in the migration guide in the project documentation, but one thing that the migration suggested in the documentation doesn’t consider is the case of rollback or the case that we need data migration and that’s what I’m gonna talk about in this post how did we do the data migration between Spring Batch 4 and Spring Batch 5.

This is a migration script provided by the Spring Batch framework and I’m gonna use the PostgreSQL version because it’s the one that we use as an example. Here is the Migration.

ALTER TABLE BATCH_STEP_EXECUTION ADD CREATE_TIME TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00';

ALTER TABLE BATCH_STEP_EXECUTION ALTER COLUMN START_TIME DROP NOT NULL;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS DROP COLUMN DATE_VAL;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS DROP COLUMN LONG_VAL;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS DROP COLUMN DOUBLE_VAL;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS ALTER COLUMN TYPE_CD TYPE VARCHAR(100);

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS RENAME TYPE_CD TO PARAMETER_TYPE;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS ALTER COLUMN KEY_NAME TYPE VARCHAR(100);

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS RENAME KEY_NAME TO PARAMETER_NAME;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS ALTER COLUMN STRING_VAL TYPE VARCHAR(2500);

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS RENAME STRING_VAL TO PARAMETER_VALUE;

As you can see by the migration, we are adding new columns. We are also renaming some columns and we are dropping other columns. This script is fine if you don’t need to roll back your application or you don’t have the risk of needing to roll back the application. But also, I’m not sure that the old batches are gonna fully open in the new application version because as you can see, we are not filling the date and other fields except for the string one.

A better migration strategy for `BATCH_JOB_EXECUTION_PARAMS`

To be able to have a smoother migration and also be able to roll back to the old version in case of any unexpected problem. We did the process in a few steps.

The first thing we did was to add the new columns and now also remove the constraint from the old calls so we can save no on them as well. As this is a backword-compatible change we added this script in the old version before the Spring Boot migration.

ALTER TABLE BATCH_STEP_EXECUTION
   ADD COLUMN IF NOT EXISTS CREATE_TIME TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00',
   ALTER COLUMN START_TIME DROP NOT NULL;

ALTER TABLE BATCH_JOB_EXECUTION_PARAMS
   ALTER COLUMN TYPE_CD DROP NOT NULL,
   ALTER COLUMN KEY_NAME DROP NOT NULL,
   ADD COLUMN IF NOT EXISTS PARAMETER_TYPE  CHARACTER VARYING(100),
   ADD COLUMN IF NOT EXISTS PARAMETER_NAME  CHARACTER VARYING(100),
   ADD COLUMN IF NOT EXISTS PARAMETER_VALUE TEXT;

So now we have the columns that we are gonna need for the new version and also we still have the columns from the old version. But when inserting day records with the old version of Spring, we are going to have null values on the new columns and also when we run with the new application, the old columns are gonna be new so we’re gonna probably have access or new records or records.

Despite I’m not being so fan of triggers, it was a good approach to temporarily use triggers to fuel the records of the new New column when saving with the old application and the old column, saving within the new application. Below is an example of a trigger that we used to accomplish that.

CREATE OR REPLACE FUNCTION keep_batch_job_execution_params_compatible()
   RETURNS TRIGGER AS
$$
BEGIN
   -- Check if the new version is being saved
   IF NEW.PARAMETER_VALUE IS NOT NULL THEN
       -- Update the older columns from the new columns
       NEW.KEY_NAME := NEW.PARAMETER_NAME;
       IF NEW.PARAMETER_TYPE = 'java.util.Date' THEN
           NEW.TYPE_CD := 'DATE';
           NEW.DATE_VAL := NEW.PARAMETER_VALUE::TIMESTAMP;
           NEW.STRING_VAL := '';
           NEW.DOUBLE_VAL := 0;
           NEW.LONG_VAL := 0;
       ELSE
           NEW.TYPE_CD := 'STRING';
           NEW.STRING_VAL := NEW.PARAMETER_VALUE;
           NEW.DATE_VAL := '1970-01-01 00:00:00'::TIMESTAMP;
           NEW.DOUBLE_VAL := 0;
           NEW.LONG_VAL := 0;
       END IF;
   ELSE
       -- Update the new columns from the old columns
       NEW.PARAMETER_NAME := NEW.KEY_NAME;
       IF NEW.TYPE_CD = 'DATE' THEN
           NEW.PARAMETER_TYPE := 'java.util.Date';
           NEW.PARAMETER_VALUE := to_char(NEW.DATE_VAL, 'YYYY-MM-DD"T"HH24:MI:SS.MS') || 'Z';
       ELSE
           NEW.PARAMETER_TYPE := 'java.lang.String';
           IF NEW.STRING_VAL IS NOT NULL THEN
               NEW.PARAMETER_VALUE := NEW.STRING_VAL;
           ELSE
               -- new version does not allow null so we wrap it with empty
               NEW.PARAMETER_VALUE := '';
           END IF;
       END IF;
   END IF;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER before_insert_batch_job_execution_params_migration_sync
   BEFORE INSERT
   ON batch_job_execution_params
   FOR EACH ROW
EXECUTE FUNCTION keep_batch_job_execution_params_compatible();

Notice that the above trigger only fills in type String and Date and not Long, Double, etc as we only had String and Date parameters. If you want to support other types, you need to modify the above script to handle such scenarios.

With this trigger, we guarantee that all the new columns on the table will be filled when the old application is running and after the update, the trigger will fill up the old columns when saving with the new application making all the records backward compatible.

Before deploying the new version of Spring Batch, we need one more step. We need to migrate the old rows to fill in the values of the new columns. For that, we run this migration.

-- update all the DATE fields
update batch_job_execution_params
set parameter_type  = 'java.util.Date',
   parameter_name  = key_name,
   parameter_value = to_char(date_val, 'YYYY-MM-DD"T"HH24:MI:SS.MS') || 'Z'
where type_cd = 'DATE'
 AND parameter_type is null;

-- Update all the STRING fields that are not null
update batch_job_execution_params
set parameter_type  = 'java.lang.String',
   parameter_name  = key_name,
   parameter_value = string_val
where type_cd = 'STRING'
 AND parameter_type is null
 AND string_val is not null;

-- update all the STRING fields which are null
update batch_job_execution_params
set parameter_type  = 'java.lang.String',
   parameter_name  = key_name,
   parameter_value = ''
where type_cd = 'STRING'
 AND parameter_type is null
 AND string_val is null
 AND parameter_value is null;

This migration can take from a few seconds to hours depending on the size of your database so I would recommend running directly on the DB before adding to the migration (Flyway) of your application. Otherwise, you could have startup problems as it can take a while.

So now we got to the point where we can just deploy the new application and it was gone is gonna work when we try to get from the database the old batches data and also if we return the application to the old version, the new records are also going to be accessible by the old application.

After the new application is running for a while and you are comfortable that you will not need to roll back to the old version anymore, we have some cleanup to do.

The first step is to drop the trigger and also drop the function that we needed for the trigger as shown by the script below.

DROP TRIGGER IF EXISTS before_insert_batch_job_execution_params_migration_sync ON batch_job_execution_params;
DROP FUNCTION IF EXISTS keep_batch_job_execution_params_compatible;

Also, we can drop the old columns as we will not be needing them anymore and we have already copied the data to the new columns. The script below does the trick.

ALTER TABLE batch_job_execution_params
   DROP COLUMN IF EXISTS type_cd,
   DROP COLUMN IF EXISTS key_name,
   DROP COLUMN IF EXISTS key_name,
   DROP COLUMN IF EXISTS string_val,
   DROP COLUMN IF EXISTS date_val,
   DROP COLUMN IF EXISTS long_val,
   DROP COLUMN IF EXISTS double_val;

Maybe there are better approaches to do this but this one worked well for us so I hope this helps you and the migration process between Spring Batch 4 and Spring Batch 5 and Spring Boot 2.7 to Spring Boot 3.3.

Cheers.

Google Anaytics (functional)

A better migration strategy for BATCH_JOB_EXECUTION_PARAMS#

A better migration strategy for `BATCH_JOB_EXECUTION_PARAMS`