When you are using relational databases you need to track and organize schema evolutions.
This becomes increasingly important when many people are collaborating on one code base and also when you want to consistently upgrade your production database.
To that end Ninja integrates the excellent Flyway migration tool (http://flywaydb.org/).
Activate migrations by setting the following property:
ninja.migration.run=true
Then set the credentials for your database:
db.connection.url=jdbc:postgresql://localhost:5432/ra db.connection.username=ra db.connection.password=
Flyway itself manages database migration scripts in a directory called src/main/java/db/migration. You should use
the following naming convention: V1__.sql is your first script, V2__.sql
is your second script and so on.
V1__.sql may look like:
-- Just a simple table
create table GuestbookEntry (
id int8 not null,
email varchar(255),
text varchar(255),
primary key (id)
);
--needed for hibernate running on postgresql
create sequence hibernate_sequence;
A migration script is just plain old SQL. It is really important to stress that Flyway's migration scripts are NOT database vendor independent.
Therefore it is strongly discouraged to use different databases in test, dev and prod.
When two or more developers are working on the same script (let's say V2__.sql) they'll get conflicts once they are trying to merge their changes.
But this is intentional and allows to fix problems in V2__.sql. After resolving the issues and running your tests you can be confident that your production upgrade will also work fine.
If you want to store your migration scripts in a non-default directory (or directories), set the following optional property with a comma-separated list of locations.
ninja.migration.locations=classpath:com.example.migration,filesystem:/sql-migrations
You can also set a custom schema list using the following property. The first schema in the list will be automatically set as the default one during the migration.
ninja.migration.schemas=schema1,schema2,schema3
In test mode Ninja automatically drops the schema and runs all migrations from scratch. In dev and production migration scripts are executed automatically when they are detected. Usually this happens when the first deployed Ninja instance connects with your database. Flyway's migrations are “clustersafe”. Therefore nothing will go wrong when you are deploying 20 instances of your application that try to run the evolutions at the same time.
Hibernate - the JPA implementation we are using is already providing a DDL (hbm2ddl) feature that can manage and create “migrations” for you.
But Hibernate's hbm2ddl is “just” a diff tool. Once your setup gets more complex Hibernate's feature is not enough. Even the creators of Hibernate strongly discourage the usage in production systems:
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. (From the book Java Persistence with Hibernate).
Our recommendation is to enable Hibernate's DDL for your first initial developments.
Enable that by setting the following properties inside your persistence unit (persistence.xml):
<property name="hibernate.hbm2ddl.auto" value="create" /> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.format_sql" value="true" />
Hint: Try using create (always drops data in your db) or update (tries to alter existing tables for new data) for hibernate.hbm2ddl.auto.
Hibernate will then print out nice SQL statements for the creation of your tables, sequences and so on.
Once your initial prototype is ready copy them to your first migration (eg V1__.sql) and deactivate Hibernate's DDL feature. From now on use Ninja's migrations and everything is safe.
Flyway offers a lot more. For instance Java based migrations, that really facilitate refactoring of blobs. Check out Flyway's excellent site at: http://flywaydb.org