When and how should SpringData + JPA schema and DB initialization be used?

user_995514 0
Viewed 102

I'm working on a simple task of adding a new table to an existing SQL DB and wiring it into a SpringBoot API with SpringData.

I would typically start by defining the DB table directly, creating PK and FK, etc and then creating the Java bean that represents it, but am curious about using the SpringData initialization feature.

I am wondering when and where Spring Data + JPAs schema generation and DB initialization may be useful. There are many tutorials on how it can be implemented, but when and why are not as clear to me.

For example:

  1. Should I convert my existing lower environment DBs (hand coded) to initialized automatically? If so, by dropping the existing tables and allowing the App to execute DDL?

  2. Should this feature be relied on at all in production envrionment?

  3. Should generation or initialization be run only once? Some tutorial mention this process running continually, but why would you choose to lose data that often?

  4. What is the purpose of the drop-and-create jpa action? Why would you ever want to drop tables? How are things like UAT test data handled?

spring-data spring-data-jpa spring-boot
2 Answers

My two cents on these topics:

  1. Most people may say that you should not rely on automated database creation because it is a core concept of your application and you might want to take over the task so that you can lnowmfor sure what is really happening. I tend to agree with them. Unless it is a POC os something not production critical, I would prefer to define the database details myself.
  2. In my opinion no.
  3. This might be ok on environments that are non-productive. Or on early and exploratory developments. Definetely not on production.
  4. On a POC or on early and exploratory developments this is ok. In any other case I see this being useful. Test data might also be part of the initial setup of the database. Spring allows you to do that by defining an SQL script inserting data to the database on startup.

Bottomline in my opinion you should not rely on this feature on Production. Instead you might want to take a look at liquibase or flyway (nice article comparing both https://dzone.com/articles/flyway-vs-liquibase), which are fully fledged database migration tools on which you can rely even on production.

user_165722950

My opinion in short: No, don't rely on Auto DDL. It can be a handy feature in development but should never be used in production. And be careful, it will change your database whenever you change something on your entities.

But, and this is why I answer, there is a possibility to have hibernate write the SQL in a file instead of executing it. This gives you the ability to make use of the feature but still control how your database is changed. I frequently use this to generate scripts I then use as blueprint for my own liquibase migration scripts.

This way you can initially implement an entity in the code and run the application, which generates the hibernate sql file containing the create table statement for your newly added entity. Now you don't have to write all those column names and types for the database table yourself.

To achieve this, add following properties to your application.properties:

spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=build/generated_scripts/hibernate_schema.sql
spring.jpa.properties.javax.persistence.schema-generation.scripts.action=create

This will generate the SQL script in your project folder within build/generated_scripts/hibernate_schema.sql

I know this is not exactly what you were asking for but I thought this could be a nice hint on how to use Auto DDL in a safer way.

user_55346420
Related