Repositories with native queries fail in test environment - postgres, jpa, spring

Question

I have set up integration tests for a spring boot project using test containers (sets up a docker instance with postgresql). The tests work great if the repositories that I am testing against do not use native queries. However, whenever a repository contains a native query I get the following error: ERROR: relation "my_table_here" does not exist. How do I get my test configuration to work to allow native queries?

Below is my test set up:

@RunWith(SpringRunner.class)
public class TestPostgresql {

    @ClassRule
    public static PostgreSQLContainer postgreSQLContainer = PostgresDbContainer.getInstance();

    /**
     * ************ REPOSITORIES ************
     */
    @Autowired
    NativeQueryRepository nativeQueryRepository;

    @TestConfiguration
    @EnableJpaAuditing
    @EnableJpaRepositories(
            basePackageClasses = {
                    NativeQueryRepository.class
            })
    @ComponentScan(
            basePackages = {
                    "com.company.project.package.repository"
            }
    )
    static class PostgresConfiguration {

        /**
         * ************ DATABASE SETUP ************
         */
        @Bean
        public DataSource dataSource() {
            DriverManagerDataSource dataSource = new DriverManagerDataSource();
            dataSource.setUrl(postgreSQLContainer.getJdbcUrl());
            dataSource.setUsername(postgreSQLContainer.getUsername());
            dataSource.setPassword(postgreSQLContainer.getPassword());
            return dataSource;
        }

        @Bean
        public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
            HibernateJpaVendorAdapter vendorAdapter = new JpaVendorAdapter();
            vendorAdapter.setDatabase(Database.POSTGRESQL);
            vendorAdapter.setGenerateDdl(true);

            LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
            factory.setJpaVendorAdapter(vendorAdapter);
            factory.setPackagesToScan("com.company.project");
            factory.setDataSource(dataSource());
            return factory;
        }

        @Bean
        public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
            JpaTransactionManager txManager = new JpaTransactionManager();
            txManager.setEntityManagerFactory(entityManagerFactory);
            return txManager;
        }
    }
}

EDIT: I believe this has something to do with the naming strategy?

For greater context here is an example of how the nativeQuery is used in the repository

@Repository
public interface NativeQueryRepository extends JpaRepository<NativeEvent, Long> {

    @Modifying
    @Transactional
    @Query(value = "UPDATE native_event SET state = :state " +
                    "WHERE secondary_id = :secondaryId", nativeQuery = true)
    void updateState(
            @Param("state") String state,
            @Param("secondaryId") String secondaryId);

}

I also tried update the testProperties on the static class inside TestPostgresql by adding the annotation:

@TestPropertySource(properties = {
            "spring.jpa.hibernate.naming-strategy=org.springframework.boot.orm.jpa.SpringNamingStrategy"
    })

However, with no change to the error received.

EDIT: add NativeEvent:

@Entity
@Table(
        name = "NativeEvent",
        indexes = {
                @Index(name = "idx_native_event_secondary_id", columnList = "secondaryId")
        }
)
@EntityListeners(AuditingEntityListener.class)
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class NativeEvent implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

    @Column(name="state")
    private String state;
}

Answer 1

You assign your table name explicitly like this:

@Table(name = "NativeEvent")

but in your native query you have a different name for that table:

 @Query(value = "UPDATE native_event ...)

Either remove the name attribute from your @Table annotations (assuming your naming strategy will produce names like native_event) or change table name in native query to be nativeevent or nativeEvent so in this case just remove the underscore.

Somewhat related post

Answer 2

You are doing manual configuration instead of using the runtime configuration. Hence different treatment of naming strategies. Instead you should be reusing the same configuration instead of writing your own.

Either use an @SpringBootTest or @DataJpaTest and only re-configure the DataSource.

Do something with an ApplicationContextInitializer to get the JDBC properties into the ApplicationContext.

@RunWith(SpringRunner.class)
@SpringBootTest
@ContextConfiguration(initializers = {TestPostgresql.JdbcInitializer.class})
public class TestPostgresql {

    @ClassRule
    public static PostgreSQLContainer postgreSQLContainer = PostgresDbContainer.getInstance();

    /**
     * ************ REPOSITORIES ************
     */
    @Autowired
    NativeQueryRepository nativeQueryRepository;

    static class JdbcInitializer
      implements ApplicationContextInitializer<ConfigurableApplicationContext> {
        public void initialize(ConfigurableApplicationContext configurableApplicationContext) {
            TestPropertyValues.of(
              "spring.datasource.url=" + postgreSQLContainer.getJdbcUrl(),
              "spring.datasource.username=" + postgreSQLContainer.getUsername(),
              "spring.datasource.password=" + postgreSQLContainer.getPassword()
            ).applyTo(configurableApplicationContext.getEnvironment());
        }
    }    
}

This will reuse the configuration from the runtime in your test. Instead of @SpringBootTest you should als be able to use @DataJpaTest(NativeQueryRepository.class) to make a sliced test for JPA only.