Categories: Tools

Save Hibernate DDL schema to a file

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:

# application-dev.properties
spring.jpa.properties.javax.persistence.schema-generation.scripts.action=create
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=schema.sql
spring.jpa.properties.hibernate.format_sql=true
…

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:

    create table project (
       id  bigserial not null,
        description varchar(255),
        name varchar(255),
        primary key (id)
    );

    create table project_member (
       id  bigserial not null,
        project_role varchar(255),
        project_id int8 not null,
        user_id int8,
        primary key (id)
    );

    create table task (
       id  bigserial not null,
        description varchar(255),
        name varchar(255),
        project_id int8 not null,
        primary key (id)
    );

    create table user_data (
       id  bigserial not null,
        password varchar(255),
        role varchar(255),
        username varchar(255),
        primary key (id)
    );

    alter table user_data 
       add constraint UK_nlc4atex50p892vsfhwccm336 unique (username);

    alter table project_member 
       add constraint FK103dwxad12nbaxtmnwus4eft2 
       foreign key (project_id) 
       references project 
       on delete cascade;

    alter table project_member 
       add constraint FKs78dyhytnqmuphtdlfko4pag3 
       foreign key (user_id) 
       references user_data 
       on delete cascade;

    alter table task 
       add constraint FKk8qrwowg31kx7hp93sru1pdqa 
       foreign key (project_id) 
       references project 
       on delete cascade;

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:

# application-dev.properties
…
spring.jpa.properties.javax.persistence.schema-generation.database.action=create

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