Heroku – PostgreSQL Addon

I´ve already explained how to use “Heroku” in my Article “Heroku – Gratis SSL für Facebook Apps“, but Heroku offers a lot more with the Add-Ons. For example, “PostgreSQL” for – surprise, surprise – PostgreSQL databases. A wonderful alternative to MySQL, to store User data in a Facebook App (for example). Here´s a mini-tutorial:

The Add-On is installed pretty fast with the following commands:

heroku addons:add heroku-postgresql
heroku pg:info

It should look like this, hopefully:

Heroku PostgreSQL Installation

 

“Conn Info” shows the connection settings, it´s good practice to use PHP Data Objects (PDO) to connect with the database:

$dsn = 'pgsql:'
    . 'host=ec2-23-23-234-187.compute-1.amazonaws.com;'
    . 'dbname=d898kfdlclsokt;'
    . 'user=uhyaawtrnswew;'
    . 'port=5432;'
    . 'sslmode=require;'
    . 'password=9nkdjUcIfDc719xvCIzRqCcMXw';
try
{
	$db = new PDO($dsn);
}
catch(PDOException $pe)
{
	die('Connection error, because: ' .$pe->getMessage());
}

After connection, it´s time to create a table and fill it with data:

$query = 'CREATE TABLE mytable (
    id SERIAL,
    facebookid BIGSERIAL,
    mytext TEXT,
    inserted TIMESTAMP
);';
$db->query($query);

$query = 'INSERT INTO mytable (facebookid,mytext,inserted)'
    . ' VALUES (1603196280,"test",now());';
$db->query($query);
var_dump($db->errorInfo());

At last, a simple SELECT – and don´t forget do close the connection:

$query = 'SELECT * FROM mytable;';
$result = $db->query($query);
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
	var_dump($row);
}
$result->closeCursor();

…and that is how you remove the Add-On:

heroku addons:remove heroku-postgresql

6 thoughts on “Heroku – PostgreSQL Addon

  1. nice blog, update again sir 🙂

    I use command “heroku addons:remove HEROKU_POSTGRESQL_ASASAS to remove add ons..

    btw how to remove table and see the name of the current table, sir?

    • you can easily create another php file for deleting a table (DROP TABLE), and there is no “current table”. you don´t connect to a table, you connect to a database and have access to all tables then.

  2. maximilian says:

    why I can’t enter my database when I use command “heroku pg:psql HEROKU_POSTGRESQL_GRAY such is example.
    Out message like this “the lcal psql command could not be located”..??

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.