Liquibase

Liquibase

Liquibase

Liquibase is a tool used to assist deploying database scripts to a database. You load it with scripts and it will execute them into the database. Subsequent liquibase executions will compare the scripts to see if they have already been loaded. If they have they will be skipped.

The thoughts are:

  • it has to be easy to use.
  • When you want to change a database structure, you create 2 scripts. One script to change the databse, another script to rollback your database changes. (SQL Scripts, for XML it knows how to roll itself back)
  • Liquibase will update the database based on the collection scripts fed to it.

Liquibase has a number of concepts.

  • A file used to control the order of scripts. This is called a changelog.
  • A table in the database holding a list of scripts that have been executed. The table name is DATABASECHANGELOG
  • Database scripts called changesets are added to the changelog. These are commands to update the structure of the database.
  • A command line tool (using the Update command) makes the changes to the system. It will read the changelog, looking for changesets. If the changeset hasn’t been listed in the DatabaseChangeLog, then then that changeset (or script) is applied to the database

Liquibase has a few more ideas.

  • There’s a open source license (free) and a professional license (you pay for, I’m not sure the price). The professional version has lots of other features.
  • The Liquibase ChangeLog can be written in a number of different file formats, for example SQL, XML, YAML, JSON. The recommended language is XML, however all of those formats work.
  • Liquibase supports updating a lot of different databases. Here are a few db codes (asany,cockroachdb, db2, db2z, derby, edb, firebird, h2, hsqldb, informix, ingres, mariadb, mssql, mysql, oracle, snowflake, sqlite, sybase, postgresql)
  • The documentation on the website is pretty good - possibly great, except finding examples are hard.
  • ??? Liquibase supports a number of different databases. I wanted to use SQL Server, so I had to download a jdbc driver fo sql at the following address: https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16 ??? Maybe not

Terminology

  • ChangeLog - this is the table containing a list of changes that are made to the database. It contains references to changesets, which are the actual database changes.
  • ChangeSet - This is a database change script. Just remember, you cannot (or should not) change these files if they have already been applied to the database.
  • Change Type - This is a type of change you will make to a database. (for example add table, or add column)
  • liquibase.properties is a text file holding configuration information.

Basic Steps

https://www.liquibase.org/get-started/quickstart provides a pretty good process on getting setup.

Download the application.

Install application

I installed in e:\p\liquibase

If you are using SQL Server

And you want to use .SQL files with liquibase, then you aught to install the JDBC device driver available from here: https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16

Create a project.

My notes API is in e:\dev\notes, so I created a folder e:\dev\notes\liquibase and in that folder executed 

1
liquibase init project

Configure the liquibase.properties to access your database.

Here is a sample of mine

classpath=E:\\p\\liquibase\\internal\\lib\\mssql-jdbc.jar
driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://127.0.0.1:1434;databaseName=notes;encrypt=false;user=liquibase_user;password=liquibase_user;
username=liquibase_user
password=liquibase_user
changeLogFile=notes_changelog.xml

where

  • Classpath is where the liquibase was installed
  • URL is a pointer to the database
  • User, password, username, password - database access. This account will need database structure access.
  • changeLogFile - This will be be where you add your change sets

open a command prompt and navigate to the folder where you added the liquibase.properties file.

execute a command similar to

liquibase generate-changeLog --changelog-file=notes_changelog.xml

This created a file notes_changelog.xml containing a data dictionary of the database. It also created 2 tables in the database, DATABASECHANGELOG, and DATABASECHANGELOGLOCK.

If you examine the file notes_changelog.xml - you’ll see a couple of changeset’s with author and id tags. These are your database definition.

Execute the following command

liquibase changelog-sync --changelog-file=notes_changelog.xml

What this did was populated the DATABASECHANGELOG to indicate those objects are loaded into the database.

If you do a select * from databasechangelog, you’ll see where those author/id combination has been registered in the database.

Execute the following command

liquibase update

or
liquibase update –changelog-file=notes_changelog.xml

This command will scan the changelog-file and look for changesets. It will check the changeset’s Author/ID against the database, and if it’s found in the database, then that changeset will be skipped. If it doesn’t exist then the database will be updated.

System is ready to use

At this point, you are now ready to add changesets to the notes_changelog.xml file. When you make a change, save the file, and then execute the following command:

liquibase update

If things look ok then changes to the database will be posted to the database. Otherwise you’ll get an error message..