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.

I assume Intellij IDEA is creating new in-memory db with default user and password when I click the button "Test Connection" — valijon, Sep 18 '18 at 14:35
In-memory database results are available only while the application is running and you have stored some data. See http://www.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. — Andrey, Sep 18 '18 at 15:19
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 — Vishal Torne, Sep 18 '18 at 16:51
Duplicate of https://stackoverflow.com/questions/28940912/connect-to-h2-database-using-intellij-database-client/28950817#28950817 ? — Javaru, Sep 19 '18 at 19:20
Duplicate of https://stackoverflow.com/a/64673466/2930427 Requires H2 to be exposed as per example code here: https://stackoverflow.com/a/52949164/2930427 — jase, Nov 04 '20 at 03:59

Francislainy Campos · Answer 1 · 2022-04-05T04:08:28.083

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

I did this and could see the tables, but my data,sql was not executed. Do I need to change CREATE-DROP to UPDATE? — Al Grant, Apr 04 '22 at 20:43
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. — Francislainy Campos, Apr 05 '22 at 04:11
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? — Francislainy Campos, Apr 05 '22 at 04:30

Phong Bui · Answer 2 · 2020-07-27T04:52:58.037

1

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:

edited Jul 27 '20 at 04:52

answered Jul 15 '20 at 11:24

Phong Bui

441
3
7

score 1 · Answer 3 · answered Apr 05 '22 at 17:18

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 Answers3

Linked