I have evolution problem Unknown data type: "JSONB" when running tests in playframework using
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?
The JSONB data type stores JSON (JavaScript Object Notation) data as a binary representation of the JSONB value, which eliminates whitespace, duplicate keys, and key ordering. JSONB supports GIN indexes.
Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
In general, most applications should prefer to store JSON data as jsonb , unless there are quite specialized needs, such as legacy assumptions about ordering of object keys. RFC 7159 specifies that JSON strings should be encoded in UTF8.
JSONB stands for “JSON Binary” or “JSON better” depending on whom you ask. It is a decomposed binary format to store JSON. JSONB supports indexing the JSON data, and is very efficient at parsing and querying the JSON data. In most cases, when you work with JSON in PostgreSQL, you should be using JSONB.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With