14

I am at the almost ready stage of my JEE development. With a lot of recommendation NOT to use Hibernate's hbm2ddl.auto in production, I decided to remove it.

So now, I found out about Flyway, which seems great for future db changes and migrations, but I am stuck at first step: I have many entities, some entities inherit from base entities. This makes the CREATE statement very complex.

What is the best practice to create the first migration file?

Thanks!

VHanded
  • 2,079
  • 4
  • 30
  • 55
  • You might also find Liquibase helpful. I mention this not to start a Flyway-vs.-Liquibase argument, but because Liquibase takes care of this particular use case as a first-class concept. See http://www.liquibase.org/documentation/generating_changelogs.html in particular. – Laird Nelson Jul 01 '16 at 18:32

4 Answers4

7

If you've taken an "entities first" approach during development you'll need to generate the initial schema in the same way for the first live deployment: This will produce the first creation script used by Flyway and there may also need to be a second associated script for populating reference data.

In a nutshell, the reasons for no longer being able to use hbm2ddl.auto after the first deployment are that create will destroy existing data and update isn't reliable enough to cover all types of schema changes (as it sounds like you may already know from this SO question).

Flyway is a very useful tool but it does require a level of discipline that may not have existed during development. When going forward from the initial release, database update scripts need to be produced for Flyway that are equivalent to the changes made to the entities since the last release. There are tools (e.g. various commercial products from Redgate) that may help here: These attempt to "diff" two schemas and generate schema and/or data update scripts for getting from database A to database B. But in my experience, none of them are perfect and they don't quite reach the holy grail of enabling a completely automated approach.

Arguably, the best way is an "as you go" manual approach to ensure that non-destructive update scripts are committed to source control whenever an entity change is made that affects the schema or reference data - but as already mentioned, this will require some discipline and/or documented processes for all team members to follow.

Community
  • 1
  • 1
Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
  • Thanks for answering!! 1 question: I am using JPA 2.1 and postgresql, I exported the schema using PGAdmin, and placed in src/main/resources/db/migration/V1__initial.sql. Next, created a class using '@Startup' and '@Singleton' to initialize Flyway.migrate(); The migration always failed with Timeout message. Why? – VHanded Jun 28 '16 at 15:14
  • Does the `migrate` command succeed when you try it using the command line? – Steve Chambers Jun 28 '16 at 15:31
  • 1
    I found out the reason. It is because of encoding issue. The .sql file have to be in UTF8 for the db engine to process it. Else it will just stuck at that line. – VHanded Jun 29 '16 at 10:35
1

For the first migration file, you just need the current ddl of your database. There are many tools which can get this for you (such as the "copy ddl" option in the IntelliJ IDEA Database tool or a GUI client from your database vendor).

kag0
  • 5,624
  • 7
  • 34
  • 67
0

I am not sure about Flyway but there is an alternate way, you can use ant tasks for hibernate to generate or update schema.

Hope it helps.

Amit Mahajan
  • 895
  • 6
  • 34
0

If you build your project with Maven, you could use Hibernate maven plugin.

Lucas Ces
  • 86
  • 5