databasespring-bootintellij-ideah2

Intellij embedded H2 database tables do not appear

I'm creating a Spring Boot application and I'm using Intellij's embedded h2 database.

I have added the following lines in my application.properties file:

spring.datasource.url=jdbc:h2:~/testdb;MV_STORE=false;AUTO_SERVER=TRUE

This is my data source configuration Data source configuration

Although the connection is successful and I can query the database using Intellij's query console, the tables do not appear in the Database tab.

Succeeded
DBMS: H2 (ver. 2.1.210 (2022-01-17))
Case sensitivity: plain=upper, delimited=exact
Driver: H2 JDBC Driver (ver. 2.1.210 (2022-01-17), JDBC4.2)
Ping: 16 ms

Database tab

When I refresh the connection or go to the schemas tab of the data source configuration, I get the following error:

[42S02][42102] org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "INFORMATION_SCHEMA_CATALOG_NAME" not found; SQL statement: select CATALOG_NAME from INFORMATION_SCHEMA.INFORMATION_SCHEMA_CATALOG_NAME [42102-210].
Solution

  • By going to the advanced tab of the data source and clicking on expert options, we are presented with a checkbox labeled "Introspect using JDBC metadata"

    Data source advanced tab

    By checking that box, the tables successfully appear in the Database tab

    Database tab

    Regarding why this works, this is taken from the official documentation: https://www.jetbrains.com/help/datagrip/data-sources-and-drivers-dialog.html

    Introspect using JDBC metadata

    Switch to the JDBC-based introspector. Available for all the databases.

    To retrieve information about database objects (DB metadata), DataGrip uses the following introspectors:

    A native introspector (might be unavailable for certain DBMS). The native introspector uses DBMS-specific tables and views as a source of metadata. It can retrieve DBMS-specific details and produce a more precise picture of database objects.

    A JDBC-based introspector (available for all the DBMS). The JDBC-based introspector uses the metadata provided by the JDBC driver. It can retrieve only standard information about database objects and their properties.

    Consider using the JDBC-based intorspector when the native introspector fails or is not available.

    The native introspector can fail, when your database server version is older than the minimum version supported by DataGrip.

    You can try to switch to the JDBC-based introspector to fix problems with retrieving the database structure information from your database. For example, when the schemas that exist in your database or database objects below the schema level are not shown in the Database tool window.