Feature: Simple solution to use runtime set properties

[b-gyula]

Search first

• [X] I searched and no similar issues were found

Description

Liquibase have support for properties set before the changelog execution. It would be great if those properties could be set during the execution in typical data seeding situations like: add related data in multiple tables -> in the referenced table the newly inserted id is required as foreign key. E.g Insert a new user (with a new unknown db id) and insert additional data in related table(s) with the new user id. If the new db id could be stored in a property at the insert change inserting the user, then the insert(s) for the related tables could use that property as required foreign key with the existing property substitution.

Steps To Reproduce

user table
| uid | email        |
|  1  | [email protected] |

account table
|  id | uid | other |
|  1  |  1  |  ...  |

Now the script creator has to write custom SQL to query the newly generated user id where liquibase's database independent objects cannot be leveraged.

<insert tableName="user">
	<column name='email' value='[email protected]'/>
</insert>
<sql>INSERT INTO account SELECT uid, '...' FROM users WHERE email='[email protected]'</sql>

Expected/Desired Behavior

Having a new attribute which would allow to define the name of the property to get the return value in like returnIn on sql or even on insert change for databases supporting the returning clause (posgresql, mssql) would make it much easier, quicker and safer like:

<insert tableName='user'>
	<column name='email' value='[email protected]'/>
</insert>
<sql returnIn='userId'>SELECT uid, 'other columns' FROM users WHERE email='[email protected]'</sql>
<insert tableName="account">
	<column name='uid' value='${userId}'/>
	<column name='other' value='...'/>
</insert>

Or even better:

<insert tableName='user' **returnIn='userId'**>
	<column name='email' value='[email protected]'/>
</insert>
<insert tableName="account">
	<column name='uid' value='${userId}'/>
	<column name='other' value='...'/>
</insert>

Some further rules could apply

• force property declaration ahead <property name='userId' />
• for easier traceability I can imagine to restrict writing these properties only once
• to distinguish these runtime variables new tag like instead of can be used

Liquibase Version

No response

Database Vendor & Version

No response

Liquibase Integration

all

Liquibase Extensions

OS and/or Infrastructure Type/Provider

all

Additional Context

I have a working solution like this for version 3.10.3. If you are interested I can create a PR updated for the latest version.

Are you willing to submit a PR?

• [X] I'm willing to submit a PR (Thank you!)