Save Hibernate DDL schema to a file

featured_image

In case you want to store a DDL script that creates a database in your Spring Boot project in a file, all you need is to add a few properties to the app configuration.

What we are going to build

I want to obtain a file with all the SQL commands that are executed when JPA creates my database. Therefore, I’m going to configure my Spring Boot app to save the DDL script in a file.

Requirements

  • I’m working on a Spring Boot 2.2.5 project with the Web and JPA dependencies.
  • You will need to configure your app to connect to a database. In my example I use a PostgreSQL database.

Configure JPA to save schema creation commands in a file

Below you can see the options set in my application-dev.properties file:

Spring Data JPA allows us to set additional native configuration options. Some of them require spring.jpa.properties.* suffix, other like spring.jpa.hibernate.ddl-auto property can be used directly. You can also learn from the documentation that:

(…) all properties in spring.jpa.properties.* are passed through as normal JPA properties (with the prefix stripped) when the local EntityManagerFactory is created.

https://docs.spring.io/spring-boot/docs/2.2.5.RELEASE/reference/html/howto.html#howto-configure-jpa-properties

Properties explained

Let’s take a look at the properties used in the snippet above. To improve readability and make documentation search easier, I removed the suffix spring.jpa.properties.* where required.

javax.persistence.schema-generation.scripts.action

This line indicates which commands will be written to a DDL script file. Valid choices are defined by the externalJpaName value of the Action enum. For example, using the drop-and-create option will generate database dropping commands followed by database creation commands. Check out the Hibernate configuration docs to learn more.

javax.persistence.schema-generation.scripts.create-target

I used this property to specify the location of the file that contains the schema creating commands. My schema.sql file will be generated in the main project folder. You can read in the documentation that this property:

(…) specifies either a java.io.Writer configured for output of the DDL script or a string specifying the file URL for the DDL script.

https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#configurations-hbmddl

hibernate.format_sql

By setting this property to true, I will get the commands formatted in my schema.sql file. It’s also useful when you are printing the Hibernate output directly to a console and want to make it more readable.

javax.persistence.schema-generation.create-source

Furthermore, you can determine whether creation commands should be generated from object/relational mapping metadata, DDL scripts, or a combination of both. Valid options are specified in the SourceType enum documentation.

In my case, the default value is metadata. Therefore I don’t need to include this property in my configuration. You can read in the documentation:

If no value is specified, a default is assumed as follows:
– if source scripts are specified (per javax.persistence.schema-generation.create-script-source), then script is assumed;
– otherwise, metadata is assumed.

https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#configurations-hbmddl

Run your application

On the application startup the commands will be written to the schema.sql file. Example file from one of my projects looks like this:

There are only 4 tiny entities in this model and it was still faster to generate the SQL than to write it manually.

How to save the DDL script and create the actual schema simultaneously

The solution presented above is adequate when you only want to generate the file with the SQL commands. Those commands won’t be executed against your database on the application startup unless you add the following line to the properties:

javax.persistence.schema-generation.database.action

We use this property to define which action should be performed automatically on an application startup. The default value is none. Therefore the schema won’t be created if you won’t override it. You can find available values in the documentation.

You should follow a single schema generation approach. Using spring.jpa.hibernate.ddl-auto won’t generate tables in our case. You can learn more in the Schema generation properties not well documented issue.

Usage

  • Source for creating and initializing a database. Spring Boot loads SQL from the standard root classpath locations: schema.sql and data.sql, respectively (documentation).
  • Source for an SQL-based migration (using Flyway or Liquibase).

You can also explore this subject on Stack Overflow. Check out example answers and comments:

Photo by  Chevanon on StockSnap

Leave a Reply

Your email address will not be published.