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.5project with theWebandJPAdependencies. - 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:
|
1 2 3 4 5 |
# 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
https://docs.spring.io/spring-boot/docs/2.2.5.RELEASE/reference/html/howto.html#howto-configure-jpa-propertiesspring.jpa.properties.*are passed through as normal JPA properties (with the prefix stripped) when the localEntityManagerFactoryis created.
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
https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#configurations-hbmddljava.io.Writerconfigured for output of the DDL script or a string specifying the fileURLfor the DDL script.
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:
https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#configurations-hbmddl
– if source scripts are specified (perjavax.persistence.schema-generation.create-script-source), then script is assumed;
– otherwise, metadata is assumed.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
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:
|
1 2 3 |
# 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.sqlanddata.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: