Liquibase

1.liquibase-basics.md

Liquibase

Parameters can be passed via commandline or using properties file

Important

Using the CLI to enter command-line arguments will always override the properties stored in the Liquibase properties file.

Help Command

liquibase <command name> --help

DB connection Parameters

url=jdbc:postgresql://localhost:5432/DEVDB
username=<name>
password=<password>

changelog parameter

changelog-file=yourchangelog.xml

Log Level Parameter

log-level=FINE

Level	Description
SEVERE (highest level)	Serious failures that may prevent program execution.
WARNING	Potential problems for program execution.
INFO	Informational messages. Complete list of the command operation.
FINE (lowest level)	Tracing information on the program execution and minor failures. Helpful in resolving errors.
OFF (default)	Hides all log messages.

update-sql command

liquibase --changelog-file=yourchangelog.xml update-sql

update command

When running the update command, Liquibase reads the changesets in the changelog file in sequential order and then compares the unique identifiers of id, author, and path to the changelog to the values stored in the DATABASECHANGELOG table. If the unique identifiers do not exist, Liquibase will apply the changeset to the database.

liquibase --changelog-file=yourchangelog.xml update

There are a few variations of this command. some notable ones are

• update-count
• update-to-tag

To verify the sql generated by liquibase add sql to the command <command>-sql like

• update-sql
• update-count-sql
• update-to-tag-sql

history Command

The history command will list the time the database was updated and the list of executed changesets with their associated deploymentId. The output can also be used to determine which Liquibase commands need to run.

liquibase history

liquibase --outputFile=history.txt history

status Command

The status command is usually run with the --verbose command attribute which lists undeployed changesets along with the path to the changeset, author, and id.

liquibase status --verbose

tag command

The tag command can be run to mark the last row inserted in the DATABASECHANGELOG table

liquibase tag release-1.0.1

To add tag to the changelog refer tagDatabase

rollback Commands

When changes are rolled back, the row associated with that change is deleted from the DATABASECHANGELOG table.

rollback-to-date

liquibase rollback-to-date <date in YYYY-MM-DD or YYYY-MM-DD'T'HH:MM:SS>

# To validate sql
liquibase rollback-to-date-sql 2025-01-25

rollback-count

liquibase rollback-count 5 

# To validate sql
liquibase rollback-count-sql 5

rollback

liquibase rollback <tagname>

custom rollbacks changesets

To add a custom rollback refer Custom rollback statements

ex

<changeSet author="liquibaseuser" id="2">
    <dropTable tableName="person"/>
        <rollback>
            <createTable catalogName="department"
                remarks="A String"
                schemaName="public"
                tableName="person">
                <column name="address" type="varchar(255)"/>
            </createTable>
    </rollback>
</changeSet>

Testing rollback

Refer update-testing-rollback command

Diff Commands

see changes made to DB. They need extra parameters referenceUsername, referencePassword, referenceURL

you can also filter using --diffTypes=<catalogs,tables,functions,views,columns,indexes,foreignkeys>

diff

compare 2 databases or database to snapshot

diff-changelog

compare 2 databases and generate changelog file

liquibase  --changelog-file=file_name.sql  --username=<USERNAME> --password=<PASSWORD>
--referenceUsername=<USERNAME> --referencePassword=<PASSWORD> diff-changelog

snapshot Commands

• Snapshots are great for keeping a record of the current database state but snapshots cannot be used to repopulate a database.
• To record a database state in a format that will allow for re-creating that state, use the generate-changelog command.

snapshot

Used to capture the current state of the database.

liquibase --output-file=yourSnapshot.json snapshot --snapshotFormat=json

generate-changelog

Used to create a changelog file that describes how to re-create the current state of the database.

liquibase --changelog-file=newgeneratedchangelog.xml generate-changelog

2.changelogs.md

Changelogs

Four components of a changelog:

• Global preconditions (changelog level)
  • Refer preconditions
• Changelog headers
• Changesets
• Changeset attributes

Labels

Labels are strings that can be added to changesets to provide users the ability to group and classify changesets with the use of label expressions.

Label expressions provide users the ability to apply complex filtering logic to their deployment at runtime. During Liquibase execution time, the label expression acts as a filter to control precisely which changesets will be executed.

<changeSet id="1" author="example" labels="1.0, pro, shopping_cart"> 

<changeSet id="1.1.1" author="example" labels="1.1, shopping_cart"> 
<changeSet id="1.1.2" author="example" labels="1.1, dashboard"> 
...

<changeSet id="2" author="example" labels="2.0,test"> 

you can selectively run the changesets using label-filter param

liquibase --output-file=update.txt update --changelog-file=changelog.xml --label-filter="1.0 or (1.1 and !shopping_cart)"

• labels
• labelfilterlogic

Context

Contexts are typically used to control which changesets to apply to a specified environment. For example, you may use contexts to indicate which changesets should run in a DEV, TEST, or QA environment.

Note

The main difference to labels is that conditions(AND, OR, !) should be added in the changeset itself and not passed during runtime

Contexts may also be applied to the include and includeAll tags. Files referenced by the include tag will follow the same context logic as contexts specified on individual changesets.

<changeSet author="Liquibase User" id="10" context="Customer_A">
    <createTable schemaName="proschema" tableName="province_table">
        <column name="province" type="CHAR(20)"/>
    </createTable>
</changeSet>
<changeSet author="Liquibase User" id="20" context="QA AND !Customer_B">
    <createTable schemaName="proschema" tableName="state_table">
        <column name="state" type="CHAR(20)"/>
    </createTable>
</changeSet>

you can selectively run the changesets using context-filter param

liquibase update --context-filter="QA,Customer_A" --changelog-file=example-changelog.xml

Preconditions

Preconditions are tags you add to your changelog or individual changesets to control the execution of an update based on the state of the database. Read Preconditions to see all available ones

<preConditions>
   <columnExists tableName="table1" columnName="column1" />
</preConditions>

Use the attributes such as onFail/onError to control precondition behavior.

<preConditions onFail="HALT" onFailMessage="Customer table is locked">
   <sqlCheck expectedResult="0">
      SELECT count(1) FROM public.customer where locked = 'true';
   </sqlCheck>
</preConditions>

onFail or OnError	changelog level	changeset level
HALT	✔️	✔️
WARN	✔️	✔️
CONTINUE		✔️
MARK_RAN		✔️

Preconditions can also be nested or wrapped with <and>, <or>, and <not> tags. The default logic is <and> if no conditional tag is specified

<preConditions>
    <or>
       <and>
          <dbms  type="oracle"  />  
          <runningAs  username="SYSTEM"  />
       </and>
       <and>
          <dbms  type="mssql"  />
          <runningAs  username="sa"  />
       </and>
     </or>
</preConditions>

runOnChange

The runOnChange attribute is useful for SQL scripts such as stored procedures. The runOnChange attribute executes the first time the changeset is encountered and each time the changeset is modified.

When using the runOnChange attribute, the SQL must be written in a way to be re-runnable such as using CREATE OR REPLACE for stored logic statements like functions, procedures, and views.

<changeSet  author="your.name"  id="changeset01"  runOnChange="true" >
    <createProcedure>
    . . .
    </createProcedure>
</changeSet>

runAlways

You can use the runAlways attribute to execute a changeset every time you make a database deployment, even if it has been run before.

<changeSet  id="1"  author="your.name"  runAlways="true">
    <update  tableName="deployment_log">
        <column  name="latest"  type="DATETIME"/>
    </update>
</changeSet>

Warning

Liquibase will throw a checksum error if you modify the contents of an existing runAlways changeset. If you need to make a change to an existing runAlways changeset READ

runOrder

The runOrder attribute specifies whether a changeset should be run before or after all other changesets instead of running it sequentially based on its order in the changelog. Valid values are first and last.

<changeSet  id="1"  author="your.name"  runOrder="last"  runAlways="true">
    <update  tableName="deployment_log">
        <column  name="latest"  type="DATETIME"/>
    </update>
</changeSet>

tags

tags can be used to rollback at a later stage

<changeSet author="nvoxland" id="tag-release25">
    <tagDatabase tag="release25">
</changeSet>

comments

• XML: <comment>My comment goes here</comment>
• SQL: --comment: My comment goes here
• JSON: "comment": "My comment goes here",
• YAML: comment: My comment goes here

<changeSet author="nvoxland" id="DB-1013" >
    <comment>New table: customer</comment>
    <sqlFile path="main/100_ddl/customer.sql"/>
</changeSet>

you can also create documentation

liquibase --changelog-file=dbchangelog.xml db-doc mychangelogDocs