Cloud-Hosted PostgreSQL: Heroku Postgres

PostgreSQL is a mature, stable, feature-rich database server. Unlike its fellow open-source contenders MySQL and Firebird, PostgreSQL has a steeper learning curve when you’re first getting started, but you can avoid most of the installation, configuration, and maintenance hassles if you use a cloud-hosted database service. Heroku Postgres is a reliable and powerful database-as-a-service based on PostgreSQL provided by Heroku. Hosting your data with Heroku lets you focus more on writing your application’s SQL rather than learning how to be a DBA. In this article I’ll show you how to get started with Heroku Postgres. I’ll share some of the benefits of using Heroku, show you how to sign up, and show you how easy it is to set up a hosted PostgreSQL database instance and connect to it from PHP using PDO.

Why Heroku?

When faced with a serious choice, it’s a good idea to weigh the pros and cons before making a final decision. At the core of almost any web-based application nowadays is its data, and so choosing the right database strategy is probably more important than most people realize. Heroku Postgres may not be right for you, but how do you know? Heroku Postgres is a tiered offering, the lowest of which is priced at $200 a month. If you’re a developer looking just to tinker around and learn something new, this might be a deal-breaker for you. (It would be nice if Heroku could offer some sort of limited free developer trial in the future so more people could learn to work with their offerings.) On the flip side of the coin, $200 is just a drop in a bucket for a successful web-based application. It’s definitely cheaper than running your own hardware and the personnel expense of maintaining the database and ensuring compliance with any relevant regulations. It’s incredibly easy to create a database instance and you’re free to upgrade or downgrade your tier as your needs dictate. In fact, the ease of database creation with Heroku Postgres alleviates one of the oft-heard complaints about PostgreSQL — that it’s difficult to install. Heroku claims to be the largest PostgreSQL database-as-a-service provider. Given this scale, it makes sense to let the experts manage your database. Heroku promises 99.99% uptime, and has done some impressive things to achieve that. Transactions are continually archived to a geographically distributed data store, and automated health-checks are performed every 30 seconds to ensure your database is available. You type one command, and Heroku literally takes care of the rest. Heroku allows almost unlimited data, so large datasets and data warehousing is no problem. Their plans are priced according to hot data set, the portion of your data that is accessed the most and stays resident in RAM for performance reasons. The lowest tier supports 1.7GB of hot data and the highest supports 68GB.

Setting Up a Database Instance

Signing up for the Heroku Postgres service is easy. Just go to postgres.heroku.com in your browser and click the Sign Up Now button in the middle of the page. If you already have an existing Heroku account you can simply provide the email address and password you’ve used for it and you will be logged in. Otherwise, you can create a new account now.

Of course, you’ll need to provide your credit card details to pay for the Heroku Postgres service. If you’ve just created a new account, you’ll be taken to the Verify Account page where you can provide the necessary details.

Once you’ve logged in and verified, and if you’re not looking at it already, go to the Your Databases page accessible from the Your DBs entry in the top menu. This page is the dashboard from which you can monitor and manage all of your database instances. Click the + pointed to by the arrow to add a database; you’ll be presented with the list of plans from which you can pick. Select your desired plan and then the Add Database button to confirm the selection.

In just a few minutes you’ll have a nice, new, shiny provisioned database instance ready for your data. When it’s ready, click on it to drill down and see its connection information.

Interacting from PHP

Now that you have a cloud-hosted PostgreSQL database running, let’s try interacting with it from PHP. Connect to the database using PostgreSQL’s interactive terminal client. If you are running PostgreSQL locally then you will already have the client installed; otherwise you’ll have to turn to your platform’s package management suite (e.g. on Ubuntu run sudo apt-get install postgresql-client) or go to postgresql.org, where you’ll find available for download pre-built binary packages suitable for FreeBSD, Linux, Mac, Solaris and Windows, as well as other installation options. To connect, call psql with the -h option to specify the server’s hostname, -U to specify the username, and then the database name:

timothy@zarkov:~$ psql -h ec2-184-73-194-179.compute-1.amazonaws.com -U dj1wcxb3x9fy3x5 ul28zxpr39no1rr
Password for user ul28zxpr39no1rr: ********
psql (8.4.9, server 9.0.5)
WARNING: psql version 8.4, server version 9.0.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

dj1wcxb3x9fy3x5=>

Create a table to store some employee information using the following SQL:

CREATE TABLE employees (
    employee_id SERIAL,
    last_name VARCHAR(30),
    first_name VARCHAR(30),
    title VARCHAR(50)
);

employee_id is a SERIAL field and will be the employee table’s primary key. last_name, first_name, and title are VARCHAR fields which will store the individual’s name and job title. Then, insert some records into the table so there will be some data to work with:

INSERT INTO employees (last_name, first_name, title) VALUES
    ('Abreu', 'Mark', 'Project Coordinator'),
    ('Nyman', 'Larry', 'Security Engineer'),
    ('Simmons', 'Iris', 'Software Architect'),
    ('Miller', 'Anthony', 'Marketing Manager'),
    ('Leigh', 'Stephen', 'UI Developer'),
    ('Lee', 'Sonia', 'Business Analyst');

As with the terminal client, most developers will probably be running a version of PHP installed using package management tools. If that’s the case, the PostgreSQL extensions can be enabled in PHP by installing PostgreSQL support (e.g. sudo apt-get install php5-pgsql). There are actually two possible PHP extensions you can use to work with PostgreSQL – the PostgreSQL extension which is essentially a wrapper for the C client library libpq, and PDO using the PDO_PGSQL driver. Both are generally installed together, but here I’ll be using PDO since it offers a consistent API to work with a variety of databases and is generally regarded as the appropriate interface for working with databases in modern, professional code. The connection is established by creating a new PDO object, passing in a DSN (Data Source Name) string at the time of instantiation. The string contains the information displayed in the Connection Settings section for the database.

<?php
$dsn = "pgsql:"
    . "host=ec2-184-73-194-179.compute-1.amazonaws.com;"
    . "dbname=ul28zxpr39no1rr;"
    . "user=dj1wcxb3x9fy3x5;"
    . "port=5432;"
    . "sslmode=require;"
    . "password=p28xwd9pjcrzyzp6mf74m99cze";

$db = new PDO($dsn);

The DSN starts with “pgsql” to specify use of the PDO_PGSQL driver, and then each piece of information is presented as a key=value pair separated from one another by semicolons. Now, with a connection established to the database, you can send queries in the same manner as if you had connected to a local-running instance.

<html>
 <head>
  <title>Employees</title>
 </head>
 <body>
  <table>
   <thead>
    <tr>
     <th>Employee ID</th>
     <th>Last Name</th>
     <th>First Name</th>
     <th>Title</th>
    </tr>
   </thead>
   <tbody>
<?php
$query = "SELECT employee_id, last_name, first_name, title "
    . "FROM employees ORDER BY last_name ASC, first_name ASC";
$result = $db->query($query);
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    echo "<tr>";
    echo "<td>" . $row["employee_id"] . "</td>";
    echo "<td>" . htmlspecialchars($row["last_name"]) . "</td>";
    echo "<td>" . htmlspecialchars($row["first_name"]) . "</td>";
    echo "<td>" . htmlspecialchars($row["title"]) . "</td>";
    echo "</tr>";
}
$result->closeCursor();
?>
   </tbody>
  </table>
 </body>
</html>

Here the code assumes the instantiation of $db has taken place and submits a SELECT query to retrieve the employee information from the remote PostgreSQL database – the employee id, last name, first name, and job title. Bookended by the appropriate HTML markup to render a table, the code iterates through the retrieved records and outputs each field. Finally, the results cursor is closed to free the connection to the server. The output of the script resembles the following:

Summary

More and more developers are realizing that it makes sense to outsource their database hosting to cloud-service provides like Heroku. This lets you focus more on writing your application’s code and a handful of SQL queries rather than learning how to be a DBA. In this article, you saw how easy it is to sign up and get started using Heroku Postgres. The whole process literally takes minutes from start to finish, and you can connect to the database from PHP using PDO like any other PostgreSQL database. If your web application uses PostgreSQL, then you definitely should investigate Heroku’s offerings. You may find they’re exactly right for you. Image via Jozsef Bagota / Shutterstock

Frequently Asked Questions (FAQs) about Heroku PostgreSQL