Liquibase templating stored procedure ChangeSets via <include> and <property>

I have N tables for which I need to generate 5 stored procedures each. All the stored procedures follow the same pattern, except for the table name in the referencing clause.

Would it be possible to do the following and is it a good practice to have the main changelog include changelog files for each of the tables?

<databaseChangeLog>
    <include file="changelogs/table1-changelog.xml"/>
    <include file="changelogs/table2-changelog.xml"/>
    <!-- … up to tableN-changelog.xml -->
</databaseChangeLog>

And each of the tables changelogs would contain for example something like this:

<databaseChangeLog>
    <property name="table_name" value="TABLE1"/>
    <include file="../templates/procedure-template-1.xml"/>
    <include file="../templates/procedure-template-2.xml"/>
    <include file="../templates/procedure-template-3.xml"/>
    <include file="../templates/procedure-template-4.xml"/>
    <include file="../templates/procedure-template-5.xml"/>
</databaseChangeLog>

Then a procedure template would be something like this:

<databaseChangeLog>
    <changeSet id="proc1-${table_name}" author="me">
        <sql>
            <![CDATA[
            CREATE OR REPLACE PROCEDURE ${table_name}_proc1()
            REFERENCING OLD AS old FOR ${table_name}
            -- rest of procedure's body
            ]]>
        </sql>
    </changeSet>
</databaseChangeLog>

From technical standpoint - Yes! Such approach should work.

1. Liquibase allows nested changelogs.

   master-changelog.xml can look like:

   <databaseChangeLog>
       <include file="changelogs/table1-changelog.xml"/>
       <include file="changelogs/table2-changelog.xml"/>
       <!-- … up to tableN-changelog.xml -->
   </databaseChangeLog>
   
   

   and table1-changelog.xml can look like:

   <databaseChangeLog>
       <property name="table_name" value="TABLE1"/>
       <include file="../templates/procedure-template-1.xml"/>
       <include file="../templates/procedure-template-2.xml"/>
       <include file="../templates/procedure-template-3.xml"/>
       <include file="../templates/procedure-template-4.xml"/>
       <include file="../templates/procedure-template-5.xml"/>
   </databaseChangeLog>
   

2. Liquibase allows inserting properties into changeLog's IDs and contents.

   I use spring-boot's application.properties but you can achieve the same with Liquibase properties as well.

   In application properties:

   spring.liquibase.parameters.my-custom-property-name=my-custom-property-value
   

   And in procedure-template-1.xml

   <changeSet id="foo_${my-custom-property-name}" author="bar">
       <comment>my property is: ${my-custom-property-name}</comment>
   </changeSet>
   

   As a result, there will be a changeSet in databasechangelog table with

   ID=213123_my-custom-property-value

   COMMENTS=my property is: my-custom-property-value

I don't see a reason why your approach would not technically work.

From a 'good practice' standpoint - I'd say it's not worth it.

Cons:

1. You lose the ability to make changes to the single table. Changes in a procedure will be propagated to all tables.

2. If (and most likely 'when') you decide to change anything in this structure, it'll be a checksum mess across all tables.

Pros:

1. Less code. And less code means fewer bugs.

2. You have full control over what's going on in each procedure in each table.

3. No checksum mess.