4

I am trying to access an in memory H2 database from the intellij console. I'm using spring boot to configure everything. The connection string is: spring.datasource.url=jdbc:h2:mem:testdb

When I connect to the database using intellij I can't see or query the tables. I can run a create table command to get it but that is it. Here is an image of that config:

enter image description here

The result of a SHOW TABLES query returns nothing.

Why can't I connect to this database?

Tim Schimandle
  • 792
  • 11
  • 22
  • 1
    Why did you decide that your app doesn't run your schema? – Cepr0 Aug 18 '17 at 20:31
  • I ran your code and its working. I would suggest to enable the h2 console in your properties file: `spring.h2.console.enabled=true` `spring.h2.console.path=/console` That way you can query your db from your browser (*http://your_path/console*). – Juan Carlos Mendoza Aug 18 '17 at 20:54
  • 1
    It's just from you log: `Executing SQL script from class path resource [schema-h2.sql]` – Cepr0 Aug 18 '17 at 21:00
  • Don't use h2-console, use direct access to DB: https://stackoverflow.com/a/43276769 – Cepr0 Aug 18 '17 at 21:02
  • And don't forget to upvote the answer if it will help you ;) – Cepr0 Aug 18 '17 at 21:03
  • @Cepr0 just updated my question with more details that are hopefully helpful. – Tim Schimandle Aug 19 '17 at 00:22
  • Have you completed all the steps from my [how-to](https://stackoverflow.com/a/43276769)? (I don't see it in your [repo](https://github.com/tschiman/water-meter)). First comment out all settings in your `app..properties` and set ones from how-to, then check again... – Cepr0 Aug 19 '17 at 05:29
  • 3
    Ofcourse you cannot see it... It is an in-memory database and is only available to the application. You are basically starting a new db from IntelliJ. You either have to start H2 in server mode and connect to it or use a file based URI instead of in-memory. – M. Deinum Aug 19 '17 at 08:00
  • Was out of town for a bit @M.Deinum you are correct. The in memory database is isolated to the running spring boot app. I cannot see it from intellij as when I create that db connection I create a new db. I've added some unit tests that confirm the database is there and operating. – Tim Schimandle Aug 23 '17 at 16:21
  • @M.Deinum if you want to answer the edited question I will award your comment as the answer. I think it is important to indicate somwhere that the in memory connection string produces an isolated db that is not accessible elsewhere. – Tim Schimandle Aug 23 '17 at 19:40

2 Answers2

6

When using an in-memory database like H2 or HSQLDB that in-memory instance is only accessible from the application.

If you want to have a shared database (so that you can see what is inside it) you have to start it in [server mode][2] or use a file based mode instead (jdbc:h2:~/your-db).

M. Deinum
  • 115,695
  • 22
  • 220
  • 224
4

The answer by @m-deinum is generally right in that it's only available for the application by default. If you want to access the in-mem instance from outside, you'll have to either:

And ensure the application is running. Otherwise you'd just be creating another in-mem instance.

eis
  • 51,991
  • 13
  • 150
  • 199