Play! framework - database issue with Evolutions

Question

I'm using Play! framework 2.0 and I'm stuck on an annoying issue involving the database.

Suppose I have a User (extends Model) class which has few attributes (first_name, last_name, email, password etc).

At some point I want to add a new attribute, lets say last_ip (it doesn't really matter what it is). So, I add the attribute to the User class, compile and run.

The thing is: I get this red alert about database changes (obviously) which asks me to press "APPLY CHANGES" (if I remember correctly). That's fine BUT! all the database records are erased!

In conclusion: I want to a new field but I don't want to lose all the records I already added to the database. Is this possible?

Rigmarole

There are several signs in your post that you might be making some dangerous/problematic design choices. Based on column name it seems highly likely that you're storing plaintext passwords, not salted password hashes. That's all kinds of bad, especially if you aren't encrypting the password and are instead storing it in plain text. Please - just don't store user passwords, use an authentication service and a secure authentication protocol – Cleotildeclepe

Consider using JSSE, Java GSS, or Java SASL to handle authentication and don't store passwords in your app - or even better, use OAuth or OpenID so someone else can look after the passwords for you. You don't want to be the next cracked site that has to tell all its users it was storing plain-text unsalted passwords; ridicule stings. – Cleotildeclepe

Also, you need to read Falsehoods programmers believe about names and Stilgherrian's "only one name" – Cleotildeclepe

Answer 1

First you need to disable automatic generation of Evolution files by deleting the first 2 commented lines of the conf/evolutions/default/1.sql:

# --- Created by Ebean DDL
# To stop Ebean DDL generation, remove this comment and start using Evolutions

# --- !Ups
...

Then, you need to create a second file, called conf/evolutions/default/2.sql containing your update on the database schema with an Ups and a Downs section:

# --- !Ups
ALTER TABLE USER ADD COLUMN last_ip varchar(30) DEFAULT NULL;

# --- !Downs

ALTER TABLE USER DELETE COLUMN last_ip;

Grommet

could you switch your answer to the community wiki? :) the question returns still and still, so we can just write whole instruction based on your answer. – Handbreadth

Answer 2

What you are probably doing is applying destructive evolutions. If you look in 1.sql (or whatever your evolutions file is), under DOWNS you have statemtnts like "DROP DATABASE X". Whenever Play detects changes in the evolution file, it runs all the down evolutions, then reapplies the up evolutions, resulting in all your data being lost.

Here is more info: http://www.playframework.org/documentation/2.0.2/Evolutions

Cespitose

ok, I read and understood more or less in first reading. but what is the right action to take when you add a new field to the database but don't want to lose all the record which added before. the question is crucial when you're in production phase. – Rigmarole

Oh, right, my bad. What I believe you should do is remove the destructive downs and maybe add a 2.sql where you use and ALTER sql statement to add a field to your database. – Cespitose

You can disable down section because it may be danger to have downs in production - in the configurations with "play.evolutions.autoApplyDowns=false" – Tricksy

Answer 3

I suggest you take a look at Liquibase. Liquibase handles database changes, is super flexible and is database independent. I use it in my applications to make sure when I apply a database change things don't get deleted or whatever.

Botello

I've never worked with Play, so I can't. Have a look at Liquibase's web site for more info, it's a well documented project. – Botello

Answer 4

You could disable Evolutions by setting evolutionplugin=disabled in application.conf

Commendation