List All Liquibase SQL Types With Java

1. Introduction

In this article, we’ll explore the various SQL data types supported by Liquibase. We’ll also examine how they are represented across databases such as MySQL, PostgreSQL, Oracle, and more.

2. What Is Liquibase

Liquibase is an open-source database schema change management tool that helps track, version, and deploy database changes. It’s commonly used in Java applications to automate database migrations. With Liquibase, we can manage database changes the same way we manage application code. This helps speed up continuous integration and delivery and maintains version control over database changes.

2.1. Using Liquibase in a Project

To use Liquibase in our project, we first need to add the latest liquibase-core dependency in our Maven pom.xml file:

<dependency> 
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>4.27.0</version> 
</dependency>

Liquibase supports a wide variety of databases such as MySQL, PostgreSQL, Oracle, and many others. Once our database is ready, we utilize a liquibase.properties file to define the connection settings. The properties file contains fields such as the database host URL, connection credentials, and driver information:

changeLogFile=db/changelog/db.changelog-master.yaml
url=jdbc:mysql://localhost:3306/mydatabase
username=dbuser
password=dbpassword
driver=com.mysql.cj.jdbc.Driver

The most important step comes next, where we define a changelog file.

2.2. The Changelog File

A changelog file acts as a single source of truth and stores the history of the changes in our database over time. The changelog file can be of any format, with the most common formats being SQL, XML, JSON, and YAML.

We should note that changelogs on an existing database can be generated automatically with Maven:

mvn liquibase:generateChangeLog

Let’s take a better look at the changelog file. A changelog consists of a sequence of changesets, each having an identifier (id). A single changeset describes an individual modification to the database. Liquibase supports a plethora of DML changes such as:

• createTable
• addColumn
• dropColumn
• update
• insert
• sql (for custom SQL)

Here’s an example changelog written in XML:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet id="1" author="baeldung">
        <createTable tableName="baeldung_articles">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="varchar(255)"/>
        </createTable>
    </changeSet>

    <changeSet id="2" author="baeldung">
        <addColumn tableName="baeldung_authors">
            <column name="name" type="varchar(255)"/>
        </addColumn>
    </changeSet>
</databaseChangeLog>

2.3. Structure of an Individual Changeset

An individual changeset header contains the following attributes:

• id: This is a mandatory attribute required to identify the changeset. This can be any alphanumeric value.
• author: The identifier of the changeset‘s author goes here.
• context: This is an optional field mainly used to specify the environment it should run on (development, production).
• label: This is another optional field and can be used to supply additional context around the change.

The change itself can be broken down into several types of database modifications. Some of the common changes that we can use are:

• createTable
• addColumn
• dropTable
• insert

Liquibase offers transactional support for changesets under a changelog. Therefore, if an individual changeset fails, the entire changelog rolls back. 

3. SQL Data Types Support in Liquibase

Liquibase in Java supports a plethora of databases. However, it’s important to acknowledge that databases support database data types differently. For example, a character data type might vary in size and name across multiple databases. Some databases might support UUID natively while others might rely on character or string types to store such values.

3.1. The Need for Uniformity in Data Types

Liquibase offers change management solutions for databases. Therefore, Liquibase needs to support database independence. This is achievable only if Liquibase can standardize data types across all the supported databases. A single changeset should be able to execute regardless of the underlying database. Developers should also be able to create a changeset without much concern for the database platform it might run on. 

3.2. Database Independence

Liquibase offers true database independence by implementing three solutions:

• Standardized Databases: Liquibase defines a standard set of data types most widely used across various database platforms like int, timestamp, and text. Developers can freely use these data types without thinking about the underlying database in use. 
• Automatic Conversion of data types: Liquibase automatically converts the data type to a platform-specific one. For example, a text Liquibase type will be automatically converted into clob in Oracle database and varchar in SQL Server.
• Configurable: Liquibase exposes an additional configurable parameter, convert-data-types. We supply this parameter if we do not want to perform automatic data type conversion. If this value is false, we must provide exact data types matching the target database.

3.3. Data Types Provided by Liquibase

Liquibase provides several data types for SQL out of the box:

bigint, blob, boolean, char, clob, currency, datetime, date, decimal, double, float, int, mediumint, nchar, nvarchar, number, smallint, time, timestamp, tinyint, uuid, varchar.

4. Conversion Chart for All the Liquibase Data Types

Liquibase manages the auto conversion of data types. However, it’s still a good practice for the developers to understand how the data type might map according to the host database. In this section, we’ll provide an exhaustive list of these mappings for the most commonly used data types:

5. List All Data Types Programmatically

We can employ a Java method to extract the compatibility of the different SQL types with Liquibase. We start by creating two lists. The first list stores the compatible databases supported by Liquibase:

private static List getDatabases() { 
    return List.of(
        new MySQLDatabase(), 
        new SQLiteDatabase(), 
        new H2Database(), 
        new PostgresDatabase(), 
        new DB2Database(), 
        new MSSQLDatabase(), 
        new OracleDatabase(), 
        new HsqlDatabase(), 
        new FirebirdDatabase(), 
        new DerbyDatabase(), 
        new InformixDatabase(), 
        new SybaseDatabase(), 
        new SybaseASADatabase()
    ); 
}

The second list stores the exhaustive list of datatypes that we want to check:

private static List<LiquibaseDataType> getDataTypes() {
    return List.of(
        new BooleanType(),
        new TinyIntType(),
        new IntType(),
        new MediumIntType(),
        new BigIntType(),
        new FloatType(),
        new DoubleType(),
        new DecimalType(),
        new NumberType(),
        new BlobType(),
        new DateTimeType(),
        new TimeType(),
        new TimestampType(),
        new DateType(),
        new CharType(),
        new VarcharType(),
        new NCharType(),
        new NVarcharType(),
        new ClobType(),
        new CurrencyType(),
        new UUIDType()
    );
}

Next, let’s iterate through the mentioned lists of Liquibase data types (dataTypes) and database types (databases) and try to convert each Liquibase data type into a database-specific data type for every database. We also print the data type’s name and database-specific type for every combination. Additionally, we handle exceptions gracefully to continue processing in case of errors:

List<LiquibaseDataType> dataTypes = getDataTypes();
List<AbstractJdbcDatabase> databases = getDatabases();

for (LiquibaseDataType dataTypeInstance : dataTypes) {
    try {
        LiquibaseDataType dataType = dataTypeInstance;
        dataType.finishInitialization("");
        System.out.println(dataType.getName());

        for (AbstractJdbcDatabase databaseInstance : databases) {
            try {
                Database database = databaseInstance;
                String databaseType = dataType.toDatabaseDataType(database)
                  .toString();
                System.out.println(databaseInstance.getName() + ": " + databaseType);
            } catch (Exception e) {
                System.err.println("Error initializing database class "
                            + databaseInstance.getName() + ": " + e.getMessage());
            }
        }
        System.out.println();
    } catch (Exception e) {
        System.err.println("Error initializing data type class "
                            + dataTypeInstance.getName() + ": " + e.getMessage());
    }
}

6. Conclusion

In this article, we looked at how Liquibase provides common data types and helps developers manage database versions.