05 Prepare : Connecting to Postgres on Heroku
Connecting to Postgres on Heroku
Heroku automatically creates an environment variable DATABASE_URL for the admin Postgres user and its password, along with the hostname and port of the database instance (it is likely not found in the localhost environment).
You can access this environment variable in PHP and parse it.
$dbUrl = getenv('DATABASE_URL');
$dbopts = parse_url($dbUrl);
$dbHost = $dbopts["host"];
$dbPort = $dbopts["port"];
$dbUser = $dbopts["user"];
$dbPassword = $dbopts["pass"];
$dbName = ltrim($dbopts["path"],'/');
$db = new PDO("pgsql:host=$dbHost;port=$dbPort;dbname=$dbName", $dbUser, $dbPassword);
Connecting to Postgres on your local machine or Heroku
The code shown above works great to connect to Postgres in the Heroku environment, but it will then NOT work for your local configuration. One way to set up your code to connect in either location, is to test for the Heroku environment variables first. If it exists, then you know you are in the Heroku environment, if not, you know you are in your local environment and can set up the variables accordingly.
// default Heroku Postgres configuration URL
$dbUrl = getenv('DATABASE_URL');
if (empty($dbUrl)) {
// example localhost configuration URL with postgres username and a database called cs313db
$dbUrl = "postgres://postgres:password@localhost:5432/cs313db";
}
The downside to this code is that you are still putting your local credentials into your source code files that will be committed to GitHub. A way around this is to create a local environment variable DATABASE_URL with the same name and format as Heroku.
The following puts these ideas together. The main components of this code could be extracted into shared PHP function.
<html>
<body>
<?php
// default Heroku Postgres configuration URL
$dbUrl = getenv('DATABASE_URL');
if (empty($dbUrl)) {
// example localhost configuration URL with postgres username and a database called cs313db
$dbUrl = "postgres://postgres:password@localhost:5432/cs313db";
}
$dbopts = parse_url($dbUrl);
print "<p>$dbUrl</p>\n\n";
$dbHost = $dbopts["host"];
$dbPort = $dbopts["port"];
$dbUser = $dbopts["user"];
$dbPassword = $dbopts["pass"];
$dbName = ltrim($dbopts["path"],'/');
print "<p>pgsql:host=$dbHost;port=$dbPort;dbname=$dbName</p>\n\n";
try {
$db = new PDO("pgsql:host=$dbHost;port=$dbPort;dbname=$dbName", $dbUser, $dbPassword);
}
catch (PDOException $ex) {
print "<p>error: $ex->getMessage() </p>\n\n";
die();
}
foreach ($db->query('SELECT now()') as $row)
{
print "<p>$row[0]</p>\n\n";
}
?>
</body>
</html>