Liquibase RefCard

Liquibase Philosophy
Liquibase, is an Apache Licensed open source library written in Java for tracking, managing and applying database changes. All changes to the database are stored in XML files and identified by a combination of an "id" and "author" tag as well as the name of the file itself. A list of all applied changes is stored in each database which is consulted on all database updates to determine what new changes need to be applied. As a result, there is no database version number but this approach allows it to work in environments with multiple developers and code branches.

Liquibase Philosophy

Liquibase, is an Apache Licensed open source library written in Java for tracking, managing and applying database changes.

All changes to the database are stored in XML files and identified by a combination of an "id" and "author" tag as well as the name of the file itself. A list of all applied changes is stored in each database which is consulted on all database updates to determine what new changes need to be applied. As a result, there is no database version number but this approach allows it to work in environments with multiple developers and code branches.

Liquibase Links
Liquibase Website: www.liquibase.org/ Liquibase Source Code: https://github.com/liquibase/liquibase Liquibase Manual: http://www.liquibase.org/documentation/

Liquibase Links

Liquibase Website: www.liquibase.org/
Liquibase Source Code: https://github.com/liquibase/liquibase
Liquibase Manual: http://www.liquibase.org/documentation/

CONCEPTS

CONCEPTS
`Changelog file`	Developers store database changes in text-based files on their local development machines and apply them to their local databases.
`Change Set`	Change Sets are uniquely identified by the "author" and "id" attribute along with with the location of the changelog file and are the units Liquibase tracks execution of.
`Changes`	Each changeset generally contains a change which describes the change/refactoring to apply to the database. Liquibase supports both descriptive changes that generate SQL for supported databases and raw SQL.
`Preconditions`	Preconditions can be applied to either the changelog as a whole or individual change sets. If a precondition fails, Liquibase will stop execution.
`Contexts`	Contexts can be applied to changesets to control which are ran in different environments. For example, some changesets can be tagged as "production" and others as "test". If no context is specified, the changeset will run regardless of the execution context

Changelog file

Developers store database changes in text-based files on their local development machines and apply them to their local databases.

Change Set

Change Sets are uniquely identified by the "author" and "id" attribute along with with the location of the changelog file and are the units Liquibase tracks execution of.

Changes

Each changeset generally contains a change which describes the change/refactoring to apply to the database. Liquibase supports both descriptive changes that generate SQL for supported databases and raw SQL.

Preconditions

Preconditions can be applied to either the changelog as a whole or individual change sets. If a precondition fails, Liquibase will stop execution.

Contexts

Contexts can be applied to changesets to control which are ran in different environments. For example, some changesets can be tagged as "production" and others as "test". If no context is specified, the changeset will run regardless of the execution context

FEATURES
Over 30 built-in database refactorings Extensibility to create custom changes Update database to current version Rollback last X changes to database Rollback database changes to particular date/time Rollback database to "tag" SQL for Database Updates and Rollbacks can be saved for manual review Stand-alone IDE and Eclipse plug-in "Contexts" for including/excluding change sets to execute Database diff report Database diff changelog generation Ability to create changelog to generate an existing database Database change documentation generation DBMS Check, user check, and SQL check preconditions Ability to split change log into multiple files for easier management Executable via command line, Ant, Maven, Servlet container, or Spring Support for 10 database systems

FEATURES

Over 30 built-in database refactorings
Extensibility to create custom changes
Update database to current version
Rollback last X changes to database
Rollback database changes to particular date/time
Rollback database to "tag"
SQL for Database Updates and Rollbacks can be saved for manual review
Stand-alone IDE and Eclipse plug-in
"Contexts" for including/excluding change sets to execute
Database diff report
Database diff changelog generation
Ability to create changelog to generate an existing database
Database change documentation generation
DBMS Check, user check, and SQL check preconditions
Ability to split change log into multiple files for easier management
Executable via command line, Ant, Maven, Servlet container, or Spring
Support for 10 database systems

Best Practices

Best Practices
Avoid multiple changes per changeset to avoid failed autocommit statements that can leave the database in an unexpected state. Partition the changelog. Instead of creating one huge changelog per application; create smaller sub-changelogs and use the include script statement to link these into the Master changelog. Maintain separate changelog for Stored Procedures and use `runOnChange="true"`. This flag forces LiquiBase to check if the Changeset was changed instead of just checking if it was run once before so it can be skipped. Try to write changesets in a way that they can be rolled back. e.g. use liquibase relevant change clause over `<sql>`. Include a `<rollback>` clause whenever a change doesn’t support out of box rollback. Always include a `<preconditions>` clause in critical changes e.g. before dropping a table it could be wise to check if the table has any data. Use `comments` in the change sets. They say "A stitch in time saves nine"! Do not ever `edit` a changeset (exceptions: `<rollback>` script, error handling, `<sql>` tags with `runOnChange="true"`) Leverage Liquibase to manage your Reference Data.

Avoid multiple changes per changeset to avoid failed autocommit statements that can leave the database in an unexpected state.
Partition the changelog. Instead of creating one huge changelog per application; create smaller sub-changelogs and use the include script statement to link these into the Master changelog.
Maintain separate changelog for Stored Procedures and use runOnChange="true". This flag forces LiquiBase to check if the Changeset was changed instead of just checking if it was run once before so it can be skipped.
Try to write changesets in a way that they can be rolled back. e.g. use liquibase relevant change clause over <sql>.
Include a <rollback> clause whenever a change doesn’t support out of box rollback.
Always include a <preconditions> clause in critical changes e.g. before dropping a table it could be wise to check if the table has any data.
Use comments in the change sets. They say "A stitch in time saves nine"!
Do not ever edit a changeset (exceptions: <rollback> script, error handling, <sql> tags with runOnChange="true")
Leverage Liquibase to manage your Reference Data.

Liquibase Core

Liquibase Core
`<!-- Liquibase --> <dependency> <groupId>org.liquibase</groupId> <artifactId>liquibase-core</artifactId> <version>${liquibase.version}</version> </dependency>`

<!-- Liquibase -->
<dependency>
	<groupId>org.liquibase</groupId>
	<artifactId>liquibase-core</artifactId>
	<version>${liquibase.version}</version>
</dependency>

Liquibase Maven Plugin configuration

Liquibase Maven Plugin configuration
<plugin> <groupId>org.liquibase</groupId> <artifactId>liquibase-maven-plugin</artifactId> <version>${liquibase.version}</version> <executions> <execution> <goals> <goal>update</goal> </goals> </execution> </executions> <configuration> <logging>debug</logging> <promptOnNonLocalDatabase>false </promptOnNonLocalDatabase> <driver>${db.driver}</driver> <url>${db.url}</url> <username>${db.username}</username> <password>${db.password}</password> <changeLogFile>${changelog}</changeLogFile> </configuration> </plugin>

<plugin>
 <groupId>org.liquibase</groupId>
 <artifactId>liquibase-maven-plugin</artifactId>
 <version>${liquibase.version}</version>
 <executions>
  <execution>
   <goals>
    <goal>update</goal>
   </goals>
  </execution>
 </executions>
 <configuration>
  <logging>debug</logging>
  <promptOnNonLocalDatabase>false
  </promptOnNonLocalDatabase>
  <driver>${db.driver}</driver>
  <url>${db.url}</url>
  <username>${db.username}</username>
  <password>${db.password}</password>
  <changeLogFile>${changelog}</changeLogFile>
 </configuration>
</plugin>

Maven Goals

Maven Goals
`dropAll`	Drops all database objects owned by the user. Note that functions, procedures and packages are not dropped.
`update`	Applies the DatabaseChangeLogs to the database. Useful as part of the build process.
`tag`	Writes a Liquibase tag to the database.
`rollback`	Invokes Liquibase rollbacks on a database.
`status`	Prints which changesets need to be applied to the database.
`updateTestingRollback`	Applies the DatabaseChangeLogs to the database, testing rollback. This is done by updating the database, rolling it back then updating it again.
`updateSQL`	Generates the SQL that is required to update the database to the current version as specified in the DatabaseChangeLogs.

dropAll

Drops all database objects owned by the user. Note that functions, procedures and packages are not dropped.

update

Applies the DatabaseChangeLogs to the database. Useful as part of the build process.

tag

Writes a Liquibase tag to the database.

rollback

Invokes Liquibase rollbacks on a database.

status

Prints which changesets need to be applied to the database.

updateTestingRollback

Applies the DatabaseChangeLogs to the database, testing rollback. This is done by updating the database, rolling it back then updating it again.

updateSQL

Generates the SQL that is required to update the database to the current version as specified in the DatabaseChangeLogs.

Liquibase Commands

Liquibase Commands
`Update`	Liquibase allows you to apply database changes you and other developers have added to the change log file.
`Rollback`	Liquibase allows you to undo changes you have made to your database, either automatically or via custom rollback SQL.
`Diff`	While the best way to track database changes is by adding change sets during development, there are times when being able to perform database diffs is valuable, particularly near the end of a project as a double-check that all required changes are included in the change log.
`SQL Output`	Depending on your development and release processes, you may not want Liquibase to directly update your database.
`DBDoc`	Using change information stored in the change logs and an existing database, Liquibase can generate database change documentation.

Update

Liquibase allows you to apply database changes you and other developers have added to the change log file.

Rollback

Liquibase allows you to undo changes you have made to your database, either automatically or via custom rollback SQL.

Diff

While the best way to track database changes is by adding change sets during development, there are times when being able to perform database diffs is valuable, particularly near the end of a project as a double-check that all required changes are included in the change log.

SQL Output

Depending on your development and release processes, you may not want Liquibase to directly update your database.

DBDoc

Using change information stored in the change logs and an existing database, Liquibase can generate database change documentation.

DDL - CREATE

DDL - CREATE
CHANGE	DESCRIPTION	ROLLBACK
`addAutoIncrement`	Converts an existing column to be an auto-increment column	`N`
`addColumn`	Adds a new column to an existing table	`Y`
`addDefaultValue`	Adds a default value to the database definition for the specified column. One of defaultValue, defaultValueNumeric, defaultValueBoolean or defaultValueDate must be set	`Y`
`addForeignKeyConstraint`	Adds a foreign key constraint to an existing column	`Y`
`addLookupTable`	Creates a lookup table containing values stored in a column and creates a foreign key to the new table.	`Y`
`addNotNullConstraint`	Adds a not-null constraint to an existing table. If a defaultNullValue attribute is passed, all null values for the column will be updated to the passed value before the constraint is applied.	`Y`
`addPrimaryKey`	Adds creates a primary key out of an existing column or set of columns.	`Y`
`addUniqueConstraint`	Adds a unique constrant to an existing column or set of columns.	`Y`
`createIndex`	Creates an index on an existing column or set of columns.	`Y`
`createProcedure`	Defines the definition for a stored procedure. This command is better to use for creating procedures than the raw sql command because it will not attempt to strip comments or break up lines.	`N`
`createSequence`	Creates a new database sequence	`Y`
`createTable`	Create Table	`Y`
`createView`	Create a new database view	`Y`

CHANGE

DESCRIPTION

ROLLBACK

addAutoIncrement

Converts an existing column to be an auto-increment column

N

addColumn

Adds a new column to an existing table

Y

addDefaultValue

Adds a default value to the database definition for the specified column. One of defaultValue, defaultValueNumeric, defaultValueBoolean or defaultValueDate must be set

Y

addForeignKeyConstraint

Adds a foreign key constraint to an existing column

Y

addLookupTable

Creates a lookup table containing values stored in a column and creates a foreign key to the new table.

Y

addNotNullConstraint

Adds a not-null constraint to an existing table. If a defaultNullValue attribute is passed, all null values for the column will be updated to the passed value before the constraint is applied.

Y

addPrimaryKey

Adds creates a primary key out of an existing column or set of columns.

Y

addUniqueConstraint

Adds a unique constrant to an existing column or set of columns.

Y

createIndex

Creates an index on an existing column or set of columns.

Y

createProcedure

Defines the definition for a stored procedure. This command is better to use for creating procedures than the raw sql command because it will not attempt to strip comments or break up lines.

N

createSequence

Creates a new database sequence

Y

createTable

Create Table

Y

createView

Create a new database view

Y

DDL - DROP/UPDATE

DDL - DROP/UPDATE
CHANGE	DESCRIPTION	ROLLBACK
`dropAllForeignKeyConstraints`	Drops all foreign key constraints for a table	`N`
`dropColumn`	Drop an existing column	`N`
`dropDefaultValue`	Removes the database default value for a column	`N`
`dropForeignKeyConstraint`	Drops an existing foreign key	`N`
`dropIndex`	Drops an existing index	`N`
`dropNotNullConstraint`	Makes a column nullable	`Y`
`dropPrimaryKey`	Drops an existing primary key	`N`
`dropSequence`	Drop an existing sequence	`N`
`dropTable`	Drops an existing table	`N`
`dropUniqueConstraint`	Drops an existing unique constraint	`N`
`dropView`	Drops an existing view	`N`
`alterSequence`	Alter properties of an existing sequence	`N`
`modifyDataType`	Modify data type	`N`
`renameColumn`	Renames an existing column	`Y`
`renameTable`	Renames an existing table	`Y`
`renameView`	Renames an existing view	`Y`

CHANGE

DESCRIPTION

ROLLBACK

dropAllForeignKeyConstraints

Drops all foreign key constraints for a table

N

dropColumn

Drop an existing column

N

dropDefaultValue

Removes the database default value for a column

N

dropForeignKeyConstraint

Drops an existing foreign key

N

dropIndex

Drops an existing index

N

dropNotNullConstraint

Makes a column nullable

Y

dropPrimaryKey

Drops an existing primary key

N

dropSequence

Drop an existing sequence

N

dropTable

Drops an existing table

N

dropUniqueConstraint

Drops an existing unique constraint

N

dropView

Drops an existing view

N

alterSequence

Alter properties of an existing sequence

N

modifyDataType

Modify data type

N

renameColumn

Renames an existing column

Y

renameTable

Renames an existing table

Y

renameView

Renames an existing view

Y

DML CHANGES

DML CHANGES
CHANGE	DESCRIPTION	ROLLBACK
`delete`	Deletes data from an existing table	`N`
`insert`	Inserts data into an existing table	`N`
`mergeColumns`	Concatenates the values in two columns, joins them by with string, and stores the resulting value in a new column.	`N`
`update`	Updates data in an existing table	`N`

CHANGE

DESCRIPTION

ROLLBACK

delete

Deletes data from an existing table

N

insert

Inserts data into an existing table

N

mergeColumns

Concatenates the values in two columns, joins them by with string, and stores the resulting value in a new column.

N

update

Updates data in an existing table

N

OTHER CHANGES

OTHER CHANGES
CHANGE	DESCRIPTION	ROLLBACK
`customChange`	Although Liquibase tries to provide a wide range of database refactorings, there are times you may want to create your own custom refactoring class.	`N`
`executeCommand`	Executes a system command. Because this refactoring doesn’t generate SQL like most, using LiquiBase commands such as migrateSQL may not work as expected. Therefore, if at all possible use refactorings that generate SQL.	`N`
`loadData`	Loads data from a CSV file into an existing table. A value of NULL in a cell will be converted to a database NULL rather than the string NULL	`N`
`loadUpdateData`	Loads or updates data from a CSV file into an existing table. Differs from loadData by issuing a SQL batch that checks for the existence of a record. If found, the record is UPDATEd, else the record is INSERTed. Also, generates DELETE statements for a rollback.	`N`
`sql`	The sql tag allows you to specify whatever sql you want. It is useful for complex changes that aren’t supported through Liquibase’s automated refactoring tags and to work around bugs and limitations of Liquibase. The SQL contained in the sql tag can be multi-line.	`N`
`sqlFile`	The sqlFile tag allows you to specify any sql statements and have it stored external in a file. It is useful for complex changes that are not supported through LiquiBase’s automated refactoring tags such as stored procedures.	`N`
`tagDatabase`	Applies a tag to the database for future rollback	`Y`

CHANGE

DESCRIPTION

ROLLBACK

customChange

Although Liquibase tries to provide a wide range of database refactorings, there are times you may want to create your own custom refactoring class.

N

executeCommand

Executes a system command. Because this refactoring doesn’t generate SQL like most, using LiquiBase commands such as migrateSQL may not work as expected. Therefore, if at all possible use refactorings that generate SQL.

N

loadData

Loads data from a CSV file into an existing table. A value of NULL in a cell will be converted to a database NULL rather than the string NULL

N

loadUpdateData

Loads or updates data from a CSV file into an existing table. Differs from loadData by issuing a SQL batch that checks for the existence of a record. If found, the record is UPDATEd, else the record is INSERTed. Also, generates DELETE statements for a rollback.

N

sql

The sql tag allows you to specify whatever sql you want. It is useful for complex changes that aren’t supported through Liquibase’s automated refactoring tags and to work around bugs and limitations of Liquibase. The SQL contained in the sql tag can be multi-line.

N

sqlFile

The sqlFile tag allows you to specify any sql statements and have it stored external in a file. It is useful for complex changes that are not supported through LiquiBase’s automated refactoring tags such as stored procedures.

N

tagDatabase

Applies a tag to the database for future rollback

Y