Support for database migration as an app

[dsyer]

It is a common requirement to be able to run database migrations as a different user than the one that runs the main app. We already support that by allowing a separate DataSource to be configured for Flyway (and possibly Liquibase as well). We don't really have a convenient way to run that as a separate process though, which is kind of part of the same requirement (so that the admin user credentials can stay separate). Ideally, probably being able to run the application JAR with a switch of some sort would be the best way to do it. You can probably already do something with PropertiesLauncher but it's not very obvious, so we need at least a proof of concept and some documentation.

See also #787.

[dsyer]

cc @robharrop

[dsyer]

This worked for me in the stock spring-boot-sample-flyway.

application.yml:

spring:
  datasource:
    url: jdbc:h2:./target/testdb
    username: sa
    password: 
  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: true

  h2:
    console:
      enabled: true

flyway:
  enabled: false

---
spring:
  profiles: admin
  main:
    web-environment: false
flyway:
  enabled: true
  url: jdbc:h2:./target/testdb
  user: sa
  password: 

Then the test works (I also added some code to delete the database files in between tests), and you can run the jar with --spring.profiles.active=admin to initialize the database.

I think that's pretty easy to extrapolate to other databases, different user credentials per profile, etc. We could put it in the user guide.

[jnizet]

Another cool thing would be to be able to clean the database as an app, during development. When you have 4 dev branches in parallel, and each of them has its own set of migrations that potentially conflict with each other, being able to restart from a clean schema is useful.

[Infeligo]

@dsyer, doesn't your example also initialize context for the default profile? It would mean that running the application in migration mode is sub-optimal in case when the app is heavy. Also, there can be issues with the execution order. At this moment, for example, Liquibase migration does not necessarily start before other datasource users.

Another requirement is to be able to run database creation scripts, which should precede migration. This script should create the database on the database server (I have Postgres in mind), create a set of roles, create a user for the migration scripts (owner of schemas) and create a user for the application (no DDL privileges). In short, this script creates everything that is global and it also creates a place for Liquibase/Flyway to keep their changelog tables. A database drop script should also exists, droping all the global users, roles and the database itself. This way the application can really clean after itself leaving nothing at all (not possible with pure migrations).

In order to run migration strictly before everything and to fullfill the database setup/cleaning requirement, I used another approach. In the main method, I ran three Spring Boot application depending on the switches:

        if (!setupDatabase(args)) {
            migrateDatabase(args);
            SpringApplication.run(clazz);
        }

The database setup used Spring's very own ScriptUtils class. The migration part utilized Liquibase autoconfiguration. The application itself had Liquibase autoconfiguration excluded. Because the setup and the migration part can be shared among all apps, I wrote a small wrapper which runs everything with one line of code (a'la Microservice.run(MyApplication.class)).

What I like about this approach is that absolutely everything is contained inside a single JAR and the app can create everything for itself and clean afterwards. This really simplifies automation.

[dsyer]

That's a really useful example for documentation. Thanks.

[kakawait]

I used @dsyer idea #6035 (comment) with adding Picocli usability rather than using a plain old CommandLineRunner that help you to describe / write command lines easier. You can checkout the sample (from starter repo)

[philwebb]

I think that there are probably enough features to allow this multi-path functionality to be implemented with the existing codebase. You can use switch in the main method and call SpringApplication.run with different configurations, there are properties to change the web application type, and you can disable specific configurations with a property.

It might be possible to make it easier to do those things, but there would be a lot of compilations if we tried to make this a first-class feature.

I'm going to close this one and suggest that people just use the techniques documented in this issue.