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.

user_6654475 · Answer

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

user_8948995 · Answer

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

right click at the datasource then choose Properties

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:

user_5938276 · Answer

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.

How to access in-memory h2 database from Intellij IDEA

3 Answers