CODEWITHSUNDEEP
javadatabaseormmigration
rmaruszewski
rmaruszewski

Reputation: 2417

How to upgrade database schema built with an ORM tool?

I'm looking for a general solution for upgrading database schema with ORM tools, like JPOX or Hibernate. How do you do it in your projects?

The first solution that comes to my mind is to create my own mechanism for upgrading databases, with SQL scripts doing all the work. But in this case I'll have to remember about creating new scripts every time the object mappings are updated. And I'll still have to deal with low-level SQL queries, instead of just defining mappings and allowing the ORM tools to do all the job...

So the question is how to do it properly. Maybe some tools allow for simplifying this task (for example, I heard that Rails have such mechanism built-in), if so please help me decide which ORM tool to choose for my next Java project.

Upvotes: 7

Views: 5999

Answers (8)

gaboroncancio
gaboroncancio

Reputation: 900

You can check this feature comparison of some database schema upgrade tools.

A comparison of the number of questions in SOW of some of those tools:

Upvotes: 2

Faisal Feroz
Faisal Feroz

Reputation: 12785

DbMaintain can also help here.

Upvotes: 1

Paul Croarkin
Paul Croarkin

Reputation: 14675

We hand code SQL update scripts and we tear down the schema and rebuild it applying the update scripts as part of our continuous build process. If any hibernate mappings do not match the schema, the build will fail.

Upvotes: 2

Adrian
Adrian

Reputation: 46432

When working with Hibernate, I use an installer class that runs from the command-line and has options for creating database schema, inserting base data, and dynamically updating the database schema using SchemaUpdate. I find it to be extremely useful. It also gives me a place to put one-off scripts that will be run when a new version is launched to, for example, populate a new field in an existing DB table.

Upvotes: 0

killdash10
killdash10

Reputation: 716

LiquiBase is an interesting open source library for handling database refactorings (upgrades). I have not used it, but will definitely give it a try on my next project where I need to upgrade a db schema.

Upvotes: 7

MarcE
MarcE

Reputation: 3731

I don't see why ORM generated schemas are any different to other DB schemas - the problem is the same. Assuming your ORM will spit out a generation script, you can use an external tool to do the diff

I've not tried it but google came back with SQLCompare as one option - I'm sure there are others.

Upvotes: 2

Jason Cohen
Jason Cohen

Reputation: 83021

We ended up making update scripts each time we changed the database. So there's a script from version 10 to 11, from 11 to 12, etc.. Then we can run any consecutive set of scripts to skip from some existing version to the new version. We stored the existing version in the database so we could detect this upon startup.

Yes this involved database-specific code! One of the main problems with Hibernate!

Upvotes: 0

Espo
Espo

Reputation: 41929

I think your best bet is to use an ORM-tool that includes database migration like SubSonic:

http://subsonicproject.com/2-1-pakala/subsonic-using-migrations/

Upvotes: 0

Related Questions