1. Introduction
In the realm of database management, ensuring consistency and traceability of schema changes is a good practice for maintaining data integrity and application reliability. Liquibase, a widely adopted database schema change management tool, helps us version control and track database changes seamlessly across various environments.
However, as applications grow in complexity, it becomes imperative to organize database objects efficiently. While PostgreSQL defaults to the public schema, best practices advocate for segregating application-specific entities into dedicated schemas.
In this tutorial, we’ll delve into running Liquibase in a custom schema and how to tackle the challenge of creating the PostgreSQL schema before Liquibase execution. Moreover, we’ll demonstrate it using a simple Spring Boot application connected to a PostgreSQL database.
2. Liquibase
Liquibase serves as a solution for managing database schema changes. It helps us by facilitating faster and safer revision and deployment of database modifications across various stages, from development to production.
It uses SQL, XML, JSON, and YAML Changelog files to list database changes sequentially. Database changes are named Changesets. Changesets, in turn, contain Change Types, which are operations to apply to the database, such as creating a table or adding a column. Liquibase stores all applied changes in the database to determine which new changes should be applied when a schema update is requested.
3. Application Setup
First, we need to set up a simple Spring Boot application that uses PostgreSQL as the database engine and Liquibase to generate tables. We’ll opt for Java 17 and Spring Boot 3 because we’ll need them later for a third-party library.
3.1. Maven Dependencies
Let’s start configuring our application by adding the spring-boot-starter-web, spring-boot-started-data-jpa, liquibase-core, and postgresql dependencies:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>3.2.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>3.2.3</version>
</dependency>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
<version>4.26.0</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.2</version>
<scope>runtime</scope>
</dependency>
3.2. PostgreSQL Configuration
Now, we need to add some properties to our application.properties file to use PostgreSQL as a database:
spring.datasource.url=jdbc:postgresql://some-postgresql-host/some-postgresql-database
spring.datasource.username=your-postgresql-username
spring.datasource.password=your-postgresql-password
Note that we’ll need to replace these properties with proper values matching our environment.
3.3. Entity
Moving on, let’s simulate an application of user management. Let’s begin by defining a simple User entity:
@Entity
@Table(name = "users")
public class User {
@Id
private Long id;
private String name;
// standard getters and setters
}
3.4. Liquibase Changelog
Next, we’ll use Liquibase to create the database table at startup. To do so, we need to create a Changelog file:
<?xml version="1.0" encoding="utf-8"?>
<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.4.xsd">
<changeSet id="1" author="unknown">
<createTable tableName="users">
<column name="id" type="bigint">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="name" type="varchar(255)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>
In the application.properties file, we need to specify to Liquibase which Changelog file to use:
spring.liquibase.change-log=classpath:liquibase/changelog.xml
If we run our application, we’ll see that the Changeset specified in our Changelog is applied, and three tables are created in the public schema. This includes our users table and two tables automatically created by Liquibase: databasechangelog and databasechangeloglock.
4. Operating Liquibase Within a Personalized Schema
As we’ve mentioned at the beginning of this article, a preferred approach is to operate Liquibase within a custom schema. This leads to better organization and clarity in managing database objects. Also, custom schemas provide a level of isolation and separation of concerns, allowing different components or modules of an application to operate independently.
To address our example, let’s imagine our user management application has become some kind of a Modulith: We’re now in charge of other things related to users, like permissions. Therefore, we would benefit from having different schemas to separate each concept.
By default, Liquibase operates in the public schema. To change this, we need to override the default-schema property in our application.properties file:
spring.liquibase.default-schema=user_management
This approach leads to Liquibase running completely within the user_management schema, and the databasechangelog and databasechangeloglock tables are also created in our custom schema.
Another approach would be to modify our Changeset, specifying the schema for table creation there instead:
<changeSet id="1" author="unknown">
<createTable tableName="users" schemaName="user_management">
<column name="id" type="bigint">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="name" type="varchar(255)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
This approach will lead to databasechangelog and databasechangeloglock tables being created in the public schema while the users table is created within the user_management schema.
However, the common drawback of these two approaches is that the schema must already exist before Liquibase can execute our migrations. If the schema we’re targeting doesn’t exist, our application won’t start. In the following sections, we’ll explore a few solutions to create a PostgreSQL schema before Liquibase execution.
5. Creating PostgreSQL Schema Before Liquibase Execution
5.1. Custom Changeset
Although Liquibase doesn’t have a bundled Change Type for schema creation, we can still include it using the sql tag. Thus, we will create a new Changelog for this use case, called changelog-customChangeset.xml, adding a Changeset to create the schema we would like to target:
<?xml version="1.0" encoding="utf-8"?>
<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.4.xsd">
<changeSet id="0" author="unknown">
<sql>
CREATE SCHEMA IF NOT EXISTS user_management;
</sql>
</changeSet>
<changeSet id="1" author="unknown">
<createTable tableName="users" schemaName="user_management">
<column name="id" type="bigint">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="name" type="varchar(255)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>
This solution is not compatible with setting the default schema for Liquibase because the Changesets will be executed after creating the two tables specific to Liquibase. Therefore, the databasechangelog and databasechangeloglock tables will still be placed in the public schema.
5.2. Spring Bean
To be able to override the default schema for Liquibase, we need to make sure that the schema already exists. One option is to create a SchemaInitializer component that creates our schema after the initialization of the DataSource bean:
@Component
public class SchemaInitializer implements BeanPostProcessor {
@Value("${spring.liquibase.default-schema}")
private String schemaName;
@Override
public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
if (bean instanceof DataSource) {
DataSource dataSource = (DataSource) bean;
try {
Connection conn = dataSource.getConnection();
Statement statement = conn.createStatement();
statement.execute(String.format("CREATE SCHEMA IF NOT EXISTS %s", schemaName));
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return bean;
}
}
5.3. Pre-Liquibase
Pre-Liquibase is a module that allows us to execute some SQL scripts before executing Liquibase Changesets. Typically, it’s used to create a database schema or database catalog.
Since it’s a Spring Boot auto-configuration module, we only need to add a dependency and the SQL script we want to execute before Liquibase execution. T****his module requires a minimum of Java 17 and Spring Boot 3.1.
To use this module, we’ll proceed with adding the preliquibase-spring-boot-starter dependency:
<dependency>
<groupId>net.lbruun.springboot</groupId>
<artifactId>preliquibase-spring-boot-starter</artifactId>
<version>1.5.0</version>
</dependency>
Pre-Liquibase has a built-in platform auto-detection feature. After detecting the database platform, it decides which SQL script to run, so we need to name our script postgresql.sql.
Let’s view the content of our script:
CREATE SCHEMA IF NOT EXISTS user_management;
6. Conclusion
As we’ve learned throughout this article, there are several ways to run Liquibase within a personalized schema and create it before Liquibase execution. By leveraging custom schemas, we can enhance organization, access control, and isolation of database objects, leading to more scalable and maintainable applications.
As always, the source code is available over on GitHub.