Converting Play! framework evolution from MySQL to PostgreSQL

Make sure the database is in a consistent state.

Assuming you have not migrated the data from the previous MySQL database and that you're working in development mode (not in production mode), so you don't have to worry about preserving data:

• Rename your migration back to 1.sql. Just because you executed the migration in a previous database, it doesn't mean that when you're going to execute it in a completely new database it's a second evolution: for the new database, it's still the first one.
• Declare your primary key columns like this: id bigserial primary key and remove the constraint.
• Make sure you have an empty database in PostgreSQL. Drop the database and recreate it (dropdb, createdb).
• Run your database migration and watch the output to make sure the migration was executed. See Managing database evolutions.
• Use PgAdmin or a similar tool such as Toad Extension for Eclipse to verify that the database structure was correctly created.

Alternatively, you may find out that Flyway provides a more comprehensive approach to database migrations. There is a plugin for Play Framework.

To avoid the exception Error getting sequence nextval, properly annotate the entity class definitions like this:

@Id@SequenceGenerator(name="product_gen", sequenceName="product_id_seq", allocationSize=1)@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="product_gen")@Column(name="id")public Long getId() { return id; }

Inspect the database to make sure that sequenceName is the name of the sequence created by PostgreSQL.

For more information see:

• Ebean looks for wrong sequence name in Play Framework 2
• Configure JPA to let PostgreSQL generate the primary key value
• EJB3 JPA: 3 Ways Of Generating Primary Key Through @GeneratedValue
• PersistenceException: Error getting sequence nextval
• Play Framework: Error getting sequence nextval using H2 in-memory database
• Annotation Type GeneratedValue