How to remotely connect to HEROKU PostgreSQL database using

Question

I have a PHP application that needs to remotely connect to a HEROKU app's postgresql database and run database queries (preferably using pg_query, though PDO is ok too). I am stuck trying to 1.) obtain live db credentials using the Heroku DATABASE_URL (as they recommend), and 2) generate any kind of db connection even when hard-coded credentials are supplied. I haven't found the proper sequence of steps to make a connection. (App is running on GoDaddy hosting - mentioned for clarity.)

I am trying to connect to the database, run a select query and assign the values from the resultset to PHP variables. I can connect to the database using PGAdmin3 so remote access isn't the issue.

What I have so far (Edited- commented lines are from original post):

//$DATABASE_URL='$({heroku CLI string, pasted from Heroku db credentials page})';
//$db = parse_url(getenv($DATABASE_URL));

$db_url = getenv("DATABASE_URL") ?: "postgres://user:pass@host:port/dbname";
$db = pg_connect($db_url);

if($db) {echo "connected";} else {echo "not connected";}

$selectSql = "SELECT id, name FROM companies ORDER BY id";   
$result =  pg_query($db, $selectSql);

while ($row = pg_fetch_row($result)) {
    $id = $row["id"];
    $name = $row["name"];
    echo "<br>id: ".$id;
    echo "<br>name: ".$name;
}

What is missing to connect to the database and run queries?

davejagoda · Accepted Answer · 2018-03-23 18:17:48Z

3

I see at least 3 problems:

1) Delete or comment out this:

$DATABASE_URL='$({heroku CLI string, pasted from Heroku db credentials page})';

2) Change this line to get the environment variable (so it looks like this). You have to set the environment variable before starting PHP. You could also hardcode the value, but not recommended:

$db = parse_url(getenv('DATABASE_URL'));

3) It doesn't look like you are actually connecting to the database, you just start your query. You have to connect first. See heroku docs for PDO or PHP docs for pg_query

Update 2018-03-23

Here is a standalone program that will make sure it is connecting:

#!/usr/bin/env php
<?php
$db_url = getenv("DATABASE_URL") ?: "postgres://user:pass@host:port/dbname";
echo "$db_url\n";

$db = pg_connect($db_url);
if($db) {echo "connected";} else {echo "not connected";}

$selectSql = "SELECT 1";
$result =  pg_query($db, $selectSql);

while ($row = pg_fetch_row($result)) {
    var_dump($row);
}
?>

edited Mar 23, 2018 at 18:17

answered Mar 22, 2018 at 15:04

davejagoda

2,5481 gold badge23 silver badges32 bronze badges

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

2 Comments

bluemccarty Over a year ago

This was helpful by pointing out the proper way to invoke the environment variable. Since I'm on a shared hosting platform and can't access the conf.d folder to add a DATABASE_URL environment variable, I may have to build the pg_connect connection string without that variable. Couldn't find a way to work around that so as to parse the URL and build out the connection string.

davejagoda Over a year ago

Here is the relevant environment variable I set before running the updated code: DATABASE_URL=postgres://postgres:password@localhost:5432/postgres

Kevin Raoofi · Accepted Answer · 2018-03-22 15:03:53Z

1

You need to call pg_connect with the correct connection string. I don't think it's the same as the URL given via Heroku; refer to the documentation for that one.

You're also using getenv improperly.

$db_url = getenv("DATABASE_URL") ?: "some default value here";
$db = pg_connect($db_url);

answered Mar 22, 2018 at 15:03

Kevin Raoofi

1,03312 silver badges17 bronze badges

1 Comment

bluemccarty Over a year ago

You are right, I did need to call pg_connect properly. Also, I passed the Heroku URL into the DATABASE_URL environment variable, as in the edited code above. I couldn't get pg_connect() to recognize it as a connection string, thinking there must be another step to allow for that.

Collectives™ on Stack Overflow

How to remotely connect to HEROKU PostgreSQL database using

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related