Update all schemas with multitenancy architecture

2

Good morning.

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

Thank you in advance.

    
asked by anonymous 16.02.2015 / 12:55

1 answer

2

I can think of several options to solve this problem:

1. Use a migration library

This is my preferred option because I can not imagine Hibernate applying automatic changes to production.

You can adopt a library such as Liquibase or Flyway / a> to manage change scripts for different versions of the database. These libraries take care of or at least facilitate the updates of different schemas or different banks, just set them up properly.

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

I'll put a summary of the implementation I made using the Spring Framework and FlyWay. The following class uses two instances of Flyway to manage the schema public and 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 framework, I store the public schema migration scripts and the specific schemas in the following directory structure:

AndconfiguretheFlyWaybeansasfollows:

@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 Hibernate on every Schema

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

I searched for 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 initialization you can discard the session and use the multi-tenant setting normally.

3. Generate scripts through Hibernate using SchemaExport

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

There is a DDL export sample in this SOen response . Just do this for each schema .

4. Apply the changes through Hibernate using SchemaExport

With the class SchemaExport you can also force the database to be updated via code.

An example, which may be slightly 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 this for each schema . In case you need to pay attention to the method and parameters used in SchemaExport . Some commands can recreate or delete database elements, others can generate a file with the script, others can only update the database.

    
16.02.2015 / 16:08