Can I persist some data into H2 database using in-memory mode?

Question

I want to save the data exactly in in-memory mode using h2 as database.

So I have application.properties file with configurations like:

spring.h2.console.enabled=true
spring.jpa.show-sql=true
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username=...
spring.datasource.password=...
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.session.jdbc.initialize-schema=always

using specific parameters:

DB_CLOSE_DELAY=-1 - to keep the database open/to keep the content of an in-memory database as long as the virtual machine is alive
DB_CLOSE_ON_EXIT=FALSE - to disable database closing on exit

as mentioned in documentation of H2 database.

At the same time, I'm using Entity for saving in database:

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity(name = "address_entity")
@Table(name = "addresses")
public class GeolocationAddress {

    public GeolocationAddress(GeolocationAddressDTO geolocationAddressDTO) {
        this.displayName = geolocationAddressDTO.getDisplayName();
        this.lat = geolocationAddressDTO.getLat();
        this.lon = geolocationAddressDTO.getLon();
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "address_id")
    private Integer id;

    private String displayName;

    private String lat;

    private String lon;
}

After sending the request, I'm getting in console information from Hibernate:

Hibernate: insert into addresses (address_id, display_name, lat, lon) values (null, ?, ?, ?)
Hibernate: insert into addresses (address_id, display_name, lat, lon) values (null, ?, ?, ?)

but after refreshing H2 database in Data Source using Ctrl+F5 combination or via:

key, I don't see there any data.

To research this behavior, I've checked formulations in documentation:

"For certain use cases (for example: rapid prototyping, testing, high performance operations, read-only databases), it may not be required to persist data, or persist changes to the data. This database supports the in-memory mode, where the data is not persisted."

and from some articles, e.g.:

"By design, the in-memory database is volatile, and data will be lost when we restart the application."

or this one:

"H2 is an in memory database. Its not a persisted database. H2 is a great tool for learning because you need zero setup."

but I still don't fully understand, can I save data in this mode while the application is running?

if I change to another mode, e.g.:

spring.datasource.url=jdbc:h2:file:./data/demo;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE

as mentioned here and here.

Then, yes, I see data in database after refreshing and data will be saved in format like:

but can I do the same with in-memory or not?

I would be grateful for any clarification on this.

UPD #1:

For instance, it's indicated here:

"Most in-memory database systems offer persistence, at least as an option."

UPD #2:

From H2-console I see the data using in-memory mode, but from Intellij Idea - not:

UPD #3: As mentioned @Turing85, I've tested url: jdbc:h2:tcp://localhost/mem:db1 to access the database over TCP/IP or TLS, but this link was invalid in Data Source.

After some investigation, I've found useful information:

":mem will not work for TCP connections. so remove :mem from connection url"

So alternative way like: jdbc:h2:./data/testdb;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=10990 also works.

UPD #4: Probably, this answer also is related to my question.

What do you mean by "*save data in this mode*"? You can save data and subsequently fetch that data, yes. But, as mentioned in the documentation, the data is volatile, i.e. will be gone when you stop/restart the application. — Turing85, Jul 31 '21 at 13:49
@Turing85 the problem is I don't see any data after refreshing using `in-memory` mode with configuration above while running application. — invzbl3, Jul 31 '21 at 13:54
did you add a data.sql file inside the resources and done the insertion? — Rohith V, Jul 31 '21 at 13:55
@RohithV, can you elaborate, please, without doing this I won't be able to save data in `in-memory` mode? — invzbl3, Jul 31 '21 at 13:57
You cannot simply "connect" to the in-memory database from another process. This is also mentioned in the [documentation you linked](http://www.h2database.com/html/features.html#in_memory_databases): "*To access an in-memory database from another process or from another computer, you need to start a TCP server in the same process as the in-memory database was created. The other processes then need to access the database over TCP/IP or TLS, using a database URL such as: `jdbc:h2:tcp://localhost/mem:db1`.*" If the process of writing to a file works, use this instead. — Turing85, Jul 31 '21 at 13:57
@invzbl3 what I meant was, you can create a data.sql file inside the /resources and add some insert commands which will add some data beforehand. Here are you inserting any values to the table? — Rohith V, Jul 31 '21 at 13:59
Sidenote: If possible, I suggest using an instance of the database you want to use in production, e.g. mysql or postgres to improve dev-prod-parity. With tools like docker or podman, we cann have a database up and running in a matter of minutes. — Turing85, Jul 31 '21 at 14:03
@RohithV Hibernate is inserting values to the table, as I see information from console: `"Hibernate: insert into addresses..."` and it works for me, if change to another mode like: `jdbc:h2:file...` — invzbl3, Jul 31 '21 at 14:03
An in memory database is only active while it is in memory. And by default the database is running in the same process as your application. So application adds data to database and data is there. But application ends, and the database is destroyed. That is probably not what you want. Also, in memory is usually not considered persistent. For exactly this reason. — Elliott Frisch, Jul 31 '21 at 14:12
@ElliottFrisch what's interesting, I've checked from the console using `in-memory` mode: `http://localhost:8080/h2-console` -> connect to the same config as from Intelij idea after inserting via Hibernate and it shows me data, but in Intelij Idea - no data. — invzbl3, Jul 31 '21 at 14:59
Congratulations! You started TWO totally independent in-memory database instances on one machine. — Elliott Frisch, Jul 31 '21 at 15:16
@Turing85, thanks for providing information about `TCP/IP`. In this case `Data source` tells me url `jdbc:h2:tcp://localhost/mem:db1` is invalid, but I've found the solution how it can be solved. — invzbl3, Jul 31 '21 at 17:49

invzbl3 · Accepted Answer · 2021-07-31T18:59:18.013

1

As mentioned Turing85, Elliott Frisch and M. Deinum, in-memory database is isolated to the running Spring Boot app, that's why I need to use other modes, e.g.:

spring.datasource.url=jdbc:h2:file:./data/testdb;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE

or

spring.datasource.url=jdbc:h2:./data/testdb;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=10990

edited Jul 31 '21 at 18:59

answered Jul 31 '21 at 18:53

invzbl3

5,872
9
36
76

Can I persist some data into H2 database using in-memory mode?

1 Answers1

Linked