Support AutoIncrement in creating objects


I would like to use liquibase not only to install or update a database schema for my application, but also to create some objects using the auto_increment feature of the database and create links between the objects, using their id's. As long as i understand, the current version of liquibase is not supporting this feature.

Let's say we have a person and an address table, with a 1:1 relationship. Both tables are using the auto_increment feature of the database and the table person contains a link to an entry in the adress table, represented by its id, which also has a not null and a foreign key constraint. When i want to create a person with an associated address using liquibase, the only way i see is to create an sql statement, that inserts the address, fetches the id of the newly created address and then inserts the person with the id fo the address or i depend on the execution order and guess the id's. Guessing the id's, the changeset might look like this:

This is bad code, i think. What if i would like to create entries in an update? There might be already data in these tables and i'm getting in trouble, guessing the wrong id's. Writing SQL statements would even be worse.

Thinking about better solutions, i thought about creating the schema using liquibase and creating objects in the application, which uses hibernate. This would be pretty simple, because i'm already creating objects in my application. But what about updates? What about error handling and rollback? I'm also a fan of the Single-Responsibility-Principle, which is broken, by doing some database installation/updates using liquibase and some in the application code.

No, the best solution would be, to create the objects using liquibase, but how can this be done? I thought about something like this:

The first insert gets an optional attribute id, which should be unique inside the change set and according to the table. The second column element of the second insert gets the optional attributes reference and id, containing the referenced table and the given id of the first insert element. I think, this is what a user would like to have. There had to be some constraints, for example, that a reference to an insert statement is limited to the changeset. The reference and the source had to be in the same changeset, because the id can only be determined on creation. Another constraint would be the order of the insert statements. The source had to be inserted before the reference is used.

Looking at the liquibase api, i thought that it might be possible, to change some sql statements, in order to get the newly created id of an insert statement. Then i have to associate this table id with the given id of the insert element in the changeset. Then i have to extend the column element, where i have to set the right value, if there is a reference to an id field of another table. This is not solving every problem, creating and updating objects, but i think it is a step in the right direction.

Is this complete non-sense, what i'm thinking about? Is it possible, to write an extension for this? Any ideas, suggestions or comments are appreciated.


I would humbly suggest that Liquibase is the wrong tool for the job. I see Liquibase’s single responsibility as ‘manage database schema changes’ and loading data is not a part of that responsibility. If you are using it to load test data, that seems like a reasonable use, maybe, but in that case your tests should not be tightly coupled to implementation details like what the id is for a raw that was inserted. If you are using Liquibase to load ‘constant’ data (i.e. US States table that is used for many address-type tables) then that should be OK too - just hard-code the IDs and ensure that every instance of the database has the same ones.

Steve Donie
Principal Software Engineer
Datical, Inc.

Hi Steve.

This is not the answer, that i was hoping for, but i think you’re right and Liquibase does a very good job managing the schema changes. Thanks a lot.