StatementCallback; bad SQL grammar only when deployed to Heroku

Question

I've created spring-boot application which uses jdbctemplate, everything works great on localhost but when i deploy my application to Heroku and visit the endpoint i'm getting this information :

StatementCallback; bad SQL grammar [SELECT id, name, price, image, description FROM products;]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "products" does not exist Position: 49

If the SQL query is bad then how is that even possible that same application works local machine ?

Here's the method with jdbcTemplate i'm using:

public Products getProductsList() {
    ArrayList<Product> productsList = new ArrayList<>();

    jdbcTemplate.query(
            "SELECT id, name, price, image, description FROM public.products;",
            (rs, rowNum) -> new Product(rs.getInt("id"), rs.getString("name"), rs.getFloat("price"), rs.getString("image"), rs.getString("description"))
    ).forEach(product -> productsList.add(product));

    return new Products(productsList);
}

Either: 1) the table "products" does not exist in the deployed environment, 2) you have a typo in the table name (camel case, snake case, lowercase, uppercase) or if those are good, then this: https://stackoverflow.com/questions/36753568/postgresql-tables-exists-but-getting-relation-does-not-exist-when-querying/36753702 — Randy Casburn, May 19 '19 at 16:15

score 1 · Accepted Answer · answered May 22 '19 at 20:28

Okay, i repaired it.

Heroku added it's own configuration to my project for some reason(addon with postgresql) and it used database from settings->config vars -> DATABASE_URL instead of database from application.properties.

I used command from this answer in Heroku CLI to delete heroku database from my application.

score 0 · Answer 2 · answered May 19 '19 at 16:20

0

I think you should put your schema name in your application.properties file like so :

spring.jpa.properties.hibernate.default_schema=public

And write your query like so :

SELECT id, name, price, image, description FROM products;

Source : this answer and this answer

answered May 19 '19 at 16:20

Mustapha Nezzari

1
1

I am not using jpa/hibernate, just jdbctemplate – Hardok May 21 '19 at 18:01

score 0 · Answer 3 · answered May 20 '19 at 13:14

0

You’ll need to create the tables using a migration framework as described in https://devcenter.heroku.com/articles/running-database-migrations-for-java-apps

answered May 20 '19 at 13:14

codefinger

10,088
7
39
51

StatementCallback; bad SQL grammar only when deployed to Heroku

3 Answers3