H2 - Postgres mode support net data type

Question

I have a DAO that is accessing a Postgres database. I want to use H2 for my unit tests - everything works great except that the table I am operating on has a column with inet datatype - so I get the exception

Unknown data type: "inet"; SQL statement: delete from table where id = ? and ip_address = ?::inet [50004-200]

Been a while since I've used H2 - I've set Postgres mode for the connection: url jdbc:h2:mem:test;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE

is there anyway I can execute this query in H2 and support this data type?

Consider using something like the postgresql module of testcontainers-java instead of trying to use a different database for your unit tests. — Mark Rotteveel
– Mark Rotteveel, Commented Mar 17, 2020 at 18:07
@MarkRotteveel brilliant, thank you. I tried zonky embedded Postgres but it couldn't build on Jenkins and the project does not give any actionable output, so no guidance on what went wrong. test-containers worked perfectly. could you post this as an answer so I can accept it — nbpeth
– nbpeth, Commented Mar 17, 2020 at 21:23

Mark Rotteveel · Accepted Answer · 2020-03-18 14:37:41Z

3

Compatibility modes like provided by H2 are not perfect, they don't provide a 100% coverage of all features, nor 100% identical behaviour.

Instead of using a different database in your unit tests, and trying to make it as close as possible (and then getting bitten by the differences in production anyway), I'd recommend using the PostgreSQL module of testcontainers-java. This allows you to spin up PostgreSQL docker images for your unit tests.

Be aware, that this will impact performance, because it will likely be a bit slower than using H2 because of the time to launch the docker image. I'd recommend to carefully consider whether your tests truly needs to go to a database, or can get away with mocking data, or at least limiting the scope of tests that (need to) go to a database.

edited Mar 18, 2020 at 14:37

answered Mar 18, 2020 at 10:15

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nbpeth Over a year ago

works like a charm - I noticed the time overhead immediately, but this will live in a functional testing layer so it shouldn't be too disruptive

Evgenij Ryazanov · Accepted Answer · 2020-03-17 02:42:39Z

0

The biggest question here is how your table with unknown data type was created? Column ip_address should have some other data type known by H2.

Actually you can create a domain like this:

CREATE DOMAIN INET AS VARCHAR;

But usage of the same database system everywhere would be better.

answered Mar 17, 2020 at 2:42

Evgenij Ryazanov

8,3932 gold badges15 silver badges24 bronze badges

5 Comments

nbpeth Over a year ago

create table myTable ( ip_address inet, id INT NOT NULL) is the sql used to create the table - resulting in Unknown data type: "inet"; SQL statement error

nbpeth Over a year ago

I ran create domain inet as varchar preceding the above table creation and encounter the same error - because the return of create is false - gotta figure out why that command is failing

Evgenij Ryazanov Over a year ago

create domain inet as varchar; create table myTable ( ip_address inet, id INT NOT NULL); completes without any exceptions in H2.

nbpeth Over a year ago

I wish I was having the same luck. I am using Scalike JDBC -

using(inMemoryPool.borrow()) { conn =>       val db: DB = DB(conn)        db.localTx { implicit session =>         val inetCreated = sql"""create domain inet as varchar""".execute().apply()         println(inetCreated) // false         sql"""create table mytable (                   ip_address inet,                   id INT NOT NULL)""".execute.apply()        }     }

-- result of the creation of domain is false and the subsequent create table fails

Evgenij Ryazanov Over a year ago

With plain JDBC and in H2 Console everything works well, but I can't help you with the wrapper that you use; I'm not familiar with it.

Collectives™ on Stack Overflow

H2 - Postgres mode support net data type

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related