Page Comparison

...

• 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

...