Generate database schema with JPA
Take your skills to the next level!
The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.
Your entity classes map a database schema, which must be created before or during application startup. You can do this in various ways. You can run your own scripts, use tools like Flyway or Liquibase, or use the schema generation feature defined by the Jakarta Persistence specification (JPA).
In this article, you will learn how to configure JPA’s schema generation feature, when to use it, and when to prefer a more advanced approach.
When to use JPA’s schema generation
As always, you should check if you’re using the right tool before you dive into its configuration details. This is especially true when generating your database schema.
Even though the Jakarta Persistence specification and Hibernate, as its most popular implementation, can generate your database schema, it’s not often the best approach.
The configuration mechanism I will show you in this article is a great fit for simple integration tests and to generate the required scripts to create your application’s initial database.
If you already deployed your application to production and you’re looking for an automated way to migrate your database alongside your application’s code, I recommend using a version-based database migration tool like Flyway or Liquibase.
JPA’s schema generation parameters
The JPA specification defines several parameters that you can use in your persistence.xml configuration to define how to create your database. You can see them below. Some depend on each other, which I’ve mentioned in the parameter’s description.
The names of all configuration parameters differ depending on your JPA version. If you use Java Persistence API 2, all configuration parameter names start with javax. Starting with Jakarta Persistence API 3, the parameter names begin with jakarta.
For a comprehensive understanding, this article primarily references the JPA 3 version of the parameter names. However, it also includes detailed parameter descriptions and all example configurations for JPA 2 and JPA 3.
Let’s start with an overview of the different parameters and follow up with some example configurations.
| Parameter | Allowed Values | Description |
|---|---|---|
| jakarta.persistence.schema-generation.database.action | create, drop-and-create, drop, none | Defines whether the persistence provider shall: – create the database, – first drop, and then recreate it, – only drop it, or – do nothing at all. If this property is not provided, no schema will be created. |
| jakarta.persistence.schema-generation.scripts.action | create, drop-and-create, drop, none | Defines which scripts the persistence provider shall create. A script target needs to be defined for each script to be created. |
| jakarta.persistence.schema-generation.create-source | metadata, script, metadata-then-script, script-then-metadata | Defines how the schema shall be created. It can be done based on: – the mapping metadata, – a script, or – a combination of both. If this parameter is not provided, the persistence provider will use only the mapping metadata or a script if the jakarta.persistence.schema-generation.create-script-source property is defined. |
| jakarta.persistence.schema-generation.drop-source | metadata, script, metadata-then-script, script-then-metadata | Defines how the schema shall be dropped. It can be done based on: – the mapping metadata, – a script or – a combination of both. If this parameter is not provided, the persistence provider will use only the mapping metadata or a script if the jakarta.persistence.schema-generation.drop-script-source property is defined. |
| jakarta.persistence.schema-generation.create-database-schemas | true, false | Defines if the persistence provider shall also create the required database schemas or only the other database objects like tables, sequences, etc. This property defaults to false. |
| jakarta.persistence.schema-generation.scripts.create-target | file URL, java.IO.Writer | Defines the target location of the create script generated by the persistence provider. |
| jakarta.persistence.schema-generation.scripts.drop-target | file URL, java.IO.Writer | Defines the target location of the drop script generated by the persistence provider. |
| jakarta.persistence.database-product-name | String | The name of the target database as it is returned by the JDBC DatabaseMetaData method getDatabaseProductName. This parameter is required if the persistence provider shall create scripts without a connection to the database. |
| jakarta.persistence.database-major-version | String | The major version of the target database as it is returned by the JDBC getDatabaseMajorVersion method. |
| jakarta.persistence.database-minor-version | String | The minor version of the target database as it is returned by the JDBC getDatabaseMinorVersion method. |
| jakarta.persistence.schema-generation.create-script-source | file URL, java.IO.Writer | Defines the location of the create script. This parameter has to be defined, if the jakarta.persistence.schema-generation.create-source property was set to script, metadata-then-script or script-then-metadata. |
| jakarta.persistence.schema-generation.drop-script-source | file URL, java.IO.Writer | Defines the location of the drop script. This parameter has to be defined, if the jakarta.persistence.schema-generation.drop-source property was set to script, metadata-then-script or script-then-metadata. |
| jakarta.persistence.schema-generation.connection | String | Specifies the JDBC connection that shall be used to create the database. |
| jakarta.persistence.sql-load-script-source | file URL, java.IO.Writer | Defines the location of the SQL script that shall be used to load data into the database. |
| Parameter | Allowed Values | Description |
|---|---|---|
| javax.persistence.schema-generation.database.action | create, drop-and-create, drop, none | Defines whether the persistence provider shall: – create the database, – first drop, and then recreate it, – only drop it, or – do nothing at all. If this property is not provided, no schema will be created. |
| javax.persistence.schema-generation.scripts.action | none, create, drop-and-create, drop | Defines which scripts the persistence provider shall create. A script target needs to be defined for each script to be created. |
| javax.persistence.schema-generation.create-source | metadata, script, metadata-then-script, script-then-metadata | Defines how the schema shall be created. It can be done based on: – the mapping metadata, – a script, or – a combination of both. If this parameter is not provided, the persistence provider will use only the mapping metadata or a script if the javax.persistence.schema-generation.create-script-source property is defined. |
| javax.persistence.schema-generation.drop-source | metadata, script, metadata-then-script, script-then-metadata | Defines how the schema shall be dropped. It can be done based on: – the mapping metadata, – a script or – a combination of both. If this parameter is not provided, the persistence provider will use only the mapping metadata or a script if the javax.persistence.schema-generation.drop-script-source property is defined. |
| javax.persistence.schema-generation.create-database-schemas | true, false | Defines if the persistence provider shall also create the required database schemas or only the other database objects like tables, sequences, etc. This property defaults to false. |
| javax.persistence.schema-generation.scripts.create-target | file URL, java.IO.Writer | Defines the target location of the create script generated by the persistence provider. |
| javax.persistence.schema-generation.scripts.drop-target | file URL, java.IO.Writer | Defines the target location of the drop script generated by the persistence provider. |
| javax.persistence.database-product-name | String | The name of the target database as it is returned by the JDBC DatabaseMetaData method getDatabaseProductName. This parameter is required if the persistence provider shall create scripts without a connection to the database. |
| javax.persistence.database-major-version | String | The major version of the target database as it is returned by the JDBC getDatabaseMajorVersion method. |
| javax.persistence.database-minor-version | String | The minor version of the target database as it is returned by the JDBC getDatabaseMinorVersion method. |
| javax.persistence.schema-generation.create-script-source | file URL, java.IO.Writer | Defines the location of the create script. This parameter has to be defined, if the javax.persistence.schema-generation.create-source property was set to script, metadata-then-script or script-then-metadata. |
| javax.persistence.schema-generation.drop-script-source | file URL, java.IO.Writer | Defines the location of the drop script. This parameter has to be defined, if the javax.persistence.schema-generation.drop-source property was set to script, metadata-then-script or script-then-metadata. |
| javax.persistence.schema-generation.connection | String | Specifies the JDBC connection that shall be used to create the database. |
| javax.persistence.sql-load-script-source | file URL, java.IO.Writer | Defines the location of the SQL script that shall be used to load data into the database. |
Example Configurations
OK, the sheer number of configuration parameters and the dependencies between them can be a little confusing. So, let’s look at some example configurations.
Simple configuration to create the database
The first example configuration is also the simplest one. It creates the database based on your entity’s mapping metadata.
If your JPA implementation shall generate the table model based on your entity classes, you only have to configure the parameter jakarta.persistence.schema-generation.database.action and set it to create.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_2.xsd" version="3.2">
<persistence-unit name="my-persistence-unit" transaction-type="JTA">
<description>Forge Persistence Unit</description>
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
<property name="jakarta.persistence.schema-generation.database.action" value="create"/>
</properties>
</persistence-unit>
</persistence><?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="my-persistence-unit" transaction-type="JTA">
<description>Forge Persistence Unit</description>
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
<property name="javax.persistence.schema-generation.database.action" value="create"/>
</properties>
</persistence-unit>
</persistence>This configuration can be used to create an application’s initial version of a database. But be careful: You need to remove the database manually before you can start the application again or switch the jakarta.persistence.schema-generation.database.action parameter to none or drop-and-create. Otherwise, the database creation will fail on the second attempt.
The typical test database setup
This is a typical approach to creating a database for automated tests. Your persistence provider will drop the existing database and create a new one based on your entity’s mapping definition. Afterward, an SQL script fills the database with some initial data.Â
To achieve this, you must set the parameter jakarta.persistence.schema-generation.database.action to drop-and-create and provide the path to the SQL script via the parameter jakarta.persistence.sql-load-script-source.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_2.xsd" version="3.2">
<persistence-unit name="my-persistence-unit" transaction-type="JTA">
<description>Forge Persistence Unit</description>
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
<property name="jakarta.persistence.schema-generation.database.action" value="drop-and-create"/>
<property name="jakarta.persistence.sql-load-script-source" value="META-INF/data.sql"/>
</properties>
</persistence-unit>
</persistence><?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="my-persistence-unit" transaction-type="JTA">
<description>Forge Persistence Unit</description>
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
<property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
<property name="javax.persistence.sql-load-script-source" value="META-INF/data.sql"/>
</properties>
</persistence-unit>
</persistence>Script based setup of a test database
The previous example used the mapping metadata to set up the test database. However, the production database is normally created via SQL scripts, so we should do the same for our test setup.
This is easily achieved by providing a few additional parameters on top of the previous configuration.
Setting the parameters jakarta.persistence.schema-generation.create-source and jakarta.persistence.schema-generation.drop-source to script tells your JPA implementation, e.g., Hibernate, to use SQL scripts instead of the mapping metadata to create the database. The path to the SQL scripts is defined via jakarta.persistence.schema-generation.create-script-source and jakarta.persistence.schema-generation.drop-script-source.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_2.xsd" version="3.2">
<persistence-unit name="my-persistence-unit" transaction-type="JTA">
<description>Forge Persistence Unit</description>
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
<property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
<property name="javax.persistence.schema-generation.create-source" value="script"/>
<property name="javax.persistence.schema-generation.create-script-source" value="META-INF/create.sql"/>
<property name="javax.persistence.schema-generation.drop-source" value="script"/>
<property name="javax.persistence.schema-generation.drop-script-source" value="META-INF/drop.sql"/>
<property name="javax.persistence.sql-load-script-source" value="META-INF/data.sql"/>
</properties>
</persistence-unit>
</persistence><?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="my-persistence-unit" transaction-type="JTA">
<description>Forge Persistence Unit</description>
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
<property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
<property name="javax.persistence.schema-generation.create-source" value="script"/>
<property name="javax.persistence.schema-generation.create-script-source" value="META-INF/create.sql"/>
<property name="javax.persistence.schema-generation.drop-source" value="script"/>
<property name="javax.persistence.schema-generation.drop-script-source" value="META-INF/drop.sql"/>
<property name="javax.persistence.sql-load-script-source" value="META-INF/data.sql"/>
</properties>
</persistence-unit>
</persistence>Generate the create and drop scripts based on mapping metadata
The persistence provider, e.g., Hibernate, can also generate scripts based on your entity’s mapping definition to create and drop your database.
You activate the script generation by setting the jakarta.persistence.schema-generation.scripts.action parameter to drop-and-create and define the path to the new scripts via jakarta.persistence.schema-generation.scripts.create-target and jakarta.persistence.schema-generation.scripts.drop-target.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_2.xsd" version="3.2">
<persistence-unit name="EFS2015-persistence-unit" transaction-type="JTA">
<description>Forge Persistence Unit</description>
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
<property name="jakarta.persistence.schema-generation.scripts.action" value="drop-and-create"/>
<property name="jakarta.persistence.schema-generation.scripts.create-target" value="./create.sql"/>
<property name="jakarta.persistence.schema-generation.scripts.drop-target" value="./drop.sql"/>
</properties>
</persistence-unit>
</persistence><?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="EFS2015-persistence-unit" transaction-type="JTA">
<description>Forge Persistence Unit</description>
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
<property name="javax.persistence.schema-generation.scripts.action" value="drop-and-create"/>
<property name="javax.persistence.schema-generation.scripts.create-target" value="./create.sql"/>
<property name="javax.persistence.schema-generation.scripts.drop-target" value="./drop.sql"/>
</properties>
</persistence-unit>
</persistence>Conclusion
As you’ve seen, the Jakarta Persistence specification defines a set of parameters that you can use to set up your database based on your entity mappings or provided SQL scripts. You can also use them to let your persistence provider generate scripts based on your entity mappings.
Using these parameters, you can easily set up test databases or generate scripts that you can review and modify before executing them manually.
If you’re looking for an automated process to migrate your database to the latest version when you deploy an application update, I recommend using tools like Flyway or Liquibase. They give you more flexibility to define your migration steps and adjust your data to the schema changes.
You can find more information about the different tools and general best practices for automated database migrations on my database migration page.
Hi Thorben,
Very useful enhancements in JPA 2.1. I liked it most about the “Constructor result Mapping” and the new schema generation features.
I developed a proof of concept using JOOQ. Have you ever tried it?
I’ve always used JPA/Hibernate but I found JOOQ very interesting. IMHO it required less effort to build queries than using JPQL or Criteria API.
By the way, I liked the way you write straight to the point. Congrats for the initiative.
Hi Elvis,
thank you 🙂
I tried jOOQ in the past and I liked it. I think it’s a good option to write SQL queries and it’s probably a good idea to use it together with Hibernate. Maybe I should write a post about it …
Regards,
Thorben
if i want to generate the ddl schema scripts. how can i execute them using the properties of JPA2.1 mentioned above?
You can set it as the value of the javax.persistence.schema-generation.create-script-source property:
<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?>
<persistence xmlns=”http://xmlns.jcp.org/xml/ns/persistence” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” version=”2.1″ xsi:schemaLocation=”http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd“>
<persistence-unit name=”my-persistence-unit” transaction-type=”JTA”>
<description>Forge Persistence Unit</description>
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name=”hibernate.dialect” value=”org.hibernate.dialect.PostgreSQLDialect”/>
<property name=”javax.persistence.schema-generation.database.action” value=”drop-and-create”/>
<property name=”javax.persistence.schema-generation.create-source” value=”script”/>
<property name=”javax.persistence.schema-generation.create-script-source” value=”META-INF/create.sql”/>
<property name=”javax.persistence.schema-generation.drop-source” value=”script”/>
<property name=”javax.persistence.schema-generation.drop-script-source” value=”META-INF/drop.sql”/>
<property name=”javax.persistence.sql-load-script-source” value=”META-INF/data.sql”/>
</properties>
</persistence-unit>
</persistence>
Hi, I have been unable to download your JPA 2.1 cheat sheet. When I submit the request I get the generic error “Unable to subscribe you to the list.” . Not sure why this is since I am a subscriber already. I have tried updating my profile thinking that might help and unfortunately it doesn’t.
Any suggestions as to how to get the JPA 2.1 cheat sheet other than through the obvious links?
Regards,
Gary
Hi Gary,
I have send you an email with the download link.
Regards,
Thorben