Hibernate: hbm2ddl.auto=update in production?

Question

Is it okay to run Hibernate applications configured with hbm2ddl.auto=update to update the database schema in a production environment?

Answer 1

It's not safe, not recommended, but it's possible.

I have experience in an application using the auto-update option in production.

Well, the main problems and risks found in this solution are:

• Deploy in the wrong database. If you commit the mistake to run the application server with a old version of the application (EAR/WAR/etc) in the wrong database... You will have a lot of new columns, tables, foreign keys and errors. The same problem can occur with a simple mistake in the datasource file, (copy/paste file and forgot to change the database). In resume, the situation can be a disaster in your database.
• Application server takes too long to start. This occur because the Hibernate try to find all created tables/columns/etc every time you start the application. He does this to know what (table, column, etc) need to be created. This problem will only gets worse.
• Database tools it's almost impossible to use. To create scripts for the database, you need to think about what will be created by the auto-update after you start the application server. If you need to fill a new column (per example) with some data, you need to start the application server, wait to Hibernate crete the new column and run the SQL script after that. Tools like Flyway it's almost impossible to use with auto-update enabled.
• Database changes is not centralized. With the possibility of the Hibernate create the tables and everything else, it's hard to watch the changes on database in each version of the application, because most of them are automatically.
• Encourages garbage on database. Because of the ease of auto-update, there is a chance your team neglecting to drop old columns and old tables.
• Imminent disaster. The imminent risk of some disaster to occur in production (like some people mentioned in other answers). Even with an application running and updatable for years, I don't think it's safe. I never felt safe.

So, I will not recommend to use auto-update in production.

If you really want to use auto-update in production, I recommend:

• Separated networks. Your test environment cannot access the homolog environment. This helps prevent a deployment that was supposed to be in the Test environment change the Homologation database.
• Manage scripts order. You need to organize your scripts to run before your deploy (structure table change, drop table/columns) and script after the deploy (fill information for the new columns/tables).

And, different of the another posts, I don't think the auto-update enabled it's related with "very well paid" DBAs (as mentioned in other posts)... DBAs have more important things to do than write SQL statements to create/change/delete tables and columns. These simple everyday tasks can be done and automated by developers and only passed for DBA team to review, not needing Hibernate and DBAs "very well paid" to write them.

Answer 2

I agree with Vladimir. The administrators in my company would definitely not appreciate it if I even suggested such a course.

Further, creating an SQL script in stead of blindly trusting Hibernate gives you the opportunity to remove fields which are no longer in use. Hibernate does not do that.

And I find comparing the production schema with the new schema gives you even better insight to wat you changed in the data model. You know, of course, because you made it, but now you see all the changes in one go. Even the ones which make you go like "What the heck?!".

There are tools which can make a schema delta for you, so it isn't even hard work. And then you know exactly what's going to happen.

Answer 3

As I explained in this article, it's not a good idea to use hbm2ddl.auto in production.

The only way to manage the database schema is to use incremental migration scripts because:

• the scripts will reside in VCS along your code base. When you checkout a branch, you recreate the whole schema from scratch.
• the incremental scripts can be tested on a QA server before being applied in production
• there is no need for manual intervention since the scripts can be run by Flyway, hence it reduces the possibility of human error associated with running scripts manually.

Even the Hibernate User Guide advise you to avoid using the hbm2ddl tool for production environments.

Answer 4

Hibernate creators discourage doing so in a production environment in their book "Java Persistence with Hibernate":

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.

Answer 5

We do it in a project running in production for months now and never had a problem so far. Keep in mind the 2 ingredients needed for this recipe:

1. Design your object model with a backwards-compatibility approach, that is deprecate objects and attributes rather than removing/altering them. This means that if you need to change the name of an object or attribute, leave the old one as is, add the new one and write some kind of migration script. If you need to change an association between objects, if you already are in production, this means that your design was wrong in the first place, so try to think of a new way of expressing the new relationship, without affecting old data.

2. Always backup the database prior to deployment.

My sense is - after reading this post - that 90% of the people taking part in this discussion are horrified just with the thought of using automations like this in a production environment. Some throw the ball at the DBA. Take a moment though to consider that not all production environments will provide a DBA and not many dev teams are able to afford one (at least for medium size projects). So, if we're talking about teams where everyone has to do everything, the ball is on them.

In this case, why not just try to have the best of both worlds? Tools like this are here to give a helping hand, which - with a careful design and plan - can help in many situations. And believe me, administrators may initially be hard to convince but if they know that the ball is not on their hands, they will love it.

Personally, I'd never go back to writing scripts by hand for extending any type of schema, but that's just my opinion. And after starting to adopt NoSQL schema-less databases recently, I can see that more than soon, all these schema-based operations will belong to the past, so you'd better start changing your perspective and look ahead.

Answer 6

We do it in production albeit with an application that's not mission critical and with no highly paid DBAs on staff. It's just one less manual process that's subject to human error - the application can detect the difference and do the right thing, plus you've presumably tested it in various development and test environments.

One caveat - in a clustered environment you may want to avoid it because multiple apps can come up at the same time and try to modify the schema which could be bad. Or put in some mechanism where only one instance is allowed to update the schema.