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:

  1. Extending the support for Oracle to include all schema object types
  2. Building an interface directly into SQLcl via a new LB (LIQUIBASE) command
  3. 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…

We’re getting XML based versions of your objects via DBMS_METADATA and writing those objects to XML files, and we’re building a controller.xml file to house the changelog for this version.

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:

We’re handling all the hard bits for you – gen the changelogs, getting ALL the object properties defined, and ordering it correctly so it can be applied and not fail due to dependency ordering.

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 ‘false’ says don’t worry about the SCHEMA.

The default behavior is to apply the changelogs with the schema prefix, but I captured in HR and I’m running in LB_DEMO.


As the update runs, we get a running log to the prompt, and I can watch along in the GUI if I so desire.

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.

I have a single XML file in my Version.NEXT folder now (EMPLOYEES_1-TABLE.XML)

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?

It sees that we need to remove one column, and add one column.

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.

thatjeffsmith
Author

I'm a Senior Principal Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

3 Comments

  1. This is awesome, is it possible to have a current release of the command line tool in order to try it before adoption ?

  2. Looks incredible Jeff! Something I’ve always wanted to learn more about is managing source control for databases and this looks like a great feature.

Write A Comment