liquibase
liquibase copied to clipboard
Feature: Simple solution to use runtime set properties
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!)