4

I want to unit test In Java Spring-boot Postgres native query for jsonb datatype without hitting the actual DB.Instead using some embedded database or other approach ,but embedded databases like H2 doesn't support Postgres jsonb specific operators like ->>, #>> etc.

Is there a way that I can to this? Please also suggest any other approach for the same.

Query Example:

@Query(value = "SELECT * FROM table WHERE column ->> 'Id'=?1", nativeQuery = true)
public Entity fertchById(String id);
Jagjit Singh Arora
  • 41
  • 2
  • 1
    use something like testcontainers to bootstrap PostgreSQL, this does require docker to be available. Embedded solutions won't work, the jsonb stuff is for PostgreSQL so no using another database to test something Postgres specific will obviously not work. – M. Deinum Jan 27 '20 at 07:47
  • 1
    If you need a database that works and behaves like Postgres, your only option is to use Postgres. Any test against a "mock database" will be meaningless if you want to find out if your queries work correctly on Postgres –  Jan 27 '20 at 08:07
  • I do agree you should use postgres on Testcontainers. But there is an answer here https://stackoverflow.com/a/50837767/66686 – Jens Schauder Mar 05 '21 at 09:21

1 Answers1

1

if you are using H2 database, and want to work with JSONB PostgreSQL feature without using docker and testcontainers (which were mentioned in comments) you can define your custom H2 dialect.

I found an example of such a dialect here:

public class CustomH2Dialect extends H2Dialect {

    public CustomH2Dialect() {
        this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
    }


    @Override
    public void contributeTypes(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
        super.contributeTypes(typeContributions, serviceRegistry);
        typeContributions.contributeType(new JsonStringType(), "jsonb");
    }
}

As you can see issue with Liquibase is still open and you should definitely think about testcontainers or another way to work with PostgreSQL directly.

humb1t
  • 87
  • 1
  • 4
  • 2
    I can use H2 with jsonb but for postgres specific operators it doesn't provide any support,and for the allowing H2 to store JSONB you can create a domain using other datatype in properties file while defining h2 as datasource.Example:spring.datasource.url=jdbc\:h2\:mem\:db;DB_CLOSE_ON_EXIT\=TRUE;MODE\=PostgreSQL;INIT\=CREATE SCHEMA IF NOT EXISTS schema\\;CREATE domain IF NOT EXISTS jsonb AS other; – Jagjit Singh Arora Jan 27 '20 at 10:31
  • 1
    Jagjit's way solved it for me, nothing else worked, I'm using now: `url: jdbc:h2:mem:test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;INIT=create schema if not exists myschema\;CREATE domain IF NOT EXISTS jsonb AS other;` – Boommeister Apr 13 '21 at 17:16