Heroku postgresql queries not working on Heroku, but work locally

Question

When running locally and connecting to Heroku with the same DATABASE_URL, I'm able to connect and query the db perfectly fine.

When the same code is running on Heroku -- the query never returns and will eventually time out.

DB Code:

const connectionString = process.env.DATABASE_URL;
const pool = new Pool(
    {
        connectionString: connectionString,
        ssl: true
    }
);

public async query(text: any, params: any): Promise<any> {
    return pool.query(text, params);
}

This works as expected when I run on localhost and attach to the Heroku Postgres add-on (Hobby tier), but when deployed to Heroku it no longer works. The DATABASE_URL is loaded correctly though.

Anthony Williams · Accepted Answer · 2021-02-08 23:18:48Z

2

Weird one, but if anyone runs into this problem here was our solution:

We used the pg package and were running version ^7.12.1, which allowed us to connect and query the Heroku hosted database locally, but not when the server was deployed on Heroku.

The fix was updating the pg package, which we are now running version ^8.5.1.

answered Feb 8, 2021 at 23:18

Anthony Williams

334 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Blair · Accepted Answer · 2024-09-05 14:26:09Z

I came across the same error when I was trying to do a full text search with a postgres searchVector field. My search worked in my local database, but failed on Heroku. I finally figured out that my default_text_search_config in my local posgres was 'pg_catalog.english', while the default_text_search_config in heroku was 'pg_catalog.simple'.

To test your database's default_text_search_config

connect to your database CMD: psql -h HOST -p PORT -U USERNAME -d DATABASENAME
print default_text_search_config CMD: SHOW default_text_search_config;

If your default_text_search_config isn't 'pg_catalog.simple', that's probably why is works in your local database, but fails on heroku's. So you have to change your default_text_search_config in heroku.

find out your dbname of your heroku database CMD: heroku pg:credentials:url DATABASE_URL
get into your heroku database CMD: heroku pg:psql
alter your database to the correct default_text_search_config CMD: ALTER DATABASE dbname SET default_text_search_config TO 'pg_catalog.english';

Collectives™ on Stack Overflow

Heroku postgresql queries not working on Heroku, but work locally

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related