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’m working on a
Spring Boot 2.2.5project with the
- 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
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 inhttps://docs.spring.io/spring-boot/docs/2.2.5.RELEASE/reference/html/howto.html#howto-configure-jpa-properties
spring.jpa.properties.*are passed through as normal JPA properties (with the prefix stripped) when the local
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.
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.
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 ahttps://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#configurations-hbmddl
java.io.Writerconfigured for output of the DDL script or a string specifying the file
URLfor the DDL script.
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.
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 (per
javax.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:
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:
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.
- Source for creating and initializing a database. Spring Boot loads SQL from the standard root classpath locations:
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: