Access h2 datatables in testing

[wadid]

Describe the bug

When running quarkus tests the test data gets loaded into an h2 in memory database. When trying to connect to the database with intelliJ I just get an empty database. But testdata is in the database and all the tests work as expected. So I just used it like that but I realized I am sometimes loosing lots of time when debugging and not being able to quickly visually check the datatables in test mode.

What is the recommended way to be able to display the datatables?

I read also there is a possibility with vertx and undertow to use h2 console in browser. https://stackoverflow.com/questions/61853691/how-to-set-h2-database-console-url-in-the-quarkus-application

What would be the way most recent way to go? Any functionality for that implemented in quarkus cannot find anything in documentation about it.

[wadid]

I followed this https://stackoverflow.com/questions/61853691/how-to-set-h2-database-console-url-in-the-quarkus-application.

Then the console is available when I run the application for normal development. under http://localhost:8082/h2/

But when running my tests (with RestAssured) the console is not available. I tried it with breakpoints that only are stopping the thread and also I tried setting a while(true) loop in one of the tests so the test would not finish and then I would try to access the console, but none of it worked.

Is there any additional settings that I need to do for accessing the h2 console when running tests?

in the application.yml I also have the following

"%test":
  quarkus:
    datasource:
      db-kind: h2
      jdbc:
        driver: org.h2.Driver
        url: jdbc:h2:mem:test

also I tried adding the META-INF folder with the web.xml not only under main/resources but also under test/resources. But I am not sure if that is something you would do?

Any idea how to access the h2 console for tests?

Maybe when running tests the servlets are not getting loaded? Or maybe it will run under a different port? I am not really sure what is the problem.

Maybe I need to add a code in the TestProfiles that loads the servlet explicitly? Any idea where and how to that?

[famod]

Back when I was using h2 in my current Quarkus project, I have been using the following:

import java.sql.Connection;

import javax.enterprise.inject.spi.CDI;
import javax.persistence.EntityManager;

import org.h2.tools.Server;
import org.hibernate.Session;

/**
 * Tooling for H2 based tests.
 */
public class H2Tools {

    private H2Tools() {
    }

    /**
     * Calls {@link #browseViaHibernate(EntityManager)} with {@link EntityManager} retrieved via {@link CDI}.
     */
    public static void browseViaHibernate() {
        final var cdi = CDI.current();
        browseViaHibernate(cdi.select(EntityManager.class).get());
    }

    /**
     * Starts the system's web browser with the h2 console. This method will block until the disconnect button is clicked!
     *
     * @param em provides the hibernate session that provides the required connection
     */
    public static void browseViaHibernate(final EntityManager em) {
        em.unwrap(Session.class).doWork(
                // note: additional unwrap to cover io.agroal.pool.wrapper.ConnectionWrapper
                connection -> Server.startWebServer(connection.unwrap(Connection.class)));
    }
}

Add a breakpoint (within a transaction!) and either run H2Tools.browseViaHibernate() manually (as a debug expression) or make the breakpoint conditional and add the call to it.

[famod]

@wadid is this working for you?

[wadid]

@famod Just tested it. Awesome!!! It worked right out of the box. Just executing H2Tools.browseViaHibernate()in the code will open the h2 console in the browser and shows me the data. I observed in my intellij console/terminal (not h2 console) when testing starts it shows for example Connected to the target VM, address: '127.0.0.1:58846', transport: 'socket'' Then when the browseViaHibernate() method gets called a browser window with the h2 console and the following url http://192.168.178.43:58854/frame.jsp?jsessionid=8f9b9573e233a9d75aefe076fc103954 opens.

So 192.168.178.43:58854 is the h2 tools server and it connects maybe to '127.0.0.1:58846' with the session id or how does it work?

I am still wondering if I can get the servlet version working. Is there a way to see if that servlet in webxml is executed or running, or however you would call that? First time I had to use something like . I am pretty new to all these framework related things used in java.

Btw, sometimes I get the following error when setting it as a condition in the breakpoint

Error: Failed to evaluate breakpoint condition 'H2Tools.browseViaHibernate()' Reason: Boolean value expected Would you like to stop at the breakpoint?

Any idea what is this about?

Thank you very much again! I was not able to find a working solution before.

[famod]

@wadid

In console when testing starts it shows for example Connected to the target VM, address: '127.0.0.1:58846', transport: 'socket'' Then when the browseViaHibernate() method gets called a browser window with the h2 console and the following url http://192.168.178.43:58854/frame.jsp?jsessionid=8f9b9573e233a9d75aefe076fc103954 opens.

So 192.168.178.43:58854 is the h2 tools server and it connects maybe to '127.0.0.1:58846' with the session id or how does it work?

See http://h2database.com/html/advanced.html?highlight=bind,address&search=bind%20address#firstFound

I remember setting this property to 127.0.0.1 because it usually doesn't make sense to have h2 listen on an external interface.

Btw, sometimes I get the following error when setting it as a condition in the breakpoint

Error: Failed to evaluate breakpoint condition 'H2Tools.browseViaHibernate()' Reason: Boolean value expected Would you like to stop at the breakpoint?

Any idea what is this about?

That method is not returning anything (void) but the expression of an conditional breakpoint must evaluate to true or false. So either end that call with; and add return false; or let that method return false.

[famod]

@geoand WDYT, is this something worthwhile having in the h2 module? It would require an optional dependency on Hibernate ORM and it should also have multi EM support.

[geoand]

That's a pretty cool trick!

I'll leave it to @yrodiere and @Sanne however as this is their wheelhouse. I'll just say that I like the idea of making the user experience better :)

[yrodiere]

Hey,

If the goal is to connect to the H2 database from some external tool, e.g. something from Intellij IDEA Ultimate, you'll want to replace this:

"%test":
  quarkus:
    datasource:
      db-kind: h2
      jdbc:
        driver: org.h2.Driver
        url: jdbc:h2:mem:test

with this:

"%test":
  quarkus:
    datasource:
      db-kind: h2
      jdbc:
        driver: org.h2.Driver
        url: jdbc:h2:tcp://localhost/mem:test

Then your H2 instance will be available through TCP.

As far as I can tell, you would have gotten that from free if you were using dev services, since we start a TCP server by default in that case.

---

As for the H2 console, I wouldn't make that dependent on Hibernate ORM, you can just get a connection directly from a Datasource object if necessary. Also, I wonder if you can just enable that console by adding options in the JDBC URL, since the org.h2.tools.Server class seems to implement both the HTTP server and TCP server... We may not need a custom class at all.

But it would certainly be a good idea to enable it in dev mode. Perhaps just add a link to it from the dev UI?

[geoand]

But it would certainly be a good idea to enable it in dev mode. Perhaps just add a link to it from the dev UI?

+1

[yrodiere]

Also, I wonder if you can just enable that console by adding options in the JDBC URL, since the org.h2.tools.Server class seems to implement both the HTTP server and TCP server... We may not need a custom class at all.

So no, we can't do this through the JDBC URL, but we could just adapt this code in dev services:

https://github.com/quarkusio/quarkus/blob/f9d0518eb08978ba574e9132e8e40f7cf5a3489d/extensions/devservices/h2/src/main/java/io/quarkus/devservices/h2/deployment/H2DevServicesProcessor.java#L39-L44

There's a Server.createWebServer as well, and also a Server.runTool method that seems able to start both the TCP server and others (such as the web server). That would probably be the way to go, though someone would need to investigate the specifics.

[wadid]

@famod

That method is not returning anything (void) but the expression of an conditional breakpoint must evaluate to true or false. So either end that call with; and add return false; or let that method return false.

Does changing the return value of the method to false work for you? It still causes the error. Maybe it is because the the expression cannot be evaluated fully because maybe code execution in the browseViaHibernate() does also stop before reaching the return statement in the method?

If I write the boolean value directly in the expression it works (if you also click on importing the H2Tools). as a Java expression: H2Tools.browseViaHibernate(); return false;; or as a Kotlin expression: H2Tools.browseViaHibernate(); return false;;

@yrodiere

Then your H2 instance will be available through TCP.

Somehow I cannot run it with the tcp configuration. I get the following error:

WARN [agroal-11] (AgroalEventLoggingListener.java:83) - Datasource '<default>': Connection is broken: "java.net.ConnectException: Connection refused: localhost" [90067-214] I tried also the other option with this configuration

"%test":
  quarkus:
    devservices:
      enabled: true
    datasource:
      devservices:
        port: 5433
      db-kind: h2
      jdbc:
        driver: org.h2.Driver
        url: jdbc:h2:mem:test

When adding the database in IntelliJ with the url jdbc:h2:mem:test I just get empty databases. :/

[yrodiere]

Somehow I cannot run it with the tcp configuration. I get the following error:

WARN [agroal-11] (AgroalEventLoggingListener.java:83) - Datasource '<default>': Connection is broken: "java.net.ConnectException: Connection refused: localhost" [90067-214]

Oh my, you're right, we use that in many integration tests but there are classes like that next to the tests to actually start the database:

@QuarkusTestResource(H2DatabaseTestResource.class)
public class TestResources {
}

See also https://stackoverflow.com/a/55063804/6692043 for a better solution.

What an awful idea it was to have such class magically affect all tests in the same package -_-'

I tried also the other option with this configuration

You don't need to (and really, shouldn't) use a JDBC URL when enabling devservices. Devservices are disabled as soon as you provide explicit connection config.

Try again without setting the JDBC URL at all.

FWIW you probably don't need to set the driver, either. It's the default when using db-kind: h2.

When adding the database in IntelliJ with the url jdbc:h2:mem:test I just get empty databases. :/

Yes that's expected. jdbc:h2:mem:test literally means "start your own new database in-memory", so it will always be empty.

[famod]

Just real quick:

I keep getting issues with h2 tcp mode (e.g. in at least one quarkus-quickstart) on some machines, so I'm not a big fan of that. YMMV!

Dev mode is a good idea, but let's not forget that many people are running QuarkusTests directly via IDE.

[wadid]

@yrodiere I removed the jdbc url and also the driver Now quarkus starts but I get many failing test methods. One error e.g.

Caused by: org.postgresql.util.PSQLException: ERROR: column "data" is of type oid but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.

If I include the driver then even more stuff breaks.

Yes that's expected. jdbc:h2:mem:test literally means "start your own new database in-memory", so it will always be empty. What way would you connect to h2 inmemory in testing?

@famod (maybe also you know that) So now with your method I can check the h2 database better and I could see now an issue that I have better. I am not really sure what that is about. In a test method annotated with (@TestTransaction @Test) I call a service method which creates and persists something (tried it also with additional flushing and the service methods are annotated with @Transactional themselves.)

Then i check the database and see the that data is inside. Also I can call another service method that gets me the data. So the data seems to be in database and available.

But then next I try to call an endpoint method that calls the exact same service method which got me the data before. But now the service method cannot find anything. So I used your code and set an conditional breakpoint. And see there the table does not contain this data anymore.

So what is this. All these method calls happen in one and the same test method. Not between different test methods. So there shouldn't be any rollback stuff happening.

It must have to do something with endpoints I guess. I noticed it only in test context and when I call an endpoint which tries to retrieve data that I created right before.

[yrodiere]

What way would you connect to h2 inmemory in testing?

Use devservices with a fixed port like you did but without specifying a JDBC URL, and connect through TCP with jdbc:h2:tcp://localhost:5433/mem:quarkus.

Or add @QuarkusTestResource(H2DatabaseTestResource.class) to your test (but then you'll need to set the JDBC URL in your configuration).

Now quarkus starts but I get many failing test methods. One error e.g.

Wait, the error comes from the postgresql driver. Your db-kind is H2. What's going on here?

I suspect you set the JDBC URL somewhere else in your config. If you set the postgres JDBC URL somewhere, be sure to prefix that config with %prod so it doesn't affect tests. Since you want to use H2 in tests.

[wadid]

@yrodiere thanks I am going to try it again. Do you have any idea about the other issue? when trying to save something in the test method and then trying to retrieve it over endpoint from same test method the previously persisted data is not in database when in endpoint methods?

[yrodiere]

Do you have any idea about the other issue? when trying to save something in the test method and then trying to retrieve it over endpoint from same test method the previously persisted data is not in database when in endpoint methods?

Beside a transaction rollback (which should appear in the logs), no I don't know. Hard to say without a reproducer.