Bonjour,
I am working on changing me Java application from using postgres to an embedded database. I would like the application to deploy with an initial set of data in the database. In the past during installation I have executed an sql script to fully generate the schema and insert the data in to my tables.
Ideally (becasue I don't really want to work out how to connect to the embedded database to generate it) I want to let JPA create my schema for the first time, and when it does I then want to be able to run my SQL to insert the data.
My search has turned up the obvious hibernate and JPA properties that allow running of an SQL script.
Firstly I found when using "hibernate.hbm2ddl.auto" you can define an import.sql file this made me very happy for a day until I realised it only works with create and not with update. My application when using postgres had this set to update. And what i would really like is for it to know if it's had to create the schema and if it has then run the import.sql. No Joy though.
I then moved on to using "javax.persistence.schema-generation.database.action" set to "create" I figured using the JPA specification was probably wiser anyway and so I defined "javax.persistence.sql-load-script-source" the spec says for "create"
The provider will create the database artifacts on application deployment. The artifacts will remain unchanged after application redeployment.
This lead me to believe it would do exactly what I wanted, only create the tables "on application deployment" however when I ran my tests using this, each test (creating a new spring context) tried to just create all the tables again and obviously failed, which made me realise application deployment didn't mean what i thought it meant (wishful thinking) and now I realise that JPA doesn't seem to even have an equivalent of Hibernates "update" property, so it's always going to generate the tables?
What I want is to have my tables and data generated when you first spin up the app and for subsequent executions to know the data is there and use it, I am assuming it's too much to hope for that this exists, but i'm sure that this must be a common requirement? So my question is what is the general recommended way to achieve the goal of allowing JPA to create my schema but being able to insert some data in to a db that persists between executions?
The answer is flyway. It is a database migration library, and if you are using Spring boot it is seamlessly integrated, with regular Spring you have to create a bean, which get a reference to the connection pool, creates a connection and does the migration.
Flyway creates a table so it keeps track of which scripts has already been applied to the database, and the scripts are simply part of the resources.
We normally use JPA to generate the initial script. This script becomes V1__initial.sql, if we need to add some data we can add V2__addUsers.sql and V3__addCustomers.sql etc.
Later when we need to rename columns or add additional tables, we simply add new scripts as part of the War file, and when the application is loaded Flyway looks at it's internal table, to see the current version, and then applies any new scripts to bring it up to de desired version.
In Spring the code would look like this
private void performFlywayMigration(DataSource dataSource) {
Flyway flyway = new Flyway();
flyway.setLocations("db/migration");
flyway.setDataSource(dataSource);
log.debug("Starting database migration.");
flyway.migrate();
log.debug("Database migration completed.");
MigrationInfo current = flyway.info().current();
if (current.getState() == MigrationState.FUTURE_SUCCESS) {
log.warn("The Database schema is version " + current.getVersion() + ", this application expects version " + flyway.getBaselineVersion().getVersion());
}
}
In general you should not JPA to create tables directly. because you sometimes need to modify the scripts, for instance on Sybase Varchar(255) means 255 bytes, so if you are storing 2 or 3 byte Unicode chars, you need more space - JPA implementation does not account for that (last time I checked).