Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • 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

...