Liquibase Generate Changelogs per Table

Liquibase provides the generateChangeLog command to create a ChangeLog file for an existing database. By default, the generateChangeLog command will generate a big ChangeLog file which will have all the tables, functions, views, columns, indexes, foreign keys, primary keys, unique constraints, data, stored procedures, triggers, sequences, etc.
but most development teams try to segregate and manage ChangeLog as per DDL and DML for each table into separate files and include them as per order required.

Straightforward there is no way to generate a separate ChangeLog per table. So the development team first generates ChangeLog for the entire database, and then manually tries to segregate them into separate files. but this approach is error-prone and sometimes creates foreign key voilation. Let's see the same with an example, assume we have database schema as below

create DATABASE IF NOT EXISTS `appledb`;
USE `appledb`;
create TABLE `taste` (
 `taste_id` int NOT NULL auto_increment,
 `taste` varchar(2000) NOT NULL,
 PRIMARY KEY (`taste_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into `taste` (`taste`) VALUES ('sweet'),('sour');


create TABLE `apple` (
 `apple_id` bigint NOT NULL auto_increment,
 `apple_name` varchar(255) DEFAULT NULL,
 `taste_id` int NOT NULL,
 PRIMARY KEY (`apple_id`),
 KEY `FKqg39etajdct3xxd813arauyit` (`taste_id`),
 CONSTRAINT `taste_id_taste_fk` FOREIGN KEY (`taste_id`) REFERENCES `taste` (`taste_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into `apple` (`apple_name`, `taste_id` ) VALUES ('Macintosh',1),('Fuji',2);
commit;

Run the above SQL, and create a database and tables with respective values. once done, let's generate a ChangeLog for the above data with the help of Liquibase.

liquibase.properties

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

execute the command liquibase generateChangeLog it will generate a changelog.mysql.xml file for the entire database.

Manually segrigating the generated ChangeLog file is very error-prone.

Generate ChangeLog for each sequence, table and data individually

As stated above, so far there has been no straightforward solution for this problem from Liquiabse, but there are some workarounds.

Liquibase provides diffTypes tag, which will only generate the database object if passed, below are the available diffTypes

  diffType default description
1 tables yes will generate the table creation ChangeLog only
2 columns yes will generate column addition ChangeLog only
3 views yes will generate views creations ChangeLog only
4 primaryKeys yes will generate primary Keys ChangeLog only
5 indexes yes will generate indexes ChangeLog only
6 foreignKeys yes will generate foreign keys ChangeLog only
7 sequences yes will generate sequences ChangeLog only
8 data no will generate insert data ChangeLog only

If No diffTypes is mentioned then all the default diffTypes will be considered

Liquibase provides another includeObjects tag, which takes table and sequence.
By combining diffTypes and includeObjects as per requirement, we can achieve the desired ChangeLog file at a time, which will be renamed and another ChangeLog will be generated, let's see this with an example.

Assume we are working with the same SQL as used above

create DATABASE IF NOT EXISTS `appledb`;
USE `appledb`;
create TABLE `taste` (
 `taste_id` int NOT NULL auto_increment,
 `taste` varchar(2000) NOT NULL,
 PRIMARY KEY (`taste_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into `taste` (`taste`) VALUES ('sweet'),('sour');


create TABLE `apple` (
 `apple_id` bigint NOT NULL auto_increment,
 `apple_name` varchar(255) DEFAULT NULL,
 `taste_id` int NOT NULL,
 PRIMARY KEY (`apple_id`),
 KEY `FKqg39etajdct3xxd813arauyit` (`taste_id`),
 CONSTRAINT `taste_id_taste_fk` FOREIGN KEY (`taste_id`) REFERENCES `taste` (`taste_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into `apple` (`apple_name`, `taste_id` ) VALUES ('Macintosh',1),('Fuji',2);
commit;

Run the above SQL, and create a database and tables with respective values. once done, let's generate a ChangeLog for the above data with the help of Liquibase.

Generate Taste Table Creation ChangeLog

Let's modify the Liquibase.properties as below and generate a ChangeLog for the taste table creation changeset.

liquibase.properties

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
overwriteOutputFile=true
includeTablespace:true
includeCatalog:true
includeObjects=table:taste

Execute the command liquibase generateChangeLog it will generate a changelog.mysql.xml file which will contain create taste table changeset only, rename this file to changelog.mysql.create.taste.xml

Generate Taste Table Insertion ChangeLog

modify the liquibase.properties as below and generate a ChangeLog for the taste table insertion changeset.

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
overwriteOutputFile=true
includeTablespace:true
includeCatalog:true
diffTypes:data
includeObjects=table:taste

Execute the command liquibase generateChangeLog it will generate a changelog.mysql.xml file which will contain insert taste table changeset only, rename this file to changelog.mysql.insert.taste.xml

 

Generate Apple Table Creation ChangeLog

modify the liquibase.properties as below and generate a ChangeLog for the apple table creation changeset.

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
overwriteOutputFile=true
includeTablespace:true
includeCatalog:true
includeObjects=table:apple

Execute the command liquibase generateChangeLog it will generate a changelog.mysql.xml file which will contain the create apple table changeset only, rename this file to changelog.mysql.insert.taste.xml

Generate Apple Table Insertion ChangeLog

modify the liquibase.properties as below and generate a ChangeLog for the apple table insertion changeset.

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
overwriteOutputFile=true
includeTablespace:true
includeCatalog:true
includeObjects=table:apple

Execute the command liquibase generateChangeLog it will generate a changelog.mysql.xml file which will contain insert apple table changeset only, rename this file to changelog.mysql.insert.apple.xml

Create Master ChangeLog and include all Individual ChangeLog

Now let's create a master changelog.mysql.xml file, which will include all the ChangeLog we have just created it in the required sequence.

changelog.mysql.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" objectQuotingStrategy="QUOTE_ALL_OBJECTS">
	<include file="changelog.mysql.create.taste.xml" />
	<include file="changelog.mysql.insert.taste.xml" />
	<include file="changelog.mysql.create.apple.xml" />
	<include file="changelog.mysql.insert.apple.xml" />
</databaseChangeLog>

liquibase.properties

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

changelog.mysql.create.taste.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="1696659398982-1">
		<createTable catalogName="appledb" tableName="taste">
			<column autoIncrement="true" name="taste_id" type="INT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="taste" type="VARCHAR(2000)">
				<constraints nullable="false" />
			</column>
		</createTable>
	</changeSet>
</databaseChangeLog>

changelog.mysql.insert.taste.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="1696659621108-1">
        <insert catalogName="appledb" tableName="taste">
            <column name="taste_id" valueNumeric="1"/>
            <column name="taste" value="sweet"/>
        </insert>
        <insert catalogName="appledb" tableName="taste">
            <column name="taste_id" valueNumeric="2"/>
            <column name="taste" value="sour"/>
        </insert>
    </changeSet>
</databaseChangeLog>

changelog.mysql.create.apple.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="1696659529449-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 name="taste_id" type="INT">
				<constraints nullable="false" />
			</column>
		</createTable>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696659529449-2">
		<createIndex catalogName="appledb" indexName="FKqg39etajdct3xxd813arauyit" tableName="apple">
			<column name="taste_id" />
		</createIndex>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1696659529449-3">
		<addForeignKeyConstraint baseColumnNames="taste_id" baseTableCatalogName="appledb" baseTableName="apple" constraintName="taste_id_taste_fk" deferrable="false" initiallyDeferred="false" onDelete="RESTRICT" onUpdate="RESTRICT" referencedColumnNames="taste_id" referencedTableCatalogName="appledb" referencedTableName="taste" validate="true" />
	</changeSet>
</databaseChangeLog>

changelog.mysql.insert.apple.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="1696659833557-1">
        <insert catalogName="appledb" tableName="apple">
            <column name="apple_id" valueNumeric="1"/>
            <column name="apple_name" value="Macintosh"/>
            <column name="taste_id" valueNumeric="1"/>
        </insert>
        <insert catalogName="appledb" tableName="apple">
            <column name="apple_id" valueNumeric="2"/>
            <column name="apple_name" value="Fuji"/>
            <column name="taste_id" valueNumeric="2"/>
        </insert>
    </changeSet>
</databaseChangeLog>

execute the command liquibase update, it will create and insert records in tables as per the below order

  1. create taste table
  2. insert record in taste table
  3. create apple table
  4. insert record in apple table

The same can be verified by running command

select * from appledb.apple ap, appledb.taste ta where ap.taste_id=ta.taste_id;

follow us on

YouTube IconLinkedIn Icon