I don’t know if this is the best way to go about it but I can tell you what we did in a similar situation.
We used liquibase to create a changelog from the initial state of our database schema and stored that in a file called base.xml. The changeSets generated by liquibase were not perfect but they didn’t need much manual attention to get them to re-create our schema.
Then we created a file called update.xml. This file doesn’t contain any changeSets, but instead includes other files that do. The rational was any new modifications to the existing schema would be grouped together into their own file and an include entry would be added to update.xml. Hence our update.xml file looks something like:
Finally we created a file called create.xml that includes both base.xml and update.xml.
So when ever we need to upgrade the schema of an existing database, we just run liquibase against the update.xml:
- liquibase --changeLogFile=./update.xml update
But when we need to create a schema from scratch we run it against create.xml:
- liquibase --changeLogFile=./create.xml update
Finally, just in case someone accidently runs base.xml against a pre-existing schema, we have some preconditions for each changeSet in base.xml that will check for the presence of something that shouldn’t be there if the schema was blank (e.g. a well known table in our schema etc) and mark the change set as ran.
There may be better ways to achieve what we wanted, but it serves our needs and it is relatively straightforward.