Liquibase Change Types loadUpdateData

Liquibase provides loadData Change Type to load data into database tables from CSV files, but loadData doesn't support updation of CSV file. To address this issue Liquibase provides another loadUpdateData Change Type.

loadUpdateData Change TYpe provides an onlyUpdate="false" flag, if true it will only update the data and will not insert if the record is missing from the table.

loadUpdateData Change TYpe first checks if there is an existing record in the table by comparing the primary key, then performs the required updation.

First, create a table with some records in it.

use AppleDB;
DROP TABLE IF EXISTS `apple`;
CREATE TABLE `apple` (
  `apple_id` bigint NOT NULL AUTO_INCREMENT,
  `apple_name` varchar(255) DEFAULT NULL,
  `current_date_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`apple_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `apple` VALUES (1,'Macintosh','2023-09-23 15:09:16'),(2,'Fuji','2022-09-23 15:09:16'),(3,'Gala','2021-09-23 15:09:16'),(4,'Jonagold','2020-09-23 15:09:16');
commit;

Assuming the table already has the above data. in the below CSV file, the current_date_time column values has been updated for apple_id having primary key 1 and 2, but the apple_id=3 is the same.

csvData/apple.csv

"apple_id","apple_name","current_date_time"
"1",'Macintosh','2024-09-23 15:09:16'
"2",'Fuji','2025-09-23 15:09:16'
"3",'Gala','2026-09-23 15:09:16'
"4",'Jonagold','2027-09-23 15:09:16'

ChangeLog.xml file with table metadata

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="shriksha (generated)" id="1695461977005-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1695461977005-2" runOnChange="true">
		<loadUpdateData catalogName="appleDb" commentLineStartsWith="#" encoding="UTF-8" file="csvData/apple.csv" onlyUpdate="true" primaryKey="apple_id" quotchar="&quot;" relativeToChangelogFile="true" schemaName="appleDb" separator="," tableName="apple" usePreparedStatements="true">
			<column header="apple_id" name="apple_id" type="NUMERIC" />
			<column header="apple_name" name="apple_name" type="STRING" />
			<column header="current_date_time" name="current_date_time" type="DATE" />
		</loadUpdateData>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update

The records will be updated corresponding to their primary key, the same can be verified by running the command

select * from appledb.apple;

loadUpdateData Update and Insert

By default, loadUpdateData has onlyUpdate="true", hence will only update the data, if needs to insert data as well then update onlyUpdate="false".

First, create a table with some records in it.

use AppleDB;
DROP TABLE IF EXISTS `apple`;
CREATE TABLE `apple` (
  `apple_id` bigint NOT NULL AUTO_INCREMENT,
  `apple_name` varchar(255) DEFAULT NULL,
  `current_date_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`apple_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `apple` VALUES (1,'Macintosh','2023-09-23 15:09:16'),(2,'Fuji','2022-09-23 15:09:16'),(3,'Gala','2021-09-23 15:09:16'),(4,'Jonagold','2020-09-23 15:09:16');
commit;

Assuming the table already has the above data. in the below CSV file, the current_date_time column values has been updated for apple_id having primary key 1 and 2, but the apple_id=3 is the same and a new record is added in CSV.

csvData/apple.csv

"apple_id","apple_name","current_date_time"
"1",'Macintosh','2024-09-23 15:09:16'
"2",'Fuji','2025-09-23 15:09:16'
"3",'Gala','2026-09-23 15:09:16'
"4",'Jonagold','2027-09-23 15:09:16'
"5",'Pink Lady','2028-09-23 15:09:16'

Changelog.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="shriksha (generated)" id="1695461977005-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1695461977005-2" runOnChange="true">
		<loadUpdateData catalogName="appleDb" commentLineStartsWith="#" encoding="UTF-8" file="csvData/apple.csv" onlyUpdate="true" primaryKey="apple_id" quotchar="&quot;" relativeToChangelogFile="true" schemaName="appleDb" separator="," tableName="apple" usePreparedStatements="true">
			<column header="apple_id" name="apple_id" type="NUMERIC" />
			<column header="apple_name" name="apple_name" type="STRING" />
			<column header="current_date_time" name="current_date_time" type="DATE" />
		</loadUpdateData>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update

The records will be updated corresponding to their primary key, and a new record will be inserted as well. the same can be verified by running the command

select * from appledb.apple;

Liquibase Change Types loadUpdateData

loadUpdateData Change TYpe provides an onlyUpdate="false" flag, if true it will only update the data and will not insert if the record is missing from the table.

loadUpdateData Update and Insert

follow us on