291

I was working on my Spring boot app project and noticed that, sometimes there is a connection time out error to my Database on another server(SQL Server). This happens specially when I try to do some script migration with FlyWay but it works after several tries.

Then I noticed that I didn't specify spring.jpa.hibernate.ddl-auto in my properties file. I did some research and found that it is recommended to add spring.jpa.hibernate.ddl-auto= create-drop in development. And change it to: spring.jpa.hibernate.ddl-auto= none in production.

But I didn't actually understand how does it really work and how does hibernate generate database schema using create-drop or none value. Can you please explain technically how does it really work, and what are recommendations for using this property in development and on a production server. Thank you

Michał Lepczyński
  • 373
  • 4
  • 12
METTAIBI
  • 3,201
  • 5
  • 22
  • 29
  • 1
    FWIW JPA 2.1 has a _standard_ property javax.persistence.schema-generation.database.action so don't really see the need to use JPA vendor specific properties for schema generation. – Neil Stockton Feb 09 '17 at 13:30
  • 1
    @NeilStockton One idea we're exploring with Hibernate 6 is the ability to be able to control schema generation differently based on categories; e.g. your orm tables might be `none` but you might want your Hibernate Search and Envers tables to be generated using `update` since they're internally managed by those projects and you don't want to manage those manually yourself. Right now we control this globally for all tables regardless of their origin/source. This would further the reason to use vendor-specific options if you wanted to use this. – Naros Jul 05 '18 at 15:53
  • This property is a way to specify hibernate to apply DDL for the database. It is hibernate-specific. More details at https://springhow.com/spring-boot-database-initialization/ – Raja Anbazhagan Jan 03 '22 at 17:18

5 Answers5

453

For the record, the spring.jpa.hibernate.ddl-auto property is Spring Data JPA specific and is their way to specify a value that will eventually be passed to Hibernate under the property it knows, hibernate.hbm2ddl.auto.

The values create, create-drop, validate, and update basically influence how the schema tool management will manipulate the database schema at startup.

For example, the update operation will query the JDBC driver's API to get the database metadata and then Hibernate compares the object model it creates based on reading your annotated classes or HBM XML mappings and will attempt to adjust the schema on-the-fly.

The update operation for example will attempt to add new columns, constraints, etc but will never remove a column or constraint that may have existed previously but no longer does as part of the object model from a prior run.

Typically in test case scenarios, you'll likely use create-drop so that you create your schema, your test case adds some mock data, you run your tests, and then during the test case cleanup, the schema objects are dropped, leaving an empty database.

In development, it's often common to see developers use update to automatically modify the schema to add new additions upon restart. But again understand, this does not remove a column or constraint that may exist from previous executions that is no longer necessary.

In production, it's often highly recommended you use none or simply don't specify this property. That is because it's common practice for DBAs to review migration scripts for database changes, particularly if your database is shared across multiple services and applications.

Naros
  • 19,928
  • 3
  • 41
  • 71
  • 29
    Yeah, never use ddl generation in production. We generate the initial scripts for the table structure using ddl, and involve the DBA in the process. We then include the db scripts as part of the deployment unit, and execute them using Flyway when the application is deployed. When we need to modify the database, we add new scripts to the next version of the application and deploy to staging. Flyway will automatically detect the current version and run the scripts needed to bring the database to the newest version. If everything works we deploy to production. – Klaus Groenbaek Feb 10 '17 at 00:03
  • 2
    what if we don't specify this property? for example I have my own ... update I had this and for some reason my tables were droped always, until I added the above mentioned property;; ps: sorry for code sample ) – Ţîgan Ion May 12 '17 at 11:19
  • I had H2 and spring boot decided that it had to use "create-drop", and I guess this overrides my property that I set https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html – Ţîgan Ion May 12 '17 at 11:34
  • 21
    Why not `validate` in Production Env? – Shamal Karunarathne Jan 16 '18 at 06:57
  • 46
    @ShamalKarunarathne Applications can use `validate` in production, but typically that should be a setting you use in your quality/test environment to verify that the database scripts you've written or applied to your database migration tool are accurate. Another reason not to use `validate` in production is that it could be a bottleneck during the startup process of your application, particularly if your object model is quite extensive in size or if other network related factors come into play. – Naros Jan 25 '18 at 20:02
  • @Naros I am using the variable as update but getting an error when i tried to add a new column "order" (datatype = Long) in my model class gallery : org.hibernate.tool.schema.spi.SchemaManagementException: Unable to execute schema management to JDBC target [alter table gallery add column order bigint ] – Smit Feb 02 '18 at 19:56
  • 1
    Without a precise stack trace its hard to speculate; however, my first guess would be that `order` is being interpreted incorrectly by the SQL parser since that is a keyword if it isn't being escaped. – Naros Feb 06 '18 at 14:01
  • I wanted to upvote this answer twice, but I can't :-( lol. – Sundararaj Govindasamy Sep 30 '20 at 04:46
  • @ShamalKarunarathne your valuable comment, should be included in your answer too. – Manuel Jordan Dec 22 '20 at 16:30
  • @ShamalKarunarathne The reason I don't suggest `validate` is because it takes extra bootstrap cycles to validate the schema against the object model and if your production environment is being managed properly, that's entirely unnecessary. Perhaps `validate` makes sense for a QA environment, but I don't see the value of it in production unless you want a final sanity check everytime you start your application. – Naros Jun 23 '21 at 10:12
59

In Spring/Spring-Boot, SQL database can be initialized in different ways depending on what your stack is.

JPA has features for DDL generation, and these can be set up to run on startup against the database. This is controlled through two external properties:

  • spring.jpa.generate-ddl (boolean) switches the feature on and off and is vendor independent.
  • spring.jpa.hibernate.ddl-auto (enum) is a Hibernate feature that controls the behavior in a more fine-grained way. See below for more detail.

Hibernate property values are: create, update, create-drop, validate and none:

  • create – Hibernate first drops existing tables, then creates new tables
  • update – the object model created based on the mappings (annotations or XML) is compared with the existing schema, and then Hibernate updates the schema according to the diff. It never deletes the existing tables or columns even if they are no more required by the application
  • create-drop – similar to create, with the addition that Hibernate will drop the database after all operations are completed. Typically used for unit testing
  • validate – Hibernate only validates whether the tables and columns exist, otherwise it throws an exception
  • none – this value effectively turns off the DDL generation

Spring Boot internally defaults this parameter value to create-drop if no schema manager has been detected, otherwise none for all other cases.

Md Kawser Habib
  • 1,966
  • 2
  • 10
  • 25
  • This part confuses me, "if no schema manager has been detected". How can it be not detected? A programmer doesn't create a schema manager manually, does they? – Sergey Zolotarev Apr 27 '23 at 05:25
  • The **schema manager** refers to `Liquibase` and/or `Flyway` - see [8.5. Configure JPA Properties](https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto.data-access.jpa-properties) – Manuel Jordan Jul 10 '23 at 14:40
3

Also depending on spring.jpa.hibernate.ddl-auto the DML files feature is enabled

DDL and DML

It is worth to understand the difference between them.

  • Data Definition Language(DDL) - related to database schema creating
  • Data Manipulation Language(DML) - related to importing data

Basically there are 3 types of database schema creating(DDL) and importing data(DML):

  • Using Hibernate
  • Using Spring JDBC SQL scripts
  • Using high level tools like Flyway/Liquibase

This topic covers Hibernate and it's DDL (first option), but it is worth to mention Hibernate DML files feature that enabled if spring.jpa.hibernate.ddl-auto is create or create-drop

That means import.sql in the root of the classpath will be executed on startup by Hibernate. This can be useful for demos and for testing if you are careful, but probably not something you want to be on the classpath in production. It is a Hibernate feature (nothing to do with Spring).

Also here is a table that explains spring.jpa.hibernate.ddl-auto and whether the import.sql can be used depending on spring.jpa.hibernate.ddl-auto value specified:

spring.jpa.hibernate.ddl-auto Create schema from entities import.sql
create true true
update update schema from entities false
create-drop true true
validate false false
none false false

Also some extra information about different types of DDL amd DML can be found in Spring docs

kerbermeister
  • 2,985
  • 3
  • 11
  • 30
2

"spring.jpa.hibernate.ddl-auto= create-drop" means that when the server is run, the database(table) instance is created. And whenever the server stops, the database table instance is droped.

Dennis
  • 99
  • 4
1

For the Propertie of JPA/Hibernate spring.jpa.hibernate.ddl-auto value should be create, update, create-drop not other then it will give an exception, where the correct meaning for these value -

  • Create : when the server will start all entity will be newly created

  • Update : when the server will start container will find which entities are update and which all are newly created the same thing will happen inside database as well old table will update as per the entity and newly table will created

  • Create-drop: when the server will start then auto all entity will crete and when the server will stop all the entities will auto remove from database

  • none : it means database ddl will not impact from back-end application Note: Production environment always set with none value

Lalit Sati
  • 21
  • 2