How do I manage database evolutions when I'm using JPA?

Question

I learned play by following the tutorial on their website for building a little blogging engine.

It uses JPA and in it's bootstrap calls Fixtures.Deletemodels(), (or something like that).

It basically nukes all the tables every time it runs and I lose all the data.

I've deployed a production system like (sans the nuke statement).

Now I need to deploy a large update to the production system. Lots of classes have changed, been added, and been removed. In my testing locally, without nuking the tables on every run I ran into sync issues. When I would try to write or read from tables play would throw errors. I opened up mysql and sure enough the tables had only been partially modified and modified incorrectly in some cases. Even if I have the DDL mode set to "create" in my config JPA can't seem to "figure out" how to reconcile the changes and modify my schema accordingly.

So I have to put back in the bootstrap statement that nukes all my tables.

So I started looking into database evolutions within Play and read an article on the play framework website about database evolutions. The article talked about version scripts, but it said, "If you work with JPA, Hibernate can handle database evolutions for you automatically. Evolutions are useful if you don’t use JPA".

So if JPA is supposed to be taking care of this for me, how do I deploy large updates to a large Play app? So far JPA has not been able to make the schema changes correctly and the app will throw errors. I'm not interested in losing all my data so the fix on dev "Fixtures.deleteModels()" can't really be used in prod.

Thanks in advance, Josh

Answer 1

No, JPA should not take care of it for you. It's not a magic tool. If you decide to rename the table "client" to "customer", the column "street" to "line1" and to switch the values of the customer type column from 1, 2, 3 to "bronze", "silver", "gold", there is no way for JPA to read in your mind and figure all the changes to do automagically.

To migrate from one schema to another, you use the same tools as if you didn't use JPA: SQL scripts, or more adavanced schema and data migration tools, or even custom migration JDBC code.

Answer 2

Have a look at flyway. You may trigger database migrations from your code or maven.