LIQUIBASE does ‘Source Control for your Database.’
It’s an Open Source project that allows you to capture changelogs for your database, including Oracle.
What we are doing:
- Extending the support for Oracle to include all schema object types
- Building an interface directly into SQLcl via a new LB (LIQUIBASE) command
- Generating the changelogs for you and managing rollbacks plus the ordering for the changelogs to avoid database object dependency errors.
A Quick Demo
I said this is a teaser, and it REALLY is a teaser – so what I’m showing you isn’t everything we’re doing. And we’ll be talking much more about this when it is available officially (19.x).
But let’s cover a very common scenario this might be useful for.
What I’m going to do is capture a schema as a ‘version 0’ or base copy of my application schema code.
Then I’m going to push that version to a new staging/test environment.
Then I’m going to make a change to my schema, and capture that changelog as a version.NEXT, and generate the SQL that would be used to update my staging/test environment.
Capture Base Version
I’m going to create a directory to hold my changelogs and master/controller file in. I then connect to my application schema in SQLcl, and run the LB command.
My schema has 692 objects in it – and I’m running this on my VBOX image on my laptop, so perf times will vary…
When I’m done, we can peak into our version 0 directory. Each object gets its own file, and then we have the ‘master’ controller file:
Put it Down Somewhere Else
So I’m going to create a new user, grant that user some privs. If I wanted to – I could include that work in the changelog as a custom SQL script, but that’s not really what I want to show today…but you can customize this stuff very easily.
So I login as that new schema, and I do a ‘lb update.’
The default behavior is to apply the changelogs with the schema prefix, but I captured in HR and I’m running in LB_DEMO.
Let’s Start on Version.NEXT
So now I’m ready to do some more work in my schema, and push it as a new version.
First, I’m going to create a Version.NEXT folder to hold my changelogs for that version.
So I hope into my IDE or CLI, and I run my work…I’m removing a column called TWITTER_HANDLE and I’m adding one called TWITTER, but this could be anything related to the table – a new foreign key, a check constraint (IS_JSON!) – it’s going to be picked up by DBMS_METADTA, and we’re going to create an XML based changelog for this new version.
The single object changelog, via lb gen command.
Preview the Proposed Upgrade SQL
So I have my changelog for the original version. And I have my new version. What would happen if I did an update based on my new version changelog in my staging/test environment which is at the base version?
If I ran the lb update command, it’d actually apply the changelog live to my connected schema.
When can we have this?
Calendar year 2019 is the closest I can say, but your biggest hint is that I’m even willing to show you anything at this point.
Even further along the calendar, we want to do cool things in the GUI (SQL Developer) around better schema compares.
I’ll be talking more about this on the Conference Circuit this year, maybe KScope Seattle and GLOC in Cleveland.