...
- 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
...