Play Evolutions and JPA in Play Framework 2.4

Question

I migrated a Play project from Version 2.3.4 to 2.4.2. The projects uses JPA (Hibernate) and Evolutions. I have an initial Evolutions SQL script, which fills the database with some sample data. This script is now not working anymore, because the Evolutions scripts are now executed before Hibernate generates the tables, which obviously leads to an error. Is this a desired behavior? Is there any way to change the order of execution?

Everetteverette

Answer 1

Evolutions are about creation and update database by plain SQL scripts. So if you use some framework that generates data tables itself, like hibernate, then you need to switch off evolutions (or switch off autogeneration and use only evolutions)

Fill database on startup

I have a similar issue on Cassandra, what I did - just create a code that read CQL file and execute it, and run this code after creation the actual data tables.

As I see Hibernate already have this feature - you need to put your custom SQL code into the /import.sql file in the root of your classpath:

If a file named import.sql exists in the root of the class path ('/import.sql') Hibernate will execute the SQL statements read from the file after the creation of the database schema.

Unschooled

Thanks for the detailed answer, but I don't agree. The Play docu states: "When you use a relational database, you need a way to track and organize your database schema evolutions". That doesn't say how to access that database. In my opinion Evolutions was a perfect addition to Hibernate because Hibernate has only limited migration possibilities. And the point is, it worked for years in Play, so I consider this a bug. – Everetteverette

You wrote "Hibernate generates the tables" it means that Hibernate organize your database. You need to use only one database update mechanic - evolution or hibernate table generation. If you want to use evolution then switch off Hibernate table generation: #221879. – Unschooled

WARNING: We've seen Hibernate users trying to use SchemaUpdate to update the schema of a production database automatically. This can quickly end in disaster and won't be allowed by your DBA. – Unschooled

Answer 2

I had the same issue after upgrading to 2.4 from 2.3 My tests did not work since I used Hibernate to create all tables in H2 in memory db and play evolutions for preparing DB with data for tests cases.

My test base was like

public abstract class TestServerBase extends WithServer {
    public static String H2_URL =  "jdbc:h2:mem:glamazon;MODE=MySQL;REFERENTIAL_INTEGRITY=False";

    public abstract String getDb();

    @Override
    protected FakeApplication provideFakeApplication() {
        final String db = getDb();
        final Map config = new HashMap();
        config.put(String.format("db.%s.driver",db), "org.h2.Driver");
        config.put(String.format("db.%s.url",db), H2_URL);
        config.put(String.format("db.%s.user",db), "");
        config.put(String.format("db.%s.password",db), "");
        config.put(String.format("db.%s.jndiName",db), "DefaultDS");
        return Helpers.fakeApplication(config);
    }
}

Since play 2.4 something has changed and hibernate was called after play evolutions. To deal with this problem I turned of automatic evolutions in my integrationists config

play.evolutions.enabled=false

and I came up with new method for my TestServerBase

@Before
public void runEvolutions(){
    final Database db = Databases.createFrom(getDb(),"org.h2.Driver", H2_URL);
    JPA.withTransaction(() ->{
        Evolutions.applyEvolutions(db);
    });
}

@After
public void deleteEvolutions(){
    final Database db = Databases.createFrom(getDb(),"org.h2.Driver", H2_URL);
    JPA.withTransaction(() ->{
        Evolutions.cleanupEvolutions(db);
    });

}

I do not agree that we can not use evolutions and Hibernate together. I found using both for integration tests as a perfect solution. If you want to use only evolutions, the problem is that evolutions are DB related so MySQL evolutions do not work with H2 db. Second reason is that I used different evolutions for different test cases and I would need to copy paste all the scripts with scheme for each evolution.

I believe that using Hibernate to create scheme for integration tests is perfectly ok and the 2.4 change is a breaking change which should have been documented.

Here is my repository with working example for play 2.6 but it should be working also with play >= 2.4 githubrepository

Vacationist