Search code examples
phppostgresqlheroku

PHP trouble connecting to Postgres on Heroku

When deployed to heroku there seems to be something not working as my endpoint isn't returning the data I entered via Heroku CLI connection to postgres. Locally I'm trying to troubleshoot it using the following command to connect. Here is a link to github which reflects how I have it setup for heroku (https://github.com/AlexMercedCoder/SavvyBackendAPI/blob/master/api/models/comments.php), the url below uses the actual host name in the string in my local environment.

$dbconn = pg_connect("host=<hostnamefromheroku>
 port=5432 dbname=d3qvmqtmq9s8b user=cticedgggntdqf password=5d17168b471db3b178e8ede79d5f92605d765375ea153444cd403c0a544f2146 sslmode=require");

WHen I run it from localhost in the browser I get the following error:

<br />
<b>Warning</b>:  pg_connect(): Unable to connect to PostgreSQL server: could not translate host name &quot;dbname=d3qvmqtmq9s8b&quot; to address: Name or service not known in <b>/home/alexmerced/gasei/PHP/savvy/api/models/comments.php</b> on line <b>11</b><br />
<br />
<b>Warning</b>:  pg_query(): No PostgreSQL link opened yet in <b>/home/alexmerced/gasei/PHP/savvy/api/models/comments.php</b> on line <b>41</b><br />
<br />
<b>Notice</b>:  Undefined variable: result in <b>/home/alexmerced/gasei/PHP/savvy/api/models/comments.php</b> on line <b>43</b><br />
NULL
<br />
<b>Warning</b>:  pg_fetch_object() expects parameter 1 to be resource, bool given in <b>/home/alexmerced/gasei/PHP/savvy/api/models/comments.php</b> on line <b>45</b><br />
[]

So test the commend I ran the same exact command in the PHP Cli locally and was able to successfully pull the data from the database. (using the same pg_query pg_fetch_object commands)

So any advice on how to climb over this wall would be very appreciated. I've read many blogs and questions on this issue and most of the solutions don't seem to fix the problem.

Here is a link to the deployed version: https://savvyhotspotsapi.herokuapp.com/api/comments

to show that it is working if you go to root or /api the hello world index.php I placed there works fine.

UPDATE 12/14/19

  • Locally I got the application to serve the API, but when I deploy to heroku is still shows me an empty array even though the exact same code is able to retreive the data successfully on my local php env.

my current connection code, which works locally:

$DBURL = "host=$h dbname=$n user=$u port=5432 password=$p sslmode=require";

$dbconn = pg_connect($DBURL);

I've tried it with or without the sslmode, I've tried it with the direct details in the string, nothing seems to change the empty array.

Solution

  • Summary

    When creating a Heroku Postgres database, a DB uri is generated under the env variable DATABASE_URL

    The trick was for me to use that instead of trying to manually pass the individual parts.

    $dbconn = pg_connect(getenv("DATABASE_URL"));

    not

    $DBURL = "host=$h dbname=$n user=$u port=5432 password=$p sslmode=require";

$dbconn = pg_connect($DBURL);