[FIXED] Spring app tries to recreate the DB on each run instead of updating

Issue

I have a small Spring Boot app and Postgresql database. I want to use JPA to leverage JpaRepository and I'm now writing the schema.sql to initialize the DB which looks like this:

CREATE TABLE historical_prices
(
id float NOT NULL,
date_time varchar(100),
rate float NOT NULL,
PRIMARY KEY (id)
);

My application properties is configured as follows:

spring.datasource.initialization-mode=always
spring.datasource.platform=postgres
spring.datasource.url=jdbc:postgresql://localhost:5432/cryptodb
spring.datasource.username=postgres
spring.datasource.password=akljevnc983iu4fneciu3infejc

spring.jpa.database=POSTGRESQL
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.PostgreSQLDialect

Yet each time I run the app I get the following error:

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration': Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/crypto/target/classes/schema.sql]: CREATE TABLE historical_prices ( id float NOT NULL, date_time varchar(100), rate float NOT NULL, PRIMARY KEY (id) ); nested exception is org.postgresql.util.PSQLException: ERROR: relation "historical_prices" already exists

Why does it say org.postgresql.util.PSQLException: ERROR: relation "historical_prices" already exists if I'm setting spring.jpa.hibernate.ddl-auto=none?

I've also tried with spring.jpa.hibernate.ddl-auto=update but still no luck.

Is there a way to omit this creation step without removing the DDL?

Solution

From Spring documentation :

Spring Boot automatically creates the schema of an embedded DataSource. This behavior can be customized by using the spring.datasource.initialization-mode property (and it can also be always or never).

You should set the property to never.

Answered By - Harry Coder
Answer Checked By - David Marino (JavaFixing Volunteer)