How to access in-memory h2 database from Intellij IDEA

Question

In Spring Boot project I am trying to see in-memory tables from my IDE.

How to access in-memory h2 database from Intellij IDEA.

Here is a snippet from my application.yml:

 h2:
    datasource:
        url: jdbc:h2:mem:mydb
        username: username
        password: 123
        driver-class-name: org.h2.Driver
        init-sql: h2.sql
    console:
      enabled: true
      path: /search/console
      settings:
        trace: false
        web-allow-others: false

Intellij has no field to input username for in-memory database: Test Connection shows success, however it doesn't see tables from h2.sql. I can access them using h2 console.

Buckra

I assume Intellij IDEA is creating new in-memory db with default user and password when I click the button "Test Connection" – Buckra

In-memory database results are available only while the application is running and you have stored some data. See h2database.com/html/features.html#in_memory_databases. If you want to create a Data Source for the in-memory database that your application uses, you need to start tcp server in your application on some tcp port and use this port when creating new H2 Data Source in IDE. – Mclendon

If you add a maven or gradle runtime dependency it will be automatically available to you and populate some default values it should work fine – Illtreat

Duplicate of #28941412 ? – Diakinesis

Duplicate of https://mcmap.net/q/243005/-spring-boot-intellij-embedded-database-headache Requires H2 to be exposed as per example code here: https://mcmap.net/q/244780/-h2-db-not-accessible-at-localhost-8080-h2-console-when-using-webflux – Disaccredit

Answer 1

Just please bear in mind this may show you the db but not the table as they will only be visible in the h2 console. For you to access them through IntelliJ you may need to change the url and connection to be of a file type rather than in memory.

So instead of this:

#spring.datasource.url=jdbc:h2:mem:testdb

You'd have something like this:

spring.datasource.url=jdbc:h2:file:~/Users/yourUser/IdeaProjects/resume-portal/src/main/resources/data/resume-portal;MV_STORE=false;AUTO_SERVER=TRUE

And then after creating a datasource from url

You should now see both the database and its tables.

And that will still also be available from the console if you connect using the new url.

PS: If'd prefer to use a relative path you can change the url to be something similar to jdbc:h2:file:./src/main/resources/data/resume-portal;MV_STORE=false;AUTO_SERVER=TRUE

Here is the application.yml file for the application.

spring.jpa.defer-datasource-initialization=true
#spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.url=jdbc:h2:file:./src/main/resources/data/resume-portal;MV_STORE=false;AUTO_SERVER=TRUE
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.datasource.username=sa
spring.datasource.password=
spring.sql.init.mode=always

Repay

I did this and could see the tables, but my data,sql was not executed. Do I need to change CREATE-DROP to UPDATE? – Viewy

I'm not sure. I don't have that on my application.yml file. I've added it to the post so you can perhaps check if you may be missing anything else. – Repay

discovered it was a different issue – Viewy

Cool. Glad it's sorted. Maybe you'd want to tell what your issue was and how you fixed it in case someone else may face the same problem? – Repay

Answer 2

I found another way. I created the H2 Server bean and added it to my SpringBoot Application. It looks like this:

// Start internal H2 server so can query from IDE
@Bean(initMethod = "start", destroyMethod = "stop")
public Server h2Server() throws SQLException {
    return Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "9092");
}

I also had the following on my application.properties

spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.defer-datasource-initialization=true
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password

spring.sql.init.mode=always

# Initialise H2 with H2GIS for spatial support ? see schema-h2.sql also
spring.sql.init.platform=h2
spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.h2geodb.GeoDBDialect

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
h2.tcp.enabled=true

In order to get my data.sql put into the tables in Intellij I had to make sure I used create-drop and added the spring.sql.init.mode=always

Note: I was using the H2GIS dialect - but this has not so far caused any issues

The connection setup in Intellij was still remote with this string jdbc:h2:tcp://localhost:9092/mem:testdb - dont forget to change the port to 9092

I the connection window in Intellij I also checked all tables under Schema. Then the tables are present for the H2 in mem database.

Viewy

Answer 3

By the default, IntellJ doesn't show any of database.

1. right click at the datasource then choose Properties

2. At the Schemas tab, you'll see a list of options to choose which database should be shown (I normally choose All databases). Choose the database which you need it to be shown

The result:

Welldressed