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

Then in the following dialog, I enter the JDBC URL

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

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.

Horntail

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. – Pharyngoscope

Duplicate of https://mcmap.net/q/243005/-spring-boot-intellij-embedded-database-headache – Imes

Answer 1

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

1. 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
2. use a server mode database

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

Uncommonly

for those who wondering how to enable mixed mode: jdbc:h2:~/test;AUTO_SERVER=TRUE (both in app config and idea db manager settings) – Snead

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? – Apostle

Answer 2

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.

Fix

Answer 3

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

Rocket

if you are using spring boot you should have spring.h2.console.enabled=true in application.properties file – Odisodium

localhost:8080/h2-console url is working not above mentioned. – Confined

Answer 4

During development you can use grails h2 dbconsole

Girth

The idea is that we want Intellij to give us code hints from the db – Apostle

Answer 5

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!

Edaphic