Connecting to Heroku Postgres | Heroku Dev Center (original) (raw)

English — 日本語に切り替える

Last updated June 11, 2024

Table of Contents

Heroku Postgres is accessible from many different programming languages and frameworks.

Heroku Postgres & SSL

Heroku Postgres requires SSL connections using TLS v1.2 or higher.

Most clients will connect over SSL by default, but on occasion it’s necessary to set the sslmode=require parameter on a Postgres connection. Add this parameter in code rather than editing the config var directly. We recommend you enforce SSL, especially if you’re using Java or Node.js clients.

Connection Permissions

Heroku Postgres users are granted all non-superuser permissions on their database. These include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE and limited ALTER and GRANT permissions.

Heroku runs the following SQL to create a user and database for you.

CREATE ROLE user_name;
ALTER ROLE user_name WITH LOGIN PASSWORD 'password' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE DATABASE database_name OWNER user_name;
REVOKE ALL ON DATABASE database_name FROM PUBLIC;
GRANT CONNECT ON DATABASE database_name TO user_name;
GRANT ALL ON DATABASE database_name TO user_name;

Multiple Schemas

Heroku Postgres supports multiple schemas and doesn’t place any limits on the number of schemas you can create.

The most common use case for using multiple schemas in a database is building a software-as-a-service application wherein each customer has their own schema. While this technique seems compelling, we strongly recommend against it as it has caused numerous cases of operational problems. For instance, even a moderate number of schemas (> 50) can severely impact the performance of Heroku’s database snapshots tool, PG Backups.

External Connections (Ingress)

In addition to being available to the Heroku runtime, Heroku Postgres databases can be accessed directly by clients running on your local computer or elsewhere.

All connections require SSL: sslmode=require.

You can retrieve the PG connection string in one of two ways. You can run theheroku pg🪪url command:

$ heroku pg🪪url DATABASE
Connection info string:
   "dbname=dee932clc3mg8h host=ec2-123-73-145-214.compute-1.amazonaws.com port=6212 user=user3121 password=98kd8a9 sslmode=require"

Also, the connection string is exposed as a config var for your app:

$ heroku config | grep HEROKU_POSTGRESQL
HEROKU_POSTGRESQL_YELLOW_URL: postgres://user3123:passkja83kd8@ec2-117-21-174-214.compute-1.amazonaws.com:6212/db982398

If you get the error The authentication type 10 is not supported. or SCRAM authentication is not supported by this driver. while connecting to your Essential database, see this Help article.

Connecting in Java

There are a variety of ways to create a connection to a Heroku Postgres database, depending on the Java framework in use. In most cases, the environment variable JDBC_DATABASE_URL can be used directly as described in the article Connecting to Relational Databases on Heroku with Java. For example:

private static Connection getConnection() throws URISyntaxException, SQLException {
    String dbUrl = System.getenv("JDBC_DATABASE_URL");
    return DriverManager.getConnection(dbUrl);
}

When it isn’t possible to use the JDBC URL (usually because custom buildpack is being used), you must use the DATABASE_URL environment URL to determine connection information. Some examples are provided below.

By default, Heroku attempts to enable SSL for the PostgreSQL JDBC driver by setting the property sslmode=require globally. If you’re building the JDBC URL yourself (such as by parsing the DATABASE_URL), we recommend explicitly adding this parameter.

It’s also important that you use a version of the Postgres JDBC driver version 9.2 or greater. For example, in Maven add the following to your pom.xml:

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.2.1</version>
</dependency>

JDBC

Create a JDBC connection to Heroku Postgres by parsing the DATABASE_URL environment variable.

private static Connection getConnection() throws URISyntaxException, SQLException {
    URI dbUri = new URI(System.getenv("DATABASE_URL"));

    String username = dbUri.getUserInfo().split(":")[0];
    String password = dbUri.getUserInfo().split(":")[1];
    String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':' + dbUri.getPort() + dbUri.getPath() + "?sslmode=require";

    return DriverManager.getConnection(dbUrl, username, password);
}

Spring/XML

The following Spring XML configuration snippet creates a BasicDataSource from the DATABASE_URL. It can be used with Hibernate, JPA, etc:

<bean class="java.net.URI" id="dbUrl">
    <constructor-arg value="#{systemEnvironment['DATABASE_URL']}"/>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="url" value="#{ 'jdbc:postgresql://' + @dbUrl.getHost() + ':' + @dbUrl.getPort() + @dbUrl.getPath() + '?sslmode=require' }"/>
    <property name="username" value="#{ @dbUrl.getUserInfo().split(':')[0] }"/>
    <property name="password" value="#{ @dbUrl.getUserInfo().split(':')[1] }"/>
</bean>

Spring/Java

Alternatively you can use Java for configuration of the BasicDataSource in Spring:

@Configuration
public class MainConfig {

    @Bean
    public BasicDataSource dataSource() throws URISyntaxException {
        URI dbUri = new URI(System.getenv("DATABASE_URL"));

        String username = dbUri.getUserInfo().split(":")[0];
        String password = dbUri.getUserInfo().split(":")[1];
        String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':' + dbUri.getPort() + dbUri.getPath() + "?sslmode=require";

        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(dbUrl);
        basicDataSource.setUsername(username);
        basicDataSource.setPassword(password);

        return basicDataSource;
    }
}

The DATABASE_URL for the Heroku Postgres add-on follows this naming convention:

postgres://<username>:<password>@<host>/<dbname>

However the Postgres JDBC driver uses the following convention:

jdbc:postgresql://<host>:<port>/<dbname>?sslmode=require&user=<username>&password=<password>

Notice the additional ql at the end of jdbc:postgresql? Due to this difference, you must hardcode the scheme to jdbc:postgresql in your Java class or your Spring XML configuration.

Remote Connections

You can connect to your Heroku Postgres database remotely for maintenance and debugging purposes. However, doing so requires that you use an SSL connection. Your JDBC connection URL must include the following URL parameter:

sslmode=require

If you leave off sslmode=require and attempt to connect to a production-tier databases, you will receive a connection error..

You must add this parameter in code rather than editing the config var directly. Automated events such as failover can change the config var, overwiring your edits.

Click here for more information see the Dev Center article on Connecting to Relational Databases on Heroku with Java.

Connecting in Ruby

To use PostgreSQL as your database in Ruby applications you must include the pg gem in your Gemfile.

gem 'pg'

Run bundle install to download and resolve all dependencies.

If you’re using the pg gem to connect to your Postgres database from a Heroku dyno, and haven’t specified an sslmode in your configuration or code, the gem defaults to sslmode: prefer. If you use sslmode: prefer, your connections can work if SSL use is enforced on your Postgres database.

Connecting in Rails

When Rails applications are deployed to Heroku a database.yml file is automatically generated for your application. That configures ActiveRecord to use a PostgreSQL connection and to connect to the database specified by DATABASE_URL. This behavior is only needed up to Rails 4.1. Any later version contains direct support for specifying a connection URL and configuration in the database.yml so we don’t have to overwrite it.

To use PostgreSQL locally with a Rails app your database.yml must contain the following configuration:

development:
  adapter: postgresql
  host: localhost
  username: user
  database: app-dev

Connecting in JRuby

To use PostgreSQL as your database in JRuby applications you must include the activerecord-jdbcpostgresql-adapter gem in your Gemfile.

gem 'activerecord-jdbcpostgresql-adapter'

Run bundle install to download and resolve all dependencies.

If using Rails, follow the instructions for Connecting with Rails.

Connecting in Python

To use PostgreSQL as your database in Python applications you must use the psycopg2 package.

$ pip install psycopg2-binary

And use this package to connect to DATABASE_URL in your code.

import os
import psycopg2

DATABASE_URL = os.environ['DATABASE_URL']

conn = psycopg2.connect(DATABASE_URL, sslmode='require')

Connecting with Django

Install the dj-database-url package using pip.

$ pip install dj-database-url

Be sure to add psycopg2-binary and dj-database-url to your requirements.txt file as well.

To parse the values of the DATABASE_URL environment variable and convert them to something Django can understand, add the following to the bottom of settings.py:

import dj_database_url
DATABASES['default'] = dj_database_url.config(conn_max_age=600, ssl_require=True)

Connecting in Go

Go apps can connect to Heroku-Postgres by providing thepqPostgres database driver to their query interface of choice (such as the standard database/sql). Your app will use the query interface, rather than using the driver directly.

Standard Usage (database/sql)

$ cd <app>
$ dep ensure -add github.com/lib/pq

import (
  "database/sql"
  _ "github.com/lib/pq"
)
...
func main() {
  db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))
  if err != nil {
    log.Fatal(err)
  }
  ...
}

SSL is required to connect to Heroku-Postgres. pq automatically sets sslmode=require, but if you use another library, you must configure ssl explicitly.

Beyond the Standard Lib

For lower-level access to Postgres, you can usepgx.

For time-saving extensions to database/sql, you can usesqlx.

Connecting in PHP

General Considerations

If a framework or library cannot natively handle database connection strings formatted as URLs, use the parse_url() function to parse the DATABASE_URL environment variables into individual values for user, pass, host, port, and database name.

The leading slash must be trimmed from the path component, since it represents the database name.

$db = parse_url(getenv("DATABASE_URL")); <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>d</mi><mi>b</mi><mo stretchy="false">[</mo><mi mathvariant="normal">&quot;</mi><mi>p</mi><mi>a</mi><mi>t</mi><mi>h</mi><mi mathvariant="normal">&quot;</mi><mo stretchy="false">]</mo><mo>=</mo><mi>l</mi><mi>t</mi><mi>r</mi><mi>i</mi><mi>m</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">db[&quot;path&quot;] = ltrim(</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal">d</span><span class="mord mathnormal">b</span><span class="mopen">[</span><span class="mord">&quot;</span><span class="mord mathnormal">p</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord mathnormal">h</span><span class="mord">&quot;</span><span class="mclose">]</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal">lt</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">im</span><span class="mopen">(</span></span></span></span>db["path"], "/");

The resulting associative array in $db contains the information from the URL, as documented, with the database name available through the “path” key.

Connecting with PDO

A DSN must be constructed to connect using PDO:

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

$pdo = new PDO("pgsql:" . sprintf(
    "host=%s;port=%s;user=%s;password=%s;dbname=%s",
    $db["host"],
    $db["port"],
    $db["user"],
    $db["pass"],
    ltrim($db["path"], "/")
));

Connecting with the pgsql Extension

The pgsql extension passes connection strings directly to the underlying libpq library, which supports URL-style connection strings, so the DATABASE_URL environment variable can be used directly:

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

Connecting with the pq Extension

The pq extension passes connection strings directly to the underlying libpq library, which supports URL-style connection strings, so the DATABASE_URL environment variable can be used directly:

$conn = new pq\Connection(getenv("DATABASE_URL"));

Connecting with Laravel

The config/database.php file returns an array of database connection info to the framework; it can simply be amended to call parse_url() on the DATABASE_URL environment variable first, and return the extracted data:

$DATABASE_URL = parse_url(getenv("DATABASE_URL"));

return [

    // …

    'connections' => [

        // …

        'pgsql' => [
            'driver' => 'pgsql',
            'host' => $DATABASE_URL["host"],
            'port' => $DATABASE_URL["port"],
            'database' => ltrim($DATABASE_URL["path"], "/"),
            'username' => $DATABASE_URL["user"],
            'password' => $DATABASE_URL["pass"],
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
            'sslmode' => 'require',
        ],

        // …

    ],

    // …

];

Connecting with Symfony 3

The DATABASE_URL environment variable can be referenced in config.yml; Symfony’s DoctrineBundle automatically parses the URL’s contents.

Connecting with Symfony 4

Symfony 4 automatically picks up the DATABASE_URL environment variable without further configuration.

Connecting in Node.js

Install the pg NPM module as a dependency:

$ npm install pg

Then, connect to process.env.DATABASE_URL:

const { Client } = require('pg');

const client = new Client({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
});

client.connect();

client.query('SELECT table_schema,table_name FROM information_schema.tables;', (err, res) => {
  if (err) throw err;
  for (let row of res.rows) {
    console.log(JSON.stringify(row));
  }
  client.end();
});

Alternatively, you can omit the ssl configuration object if you specify the PGSSLMODE config var: heroku config:set PGSSLMODE=no-verify.