CODEWITHSUNDEEP
node.jstypescriptpostgresqlheroku
Anthony Williams
Anthony Williams

Reputation: 33

Heroku postgresql queries not working on Heroku, but work locally

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.

Upvotes: 1

Views: 349

Answers (2)

Blair
Blair

Reputation: 1

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

  1. connect to your database CMD: psql -h HOST -p PORT -U USERNAME -d DATABASENAME
  2. 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.

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

Upvotes: 0

Anthony Williams
Anthony Williams

Reputation: 33

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.

Upvotes: 2

Related Questions