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.
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.
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.
15 Comments
in sqlcl 19.4 i always get ‘SP2-0042: Unbekannter Befehl “liquibase” – Restliche Zeile wird ignoriert.’
I have no Idea why
If you run help, do you see the liquibase (lb) command listed?
No, it is not inluded.
My OS is Oracle Linux 7.7 and i installed sqldeveloper via the 19.4 RPM.
Greetings
Peter
sqlcl DEVPROJECT2/XXXXXXXXXX2@ORADEV1
SQLcl: Release 19.4 Production auf Fr Feb 07 14:13:32 2020
Copyright (c) 1982, 2020, Oracle. All rights reserved. Alle Rechte vorbehalten.
Last Successful login time: Fr Feb 07 2020 14:13:34 +01:00
Verbunden mit:
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 – Production
Version 18.9.0.0.0
SQL> help
Um die Hilfe zu einem Thema anzuzeigen, geben Sie help ein
Liste der verfügbaren Hilfethemen:
/
@
…………….
…………….
INPUT
LIST
LOAD*
NET*
………….
………….
It might be missing the JAR, as a workaround, go get the SQLcl.zip and extract it where you’d like it to live…or take the JARs from the lib folder and move them over into your SQLDev dist.
Hi,
with this now available, do you have a short how-to ?
I try to follow the teaser but with no luck. the lb gen … command to generate the changeset does not show up in the help lb .
Thanks,
Knut
SQL> lb genobject help _.xml in the current working directory. LB GENOBJECT
Generate a change log for the object identified by supplied object_type and object_name.
Outputs a file named
EXAMPLE:
lb genobject table employees
Creates - employees_table.xml
Database Object Types Supported:
AQ_QUEUE AQ_QUEUE_TABLE AQ_TRANSFORM
ASSOCIATION AUDIT AUDIT_OBJ
CLUSTER CONSTRAINT CONTEXT
DB_LINK DEFAULT_ROLE DIMENSION
FGA_POLICY FUNCTION INDEX
JOB LIBRARY MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG OBJECT_GRANT OPERATOR
PACKAGE_SPEC PACKAGE_BODY PROCEDURE
PROFILE PROXY PUBLIC_SYNONYM
REF_CONSTRAINT REFRESH_GROUP RESOURCE_COST
RLS_CONTEXT RLS_GROUP RLS_POLICY
RMGR_CONSUMER_GROUP RMGR_PLAN RMGR_PLAN_DIRECTIVE
ROLE ROLLBACK_SEGMENT SEQUENCE
SYNONYM TABLE TABLESPACE
TRIGGER TRUSTED_DB_LINK TYPE
TYPE_SPEC TYPE_BODY USER
VIEW XMLSCHEMA XS_USER
XS_ROLE XS_ROLESET XS_ROLE_GRANT
XS_SECURITY_CLASS XS_DATA_SECURITY XS_ACL
XS_ACL_PARAM XS_NAMESPACE RMGR_INTITIAL_CONSUMER_GROUP
SQL>
Got it and like it.
Knut
Can’t wait for it.
Can those changes be tagged with an task-is?
yes, but manually – we didn’t implement tagging support in the SQLcl interface, but you can of course add them on your own
I wrote a post about source control for your database (DDL) in my blog in which I mentioned one of your posts about using SQL Developer to dump the DDL that you can then put under source control. You replied asking me if I had tried Liquibase, and I’m pretty sure that you were thinking about the coming feature that you posted about here.
My answer was that I had looked at Liquibase, and I didn’t see much advantage in this over plain SQL scripts. In my new job, I have encountered an open source tool that I had never heard of before, and I wish I’d known about it sooner. It is Flyway (https://flywaydb.org/) and it is awesome. It uses my preferred SQL scripts for deployment. For Oracle databases, it even supports most SQL*Plus commands. Check it out.
We are in the process of going down the LIQUIBASE path and are very interested in this feature as soon as possible. If you need a willing partner to work with you on it please contact me.
Thanks !!!!!
Wow this seems really useful. Great work Jeff!
This is awesome, is it possible to have a current release of the command line tool in order to try it before adoption ?
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.