How to use JSON, XML, YAML Liquibase changeSets in SQLcl

When talking about our Liquibase support in Oracle SQLcl, I tend to carried away by our automated SXML changeSets that we generate for you. SQLcl ALSO supports JSON, XML, YAML Liquibase changeSets!

Our automated SXML is the workflow where you do some database work, then you use your current schema state to be versioned and stored as Liquibase changeLog with changeSets for each schema object. This is accomplished with the the generate-schema command.

The benefit of this approach is we manage your changeLogs, ordering the changeSets, and figure out what SQL/PLSQL to apply to your system for you. Oh, and we also generate automatic rollback changeSets.

Most if not all of my demos assume you are on THIS path, or way of doing things.

But – you don’t have to use our (SQLcl generated) changeLogs!

You have more options, you can run your ‘classic’ Liquibase changeSets with SQLcl! So let’s show a bit of that (YAML, JSON, & XML.) So if you’ve been using Liquibase for awhile, you can leverage your existing changeLogs with SQLcl.

If you’re looking to see how to run SQL scripts through SQLcl’s script runner via a changeSet, that’s a different post.

YAML changeSet to create a table

databaseChangeLog:
 - changeSet:  
    id:  createTable-example  
    author:  thatjeffsmith  
    changes:  
    -  createTable:  
        columns:  
        -  column:  
            name:  ADDRESS  
            type:  varchar2(255)  
        remarks:  A String  
        tableName:  PERSON  
        tablespace:  USERS

Now let’s run this into an empty schema I’ve created called ‘LIQUIBASE.’

And let’s go look at our new table –

JSON changeSet to add columns to a table

Same table as above, but let’s add a couple of columns.

{
    "databaseChangeLog":[
       {
          "changeSet":{
             "id":"addColumn-example",
             "author":"thatjeffsmith",
             "changes":[
                {
                   "addColumn":{
                      "columns":[
                         {
                            "column":{
                               "name":"CITY",
                               "type":"varchar2(255)"
                            }
                         },
                         {
                            "column":{
                               "constraints":{
                                  "nullable":false
                               },
                               "name":"ZIPCODE",
                               "type":"varchar2(15)"
                            }
                         }
                      ],
                      "tableName":"PERSON"
                   }
                }
             ]
          }
       }
    ]
 }

And we’ll run that…

XML changeSet to insert a row

My table is lonely, it needs a record!

<?xml version="1.0" encoding="UTF-8"?>
 <databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext" 
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
  <changeSet  author="thatjeffsmith"  id="insert-example">  
     <insert tableName="PERSON">  
         <column  name="ADDRESS"  value="123 Maple LN"/>
         <column  name="CITY"     value="Minas Tirith"/>
         <column  name="ZIPCODE"  value="48169"/>
     </insert>  
  </changeSet>
</databaseChangeLog>

And let’s now run that.

I meant to insert ‘Minas Morgul’ if you were curious about the ZipCode, oops.

This demo was generated using SQLcl version 22.4

Version 22.4 of SQLcl was released just yesterday. It contains MANY Liquibase updates, improvements, and bug fixes. I’ll be doing more Liquibase with SQLcl show-and-tell over the next few weeks, months, …