Real-time monitoring for Liquibase database changes is here!

Using Open Source Liquibase, Titan & Jenkins

Titan goes a long way in solving the problem of database delivery and speeding development workflows. It’s as simple as cloning an existing database locally, making changes, and persisting those changes for others to use. We’ve had the process for a while with application code development. It’s about time we have that for the data.

Of course, the next challenge is how do we get those structural changes (i.e., DDL for new tables, columns, etc.) to our later environments. We can’t just ship a Titan commit hash over to the SRE team and say “Use this one!”. We must evolve our database structure so that we do not lose production data. By using Liquibase and Titan together, we can speed development efforts and accelerate our software delivery to test, production, and all points in between.

You need to decrease the time setting up databases for developers (Titan) and you need to store your database changes in source code control (Liquibase + Jenkins).

Tutorial

  1. Download Titan. It’s helpful to read through this post for details on how to set Titan up.
  2. Get your system ready.
    • Create a directory for the Postgres JDBC jar. I used /home/r2/jdbc. Make sure those are world-readable as you’re going to mount that on the Liquibase docker container.
    • Create a directory for the changelog. I used /home/r2/changelog and made sure it was world-readable so the Liquibase container can write to it.
    • You need to determine the IP address your Titan Postgres container is running on. Just issue docker network inspect bridge and find the IP address of your titan container. Mine was 172.17.0.3.
    • Create an alias to avoid lengthy arguments. Remember to set update ${JAR}.
    alias liquibase=”docker run -v /home/r2/jdbc:/liquibase/jdbc -v /home/r2/changelog:/liquibase/changelog liquibase/liquibase --driver=org.postgresql.Driver --classpath=/liquibase/jdbc/${JAR} --url=”jdbc:postgresql://172.17.0.3:5432/postgres” --changeLogFile=/liquibase/changelog/changelog.xml --username=postgres --password=postgres”

     

  3. Next, let’s get a changelog for our Titan-provided Postgres database:
    liquibase generateChangeLog

    This will create a changelog.xml file that is the baseline of your database. You can view it on your local system in your “changelog” folder.

    Pro tip: Liquibase supports XML, JSON, YAML, and annotated SQL. Simply change the extension to get a different format (e.g., changelog.xml becomes changelog.yaml). SQL would have to be changelog.postgres.sql to tell Liquibase you want a Postgres compatible annotated SQL file.

  4. Next, you need to assert that your newly created changelog has already been persisted to the schema. Of course, by definition it already has. So, we’re going to synchronize this file with a schema. Liquibase does this by creating a table DATABASECHANGELOG.
    liquibase changeLogSync

     

  5. Examine the table with the following command:
    psql postgres://postgres:postgres@localhost/postgres -t -c 'SELECT * FROM DATABASECHANGELOG;'

    This will create a changelog.xml file that is the baseline of your database. You can view it on your local system in your “changelog” folder.

  6. Finally, persist the updated database to the Titan server with the following:
    titan commit -m "baselined with Liquibase" hello-world

    Checkin the changelog.xml file into the same source code repository as your application.

  7. Now let’s make a change. But instead of just going to the database and monkeying around with SQL, let’s add the following new changeset to the XML.
    <changeSet author=”r2" id=”2">
       <createTable tableName=”newtable”>
          <column name=”colname” type=”VARCHAR(255)”/>
       </createTable>
    </changeSet>

    Save the file and run the following to make sure you have updated the file correctly:

    liquibase validate

     

  8. Now issue this to see the SQL that will be run on the server:
    liquibase updateSQL

     

  9. Finally, run the following to persist the new table:
    liquibase update

    You can also verify that the table was created:

    psql postgres://postgres:postgres@localhost/postgres -t -c ‘\d newtable;’

     

  10. Once it all looks good commit, your data changes to Titan and your updated changelog to source code control.
    titan commit -m "Created table newtable with Liquibase." hello-world

    That’s it! You’ve done it. Now you can use Liquibase to update database schema change in non-development environments. After all, your software isn’t done until it’s in production. That’s the DevOps way.

Presenters
Robert Reeves Co-Founder & CTO of Liquibase
Adam Bowen