0

I'm using PostgreSQL 10, Java 11, STS 4, and am attempting to build a Spring Boot 2 application. In Django and Rails, there are tools that allow you to auto-generate SQL scripts after you have constructed your models. Does the same exist for Java/Spring? I created this application.properties file

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect

spring.jpa.hibernate.ddl-auto=create

spring.jpa.hibernate.show-sql=true

spring.datasource.url=jdbc:postgresql://${PG_DB_HOST:localhost}:5432/${PG_DB_NAME}

spring.datasource.username=${PG_DB_USER}
spring.datasource.password=${PG_DB_PASS}

flyway.url = jdbc:postgresql://${PG_DB_HOST:localhost}:5432/${PG_DB_NAME}
flyway.schemas = ${PG_DB_NAME}
flyway.user = ${PG_DB_USER}
flyway.password = ${PG_DB_PASS}

and I have this Java entity ...

import java.util.UUID;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "Occasions")
public class Occasion {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="id")
    private UUID id;

    @Column(name="name")
    private String name;
    
}

but I'm unclear how or if it is possible to auto-generate SQL scripts using my entity, or if I have to write them myself. Any information is appreciated.

Svetlin Zarev
  • 14,713
  • 4
  • 53
  • 82
satish
  • 703
  • 5
  • 23
  • 52
  • Yes, you can, but you should not - the ORMs are pretty terrible at doing that. Instead you should use a proper db migration tool such as Flayway and write the scripts yourself – Svetlin Zarev Jun 20 '20 at 17:42
  • If I'm writing the scripts myself, what is the point of using Flayway? – satish Jun 20 '20 at 18:50
  • Does this answer your question? [How to see the schema sql (DDL) in spring boot?](https://stackoverflow.com/questions/37648395/how-to-see-the-schema-sql-ddl-in-spring-boot) – DEWA Kazuyuki - 出羽和之 Jun 20 '20 at 19:34

2 Answers2

-2

If you are using spring-boot, jpa + hibernate frameworks are by default.

With this frameworks is not necessary the DDL scripts creation, because when your app starts, the tables are created according to your configurations: @Entity , @Table, etc

So, just run your app with this parameter: create-drop

spring.jpa.hibernate.ddl-auto=create-drop

And review your database to search your new tables.

Reference:

https://docs.spring.io/autorepo/docs/spring-boot/1.1.0.M1/reference/html/howto-database-initialization.html

JRichardsz
  • 14,356
  • 6
  • 59
  • 94
  • 1
    This would work for development, but I feel insecure about this kind of setup for production. I would feel much better if there were migration scripts generated on development that I could store in Git and then run at the appropriate time when deploying to production. – satish Jun 20 '20 at 18:49
  • You are right. A common practice is export your created database as dump and import in your next environments. In production, this value must be none or validate – JRichardsz Jun 20 '20 at 20:14
  • What if there are incremental changes to a table between releases, for example, adding a column? It would seem exporting my database as a dump and then re-importing that to production may not work, and might come with its own set of risks. – satish Jun 21 '20 at 18:17
  • Of course that just works for early production deployments. For incremental changes, you could use `spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=create.sql` as @出羽和之 said. Your questions was for scripts creation from your model. The answer is yes but is not required in development. For next environments, export the dump or use **schema-generation.scripts.action** – JRichardsz Jun 21 '20 at 18:24
-2

Put on application.properties:

spring.jpa.properties.javax.persistence.schema-generation.create-source=metadata
spring.jpa.properties.javax.persistence.schema-generation.scripts.action=create
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=create.sql

see: https://stackoverflow.com/a/36966419/4506703

DEWA Kazuyuki - 出羽和之
  • 2,453
  • 14
  • 57
  • What does a workflow normally look like with this solution? In other words, does one run a command that generates an SQL script that is run on different environments before the application is started? Also, does this generate only the scripts that were missing since the last time the script generation command was run? – satish Jun 21 '20 at 18:41