Connect to H2 database using IntelliJ database client

Question

My Grails app uses a h2 database in dev mode (the default behaviour for Grails apps). The DB connection settings in DataSource.groovy are

dataSource {
    pooled = true
    jmxExport = true
    driverClassName = "org.h2.Driver"
    username = "sa"
    password = ""
    dbCreate = "create-drop" // one of 'create', 'create-drop', 'update', 'validate', ''
    url = "jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"
}

I'm trying to setup a connection for this database using IntelliJ IDEA's database client tools. I start off creating the connection like so

enter image description here

Then in the following dialog, I enter the JDBC URL

enter image description here

And choose all available databases on the "Schemas & Tables" tab.

enter image description here

The "Test Connection" button indicates success, but as you can see from the red circle, no tables are found. It seems like I've correctly setup a connection to the h2 server, but not the schema itself.

BTW, I try to setup this connection once the app is running, so I'm sure that the schema/tables do actually exist.

Did you ever figure this out? I used Mark's recommendations to no avail (use AUTO_SERVER=TRUE, and embedded DB in root directory). I can never seem to browse an H2 db from Intellij. This has become ridiculously difficult. — Jack, Jul 19 '15 at 05:39

score 126 · Answer 1 · answered Mar 09 '15 at 20:05

Your configuration is for an h2:mem database. Memory Databases have no tables upon connecting to them, and any & all tables are lost when all the connections are closed. Furthermore, a (named) in memory database is unique to the JVM process that opens it. From the H2 documentation:

Sometimes multiple connections to the same in-memory database are required. In this case, the database URL must include a name. Example: jdbc:h2:mem:db1. Accessing the same database using this URL only works within the same virtual machine and class loader environment. (Emphasis added)

This means IDEA will create a unique devDb in its JVM (and classloader) space and your application will create a unique devDb in its JVM (and classloader) space. You can not connect to an in memory database from an external JVM process.

If you want to connect both your application and IntelliJ IDEA (or any other DB tool) to an H2 database at the same time, you will need to either

use an embedded database (that writes to a file) in your application and use Mixed Mode to allow IntelliJ IDEA (and/or other database tools) to connect to it
use a server mode database

See http://www.h2database.com/html/features.html#connection_modes for more information.

for those who wondering how to enable mixed mode: jdbc:h2:~/test;AUTO_SERVER=TRUE (both in app config and idea db manager settings) — deviant, Feb 13 '18 at 16:03
Why does Intellji have a H2 option for connecting to it, if its not possible on the JVM level? Specifically for the mixed mode use? — Janac Meena, Jan 17 '20 at 18:39

score 21 · Answer 2 · edited Jan 27 '20 at 17:44

This article has a great write up on how to set up the IntelliJ database client to connect to an H2 in-memory database if you happen to be using Spring Boot: http://web.archive.org/web/20160513065923/http://blog.techdev.de/querying-the-embedded-h2-database-of-a-spring-boot-application/

Basically, you wrap the in-memory database with a tcp server, then you have an access point to connect with a sql client via remote access.

score 5 · Answer 3 · answered May 09 '17 at 06:46

5

Try to open http://localhost:8080/dbconsole and fill your jdbc url

answered May 09 '17 at 06:46

Peter.Chu

357
5
4

8

if you are using spring boot you should have `spring.h2.console.enabled=true ` in `application.properties` file – Basheer AL-MOMANI Jul 08 '18 at 06:58
8

http://localhost:8080/h2-console url is working not above mentioned. – Muhammad Azam Jan 15 '20 at 12:08

score 3 · Answer 4 · answered Aug 25 '15 at 19:36

3

During development you can use grails h2 dbconsole

answered Aug 25 '15 at 19:36

demon101

544
1
11
39

The idea is that we want Intellij to give us code hints from the db – Janac Meena Jan 17 '20 at 18:39

score 3 · Answer 5 · answered Oct 30 '21 at 10:37

Let's imagine you've already created entities (Users, Addresses)

Step 1. In application.yml file add H2 properties.

server:
  port: 8080

spring:
  datasource:
    url: jdbc:h2:~/data/parserpalce (for Mac OS)
    username: sa
    password: password
    driver-class-name: org.h2.Driver
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    hibernate:
      ddl-auto: update
  h2:
    console:
      enabled: true

Step 2. Add H2 Database client

Step 3. Configure H2 database client properties based on your application.yml properties.

Step 4. Run the application.

Step 5. Check if tables(Users, Addresses) are created.

Or you can use H2 console for it in browser: http://localhost:8080/h2-console

P.S. Do not forget to paste appropriate values in fields!

Connect to H2 database using IntelliJ database client

5 Answers5

Linked

Related