0

I'm setting up a small SpringBoot application that will be using PostgreSQL as database. My idea for development was to use a H2 embedded database "passing as" the Postgre database, but I'm not really understanding what I need to do to set up everything:

1) Just in case, we are using Gradle for dependency management:

compile group: 'com.h2database', name: 'h2', version: '1.3.148' 
compile group: 'org.springframework', name: 'spring-jdbc', version: '5.2.6.RELEASE'
compile group: 'org.springframework.boot', name: 'spring-boot-starter-jdbc', version: '2.3.0.RELEASE'

2) This is my application-dev.properties ("dev" is the profile I'm running the application) (Updated after a comment down below and after reading this other question):

spring.datasource.url=jdbc:h2:mem:testdb;MODE=PostgreSQL
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password

spring.datasource.schema=classpath:/sql/schema.sql
spring.datasource.data=classpath:/sql/data.sql
spring.datasource.initialize=true

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

3) (Updated) schema.sql is our database creation script, data.sql is the data script (obviously) and both are in /resources/sql.

The issue here is that the application starts, it loads the profile (The following profiles are active: dev), but when going to the h2 console, the database's empty. I even tried to change the name of the script (to "_ddl.sql") and the application just starts the same, as if it wasn't even trying to find the file.

Also, does it make sense to mark GenericPostgreSQL on the H2 console options when login?

Any leads on what I'm missing here?

Neuromante
  • 531
  • 2
  • 12
  • 29
  • you could remove the `spring.datasource.data` entry in your properties file and rename your sql script to `data.sql`, see if your database is populated that way. Note that it shouldn't contain any DDL though - you can use `schema.sql` for that. – Krisz Jun 05 '20 at 08:32
  • Ok, the file I was given contained both the schema and data. I've separated the contents and renamed the file (Now I got data.sql and schema.sql), removed the spring.datasource.data entry, but the files still don't load. – Neuromante Jun 05 '20 at 08:47

2 Answers2

1

If you have your database creation script in ddl.sql, the correct entry in the properties file would be spring.datasource.schema. spring.datasource.data is used to load initial data into the database during application startup. You are mixing these up.

Krisz
  • 1,884
  • 12
  • 17
  • It seems that as soon as I add the "spring.datasource.schema" line, the application crashes on startup because `Error creating bean with name 'h2Console' defined in class path resource [org/springframework/boot/autoconfigure/h2/H2ConsoleAutoConfiguration.class]` I guess I'll be checking this error, as it seems there's more setup to do... – Neuromante Jun 05 '20 at 08:51
  • I am not familiar with h2 console, perhaps this could help you? https://stackoverflow.com/questions/48564043/spring-boot-legacy-with-h2-console Otherwise you could check the reason why it couldn't create a bean using `H2ConsoleAutoConfiguration`, you can find it in the stacktrace, that will help – Krisz Jun 05 '20 at 08:56
1

Well, I kind of solved it.

It seems it was an issue with mixed dependencies and very long error messages. The "final" state of these dependencies is the following:

    compile group: 'com.h2database', name: 'h2', version: '1.3.148'
    compile group: 'org.springframework.boot', name: 'spring-boot-starter-jdbc', version: '2.3.0.RELEASE'

I was (also) using spring-jdbc and it seems it collided with spring-boot-starter-jdbc.

Now, the most important part is what I read here after realizing that now the errors I was getting involved syntax problems: Basically, H2 and PostgreSQL are not really good friends and is better to just use a PostgreSQL database for local development. Welp.

Neuromante
  • 531
  • 2
  • 12
  • 29
  • even though I agree that it's best to use the same stack between environments, I have never had problems with using H2 in the early stages of the development, so I would encourage you to go ahead and use it as long as it is convenient. Hibernate does a really good job abstracting away the underlying database. – Krisz Jun 05 '20 at 11:22
  • The problem is that I'm in the earliest of development. The SQL file I was handed stops working within the second line, and given my only experience is with Oracle, I'll pass on doing more research, create a local postgre database and move on. A shame, because the H2 option seemed to be very elegant, but... – Neuromante Jun 05 '20 at 11:46
  • It's better to use the same DBMS in both production and tests, but if you want to use H2 in tests you at least should use some recent version, such as 1.4.200, the version 1.3.148 was released almost 10 years ago. – Evgenij Ryazanov Jun 05 '20 at 11:52