「pg」パッケージを使ってローカルの PostgreSQL や Heroku Postgres に接続する

Heroku Postgres をいじり始め、ローカルにも PostgreSQL 環境を構築したので、Node.js スクリプトから PostgreSQL DB に接続してみようと思う。

今回は、Heroku の公式リファレンスにも紹介されていた pg という npm パッケージを使ってみる。

目次

素振り環境の用意

まずはお試し作業ディレクトリを作成し、pg パッケージをインストールする。

$ mkdir practice-pg && cd $_
$ npm init -y

# pg パッケージをインストールする
$ npm install --save pg

# サンプルコードを実装するファイルを作成する
$ touch main.js

以降、main.js ファイルにコードを実装していく。

PostgreSQL への接続文字列を確認する

PostgreSQL への接続の仕方は、ホスト・ユーザ名などをそれぞれ指定するパターンと、全てを盛り込んだ一つの「接続文字列」とするパターンがある。今回は管理が楽なので、「接続文字列」を渡して接続してみようと思う。

PostgreSQL の接続文字列の書式は以下のとおり。

postgres://username:password@hostname:port/database

つまり、ユーザ名「postgres」、パスワードが「PASSWORD」なユーザを使い、localhost:5432 の my_local_db というデータベースに接続するには、以下のように記述する。

postgres://postgres:PASSWORD@localhost:5432/my_local_db

Heroku Postgres の場合、Heroku Postgres をインストールすると、ダッシュボード上で確認できる環境変数の一つに DATABASE_URL というモノが追加されており、このような接続先文字列が環境変数に定義されている。本番環境ではこの環境変数を参照することを想定して、ローカル環境でも接続先文字列の書式でアクセスするコードを作っておけば、開発環境・本番環境ともにうまく動作させられるだろう。

ローカルの PostgreSQL にテスト用データベースとテーブルを用意する

DB に接続するコードを書く前に、まずはお試し用のデータベースを新たに作っておき、ダミーデータを入れておこうと思う。先程の例で「my_local_db」というデータベースに接続するような接続文字列を作ったので、my_local_db データベースを作ってみる。

$ psql -U postgres
# パスワードを入力する

# 接続完了。「my_local_db」という名前のデータベースを新規作成する
postgres=# create database my_local_db;
CREATE DATABASE

# 参照するデータベースを切り替える
postgres=# \c my_local_db;
データベース "my_local_db" にユーザ "postgres" として接続しました。

# プロンプトが変わる
my_local_db=#

# テスト用にテーブルを作り、データを入れる
my_local_db=# create table test_table ( id integer primary key, name text );
my_local_db=# insert into test_table ( id, name ) values ( 1, 'ローカルテスト1' );
my_local_db=# insert into test_table ( id, name ) values ( 2, 'ローカルテスト2' );
my_local_db=# select * from test_table;
# データが2件取得できるはず

最後に叩いた SELECT 文と同様のことを、このあと pg パッケージで行う、というワケ。

Heroku Postgres にもテスト用データを入れる

ローカル環境の PostgreSQL と同じことを、Heroku Postgres にもやってみる。Heroku CLI のコマンドを使えばデータベースごと相互に移行したりできるのだが、今回はそうしたバックアップの用途ではないので、サクッとお試しテーブルを作ることにする。

# Heroku Postgres に接続する
$ heroku psql

# データベースはデフォルトのままにしておく
# テストテーブル作ってみる
example-app::DATABASE=> create table test_table ( id integer primary key, name text );
example-app::DATABASE=> insert into test_table ( id, name ) values ( 1, 'Heroku テスト1' );
example-app::DATABASE=> insert into test_table ( id, name ) values ( 2, 'Heroku テスト2' );
example-app::DATABASE=> select * from test_table;
# データが2件取得できるはず

ちょっとだけ登録したデータの文言を変えているので、このあと pg パッケージからデータを取得した時に、どちらに接続しているか一目瞭然になるかと思う。

ローカルの PostgreSQL に接続する Node.js スクリプトを書いてみる

いよいよ pg パッケージを使用して DB に接続する、簡単な Node.js スクリプトを書いてみる。コードは Heroku 公式にあるサンプルコードが参考になるだろう。

自分は Client ではなく Pool を使って、またコールバック関数形式ではなく Promise 形式で書いてみた。main.js のコードは以下のとおり。

const pg = require('pg');

// 接続先文字列
const connectionString = 'postgres://postgres:PASSWORD@localhost:5432/my_local_db';

console.log(`接続開始 : ${connectionString}`);
const pool = new pg.Pool({
  connectionString: connectionString
});

// SELECT してみる
pool.query('SELECT * FROM test_table')
  .then((result) => {
    console.log('Success', result);
    // 結果データの表示
    if(result.rows) {
      result.rows.forEach((row, index) => {
        console.log(index + 1, row);
      });
    }
  })
  .catch((error) => {
    console.log('Failure', error);
  })
  .then(() => {
    console.log('切断');
    pool.end();
  });

あとはコレを $node main.js のように実行する。コンソールに SELECT したデータが表示されたことだろう。

ちなみに、pool.query() 部分を以下のように書けば、PreparedStatement も書ける。

pool.query({
  text: 'UPDATE test_table SET name = $1 WHERE id = $2',
  values: ['テストリネーム2', 2]
})

その他 pg (node-postgres) パッケージの使い方は公式を参照。

Heroku Postgres に接続する Node.js スクリプトに作り変える

さて、このままでは、main.js は localhost の DB に接続を試みてしまうので、このスクリプトを Heroku 上にデプロイしても、Heroku Postgres とは接続してくれない。

そこで、先程も書いたように、Heroku Postgres アドオンをインストールした時に自動設定される DATABASE_URL 環境変数を参照して、接続先文字列を取得するように、スクリプトを書き換えてみる。

といっても簡単で、const connectionString 部分を以下のように変更するだけ。

const connectionString = process.env.DATABASE_URL || 'postgres://postgres:postgres@localhost:5432/my_local_db';

ローカルのコンソール上で、Heroku Postgres に接続するようにしてみたければ、以下のように環境変数を設定してから Node.js スクリプトを実行すれば良い。

# Heroku Postgres の接続先文字列を export コマンドで環境変数として設定する
$ export DATABASE_URL=postgres://hogehoge:fugafuga@ec0-00-00-00-000.compute-1.amazonaws.com:5432/useruser

# 先程のスクリプトを実行する
$ node main.js

ココで、自分の場合は以下のようなエラーメッセージが表示された。

Failure { error: no pg_hba.conf entry for host "153.184.168.17", user "USERNAME", database "MY_POSTGRES_DB", SSL off

どうも Heroku Postgres と SSL 通信ができていないせいみたい。色々調べたが、Heroku 上の環境変数に PGSSLMODE=require もしくは PGSSLMODE=allow というモノを設定しておくと上手く行った。ローカルのターミナルでは同じく export コマンドで設定しておけば動作する。

$ export PGSSLMODE=allow

Heroku の環境変数は Heroku CLI から heroku-config というプラグインを使って設定できるので、以下のように設定してみよう。ブラウザでダッシュボードを開いた時に、ちゃんと環境変数が設定されているはずだ。

# 環境変数を扱うためのプラグインをインストールしておく
$ heroku plugins:install heroku-config

# 環境変数を設定する
$ heroku config:set PGSSLMODE=allow
Setting PGSSLMODE and restarting ⬢ example-app... done, v6

この辺イマイチ分かっていない。

以上

ざっとこんな感じ。このままでは main.js はただの Node.js スクリプトなので、Web アプリからのリクエストに応じて呼び出し、データを返すような実装にしていかないと、アプリっぽくならない。

また、生の SQL をシコシコ書くのは大変なので、実際のアプリ開発では Sequelize などもう少し楽になるライブラリを使って DB 接続した方が良さそうだ。