Spring Boot + IntelliJ + Embedded Database = Headache

Question

Either I'm missing some core concept buried deep within some documentation (Spring, Spring Boot, H2, HSQLDB, Derby, IntelliJ) or I've been staring at this for too long.

I have a Spring Boot project going. Tried using and initializing an H2 DB at first, tried connecting to it in IntelliJ only to realize that I might not be able to easily browse the db without giving up my firstborn child (Connect to H2 database using IntelliJ database client).

So I moved to DerbyDB. Same thing - db root folder is created in my app, I connect to it in IntelliJ but my tables, that were just created from starting the app are not available to browse.

I even tried SQLite, but support for SQLite is not as good and certain update functions were not available, but I could atleast find my tables in IntelliJ browser!

I just want a simple single file embedded DB that I can use, browse, and play with easily. Any suggestions?!

When I run the application, I see that the schema is exported:

2015-07-19 09:37:45.836  INFO 98608 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export
Hibernate: drop table user_roles if exists
Hibernate: drop table users if exists
Hibernate: create table user_roles (id bigint generated by default as identity, role_name varchar(255), version bigint, user_id bigint, primary key (id))
Hibernate: create table users (id bigint generated by default as identity, email varchar(255), password varchar(255), username varchar(255), version bigint, primary key (id))
Hibernate: alter table user_roles add constraint FK_g1uebn6mqk9qiaw45vnacmyo2 foreign key (user_id) references users
2015-07-19 09:37:45.849  INFO 98608 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000230: Schema export complete

In IntelliJ, nothing (using a remote source of jdbc:h2:./test;AUTO_SERVER=TRUE as per heenenee suggestion):

I see some votes to close because its unclear what I'm asking:

How do I develop applications using H2, HSQLDB, or Derby databases and connect to them with IntelliJ?

Answer 1

H2 Automatic Mixed Mode should be fine for you. Use jdbc:h2:~/mydbInHomeDir;AUTO_SERVER=TRUE as your spring.datasource.url. In IntelliJ, create a remote H2 data source and use the exact same JDBC URL. You may have to explicitly press the Synchronize button in the IntelliJ Database window to get the database tables to show up.