Utilizing Multiple Schemas in Spring Boot: A Practical Guide for Productive and Test Code

As we worked towards phasing out one of our database systems, we faced the task of migrating data into our established MySQL Database. However, the data didn't align with our current business data/model stored in the database. This led us to a decision: create an entirely new MySQL instance or establish a new schema within the existing database. Opting against a new database was driven by the complexity of setting up a fresh connection and configuration.

In this article, we'll share our approach to managing connections to different schemas within the same MySQL database. Without delving into the reasons for choosing MySQL over potentially more suitable data storage options, we'll focus on the practical aspects of handling multiple schemas effectively.

Configuration for the connection

Establishing a connection to a MySQL instance typically involves utilizing a connection URL. The URL structure commonly appears as follows:

 url: jdbc:mysql://<url>:<port>/<schemaName>?<additionalSettings> 

Fortunately, there was no need for us to alter anything in the connection URL. The <schemaName> parameter in the URL defined our existing schema, which, for simplicity's sake, we'll refer to as the default schema (even though it's not technically the default schema). Let's assume the default schema bears the name "schema1," while the new schema is labeled "schema2." Consequently, unless specified otherwise, all our application entities are stored in schema1.

Since there's no requirement for a new database connection, configuring elements such as the TransactionManager or the Hikari Connection Pool is unnecessary. It's crucial to be mindful of this, as it may entail some side effects that might go unnoticed (for instance, queries to schema2 utilizing the same Hikari Connection Pool as queries to schema1).

Implementation for Schema Creation and Table Addition

Firstly, you need to create a new schema in your database, a task achievable either manually or through a database change management tool. In our case, we opted for Liquibase. Liquibase lacks a preconfigured tag for schema creation, prompting us to write a custom SQL script:

   <changeSet author="kai" id="20231010210000-1">
        <sql>
            create schema schema2;
        </sql>
    </changeSet>

After successfully creating the new schema, the subsequent task is to introduce new tables to it. This represents a shift from our usual workflow that was centered around schema1 exclusively.

    <createTable schemaName="schema2" tableName="first_table_in_new_schema">
        <column name="id" type="bigint" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="column_1" type="bigint">
            <constraints nullable="true"/>
        </column>
        ...
    </createTable>

In bold letters, I've highlighted the crucial part indicating how to instruct Liquibase to implement the desired changes in a specific schema. This is essentially all that is required to direct Liquibase to use our new schema. If we dont specify schemaNameLiquibase defaults to the original schema (schema1).

Schema Declaration on a JPA Entity

To specify the schema on a JPA entity, we must modify the @Table annotation as follows:

 @Entity
 @Table(name = "first_table_in_new_schema", catalog = "schema2")
 public class FirstTableInNewSchema {
 ...
 }  

It's essential to note that we use the catalog field for this purpose. Although there is also a schema field in @Table, when working with MySQL, it's crucial to utilize catalog.

This essentially covers all the necessary steps. When you use the corresponding repository for the above entity, the new schema will automatically be prefixed to the table name.

Handling End-to-End Tests with H2 Database

Everything mentioned above functions seamlessly... until you encounter the need to run your end-to-end tests using a database system other than MySQL. While some may argue for using MySQL test containers to ensure testing against the same underlying database, we opted to address this challenge using our existing H2 Database. In order to closely replicate the production database, we utilize identical Liquibase scripts for both our tests and production environments.

The challenge arises as H2 mandates the use of the schema field in the @Table annotation instead of the catalog field.

Simple, right? Just include both annotations on the entity:

 @Entity
 @Table(name = "first_table_in_new_schema", schema = "schema2", catalog = "schema2")
 public class FirstTableInNewSchema {
 ...
 }

However, this approach fails, and you will notice a schema validation error during startup:

 Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing table [schema2.schema2.first_table_in_new_schema]

To resolve this, we need to remove the catalog field at the startup of the End-to-End Test. Spring/Hibernate uses the interface PhysicalNamingStrategy to create effective names for tables and fields. There are a few default implementations that for example can replace underscore with dots, or change camelcase to snakecase. We used the SpringPhysicalNamingStrategy, overridden with a custom naming strategy solely for E2E tests. This strategy removes the value of the catalog field. In the application.yml file for E2E tests, we configured Spring/Hibernate to use our CustomPhysicalNamingStrategy:

 spring:
  jpa:
    hibernate:
      naming:
        physical-strategy: path.to.config.CustomPhysicalNamingStrategy

The CustomPhysicalNamingStrategy looks like this:

 @Configuration
 @Profile("test")
 public class CustomPhysicalNamingStrategy extends SpringPhysicalNamingStrategy {

    @Override
    public Identifier toPhysicalCatalogName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        if (name != null) {
            return super.toPhysicalCatalogName(null, jdbcEnvironment);
        }
        return super.toPhysicalCatalogName(name, jdbcEnvironment);
    }
 }

Explanation of annotations and code used:
‍
@ConfigurationNecessary for the bean to be initialized at the application startup.
‍
‍@Profile("test")Specifies that this bean is only initialized when the application starts with the test profile (in our case, during E2E tests). The CustomPhysicalNamingStrategy should exclusively be used in E2E tests, as configured in the application.yml. This annotation prevents the bean from being initialized outside of an E2E test.
‍
‍toPhysicalCatalogName: This overridden method checks if the catalog name is set. If so, we set it to null during startup, effectively "removing" the catalog field in the @Table annotation if it's set.

Information regarding Transaction Management Across Multiple Schemas

As we utilize the same connection URL for both schemas, all database configurations are shared, as mentioned earlier. This implies that when operating within a @Transactional context, the transaction stretches over both schemas if changes are made to both. Therefore, if you write to the default schema and also to schema2 in one transaction, and an issue arises while writing to schema2, the entire transaction will roll back. Even if there were no errors while writing to schema1, the changes will be rolled back as well.

Kai Müller
Software Engineer