Micronaut Data (JDBC) w/Postgres Generates SQL with backtics `

Question

My application runs great with the H2 test database, but when I run it with Postgresql, it creates sql with backticks. What am I missing?

Edit

This was an Environment problem. I had a test Repository that extended the repository below but used the H2 dialect. I inadvertently put this bean in src.main.java instead of src.test.java. If you're seeing this issue, look at any test resources or config that is using H2.

INSERT INTO `email_event` (`event_dts`,`category`,`email`,`event`,`sg_template_name`,`template_id`,`template_version_id`,`uid`,`campaign_name`,`major_classification`,`minor_classification`,`sg_event_id`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)

Postgresql doesn't like it and the application throws this exception:

io.micronaut.data.exceptions.DataAccessException: SQL Error executing INSERT: ERROR: syntax error at or near "`" Position: 13

build.gradle

I'm using Micronaut Data, with JDBC, micronaut-hibernate-jpa (for the Entity class), hikari db connection pool, and Postgres jdbc driver.

dependencies {
    annotationProcessor("io.micronaut.data:micronaut-data-processor")
    implementation("io.micronaut:micronaut-validation")
    implementation("io.micronaut:micronaut-runtime")
    implementation("javax.annotation:javax.annotation-api")
    implementation("io.micronaut:micronaut-http-client")
    implementation("io.micronaut.sql:micronaut-jdbc-hikari")
    implementation("io.micronaut.data:micronaut-data-jdbc")
    compileOnly("io.micronaut.sql:micronaut-hibernate-jpa") //For @Entity
    compileOnly("io.micronaut.spring:micronaut-spring-context")
    runtimeOnly("ch.qos.logback:logback-classic")
    runtimeOnly("com.h2database:h2")
    compile("org.postgresql:postgresql:42.2.16")// <- Tried changing versions with no luck
}

application.yml

I'm specifying the POSTGRES dialect in the configuration (maybe I need more here?).

datasources:
  default:
    url: jdbc:postgresql://my.dburl.com:5432/my_db
    driverClassName: org.postgresql.Driver
    username: username
    password: supersecret
    dialect: POSTGRES
    auto-commit: true

Repository class

The repository is basic and repeats the Dialect configuration.

package com.access.dao;

import com.access.model.EmailEvent;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;


@JdbcRepository(dialect = Dialect.POSTGRES)
public interface EventRepository extends CrudRepository<EmailEvent, String> {
}

Entity class

The Entity class is basic as well.

package com.access.model;

import com.fasterxml.jackson.annotation.JsonProperty;
import io.micronaut.core.annotation.Introspected;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;

import javax.annotation.Nullable;
import javax.persistence.Column;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.List;

@Introspected
@MappedEntity
public class EmailEvent {
    @Id
    @JsonProperty("sg_event_id")
    @Column(name = "sg_event_id")
    private String sgEventID;

    private List<String> category;
    @Column
    private String email;
    @Column
    private String event;

    @JsonProperty("sg_template_name")
    @Column(name="sg_template_name")
    @Nullable
    private String sgTemplateName;

    @JsonProperty("template_id")
    @Column(name = "template_id")
    @Nullable
    private String templateID;

    @JsonProperty("template_version_id")
    @Column(name = "template_version_id")
    @Nullable
    private String templateVersionID;

    private Long timestamp;

    @Nullable
    private String uid;

    @JsonProperty("campaign_name")
    @Column(name="campaign_name")
    @Nullable
    private String campaignName;

    @JsonProperty("major_classification")
    @Column(name = "major_classification")
    @Nullable
    private String majorClassification;

    @JsonProperty("minor_classification")
    @Column(name = "minor_classification")
    @Nullable
    private String minorClassification;

    
    @Column(name="event_dts")
    public LocalDateTime getEventDts(){
        return Instant
                .ofEpochSecond(this.timestamp)
                .atZone(
                        ZoneId.of("America/Denver")
                ).toLocalDateTime();
    }

    public String getCategory(){
        return String.join(",", this.category);
    }

    public void setCategory(List<String> category) {
        this.category = category;
    }

    // Other getters/setters
}

Answer 1

This was a problem with where I had placed a test repository. I created (from duplicating the production class) a test repository for use with an in memory H2 datababse.

@Replaces(EventRepository.class)
@JdbcRepository(dialect = Dialect.H2)
public interface TestEventRepository extends EventRepository {

}

However, I neglected to move this class to the src.test.java directory, so it conflicted with the production version.