Unknown data type "JSONB" when running tests in play slick with H2 Database

Question

I have evolution problem Unknown data type: "JSONB" when running tests in playframework using

playframework v2.6.6 for scala
play-slick v3.0.2
play-slick-evolutions v3.0.2
postgresql - 42.0.0
h2database - 1.4.194

My H2DbConnector looks like this:

import entities.StubData._
import org.scalatest.{BeforeAndAfterAll, FunSuite}
import play.api.db.DBApi
import play.api.db.evolutions.Evolutions
import play.api.inject.guice.GuiceApplicationBuilder

trait H2DbConnector extends FunSuite with BeforeAndAfterAll {
  val appBuilder = new GuiceApplicationBuilder()
    .configure(configuration)

  val injector = appBuilder.injector
  lazy val databaseApi = injector.instanceOf[DBApi]

  override def beforeAll() = {
    Evolutions.applyEvolutions(databaseApi.database("default"))
  }

  override def afterAll() = {
    Evolutions.cleanupEvolutions(databaseApi.database("default"))
  }
}

In application.test.conf

slick.dbs.default.driver = "slick.driver.H2Driver$"
slick.dbs.default.db.driver = "org.h2.Driver"
slick.dbs.default.db.url = "jdbc:h2:mem:play;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE"

I've got one problematic line in evolutions 2.sql file

ALTER TABLE "Messages" ADD COLUMN "metaJson" JSONB NULL;

When I run dao tests getting error like

2017-12-21 16:08:40,409 [error] p.a.d.e.DefaultEvolutionsApi - Unknown data type: "JSONB"; SQL statement:
ALTER TABLE "Messages" ADD COLUMN "metaJson" JSONB NULL [50004-194] [ERROR:50004, SQLSTATE:HY004]
[info] OptoutsDaoTest *** ABORTED ***
[info]   play.api.db.evolutions.InconsistentDatabase: Database 'default' is in an inconsistent state![An evolution has not been applied properly. Please check the problem and resolve it manually before marking it as resolved.]
[info]   at play.api.db.evolutions.DatabaseEvolutions.$anonfun$checkEvolutionsState$3(EvolutionsApi.scala:285)
[info]   at play.api.db.evolutions.DatabaseEvolutions.$anonfun$checkEvolutionsState$3$adapted(EvolutionsApi.scala:270)
[info]   at play.api.db.evolutions.DatabaseEvolutions.executeQuery(EvolutionsApi.scala:317)
[info]   at play.api.db.evolutions.DatabaseEvolutions.checkEvolutionsState(EvolutionsApi.scala:270)
[info]   at play.api.db.evolutions.DatabaseEvolutions.evolve(EvolutionsApi.scala:239)
[info]   at play.api.db.evolutions.Evolutions$.applyEvolutions(Evolutions.scala:193)
[info]   at H2DbConnector.beforeAll(H2DbConnector.scala:15)
[info]   at H2DbConnector.beforeAll$(H2DbConnector.scala:14)
[info]   at OptoutsDaoTest.beforeAll(OptoutsDaoTest.scala:5)
[info]   at org.scalatest.BeforeAndAfterAll.liftedTree1$1(BeforeAndAfterAll.scala:212)
[info]   ...

Could you help me please to fix this issue?

You can have a look at [Acolyte](http://acolyte.eu.org/) to "mock" any JDBC connection, whatever is the target DB for runtime — cchantep, Dec 21 '17 at 23:12
Does this answer your question? [How can solve JSON column in H2](https://stackoverflow.com/questions/39620317/how-can-solve-json-column-in-h2) — Jens Schauder, Mar 05 '21 at 09:14

Eduardo Pinheiro · Answer 1 · 2020-07-29T16:12:54.230

I recently had this problem with JSONB and H2 too. I solved it by creating an alias of JSONB to JSON and have it run only during the tests profile on H2.

CREATE TYPE "JSONB" AS json;

It's not JSONB but the difference of JSONB to JSON (at least in postgres) is essentially the perfomance of reading, which for the test purposes doesn't really matter (so much).

Maybe this example helps too:

This is an example using flyway. Create an sql entry to create an alias type to jsonb on /resources/db/tests that runs only on the test profile.

We were using spring so here's the entrance on the application.yml:

spring:
  profiles: mytest
  datasource:
    continueOnError: false
    url: jdbc:h2:mem:myapp-db;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE
flyway:
   enabled: true
   locations: classpath:db/migration, classpath:db/tests
  [......]

And heres a list of the ${project.dir}/resources/db/

Here's the magic:

In the content of the file I create a type called JSONB that is basically an alias to the JSON type. note: For what I've understood, Uppercase is necessary (specially when you're referring to it on the creation of the table) cause H2 seems to automatically change the types names to UPPERCASE:

CREATE TYPE "JSONB" AS json;

Here is an example of a creation of a table with this type:

CREATE TABLE "XXX" (
    id BIGSERIAL PRIMARY KEY,
    my_json_column_name JSONB NOT NULL
);

On the side of hibernate I use the type JsonBinaryType from hibernate-types52 See more on this link.

@Data
@TypeDef(name = "jsonb", typeClass = com.vladmihalcea.hibernate.type.json.JsonBinaryType.class)
@Entity(name = "XXX")
@Table(name = "XXX")
public class XXX {

  @Type(type = "jsonb")
  @Column(name = "my_json_column_name", nullable = false)
  private String myJsonColumnName;

  //OR

  @Type(type = "jsonb")
  @Column(name = "my_json_column_name", nullable = false)
  private List<MYCustomTypeThatMatchesJsonObject> myJsonColumnName;

}

I hope it helps someone. It worked for me.

UPDATED AT 2020-07-13

I stopped using H2 on my projects and started to use testcontainers. Very easy to setup and you can test in your real db environment.

I would like to underscore the recommendation to consider using testcontainers. — Jon Edwards, Aug 07 '20 at 21:50

Nagarjuna Pamu · Accepted Answer · 2017-12-21T12:20:48.267

3

H2 does not support JSONB column type.

All supported column types Supported datatypes of H2

Try to use postgres also in tests or write standard SQL statments which both databases understand.

edited Dec 21 '17 at 12:20

answered Dec 21 '17 at 11:59

Nagarjuna Pamu

14,737
3
22
40

1

Is there another workaround to support data type **JSON** in Postgres and H2 Database? – Azik Dec 21 '17 at 12:32
Create a test account for postgres and Use postgres in your tests also. – Nagarjuna Pamu Dec 21 '17 at 14:20

score 1 · Answer 3 · answered Sep 20 '19 at 21:55

You can not use PostgreSQL for unit test as you are connecting to something, unit test should only relay on in-memory test as it will be trigged by your build and it's very unlikely to have build server accessing any physical DB, you may need another way to mock your data and avoid DB accessing from it, or change your data type to string[] and encapsulate it to produce JSON

score 1 · Answer 4 · answered Oct 27 '21 at 00:00

1

For those who still have this problem in either H2 and PostgreSQL database even after defining a TypeDef ...etc, Check out my answer here

answered Oct 27 '21 at 00:00

Hamza Assada

744
8
23

Unknown data type "JSONB" when running tests in play slick with H2 Database

4 Answers4