Create new schema using Flyway from Java code

Question

We have introduced Flyway inside the codebase. Previously, we have the Postgres function stored in the public schema and we use that to replicate the tenant schema to create a new schema that has the same structure as the tenant schema. The repo code is as follows:

@Repository
public interface TenantRepository extends JpaRepository<Tenant, UUID> {

        @Query(nativeQuery = true, value = "SELECT clone_schema(:defaultSchema,:newSchema,:isCopyData);")
    String callCloneSchema(@Param("defaultSchema") String defaultSchema, @Param("newSchema") String newSchema,@Param("isCopyData") boolean isCopyData);
}

I would like to delete these functions and want to create a new schema using the Flyway. Does Flyway offer such a possibility?

Answer 1

Here are some steps used to manually trigger Flyway migration with individual schemas:

1. Disable "auto migration"

By default, Spring boot would like to automatically run the Flyway SQL scripts. So you have to disable this "auto migration" (see 4.3. in this Blog). Here, this is done in the "Spring Boot main class":

@SpringBootApplication                                        
public class FooApplication {                                 
                                                              
    public static void main(String[] args) {                  
        SpringApplication.run(FooApplication.class, args);    
    }                                                         
                                                              
    @Bean                                                     
    public FlywayMigrationStrategy flywayMigrationStrategy() {
        return flyway -> {                                    
            // do nothing                                     
        };                                                    
    }                                                         
                                                              
}                                                             

2. Manually migration

To automatically do migration using different schemas, this would be one solution:

• inject (the default) Flyway instance
• duplicate the configuration but overwrite the schemas to be used
• trigger the migration

Example code: Trigger of the migration via a GET request (the SQL files are under src/main/resources/db/migration/V#_#_#__xyz.sql)

@Controller                                                               
public class FooController {                                              
                                                                          
    @Autowired                                                            
    Flyway flyway;                                                        
                                                                          
    @GetMapping("foo")                                                    
    public ResponseEntity<?> foo(@RequestParam("schema") String schema) { 
        Flyway.configure()                                                
                // apply/use the default (Spring) flyway configiration    
                .configuration(flyway.getConfiguration())                 
                // use the passed schema                                  
                .schemas(schema)                                          
                .defaultSchema(schema)                                    
                // get a Flyway instance                                  
                .load()                                                   
                // run the migration                                      
                .migrate();                                               
        return ResponseEntity.noContent().build();                        
    }                                                                     
                                                                          
}