Update all schemas with multitenancy architecture

Question

Good morning.

Next, I have a multitenancy application using Hibernate with JPA. We recently implemented the multitenancy architecture and persistence.xml was configured to automatically update the default schema when deploying in the project, this was removed since this architecture does not allow the use of this property. But now I don’t know how to update all the schemas that are in the database. Can present me solutions?

Thank you in advance.

Answer 1

I can think of several options to solve this problem:

1. Use a migration library

This is my favorite option, because I can’t imagine Hibernate applying automatic changes in production.

You can adopt a library as Liquibase or Flyway to manage scripts for changes to different versions of the database. These libraries take care of or at least facilitate updates for various schemas or different banks, just configure them properly.

I implemented a project a few weeks ago with Flyway and it was very easy to update several schemas in Postgresql. In this case, the schemas were created automatically and I kept a list of them on public to then make the update.

I’ll put a summary of the implementation I did using the Spring Framework and Flyway. The following class uses two instances of Flyway to manage the schema public and the specific schemas.

@Service
public class DatabaseMigration {

    @Autowired @Qualifier("public") Flyway publicFlyway;
    @Autowired @Qualifier("specific") Flyway specificFlyway;
    @Autowired SchemaService schemaService;

    @PostConstruct
    void migrate() {
        migratePublic();
        migrateAllSpecificSchemas();
    }

    /**
     * Updates public main schema when application is starting
     */
    void migratePublic() {
        publicFlyway.migrate();
    }

    /**
     * Updates all schemas when application is starting
     */
    void migrateAllSpecificSchemas() {
        List<String> schemas = schemaService.listAll();
        for (String schema : schemas) {
            specificFlyway.setSchemas(schema);
            specificFlyway.migrate();
        }
    }

    /**
     * Used when creating a new schema for a new client
     */
    public void migrateSpecificSchema(String schema) {
        specificFlyway.setSchemas(schema);
        specificFlyway.migrate();
    }

}

In my project, which follows the Maven structure, I store the public schema migration scripts and specific schemas in the following directory structure:

And configure the Flyway Beans as follows:

@Bean @Qualifier("public")
Flyway getFlywayPublicInstance(DataSource dataSource) {
    Flyway f = new Flyway();
    f.setDataSource(dataSource);
    f.setLocations(publicMigrationsDirectory);
    f.setOutOfOrder(true);
    return f;
}

@Bean @Qualifier("specific")
Flyway getFlywaySpecificInstance(DataSource dataSource) {
    Flyway f = new Flyway();
    f.setDataSource(dataSource);
    f.setLocations(specificMigrationsDirectory);
    f.setOutOfOrder(true);
    return f;
}

2. Initialize the hibernate in each Schema

At the beginning of the application, you can manually initialize Hibernate once for each schema. I’ve never done it, but it should work well.

I researched an example of how to do this and found this:

Configuration config = new Configuration().configure();
config.setProperty("hbm2ddl.auto", "update");
SessionFactory sessionFactory = config.buildSessionFactory();
sessionFactory = hibernateConfiguration.buildSessionFactory();

After this manual startup you can discard the session and use the multi-tenant configuration normally.

3. Generate scripts by Hibernate using SchemaExport

With class SchemaExport you can generate the necessary DDL and then apply it to the different schemas.

There’s a example of exporting DDL in this Soen response. Just do it for each schema.

4. Apply changes by Hibernate using SchemaExport

With class SchemaExport you can also force the bank update via code.

An example, which may be a little outdated, is at the end of this page:

AnnotationConfiguration config = new AnnotationConfiguration();
config.addAnnotatedClass(User.class);
config.configure();
new SchemaExport(config).create(true, true);

Just do it for each schema. In case you need to pay attention to the method and the parameters used in SchemaExport. Some commands can recreate or delete database elements, others can generate a file with the script, others only update the database.