Failure to use Hibernate with QuestDB: Missing Schema Table

Question

I am using attempting to setup QuestDB with Hibernate and the Java Micronaut Framework. I am failing to map correctly to Java entities. Also, it looks like QuestDB is failing to generate a schema table on startup. I am using the postgres driver to connect with QuestDB and Hibernate for mapping. Any insight is very appreciated, as I am deep into documentation to understand it all.

So I have tried to boil it down to as simple as possible for example. I will start with the errors.

On startup I receive the following from Micronaut :

Caused by: org.hibernate.exception.GenericJDBCException: Unable to build DatabaseInformation
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:163)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:96)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469)
    at io.micronaut.configuration.hibernate.jpa.EntityManagerFactoryBean.hibernateSessionFactory(EntityManagerFactoryBean.java:219)
    at io.micronaut.configuration.hibernate.jpa.$EntityManagerFactoryBean$HibernateSessionFactory3Definition.build(Unknown Source)
    at io.micronaut.context.BeanDefinitionDelegate.build(BeanDefinitionDelegate.java:153)
    at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:1979)
    ... 73 more
Caused by: org.postgresql.util.PSQLException: ERROR: table does not exist [name=information_schema.sequences]
  Position: 15
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
    at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:236)
    at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
    at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
    at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:42)
    at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:65)
    at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:59)
    at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:155)
    ... 82 more

In the QuestDB logs, it gives a few more details:

2021-06-12T19:49:08.431644Z I i.q.c.p.PGConnectionContext protocol [major=3, minor=0]
2021-06-12T19:49:08.431661Z I i.q.c.p.PGConnectionContext propertry [name=user, value=admin]
2021-06-12T19:49:08.431672Z I i.q.c.p.PGConnectionContext propertry [name=admin, value=database]
2021-06-12T19:49:08.431681Z I i.q.c.p.PGConnectionContext propertry [name=database, value=qdb]
2021-06-12T19:49:08.431698Z I i.q.c.p.PGConnectionContext propertry [name=qdb, value=client_encoding]
2021-06-12T19:49:08.431709Z I i.q.c.p.PGConnectionContext propertry [name=client_encoding, value=UTF8]
2021-06-12T19:49:08.431720Z I i.q.c.p.PGConnectionContext propertry [name=UTF8, value=DateStyle]
2021-06-12T19:49:08.431730Z I i.q.c.p.PGConnectionContext propertry [name=DateStyle, value=ISO]
2021-06-12T19:49:08.431740Z I i.q.c.p.PGConnectionContext propertry [name=ISO, value=TimeZone]
2021-06-12T19:49:08.431750Z I i.q.c.p.PGConnectionContext propertry [name=TimeZone, value=America/Chicago]
2021-06-12T19:49:08.431761Z I i.q.c.p.PGConnectionContext propertry [name=America/Chicago, value=extra_float_digits]
2021-06-12T19:49:08.431771Z I i.q.c.p.PGConnectionContext propertry [name=extra_float_digits, value=2]
2021-06-12T19:49:08.431780Z I i.q.c.p.PGConnectionContext propertry [name=2, value=]
2021-06-12T19:49:08.432279Z I i.q.c.p.PGConnectionContext parse [q=SET extra_float_digits = 3]
2021-06-12T19:49:08.432493Z I i.q.c.p.PGConnectionContext parse [q=SET application_name = 'PostgreSQL JDBC Driver']
2021-06-12T19:49:08.464065Z I i.q.g.CharacterStore resize [capacity=8192]
....
PGConnectionContext error [pos=4525, msg=`too few arguments for '::' [found=1,expected=2]
...

The database was created using the very simple command below. The interesting thing about QuestDB is that there are no primary key support.

CREATE TABLE genre(id LONG, name STRING);

The datasource configurations and dialect are listed below. Note that I am using the postgres DIALECT. According to QuestDB docs it should work with this...

micronaut:
  application:
    name: micronautguide
---

application:
  max: 50

---

datasources:
  default:
    url: jdbc:postgresql://172.17.0.3:8812/qdb
    username: admin
    password: quest
    driverClassName: org.postgresql.Driver
    sslmode: disable
---

jpa:
  default:
    properties:
      hibernate:
        hbm2ddl:
          auto: update
        show_sql: true
        dialect: org.hibernate.dialect.PostgreSQLDialect

Here is the basic repository and entity for mapping.

package example.micronaut;

import example.micronaut.domain.Genre;
import javax.inject.Singleton;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceException;
import javax.persistence.TypedQuery;
import javax.transaction.Transactional;
import io.micronaut.transaction.annotation.ReadOnly;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.util.Arrays;
import java.util.List;
import java.util.Optional;

@Singleton 
public class GenreRepositoryImpl implements GenreRepository {

    private final EntityManager entityManager; 
    private final ApplicationConfiguration applicationConfiguration;

    public GenreRepositoryImpl(EntityManager entityManager,
                               ApplicationConfiguration applicationConfiguration) { // <2>
        this.entityManager = entityManager;
        this.applicationConfiguration = applicationConfiguration;
    }

    @Override
    @ReadOnly  
    public Optional<Genre> findById(@NotNull Long id) {
        return Optional.ofNullable(entityManager.find(Genre.class, id));
    }
}

Here we have our repository and entity mapping.

package example.micronaut.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;

@Entity
@Table(name = "genre")
public class Genre {

    public Genre() {}
    public Genre(@NotNull String name) {
        this.name = name;
    }

    @Id
    @NotNull
    @Column(name = "id", nullable = false)
    private long id;

    @NotNull
    @Column(name = "name", nullable = false)
    private String name;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Genre{" +
            "id=" + id +
            ", name='" + name + '\'' +
            '}';
    }
}

Answer 1

I was able to fix this by adding annotation how ID is generated Though my id is uuid type in QuestDB for unique column named uid

@Id
@GeneratedValue(strategy=GenerationType.UUID)
private String uid;

This way Hibernate skipped querying information_schema.sequences table

Answer 2

QuestDB has Postgres compatibility on transport level so that if you run QuestDB SQL query you be able to get back the data. The query language is not 100% same and QuestDB misses a lot of schema table / views Postgres.

Making any ORM work with QuestDB using Postgres driver is much more than binary protocol compatibility so it is expected to not work. In this case it's missing information_schema.sequences table / view. Even if this view added by QuestDB (as empty, sequences are not supported) there probably be more queries of the schema type and SQL syntax differences so it is quite challenging to make an ORM work via Postgres driver with QuestDB.

So the choice is to switch off all ORM smart features like schema queries (if it's possible in Micronaut) or ask for the support of the ORM in github.