OpenLMIS 3.0 Database Migrations

Two things need to be documented in relation to database migrations for v3.0:

1. Moving from development to production-ready paradigm for database changes for each Service
2. How to add database changes to a Service once the production-ready paradigm is in place

Moving to Production-ready Paradigm for Database Changes

Each Service is currently using Hibernate to auto-generate the DDL (schema) based on Hibernate annotations in the Java code and Spring Boot configuration properties (application.properties). This is convenient for developers when coding, since the schema is likely to be changed. However, this does not make sense in a production environment. Following are steps to move a Service to the new paradigm:

• The easiest way to create a schema migration SQL file similar to what Hibernate auto-generates is to do a pg_dump of the database with only the serviceName schema, schema_only option, and column_inserts (if you do not specify column_inserts, Postgres uses COPY, which has had problems)
  • Modify the SQL file to remove the beginning section, until it creates the first table
• We are using Flyway for database migrations, so add the Spring Boot Flyway plugin into the gradle build script
• Disable Hibernate auto-generate DDL and add flyway configuration settings in application.properties
  • spring.jpa.generate-ddl=false
  • spring.jpa.hibernate.ddl-auto=validate
    
  • flyway.schemas=<serviceName>
  • flyway.sql-migration-prefix= (we do not want to use the prefix of 'V')
  • flyway.locations=classpath:db/migration,classpath:db/starter
  • Remove spring.jpa.properties.hibernate.hbm2ddl.import_files
• Copy the flyway configuration from here to regenerate the schema now that hibernate is no longer cleaning and generating.
• Remove schema.sql and bootstrap.sql files (they no longer apply)
• Use gradle generateMigration -PmigrationName=<migrationName> to generate a migration SQL file in src/main/resources/db/migration folder
• Take the pg_dump SQL file contents and put it in the newly generated migration file above
  • If there's any bootstrap data, do separate pg_dumps for schema and then for bootstrap data, and create a separate initial migration script for each.
  • Note: this should only be done for the first Flyway migration. We should not be using pg_dumps as ways to create migration scripts.
• For starter and demo data: (NOTE: this is pretty hacky and not an ideal solution, but it works for now)
  
  • Create a new folder in src/main/resources/db/starter and create a file called afterMigrate.sql, which contains your starter data. (You may also want to create a .gitignore file in that folder as well.)
  • Modify the demo_seed.sh script to rename the demo data SQL file to afterMigrate.sql.
  • Additionally, add some lines in the shell script to do three things:
    • Prepend a conditional into the beginning of the script, which checks for the existence of a table
    • Prepend the afterMigrate.sql script from the starter folder into the demo data afterMigrate.sql
      • Note: this is necessary, because Flyway does not like multiple files with the name afterMigrate.sql.
    • Append a CREATE TABLE of the table being checked for at the beginning of the script

Additionally, for your Distribution (or the Reference Distribution, blue) to load demo (and starter) data:

• You will need to set JAVA_OPTS for your Service to override flyway.locations: '-Dflyway.locations=classpath:db/migration,filesystem:/demo-data'

Adding Database Changes in the Production-ready Paradigm

Once your Service is using Flyway for database changes, to add a change to the database:

• Use gradle generateMigration -PmigrationName=<migrationName> to generate a migration SQL file in src/main/resources/db/migration folder
• Add your database schema changes into the newly generated migration file above

Better Solution for Starter and Demo Data

A better way to load starter and demo data would be to create another Docker container that simply contains Flyway, where the "migrations" would be starter and demo data for each service. It makes more sense, because this data should not reside in each Service's image, but in something like the Reference Distribution. However, this would require a good amount of work to refactor out the demo and starter data from each Service into this new Flyway image/container. But it should be considered for future releases.